Budget Forecasting Secrets
One of the joys of Excel is that there are so many ways to skin your calculation cat. You can assemble a budget in any way you like because the ultimate math involved is very simple: Multiply the price times the number of times you buy something, then sum everything. It may even seem unnecessary to layer a little complexity into a budget worksheet, but there are some good reasons to elevate your Excel skills.
The hallmark of a great budget spreadsheet is that it contains a little intelligence of its own: the ability to handle a wide range of details more consistently than any one person can remember to do, time after time. This quality becomes even more important when you want to re-use the budget worksheet year after year. The spreadsheet also can ensure that all the consequences of assumptions are addressed.
To make a smart budget model, rely on Excel’s many techniques for writing clear formulas. By naming variables and arrays, you can write =A10*pressrun, or (width*depth*basisWeight)/475, and, in each case, you’re more certain of what you’re calculating and able to repeat the structure elsewhere in the worksheet.
I’ll assume you’re familiar with defining names in Excel and note here only a few tips on naming conventions. I like to show calculated quantities, like the number of 48s or 32s, as n.48, n.32 in order to dodge the problem of leading with a number. I also like to make all my names start in lower case, and use an “up cap” for compounds, like bindStops and paidAdPages. These are easy to read and have an added bonus: If you type a formula using such a name in all lower case and the up cap doesn’t show up, you know you’ve made a typing error or failed to define the term. Finally, I use a good number of binary variables, with a specified or calculated answer of either 1 or 0, on which other formulas are dependent. When I’m after a binary answer, I write UV? to show I’m in a yes or no format. If the cover has UV coating, there’s a 1; if not, there’s a 0.