How to forecast the Balance Sheet?

Forecasting the Balance Sheet

Many companies focus on the income statement when forecasting their future cash flows but neglect to also include important aspects from the balance sheet.

So let us explain how the basic mechanics and principles work to project a company’s balance sheet in the future. Basically there are five topics to be considered:

The last one, cash is the most difficult one and actually requires forecasting the company’s cash flow statement in the future in order to calculate the year-end’s cash balances. Thus we will now work through each of the topics and every time also updating the cash flow statement so that we can calculate the year end’s cash position.

Net Working Capital

Forecasting net working capital simply requires estimating the year end’s net working capital positions such as receivables, inventory, payables and other current assets or liabilities.

NWC

Net working capital is driven by sales and the cost of purchased materials. Therefore we can link our estimations to revenues (for receivables) and cost of good sold (COGS) (for inventory and payables) coming from the Income Statement.

RevCogs

We now need to estimate the days sales, days inventory and days payables outstanding which we will do for each year (or linking each year’s estimate to the previous estimate).

DaysRevCOGS

We now use the following formulas (Days Sales Outstanding is the same as Days Receivables).

  • Receivables = Sales x Days Sales Outstanding / 360
  • Inventory = COGS x Days Inventory / 360
  • Payables = COGS x Days Payables / 360

The year has either 365 or 366 days but to make it simple we just take 360 business days. If we have other current assets or liabilities we simply can estimate their year-end’s positions as % of revenues or % of COGS unless there is a specific reason that they would be driven by other factors.

Now as we have calculated the year-end’s net working capital positions, we also can take their yearly differences and use them to calculate the cash flow from operations in the cash flow statement forecast.

NWC2

Fixed Assets

The next topic to forecast is to project the fixed asset positions which either can be fixed assets itself or intangible assets such as goodwill and other. Capital expenditures (CAPEX) is the investment which increases fixed asset balances but as the assets get older, they depreciate in value, so depreciation has to be deducted. Here we don’t go into the details of a fixed asset and depreciation schedule, we simply consider the yearly forecasted depreciation and amortization expenses as a given.

FA1

The formulas to be used thus are the following:

  • Fixed assets = Fixed assets last year + CAPEX – Depreciation
  • Intangible Assets Next Year = Intangible Assets Last Year + Investment – Amortization
  • Goodwill = Goodwill last year

Fixed Assets are very simple to calculate as long we simplify the depreciation and amortization estimates (which we do here). Same for intangible assets such as software development costs.

Goodwill is created by one-time events such as a business purchase, which we normally cannot anticipate in the future. Thus we just leave an eventual Goodwill position constant and also would not expect to amortize Goodwill.

FA2

Now we use our CAPEX and depreciation expenses to update the cash flow statement.

FA3

Financial Debt

Forecasting financial debt is quite easy. We just add the change of financial debt to the previous year’s position. Therefore:

  • Financial Debt = Financial Debt beginning of year + change in Financial Debt

This means all we need is estimating the yearly change in financial debt going forward. This information is available in the terms of the loan agreement by when the debt has to be repaid.

Debt1

The other component of financial debt is the resulting interest payment. We calculate the yearly interest expenses simply by the debt position times the interest rate (some analysts will take the average position of beginning and ending year debt balance). Thus:

  • Interest payment = Interest rate * (Financial Debt Year End + Financial Debt Beginning of Year)/2

Debt2

We now can update the cash flow statement also for the impact of the change of financial debt and interest expenses on the change in cash.

Equity

We forecast the equity position on the balance sheet by taking previous year’s balance increased by the Net Income and decreased by eventual dividends and change in the equity capital itself.

  • Equity year end = Equity last year + Net Income – Dividends + Change in Equity Capital

Equity1

As we can see, the equity also will affect the cash flow from financing, so we update the cash flow statement accordingly.

Equity2

Cash

We now can benefit from the previous work done as the cash flow statement allows us to calculate the yearly change in cash. We now simply can add prior year’s cash position to the change in cash and will have the cash position at year end.
Cash

Cash2

That’s basically it. We now have calculated all major balance sheet positions. The remaining thing to do is the check if total assets equal total liabilities and shareholder’s equity. If the difference is zero, this means our model is correct, if there should be a difference we would need to go back and find out why.

Leave a Reply