SPREADSHEETS - 29.03.2011

Formulae hidden in budgets

The annual exercise

Let’s say you’re only a couple of months away from your company’s year-end and the MD is keen for you to get started on the annual budgetary exercise. That means getting out last year’s budget spreadsheet (plus supporting assumptions) and discussing with the budget holders the percentage growth/increases/decreases you are going to include in the monthly revenue and costs figures (in terms of both quantities and values). Most budget spreadsheets have embedded relationships (represented by formulae) between revenue (quantities, prices, yields etc.) and costs (some fixed in value but most varying with sales quantities).

For example. Delivery of your product to either a UK or overseas address will have two distinct costs per package (depending on its weight, of course). In the 2010/11 budget, let’s say you used a price per package of 45p for the UK and £1.30 for overseas. For this budget, you estimated that you would sell, on average, 5,000 units overseas every month, so in the 2010/11 budget you had a cell containing total estimated sales quantities for April (in, say, B9) then the postage and carriage line for that month (in, say, cell B25) could be defined using the following formula:

For 2011/12’s budget, you now have to go back into your spreadsheet and update it for the estimated overseas sales units per month for the forthcoming year and the revised postal rates per unit. But this is just one of the many hundreds of cells in your budget spreadsheet, so how do you know which ones to update?

Ease and effectiveness

Problem. Which cells contain data and which contain formulae? It’s not always obvious and it’s laborious viewing one cell at a time in the formula bar, flicking back and forth from originating and destination cells and reassessing the formulae. You could select the cell you are interested in and then under the Formulas tab (on Excel 2007 and later) click on the Trace Precedents function. The arrows generated on screen will lead you to the cells on which this cell is dependent. However, this still means you have to use the formula bar to review what’s in that cell.

Tip 1. Use Excel’s Show Formulas function under the Formulas tab (or in Excel 2003, under Tools, Options). Then set the print area covering, say, the first two months and run off a hard copy (in landscape format) so you can easily follow the formulae on screen.

Tip 2. Annotate this hard copy with the new constants you want in the formula (in our example postal rates) or refinements in defining the relationships between revenue and cost. File this useful paper tool on your budget folder for future reference.

Tip 3. For ease of use go back into the spreadsheet itself and change the font (from default black to, say, light blue) for those lines you actually have to put data in for. This will also help you avoid typing over cells endowed with calculated relationships.

For an example workbook showing how to easily update the assumptions behind each cell in your budget spreadsheet, visit http://financialcontroller.indicator.co.uk (FC 03.07.09).