There are two separate budgeting systems included with your Highway Management Software. Typically Road Commissions use Budget Calc, and Townships/Cities/Villages typically use the Budget Data system, but both are available to all entities.
The first is a system for creating your own custom reports that can pull data from any or all of your financial transactions and format it with various totals and calculations. (Typically used by Road Commissions).
The second allows you to enter budget amounts and/or adjustment to budget amount using GL Post (sources of entry BGT and BGA) into the financial system and includes a set of reports to show the results. (Only those reports already programmed are available for use.) (Typically used by Townships).
BUDGET CALC
The Budget Calc program allows to configure – within the constraints of the Budget Calc program – a report customized to your needs.
The Budget Calc program is found under the General Ledger menu in Budget Calc
The basic capability of the Budget Calc program is to set up financial reports as you would like to see them.
Examples of reports that can be created will be shown next, and then we will show you how to create a report from scratch.
SAMPLE 1: PRIMARY & LOCAL MAINTENANCE – BUDGET TO ACTUAL
SAMPLE #2: 515 ACCOUNT – BUDGET TO ACTUAL
SAMPLE #3 – MAINTENANCE BY TOWNSHIP
The basic capability of the Budget Calc program is to set up spreadsheets, similar to how you would in Microsoft Excel, however the greatest advantage and power of Budget Calc is that it will automatically pull all the necessary data (basically a query) from your financial records in Pro Fund Accounting. No more hand entering numbers into an Excel spreadsheets! Plus once you set up your reports, all you have to do in future months/years is run a report and the numbers automatically download!
Since Budget Calc is made up of columns and rows and defined by how you set up each column and row, it is a good idea to know ahead of time how you would like your spreadsheet to be laid out.
Let’s start setting up your spreadsheet! We’re going to set up a spreadsheet that looks like example #2.
1. Click on General Ledger, then Budget Calc.
2. Click on EDIT, then EDIT REPORT TITLES.
3. On this screen you can give your report two titles, with the first one being the main title. Also if you would like every other line shaded (for easier reading across the rows/columns), then click on PRINT REPORT WITH ALTERNATING LINE BARS. (This will make it permanent for the grayed lines, so you don’t have to ask for it every time you print the report.) Click OK. (Once you click OK, you will see the words ADMINISTRATION (A515 ACCOUNTS) at the top of the Budget Calc form.)
ADDING COLUMNS
1. (Make sure before you start that you have an idea how this spreadsheet should look.) Click EDIT, then ADD COLUMN. NOTE: The first column (which you don’t add), is automatically your description column. Keep this in mind when adding columns. (You can always delete or add one later, so don’t worry!)
We will go over each item on the following screen and instruct you how to fill them out.
2. TITLES: First give a title to the column you are adding. (NOTE: While you are setting up column titles and calculations, you will get some messages saying that there are no lines defined. That is simply a reminder that at some point you will need to define the lines of the report. For now, don’t worry about them. We will explain how to set those up after we explain how to set up columns.)
3. DATA TO ACCUMULATE: Enter the type of Data to Accumulate. Typically Financial is used and it defaults to that. (Budgetary is used for SOE’s BGT & BGA, and Quantitative is used for items such as labor hours, equipment hours, inventory quantities, etc.)
4. PRINT THIS COL: Make sure PRINT THIS COL is checked if you wish the column to print when you print the report. If this is just a holding column to help calculate another column, then uncheck PRINT THIS COL.
5. REVERSE SIGN: Revenue accounts contain credit data which is specified with negative numbers. These negative numbers can be confusing to the non-accounting type person. By checking this item, the signs are reversed.
6. APPLY PCT1/2/3: Three percentages (%) can be applied to each figure in a column. If you add these to your figures, please be advised that you will no longer see raw figures, but instead the raw figures along with all your benefits/admin charges added to them. Pay special attention to this if you are configuring a spreadsheet that already has benefit numbers in it, for example, 513/A513 account numbers which already contain benefits in them. An example where you may want to use them is if you are running a spreadsheet that shows all of a projects costs and the engineering department wants to
see the total cost that you would bill a Township, which would include benefits and overhead(admin).
7. INCLUDE BY ACCOUNT TYPES: Normally all data is accumulated as defined by the line specifications. This list of Account Types gives you the ability to include, say, only Revenues in the column. Accounts types are R for revenue, X for expense, A for assets, L for liabilities, and E or F (for equity or fund balance, respectively). If you want to have both revenue and expense displayed, enter R,X (separate the R and the X by a comma without any spaces) in this box.
8. INCLUDE BY SOEs: Normally all SOEs (sources of entry) are included but if you wanted just Inventory data you would enter INV in this box. Multiple SOEs should be separated by commas without any spaces (i.e. INV,PR).
9. EXCLUDE BY SOEs: Normally no data is excluded. To exclude a type of data based upon its source of entry, the SOE should be entered here. Again, multiple SOEs to exclude should be separated by commas without any spaces (i.e. PR,EQU).
10. ADDTIONAL SQL QUERY ‘WHERE’ STRING: This is for advanced users
who understand SQL. One usage could be to retrieve only overtime hours for employees by entering HRSTYPE = ‘OT’. A knowledge of SQL and the HMS/MMS database as well as the user’s configurations are all required to make valid entries to this box.
11. SET FORMULA: The Set Formula button allows you to enter very simple calculations between columns. You can add/subtract/multiply/divide between columns.
Let’s look at our example #2 from above where we have the Original Budget, Actual dollars spent, Variance and Percent.
To set up the VARIANCE column,
A. You would want to list the FIRST OPERAND as the first column you want a data from, which in this case is the BUDGET column.
B. Next you want to tell the VARIANCE column what type of operation you want, which in this case is SUBTRACT.
C. Lastly, you want to choose the SECOND OPERAND which is the second column you want data from, which in this case is the ACTUAL column.
D. Select OK.
To set up the PERCENTAGE column: (This will give you the percent of the budget that you have spent to date.)
A. You would want to list the FIRST OPERAND as the first column you want a data from, which in this case is the ACTUAL column.
B. Next you want to tell the PERCENTAGE column what type of operation you want, which in this case is PERCENTAGE.
C. Lastly, you want to choose the SECOND OPERAND which is the second column you want data from, which in this case is the ORIGINAL BUDGET column.
D. Select OK.
12. SET DATE RANGE: This is where you will instruct the columns what time period you want to use for the information. For example: Month to Date, Year to Date, Fiscal Year to Date, etc.
For the ACTUAL column, you would want the information to run through your FISCAL YEAR TO DATE, so click on that option in the Budget Date Range Editor, then click OK.
From our example #2 above that shows the months of January, February, etc. you would want to instruct the column to choose the appropriate month.
To choose the MONTH TO DATE for January, you would choose the MONTH TO DATE option. NOTE: You will need to state the name of the month on the SECOND column title so the computer knows which month to look for the information.
13.
SELECT OK.
TO EDIT AN EXISTING COLUMN
1. Click on the column you want to change, near the title area. Then right click and click EDIT. Then proceed as needed.
TO DELETE A COLUMN
1. You can only delete a column when it is moved to the far right side (or end) of the columns.
2. To Drag a column, click on the column you want to delete and HOLD the mouse button. Drag the column to the far right side (or end) of the columns. Once you release the mouse, the column will then stay where you put it.
3. RIGHT CLICK on the column and choose DELETE. It will give you a confirmation of the column you want to delete before you choose OK.
TO INSERT A COLUMN
1. Right click on a column and click INSERT.
2. Your normal screen to choose all the options will appear. Fill out these as normal, then click OK.
3. NOTE: The column will show up at the very right side of the spreadsheet. You can then drag it to the place you want it in your spreadsheet.
TO MOVE A COLUMN
1. Left click and hold, move the mouse to the desired insert point, and release the mouse button.
To set up the VARIANCE column,
A. You would want to list the FIRST OPERAND as the first column you want a data from, which in this case is the BUDGET column.
B. Next you want to tell the VARIANCE column what type of operation you want, which in this case is SUBTRACT.
C. Lastly, you want to choose the SECOND OPERAND which is the second column you want data from, which in this case is the ACTUAL column.
D. Select OK.
To set up the PERCENTAGE column: (This will give you the percent of the budget that you have spent to date.)
A. You would want to list the FIRST OPERAND as the first column you want a data from, which in this case is the ACTUAL column.
B. Next you want to tell the PERCENTAGE column what type of operation you want, which in this case is PERCENTAGE.
C. Lastly, you want to choose the SECOND OPERAND which is the second column you want data from, which in this case is the ORIGINAL BUDGET column.
D. Select OK.
12. SET DATE RANGE: This is where you will instruct the columns what time period you want to use for the information. For example: Month to Date, Year to Date, Fiscal Year to Date, etc.
For the ACTUAL column, you would want the information to run through your FISCAL YEAR TO DATE, so click on that option in the Budget Date Range Editor, then click OK.
From our example #2 above that shows the months of January, February, etc. you would want to instruct the column to choose the appropriate month.
To choose the MONTH TO DATE for January, you would choose the MONTH TO DATE option.
NOTE: You will need to state the name of the month on the SECOND column title so the computer knows which month to look for the information.
13. SELECT OK.
TO EDIT AN EXISTING COLUMN
1. Click on the column you want to change, near the title area. Then right click and click EDIT. Then proceed as needed.
TO DELETE A COLUMN
1. You can only delete a column when it is moved to the far right side (or end) of the columns.
2. To Drag a column, click on the column you want to delete and HOLD the mouse button. Drag the column to the far right side (or end) of the columns. Once you release the mouse, the column will then stay where you put it.
3. RIGHT CLICK on the column and choose DELETE. It will give you a confirmation of the column you want to delete before you choose OK.
TO INSERT A COLUMN
1. Right click on a column and click INSERT.
2. Your normal screen to choose all the options will appear. Fill out these as normal, then click OK.
3. NOTE: The column will show up at the very right side of the spreadsheet. You can then drag it to the place you want it in your spreadsheet.
TO MOVE A COLUMN
1. Left click and hold, move the mouse to the desired insert point, and release the mouse button.
ADDING ROWS
Note: Adding lines can only be done after specifying at least once column.
1. (Make sure before you start that you have an idea how this spreadsheet should look.) Click EDIT, then ADD LINE.
We will now go through each line of the Line Editor below:
2. LINE DESCRIPTION: Enter the name you wish for the line you are entering. For our example we’ll use: A515 703.01 – COMM. SALARY
3. PRINT OPTIONS:
PRINT THE LINE:
This line will print on the report and will be visible in the grid.
TITLE ONLY:
This is a title that you want to use for a specific line, perhaps highlighting different sections (for example: Revenue, Expenses), but there will be NO data calculations on that line.
SUBTOTAL NO PRINT:
This is a line for subtotals. It will appear on your grid, but when you go to print, it will not print.
DON’T PRINT – ACCUMULATE:
There are times when it is necessary to have complex data from several lines accumulate, but only need to print once. To handle this, check this item on all BUT THE LAST line of a range of lines. On the last line of the range, you would check the “Print the Line” check box.
SUBTOTAL:
Some lines get their columnar data by performing a subtotaling function. If Subtotal is checked, a subtotal number is required. Subtotals are comprised of 19 sets of ‘accumulators’. In other words, you can create up to 9 subtotals. The mechanics of Subtotals is described below (For more information on SUBTOTALS, see the end of this Help Section under OTHER).
4. SUBTOTAL NUMBER 1-9: Enter the subtotal accumulator number (1-9) that this line is for.
5. REVERSE AMOUNTS: As described when setting up the Columns, if you want to show revenue data as positive amounts (e.g., without the minus signs or parenthesis), select this option.
6. SPACING OPTIONS:
SINGLE: Select this item if you want this line to print right after the previous line (in other words, no blank line in between).
DOUBLE: Select this item if you want a blank line between this line and the previous.
TOP OF FORM: Select this item if you want to print this line as the first line on a new page.
(Note: Your titles will print out first before the line is printed on a new page.)
7. Select OK, once you’ve made all your decisions above, or CANCEL if you don’t want to make any changes.
TO EDIT AN EXISTING ROW
1. Click on the row want to change, on the very left side. Then right click and click EDIT. Then proceed as needed.
TO DELETE A ROW
1. You can only delete a row when it is moved to the very bottom of the spreadsheet.
2. To Drag a row, click on the row you want to delete and HOLD the mouse button. Drag the row to the very bottom of the spreadsheets. Once you release the mouse, the row will then stay where you put it.
3. RIGHT CLICK on the row and choose DELETE. It will give you a confirmation of the row you want to delete before you choose OK.
TO INSERT A ROW
1. Right click on a row and click INSERT.
2. Your normal screen to choose all the options will appear. Fill out these as normal, then click OK.
3. NOTE: The row will show up at the very bottom of the spreadsheet. You can then drag it to the place you want it in your spreadsheet.
TO MOVE A ROW
1.
Left click and hold, move the mouse to the desired insert point, and release the mouse button.
CELLS
Now we need to specify where the data will come from for each cell in our example.
1. Let’s start with the cell under the ORIGINAL BUDGET, but next to A515 703.01 – COMM. SALARY. (12000 in our example). Since this is a set number, not a calculation, you’ll need to enter this number. In Excel, you would simply enter the “12000” in the cell, however in Budget Calc, since it is a database/query system, you’ll need to enter the number in a different way.
A. First click on the cell we mentioned above (the one containing 12000). It should highlight to a color.
B. Next, Right-Click on that same cell, then click EDIT.
C. The following screen then comes up.
D. Next, click on the button next to FIXED AMOUNT.
E. Enter the number you want here….in our example 12000.
F. Select OK.
2. Now let’s proceed to the cell under the ACTUAL column, but still in the A515 703.01 – COMM. SALARY row. (In the example above, the number was 123. Since this number will be a calculation of the actual dollars spent to date for this line item, we need to let the computer know which line item to look for.
A. First click on the cell we mentioned above (the one containing 123). It should highlight to a color.
B. Next, Right-Click on that same cell, then click EDIT.
C. The following screen then comes up.
A. First, select ACCOUNT RANGE. This let’s the computer know that we are going to select a range of account numbers for it to get data from.
B. Next, enter the FROM (or starting account number range) in the FROM ACCOUNT NUMBER dialog box. Then enter the TO (or ending account number range) in the TO ACCOUNT NUMBER dialog box. When you enter the account number, you’ll need to have seven layers of the account number, with a dash in between each number (and no space….the space shows up after you save it.) In our example you would enter it as 201-0-A515-A515-0-701-01. If your account number wanted to include all the A515’s, you could enter it as 201-0-A515—-. (After the A515, you would enter 4 dashes, because you always have to have seven levels.)
C. Click OK.
D. After you click OK, the computer will come up with the following question:
The computer wants to know whether you want to copy this same account number range for every cell on this row. In our example, we would want to choose YES, since we want the account number range to show up on the ACTUAL, VARIANCE, PERCENTAGE, and each MONTHLY column for that row. An example of choosing NO, would be the following spreadsheet, where you’re combining Primary, Local and YTD Data in the same line, and thus the account numbers would be a little different.
NOTE: If you didn’t put in the dollar amount in the BUDGET column ahead of time, and you say YES to Propagate, then it will copy that formula of account number ranges to your BUDGET column. You would just need to go back into that cell and update the number to a FIXED number and enter it there. It’s always a good idea to enter the FIXED numbers First!
This Propagate function saves you from having to enter account number ranges for every single cell on a particular row. A great timesaver!
3. The last option is NO AMOUNT. If you don’t want any information in a certain cell, you can pick this option.
A. Select OK.
You will need to continue this same process (entering the account number ranges), for each row in your spreadsheet. Once these are all set up, you will be able to run your Budget Calc Reports whenever you need them, usually at month-end. You can save the same spreadsheet for next year, and simply update the BUDGET numbers. No redoing the whole spreadsheet!
When your report is finished, the screen should look similar to the following:
BEFORE YOU PRINT, MAKE SURE TO “RECALCULATE” IT. This will update all of the cells with the latest data.
To RECALCULATE, click on Recalculate at the top of your screen.
Once you click on Recalculate, the following screen will come up:
This is where you will choose whether you want Benefits/Road/Admin percentages ADDED to your actual totals, and also enter your date ranges. In our example, we will want to put in our Fiscal Year Start date. If you want the data to show all your actual data through a certain month, let’s say May, then you will need to enter the ending date as 5-31-15, instead of 12-31-15.
For the BY MONTH date, you will want to start with the first month of your fiscal year. In our case it is 1-1-15. If you were a 9-30 year-end, you would enter 10-1-15 as your BY MONTH date, (and you will also have your MONTH columns in order from October through September.)
To change fiscal years, you can simply use the up and down arrow keys next to CHANGE YEARS.
Then select OK.
SAVING YOUR SPREADSHEET
To save your spreadsheet:
1. Click on FILE.
2. Click on SAVE (or SAVE AS), and save to the appropriate place on your computer. The SAVE AS feature is helpful when you want to bring up last year’s spreadsheet and save it as NEXT year’s spreadsheet.
PRINTING YOUR REPORTS
To print your report, select PRINT at the top of your spreadsheet screen.
The print screen will look something like this:
You can now select the Print Icon and print your report OR you can select the EXPORT icon and export your report to other types of products, such as Excel, PDF, etc.
OTHER ITEMS
Subtotals
Under the Edit menu item in Budget Calc there are three options: Enter Report Titles, Add Column and Add Line. The Add Line option brings up the Budget Line Editor dialog box.
The Budget Line Editor has check boxes that define the types of lines. A subtotal is a type of line and the cells in that line don’t need any data range parameters because the line will be calculated from the preceding data that makes up the subtotal. A subtotal line needs a subtotal number (a number from 1 to 9) and you must understand the structure of subtotals to be able to specify the number so that it gives you the desired results.
There are five different types of lines that can be added to a budget report:
* Print the Line – will print a line of data as defined by the Column and the Line
* Title Only – will print a line containing no data but just the Line Description
* Subtotal No Print – will do all the subtotaling procedures described below, but not print
* Don’t Print – Accumulate – will process the data defined by the Column and the Line but will not print the line while still adding into the Subtotals
* Subtotal – will print a Subtotal and clear all ‘lower’ subtotals (described below)
A Subtotal (or Subtotal No Print) is a type of line and the cells in that line don’t need any data range parameters because the line will be calculated from the preceding data that makes up the subtotal.
There is a possibility of 9 subtotals, identified as subtotal 1 thru subtotal 9. Imagine a report of funds, accounts and sub accounts. There would typically be 3 sets of subtotals:
Subtotal 1 for the totals of several accounts within the funds
Subtotal 2 for the totals of each of the funds
Subtotal 3 would be for grand totals on the report
The subaccounts would show as simple lines with the data summed from the specifications of the Column and the Lines. Note that subtotal 1 is ‘inside’ subtotal 2, which in turn is ‘inside’ subtotal 2. The ‘highest’ subtotal for grand totals is also the highest subtotal number.
A Subtotal is not a single number but the totals of each Column in the report. All data defined by Columns and Lines is added into each of the 9 possible Subtotals. When a subtotal is printed (or not printed in the case of Subtotal No Print), that subtotal and all lower number subtotals are set to 0 so that further data can be accumulated. So when Subtotal 2 is printed, Subtotal 2 and Subtotal 1 are set to 0.
Imagine a very simple report with 9 lines. We only want a subtotals and a grand total line. There are only 2 subtotals, Subtotal 1 will be subtotals in the body of the report and Subtotal 2 will be for the grand total. To place a subtotal on line 5 that encompasses the total of lines 1 through 4, we would specify line 5 as a Subtotal and enter 1 into the subtotal number. If we wanted a further subtotal on line 8 that covered lines 6 and 7 we would identify the line as a Subtotal line and the subtotal number would again be 1. Finally, we would add a line for Subtotal 2 at the end of the report.
As the report runs, the data from the lines are added into all the subtotal accumulators. When a subtotal is printed the accumulated amounts for that subtotal are zeroed. Actually that subtotal number (1 through 9) and all lower numbered subtotals are cleared. So when the subtotal line #5 for subtotal #1 was encountered, its totals were printed and subtotal #1’s and any lower Subtotal accumulators are cleared. As lines 6 and 7 were processed their data was added to the accumulators and when line 8 was processed for subtotal #1 the data accumulated there was for only lines 6 and 7. Since the grand total Subtotal 2 was never cleared, when it is processed and printed it will contain data from the whole report.
To summarize: There are 9 sets of subtotal accumulators. Each line of data adds its contents to each of the 9 sets of accumulators. When a subtotal is printed, that set of subtotal accumulators is cleared as well as all accumulators for lower numbered subtotals. Another way to look at it is that a subtotal accumulates all the preceding data up to a previous subtotal at the same or larger subtotal number.
Budget Data System – typically used by Townships
All data entered into the system is categorized by a Source of Entry (SOE). Many SOEs are predefined, such as PR for payroll and AP for accounts payable. Data from earnings are entered into the system as SOE PR, data from accounts payable entries go in as SOE AP. SOEs are set up and changed from the General Ledger menu under the Source of Entry Maintenance program.
The Budget Data System depends on two SOEs – BGT for budget data and BGA for budget adjustments. This budget data is entered using the General Ledger Post program.
In General Ledger Post, post budget data to SOE BGT or BGA (source of entry for budgets or budget adjustments). Though these are entered into the financial system and maintained there, they are not actual financial data and do not affect any financial balances or reports. Budget data is separate from the usual financial data.
Because it is entered with General Ledger Post, budgetary data must be categorized by your standard financial structure. This allows budget entries to be intermixed with the actual financial data while at the same time keeping financial and budgetary data separate.
After the budget figures are accepted, they are entered into the system as SOE BGT and dated with the first day of your fiscal year. Entries can be made to any account that exists in your account structure. As the year progresses and changes or adjustments are needed, these are entered as SOE BGA for budget adjustments. BGT and BGA journal types do not need to balance.
After these entries are done the financial system will then contain both “actual” data (real payroll or accounts payable entries for example) and “budgetary” data (from the BGT and BGA entries).
There are several reports that can take this mix of budgetary and actual data and use it with various options. These are found in the Reports menu. A typical budget report will show a list of accounts that have budgetary entries (BGT or BGA) and compare the budget amounts to the actual amounts. Columns for this type of report vary but typically contain: Year Budget, Year Adjustments to Budget, Total Budget, Total Actual, Budget minus Actual and a variance percentage.
Since all budget data is entered with full account numbers there are totals for control numbers and/or funds for each column on the report as well as grand totals.
Because budgetary data is kept separate, you are encouraged to make trial entries to BGT and BGA and run the reports to see the results to learn how the system works. Trial BGT and BGA data can be deleted at any time without affecting the balances of real financial data.
Note that the reports in the Budget Data System are predefined by us. You may only run the reports that we have provided. Although these reports have many parameters to help customize the data being reported, you cannot define the reports themselves. Customized reporting is the job of the Budget Calc program.