Balance Sheet Budgeting & Forecasting

Building out the balance sheet is probably one of the most annoying parts of modeling. It usually comes last in a three statement model and isn’t a driver like the income statement, so I don’t particularly enjoy it. That said, unfortunately balance sheet budgeting and forecasting is an important skill to learn. Here’s a quick overview of how to do it using both quick and dirty techniques and a full build.

Quick Balance Sheet Overview

Before we start, it’s probably helpful to see a quick balance sheet first so we know what we’re working with. At its most simplified, you’ll have Assets = Liabilities + Shareholder’s Equity. Within these buckets, you’ll have Current Assets and longer-term Assets, as well as Current Liabilities and longer-term liabilities.

Cash

The first step in the balance sheet budget will be our cash line. This is pretty simple if you already have a statement of cash flows, and will simply equal the period’s ending cash.

To break it down further, let’s say we’re looking at budgeting the January closing balance sheet. We’ll take a look at December’s closing cash figure, say it’s $10M. Then we’ll add to that the combined cash flow of the business over the month of January (CF from Operatings, Investing, and Financing activities), say this is $5M. Then we’ll have a January closing cash figure of $15M that will be our entry on the balance sheet.

Just to clarify, the same process will apply whether we’re looking at months, quarters, or years.

Current Assets

The next step as we work down our balance sheet will be to forecast Current Assets. We can do this using a quick and dirty methodology or a more involved forecast.

Quick and Dirty Current Assets

In this scenario, we’ll simply forecast things out as a percent of revenue and direct expense (COGS). Determine a percentage based on historical performance and future expectations, then apply to the relevant metric.

• Accounts Receivable – Forecast based on a percentage of Revenue because AR is directly linked
• Inventory – Forecast based on a percentage of Direct Expense (COGS) because they are linked (direct expense is the recognition of the cost of your inventory)
• Prepaid Expenses – In this example we forecast based on revenue to keep things simple, but you can also forecast it based on a percentage of Indirect Expense (SG&A) to be slightly more precise
• Other Current Assets – Typically a lumping of smaller items, we’ll also forecast this based on a percentage of Revenue

More Complex Current Assets

A more complex current assets can be forecast based on Days Sales Outstanding (DSO) and Days Sales of Inventory (DSI). This is potentially more precise, but modeling is an art not a science so nothing is guaranteed. I personally prefer to just slap some percentages into the model and call it a day, but your MD might have a tantrum and ask for a more complete build. For now we’ll leave it with the quick and dirty method, but I’ll outline the more complex approach in a follow-up post.

Other Assets

The most important ‘non-current’ asset will be your PP&E, or property, plants, and equipment. This is a catch-all term for any asset that has been capitalized. These days you’re just as likely to have capitalized software development expense as you are to have a factory.

With that, it makes sense that your PP&E will be driven by your capital expenditures. This figure will be taken from your Cash Flow Statement, or a separate CapEx forecast if your CF isn’t available. You can also assume a percentage of revenue if you’re doing a quick and dirty build.

Each period’s PPE balance will be the sum of the current period and all prior period’s capital expenditures, less depreciation in the current period and all prior periods (known as Accumulated Depreciation and sometimes broken out separately on the balance sheet).

The final asset to worry about is Goodwill. This is really only something you need to worry about in an acquisition scenario, so not something to mess around with during balance sheet budgeting / forecasting in normal course. Just hold it flat. For background on Goodwill in an acquisition, I’ll follow up with a separate post on purchase price accounting.

Current Liabilities

Here we’ll take a similar approach to Current Assets. Using a quick and dirty methodology, we’ll forecast Current Liabilities based on Income Statement Metrics.

• Accounts Payable – Forecast based on percentage of Direct Expense (COGS)
• Accrued Liabilities – Forecast based on percentage of Indirect Expense (SG&A)
• Other Current Liabilities – Forecast based on percentage of Revenue

Similar to Current Assets, you can make this more complex using ratios such as Days Payables Outstanding (DPO). However, this will take more time and not necessarily be any more precise.

Long-Term Liabilities

The most important piece of long-term liabilities will be any debt that the Company has. Our job here is to understand how that debt will trend over time. If we have an existing debt schedule or cash flow statement (with CF from Financing) we’ll pull our info from there. If not, we can create a simple debt schedule and project ourselves.

Here we have a simple debt schedule with the debt balance at the beginning of the period, the change in the balance over the period, and the ending balance that will go on our balance sheet. Note that this must tie to your cash flow statement for your balance sheet to be correct. This is because any debt paydown or draw will impact the Company’s cash.

The table above assumes we have a 1% mandatory amortization on both Term Loan A and Term Loan B. It also shows a $50M additional draw of the Term Loan A in Year 4. This could be, for example, due to an acquisition or major capital expenditure. The table assumes no Excess Cash Flow sweep, but any additional paydown would flow through that line.

There may also be other long term liabilities, but these will be specific to your Company. It’s probably best to leave them as is absent any additional information.

Shareholder’s Equity

We’re now down to the final piece of the build, Shareholder’s Equity. This is pretty simple – take a cumulative measurement of Net Income from the Income Statement. Your current period’s Shareholder’s Equity will be that period’s Net Income plus the accumulated Net Income from all prior periods.

You may have a situation where the Company is paying out a dividend or distributing cash to shareholders. In that case, take the above formula but simply subtract and cash paid out from the Company. Note that any cash payout should match what is shown on the Cash Flow Statement in order for your balance sheet to tie correctly.

Closing Thoughts on Balance Sheet Budgeting & Forecasting

That should be all you need to know to go ahead with a simple balance sheet budget or forecast. Note that every Company will have their own unique situation, but these approaches are broadly applicable and you can tweak where necessary.

The one thing you absolutely, 100% without a doubt have to do is make sure your Assets = Liabilities + Shareholder’s equity. This is the easiest way to see if your balance sheet is right or wrong. Never send someone a balance sheet that doesn’t balance or they will think you are an idiot.

That said, it can be tricky to get your balance sheet to balance the first few times you do it. There is a ton of interlinking between the three statements and any one thing could be throwing you off. You’ll get better with practice and eventually this will all be second nature.

One tip if your balance sheet isn’t balancing is to look at the Assets = Liabilities + Shareholder’s Equity check over time. If it’s static, say in every period you’re off by $100M, then you probably have an error in some static entry like Goodwill. If it’s increasing over time, you have an error in a dynamic entry like Shareholder’s Equity (increasing/decreasing with Net Income) or something like net working capital (and therefore cash) not footing to the Cash Flow Statement.

Drop some thoughts below and let me know if you’re having any issues or if it would be helpful to go deeper on any of the topics we went through above.