SKILL SETS: BUDGETING

Building a Better Budget, Part 4: Taming All Those Numbers

by Marion Gropen

So far in this series on how to build a budget from the ground up, we’ve explored “The Process and the Plan,” “Forecasting Sales,” and “Forecasting Expenses.” Along the way, we’ve accumulated a large and intimidating collection of numbers—and they’re all interrelated.

Obviously, you need to use a spreadsheet program to manage the calculations. But we’re book people. Spreadsheets aren’t always in our comfort zone.

If you know everything there is to know about building linked spreadsheets and keeping them under control, then skip what follows. If you’re a little hesitant about taming a monster spreadsheet, I have three suggestions for you:

• Build your sheets as templates for future uses as well as for current needs.

• Allow for your own fallibility and build to detect common errors.

• Let computers do the grunt work, and reserve your brain for the things that

need a human’s judgment.

Design for the Future

As you spend time to build this year’s budget, invest a little more time and build a template that you can use over and over. Use a spreadsheet program such as Excel so you can put a number of linked pages of calculations into one large file. In Excel, note the tabs at the edge of the worksheet that allow you to switch from one sheet to the next. You can add or delete and rename these sheets, and even change the order in which they’re displayed. And when you change the name of the whole file, or move it, all the links remain intact.

Obviously, you’ll improve your model each year, and ways to make it fit future plans will vary from company to company, but there are at least two steps you should always take. You should always retain a backup copy of the old version of any spreadsheet you’re about to alter, and you should always build the mathematical formulas so that you can add columns, rows, or even sheets without disrupting the formulas or the pointers that pull numbers from one part of your files to another.

Make a copy of the template you’ll be using for budgeting, and save it under a different name before you start your budgeting work. You may need it later.

Work only on a copy, no matter what, and incorporate a date or description in the filename of each version you create to differentiate among them. Keep all related versions in one folder so you can easily go back to an older version of the template when you need to do so.

On occasion, you will see a need to make changes in the structure of the files as you’re building your budget. When this happens, save a pre-change copy, and then continue your work. At the end of the budgeting process, if you have made improvements to the template, save another copy, and then empty all the data from that one so it can serve as your template going forward. (Try hard not to erase any formulas! Consider coloring the cells that you can enter data into—it makes them easier to spot.)

Once you have a good set of spreadsheets, saving a template version of the whole file will also help you avoid having to rebuild. Each year, you can add a few new titles, delete a few old ones, and make a few updates. Then when you populate the new file with the latest data, you’ll be a long way ahead of the curve.

Adding rows or columns to a spreadsheet full of formulas can cause an error if those rows or columns are at outer edges of your work area. And, of course, since you’ll continually be adding data for a new year to data for years in the past, that’s where new numbers will be.

The solution is simple. Add a blank row or column at each side of your data. Then, when you sum across a group of numbers—or do anything else to numbers en masse—make sure you include those blank rows and columns. Insert new rows and columns between the blanks and the last filled ones. Never put anything in the blank ones. And when you delete the prior year’s data, make sure you don’t delete the blanks areas at the edges. That way, you’ll not only make the sheets easier to read; you’ll also make sure your formulas don’t have to be reentered each year.

Design for Fallibility

You will make mistakes; that’s a given for everybody. So employ tools that make detecting and correcting your errors easier. These are the most common ones:

• Keep the number of calculations done at any one step small, so that it will be easier

to notice an error in that step.

• Go back to the last correct version, and start over with a copy of it, instead of trying

to update a number of different formulas after you’ve discovered that one in a series

of calculations is wrong.

• Make sure that numbers flow automatically from the beginning of the set of

spreadsheets to the end.

• Check the accuracy of your formulas.

• Do a back-of-the-envelope estimate of how the totals should look, and if the totals on

your spreadsheet are wildly off, hunt for the problems.

Building a large set of linked spreadsheets is hard not because any one step is hard, but because there are a lot of steps. It’s tempting to try to put many steps onto one sheet. Don’t succumb. Instead, break the whole thing into small pieces, and tackle one at a time.

As an added bonus, this may help you notice things that had faded into the background.

When (not if) you make a mistake while building your template, correct it only if you find the error before you’ve built on that foundation and used the results of your incorrect calculation in other parts of your budget.

If you find your error after you’ve built more formulas based on it, you may have to scrap all the work you’ve done since you made the mistake, and then correct the error and start over. It is almost impossible to find every place that an incorrect calculation was used in a complex series of formulas. And if you fail to find every single place, that incorrect calculation can bedevil you for years, embedded in template after template, unquestioned and unnoticed.

Checking Techniques

To check the accuracy of each formula you program into the spreadsheets, you can use check totals and sample data.

Using check totals entails having numbers that you can find two ways. For example, you can find the total sales for a year either by adding up the total sales for each title across the whole year and then adding those subtotals, or by adding up the total sales for each month across all titles and then adding up those subtotals. These two numbers should, of course, be the same.

Every time you have two calculations that should yield the same number, subtract the number for one of them from the number for the other. If that calculation doesn’t yield zero, it’s a red flag.

Similarly, when you have a known number—for instance, the amount your distributor is supposed to pay you over the next few months—check to see whether your cash-flow worksheets are projecting those totals correctly. If not, you have a problem.

Checking a formula with sample data involves using a very simple set of numbers. For example, when I program a royalty calculation, I might test it by inputting figures for sales of $10,000 or 10,000 copies, and rates of 10, 20, and 30 percent. The right answers are pretty clear, and if they don’t show up, I’ll see it immediately. There are almost always simple data sets you can use to test a single line or column of your formulas.

Formulas should flow data from the first time you need them through to the end. It can be intimidating to pull the data from one sheet to the next in your spreadsheet file, and it can seem simpler just to copy it over by hand. My experience suggests, however, that as soon as you start doing this someone will interrupt you with a crisis, and when you come back you’ll forget where you were. Something won’t get changed that should have been changed, and accuracy will fly out the window.

Another basic error-detection technique is the smell test, which you can use once you know how the numbers should turn out, more or less. You may have an intuitive sense after a few years with a company, or you may need to do a rough estimate. Then, if your model is drastically different, you know that there’s a problem in your understanding of the operation or in your programming of the spreadsheets.

Go back and dig into the way the problematic section of your budget is computed. Are there any errors in logic? Any errors in computation? Or is the underlying reality different than you thought it was? If so, that’s critical information, since the budget process is all about pinpointing shifts in reality before you trip over them.

Allocate Functions

Humans are the only source of judgment. Computers are far better at brute number-crunching. So make the computer do the heavy lifting, and save your own time and energy for things that only humans can do.

Human beings’ budgeting tasks include:

• looking for errors, as above

• evaluating results for acceptability

• finding alternative tactics if and as necessary, and then running them

through the process

• reconsidering goals, and devising the master plan for achieving them

When they’ve put all the numbers into a budget for the first time, most people stop, heave a sigh of relief, and put the printouts in a file somewhere. That’s a huge mistake. Budgets are a tool and a process, and now that you’ve built one, it’s time to use that tool.

Look at the bottom line. Is that enough profit to satisfy investors, reinvest in the company, and still cover your debt? Are you putting out enough of the right type of titles to reach any nonmonetary goals? Is the future you see good enough?

The usual answer to such questions is initially No, which means you’ll want to figure out what parts of your operation can be changed.

Should you do fewer titles and try to increase the margins each one yields? That might mean investing more time from a star editor or a publicist, or investing more in training your authors to be their own best publicists.

Should you do more titles, and invest less in each one until it proves itself? Should you do a different type of title? Or a different type of cover? Or change your mix of distribution channels? The options are endless, but some of them will make sense to you within your current strategic plan.

When you know what tactics you might change, go back and reestimate your sales and your expenses, and recompute your projections. And then lather, rinse, and repeat until the desired results seem to be within your grasp.

Also think about a change in strategy. Might that be needed even more than a change in tactics? Are your goals still valid? Is the mission of the company still the same? Where do you want to be in 10 years? In 20? Why? What are your strongest talents and advantages? What makes you special? Are you capitalizing on that as much as you can to reach your (new or old) goals? What changes in the industry, in readers, in the world, will affect your efforts? How?

Budgeting is essentially all about dreaming—and then putting numbers to your plan for achieving your dream. In fact, all financial analysis is about using numbers to help find a path toward your goals. The tools and techniques discussed in this series of articles can be applied to many types of analysis that will support your effort to make your dreams as a publisher come true.

Marion Gropen consults with micro- to medium-sized publishers on financial and operational issues and offers consultations by the question for smaller publishing companies. She recently published the first part of her e-book series, The Profitable Publisher. To learn more, visit GropenAssoc.com. You can reach her, with questions about this article or other publishing issues, at Marion.Gropen@GropenAssoc.com.