Why an accurate and detailed forecasted Balance Sheet isn’t realistically feasible with a spreadsheet, but why it is important
Guest post by Alan Hart, MBA
I often wonder how many organizations or financial professionals actually forecast their company’s Balance Sheet. As someone who was determined in the past to accomplish this task, and failed miserably, I think I know what the answer to this question is. Forecasting a Balance Sheet is very hard; in fact, it is a daunting task.
First, you need to have a beginning balance sheet, which is the easy part – you pull it from the actual data at the period end, just prior to the beginning period of your budget plan. Then you must figure out how each of your forecasted items is going to affect every Balance Sheet account you need to report on.
For example, the cash account is affected by customer accounts receivable collections, cash sales, payments to vendors, payments on loans and lines of credit, borrowings, selling of fixed assets, paying your employees, making tax payments and many other possible transactions.
Most organizations, using a spreadsheet method for their budget and forecasting preparation discover that modeling all these scenarios to track the movement of just the cash account is simply not realistic. There are too many variables, too many types of transactions and too many distinct budget lines (e.g., revenue lines, expense lines), affecting the cash account balance at each period end of your budget plan.
Have you considered that in the real world, your customers may have several payment terms (e.g., 30 days, 60 days, cash, etc.), and not all of your vendors are paid by you the same way. Each of the remaining balance sheet accounts have their own peculiar characteristics, which further complicate the programing of spreadsheets to arrive at forecasted account balances.
This is precisely why the balance sheet is usually not part of the annual budget or the periodic forecasts. It’s too much of an effort to do and the results are often meaningless. Do you really have the extra time to become a programmer or would you rather employ purpose-built software to do all this work for you, freeing you to analyze the actual results vs. budget, make adjustments to the budget, or realign your business in response to the actual results?
I always thought that the entire chart of accounts should be part of the budget plan. That, of course, includes the balance sheet. A tool that allows you to effortlessly do that is Budget Maestro, where you forecast all of your relevant GL accounts by using a vast library of business rules built into the software. The software then creates, in real time, a budget based on forecasted, detailed journal entries, automatically made in future periods as defined in the budget plan.
Coupled with your own assumptions and data you provide, (always representing your own business and its unique subtleties) the software seamlessly produces a full set of projected financial statements, including an Income Statement, Balance Sheet and Statement of Cash Flows.
What’s more remarkable is the fact that the Balance Sheet exactly follows the level of detail you provide through your data input, business rules you choose for each budget line, and assumptions you incorporate into each of these lines.
Did I mention that you don’t enter a single formula in the entire process, or program a single link? Finally, after many years, I can get a forecasted Balance Sheet, as part of my budget process. I can gain insight into the projected future health of the organization; obtain projected financial ratios and more. Knowing what I know now, I wouldn’t settle for less.
Alan Hart, MBA, is Principal Consultant at Pacific Shine Group in Portland, Oregon, with responsibility for client business development and hands-on client project implementations. You can read his full bio here. Contact Alan at (310)-384-1453 or by email at alan(dot)hart (at) pacificshinegoup(dot)com.