Budget Basics using a Spreadsheet
Spreadsheets, such as Microsoft Excel, are an invaluable tool for the preparation of budgets. In the hands of the experienced operator they allow:
- Information to be inserted, laid out, formatted and arranged for maximum readability with relative ease.
- Easy computation of budgetary estimates i.e.calculation of income and expenditure items
- Quick and easy modification of figures with instantaneous results
- Transmission of budget workings between work colleagues or members of the organisation management committee
- Long term storage of workings to enable others in future years to follow how budgets were calculated
- Mathematical modelling of financial problems to find best solutions
In short, if you are engaged in budgeting, it is essential to use spreadsheet software and Microsoft Excel is the best known.
An excellent feature of spreadsheet software is that it allows the user to create separate workings for each element of the budget (instead of putting everything into one sheet). This is really essential for anything more than the very simplest of budgets. By creating a separate sheet for each item in the budget (i.e. membership revenue, equipment costs, salaries, referee fees, etc) it becomes comparatively easy to deal with formatting. Thus each sheet can be published as if it were a page in the book, nicely formatted and easy to read.
The figure to the right illustrates that four separate worksheets have been created (Rent, Salaries, Office Expenses and Summary). The "Summary". page is where all the final figures from each of the other sheets is collected to make a complete income and expenditure statement. An outstanding feature of Excel spreadsheets is that the summary sheet can be linked to all the other worksheets in the budget. Therefore any changes to the worksheets is automatically reflected on the summary sheet. This not only saves time but also reduces the likelihood of error.
Here's an example of a worksheet that calculates the salaries budget.
| 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 | 3,150.00 | 38,150.00 | |||
| 5 | Activities Manager | 30,000.00 | 2,700.00 | 32,700.00 | |||
| 6 | Admin Assistant | 52 | 20 | $13.00 | 13,520.00 | 1,216.80 | 14,736.80 |
| 7 | Activities Assistant | 52 | 38 | $15.00 | 29,640.00 | 2,667.60 | 14,736.80 |
| 8 | Total | 108,160.00 | 9,734.40 | 117,894.40 |
In the above illustration of a worksheet for salaries, the basic information stored includes job titles and rate of pay. Based upon this information, the spreadsheet calculates the Super (superannuation), the annual cost of employment for each employee and the total cost of all employees. The total of $117,894.40 is the amount to be transferred (and linked) to the summary sheet.
When constructing and spreadsheet the golden rule is not to EMBED any figures in the formulae. If you do - this will lead to errors.
In cell E6 in the spreadsheet illustration above, the need is to calculate the annual salary of the Admin Assistant. The formula could be created in one of two ways, one right, one wrong.
Incorrect example formula: =52 x 20 x 13.00
Correct example formula: = B6 x C6 x D6.
(Note: all formulas begin with an = (equals sign))
So the correct thing to do is to use cell references in the formula rather than the actual numbers. If you use cell references then anytime the raw data is changed i.e. the wage rate is increased from $13 to $15 per hour, the spreadsheet instantaneously provides the correct answer.
Linking figures on the summary sheet to all the 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 "=". Then, point with your mouse to worksheet containing the answer and the cell where the answer can be found. Then hit the ENTER key.
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.