Budget Basics using a Spreadsheet
Compiling a budget, if it is to be a worthwhile, is a very complex process requiring considerable research, the involvement of many people and very many calculations.
Spreadsheets, such as Microsoft Excel, are an invaluable tool for the construction of budgets because in the hands of the experienced operator they allow:
- Modifications to any budget figures yield instant results (i.e. calculations are performed instantly)
- Information to be laid out, formatted and rearranged for maximum readability with great ease
Calculations to be stored and viewed for future reference and amended if necessary.
Advanced spreadsheets such as MS Excel
allow the user to create as many "worksheets" as
required. Figure 1 portrays a sheet for "Rent",
a sheet for "Salaries" and one for "Office
Expenses". There is also a summary sheet.
Figure 1
The first important technique in using a spreadsheet is to store information about various components of the budget on separate sheets. If there is information and calculations to be stored about Travel expenses then a new worksheet called "Travel" should be created. Similarly information about promotion expenses would not be stored in Office Expenses but would deserve a worksheet of its own. With MS Excel it is possible to create as many worksheets as is needed. In reality, for a recreation organisation, about 30 worksheets will be sufficient to create a budget.
The second important technique is to link each worksheet with the summary sheet so that any changes to any of the worksheets will be immediately reflected in the summary sheet. If this does not happen there is potential for errors. The summary sheet is in fact the Income and Expenditure Budget - the document that is finally published. It would be embarrassing to publish a budget that when questioned did not relate to stored information and calculations.
| A | B | C | D | E | F | G | |
| 1 | Weeks | Hrs/wk | Rate/hr | Salary | Super | Total | |
| 2 | 9% | ||||||
| 3 | |||||||
| 4 | Executive Director > | $35,000.00 | $3150.00 | $38,150.00 | |||
| 5 | Activities Manager | $30,000.00 | $2700.00 | $32,700.00 | |||
| 6 | Admin Assistant | 52 | 20 | $13.00 | $13,520.00 | $1216.80 | $14,736.80 |
| 7 | Activities Assistant | 52 | 38 | $15.00 | $29,640.00 | $2667.60 | $14,736.80 |
| 8 | Total | $108,160.00 | $9734.40 | $11,7894.40 | |||
| 9 |
Figure 2
Before discussing the technique of linking worksheets it might be prudent to give an example of the information and calculations that are store on worksheets.
In figure 2, the worksheet for salaries, the information stored is the job titles, their respective salaries or wage rates. The calculations include working out the Superannuation Guarantee Levy for all employees, the annual cost of employment for each employee and the total cost of all employees. The total of $115,731.20 is the amount to be transferred (and linked) to the summary sheet.
The summary worksheet should be linked to each and every worksheet for individual items appearing in the summary. You should pay particular attention to not “embedding” figures in any worksheet. For example if you need to find out the total of membership fees by multiplying 200 members by $20 fees each:
· You should not enter the formula in a cell =200 * 20
But instead use relative references of cells containing figures you wish to multiply. "A2" is an example of a relative reference.
|
||||||||||||||||||
Linking figures on the summary sheet to their respective worksheets is a simple process. On the summary sheet in the cell where the total of salaries is to appear, type an equal sign i.e. type "=". All formulas must begin with = . Then, when you have typed the "=" sign, point with your mouse to worksheet containing the answer and the cell where the answer can be found. In figure 2, the answer for salaries can be found in cell G9. As a result of this simple procedure the formula that should appear in the cell for total Salaries on the Summary sheet is "=Salaries!G9". This formula denotes a link to cell G9 on the Salaries worksheet. Armed with these two techniques, spreadsheet users can prepare a budget workbook much more quickly than can be produced by working with pencil and paper alone. Of course, the really important aspect of budgeting is that the figures are well researched, informed, reliable and accurate. No budget will be able to accurately predict the future, but the more work that goes into research and preparation, the more accurate it is likely to be. Finally, spreadsheet users should make an effort to format each worksheet to give a professional appearance. The following formatting features should be employed where possible:
|
||||||||||||||||||
