This tutorial will talk you through the following:
Using the Excel Crosswalks
Creating named ranges
Let's get started
Save the Exhibit Generation Crosswalk tutorial to your local hard drive. In our case we have used “E:\” disk.
From Wikipedia http://en.wikipedia.org/wiki/Crosswalk_(disambiguation):
“Crosswalk is the allocation table of budget authority to outlay amounts in a budget. The crosswalk used to control the allocation of funds. The analogy is that the funds get transferred (walk) from one set of balance sheets or budget to another“
Let’s look at the example crosswalk file “FundsCrosswalk - Original.xslx” located in the “E:\Crosswalk\Data”. This crosswalk spreads city reserve funds across multiple fiscal years base on fund and reserve class.
Note: If do not do not have Microsoft Excel installed you can use The Microsoft Excel Viewer to view Excel workbook files.
“FundsCrosswalk - Original.xslx” file is just a simple excel file to spread budget amounts, what we will do in the next few steps is prepare this crosswalk for the Budget In Brief application.
To tell the Budget In Brief application what data should be pulled out of this excel file, the file should have named ranges. The named range is the range of cells that have a name. The range could consist of something as little as one cell, it could include a column, or a row, or a set of cells.
In our example, the PYBalance named range includes cells from D1 to D37 in the D column.
The original document from our example does not have any named ranges.
Now, let’s open "FundsCroswalk.xslx" file from "E:\Crosswalk\Data" folder where we have created "named ranges".
In our example, we used the following named ranges:
Fund – the range of cells that identify the fund; in the example we have General Fund and Water Fund, but you can add more funds if you need them;
ReserveClass – the range of cells that identify the class of the reserve. In the example we have Statutory and Non-Statutory Reserves; you can extend classification in the real life scenario;
Reserve – the range of cells that identify the reserves; you can extend list of reserves by simply inserting new rows, it is that easy;
PYBalance – the range of cells that identify the Prior Fiscal Year Balance; for this example we assumed that this is an already known value;
CYBalance – the range of cells that identify the Current Fiscal Year Balance; this name range uses formula to calculate value. It takes the prior year balance adds additions and subtracts withdrawals;
BYBalance, BBYBalance, BBBYBalance, and BBBBYBalance – respectively, the four ranges of cells that identify the related Budget Year Balance;
CYAdditions – the range of cells that identify the Additions for the Current Fiscal Year;
BYAdditions, BBYAdditions, BBBYAdditions, and BBBBYAdditions - respectively, the four ranges of cells that identify the related Additions for the related Budget Year;
CYWithdrawals - the range of cells that identify the Withdrawals for the Current Fiscal Year;
BYWithdrawals, BBYWithdrawals, BBBYWithdrawals, and BBBBYWithdrawals - respectively, the four ranges of cells that identify the related Withdrawals for the related Budget Year.
This is all you need to do to prepare crosswalk for the Budget In Brief application. In many cases that is what you've already been doing all along.
The Budget In Brief application supports excel files, with multiple worksheets and long list of supported by Excel functions.
The Budget In Brief application does not requires Microsoft Excel, and yet will extract the all data from the excel files, even on computers that do not have Microsoft Excel installed.