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.By the way, let’s look at how useful binary variables can be. For specifications that are essentially either true or false, like applying UV coating, saddle or perfect-binding, or co-mailing, the specification variable becomes a 1 or a 0. You can either solicit the 1 or 0 answer directly, or write another formula to convert a “yes” or “no” to a 1 or a 0. Then, if you multiply a price by a variable named saddle?, you’ll be multiplying by 1 or 0—either getting a cost or a 0 depending on whether the variable says you’re using the specification. Plus, binary variables make for sleek IF statements because the test for a 1 or 0 is implicit. Say you need to count the number of binding pockets, and will add one more for the cover on a saddle-stitch issue, but not count the cover if perfect-bound. If the signature and insert pockets have already been calculated to make a variable called n.pockets, you don’t have to write =IF(saddle?=1,n.pockets+1,n.pockets). Instead, just write =IF(saddle?,n.pockets+1,n.pockets), or, still more compactly, =n.pockets+saddle?.Excel is flexible, so there aren’t too many “nevers,” but there is one: Never put a specific number into a formula. I call this the Hard Number Rule, and you will be tempted to defy it in the heat of battle when you have to crank out another projection in 10 minutes. But hold fast. Writing =SUM(A1:A250)*1.025 in order to escalate a total by 2.5 percent is dangerous, because you can no longer see the escalation percentage. Instead of hiding it in a formula, write =SUM(A1:A250)*escalation. Type the percentage in a visible cell and name the cell “escalation” to use the variable in formulas. This will also help you remember to use that escalation assumption in a variety of formulas.Creating a Simple, but Effective Budget WorksheetNow for the flexible part. There are so many ways to build formulas in Excel that it’s hard to pick the best one. The sidebars here contain a few ideas on how to use some of these functions in a budget worksheet.The overall design of the worksheets must create a clear and orderly structure that makes it easy to use and re-use the spreadsheet without forgetting how it works. You’ll have more confidence when the spreadsheet has clear captions, logical order, and no stray calculations tacked on to solve problems in an end run.I like to take advantage of Excel’s tabs, and split up a budget into at least five worksheets: publication specs, issue specs, prices, calculations and report. If I’m doing multiple publications in one worksheet, I may have additional price worksheets for different vendors and a rollup worksheet to collect the data from all the titles.The publication specs section contains the variables that tend not to change issue by issue, or that may change once or twice during the year. Instead of having to remember to enter, alter or check them for each issue, make them global specifications. Something like trim size or basis weight might change from time to time, but still is best placed in this overall worksheet, along with variables to allow a change when a trigger value is met.The issue specs worksheet contains items like quantity, page count and binding requirements that will change with each issue. In this worksheet, you’re looking for tricks to be certain you’ve foreseen all the consequences of the variables you enter. Little error checks, like testing that there are enough signature breaks to hold all the inserts, will keep your calculations accurate. One error check I learned to make from experience is a check to see if total inserts and signature deliveries exceed the number of binding pockets listed in the price schedule. If your price table stops at 18 pockets and the model calculates a need for 19, your binding appears to be free, because there are no prices to use. Write a pocket-count check to verify your model is working properly.The price worksheet contains the price schedule, but it doesn’t stop there. To allow for future escalations, I enter the prices and then scale them into an adjacent active price table. This also forms a useful test of the printer’s revised price schedule.The calculations section applies the variables from publication specs and issue specs to the prices. There are many ways to build the lookup structure for this, but we don’t have space to address them in this column. For homework on your own, look into OFFSET. To use it well, you’ll need to convert to R1C1 notation in Excel. If you can make this leap, you will see there is a more efficient way to write formulas that combines relative and absolute references, and that Excel offers some especially handsome ways to look things up. For example, a concise formula like =OFFSET(textPages,titleFind) locates the text pages of one issue of potentially dozens you’re simultaneously modeling by finding the intersection of the issues columns (from the counting variable titleFind) and specifications rows (from the name textPages).Finally, the report collects all the relevant subtotals and arranges the material to suit various constituencies. On the report worksheet, I’ll calculate key numbers a publisher likes to see, like the cost per page, distribution costs per sub copy and per newsstand copy, and per M prices for incremental costs above initial makereadies.Budgeting is a dynamic and demanding job. The key components of a well-designed model are the little traces of intelligence that allow it to remain accurate and consistent, plus the ability to take on changes and updates easily without introducing errors. Alex Brown is a consultant to magazine publishers specializing in manufacturing and magazine management. She founded her consulting company, Printmark, in 1984, and is a frequent speaker at industry events.

Publishing News

Budget Forecasting Secrets

Alex Brown

About

Subscriptions

Events

Publications

WORK SMARTER - LEARN, GROW AND BE INSPIRED.

Subscribe today!

Alex Brown