Forecasting a Balance Sheet in a Spreadsheet World

Why an accurate and detailed forecasted Balance Sheet isn’t realistically feasible with a spreadsheet, but why it is important

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, so if you are determined to leave the spreadsheet world when performing the budget process, then search for an application that truly and seamlessly generates a forecasted Balance Sheet and a Statement of Cash flows, in detail, and using your precise data input, business rules and assumptions. I simply wouldn’t settle for a solution that approximates the Balance Sheet projected balances, after requiring you to do much programming of your own.

Leave a Reply

Your email address will not be published. Required fields are marked *