Close the Skill Gap -> Enroll to be a Certified Financial Modeling & Valuation Analyst (FMVA)® Today!

Forecasting Balance Sheet Items in a Financial Model

Step-by-step guide

Forecasting Balance Sheet Items in a Financial Model

This article aims to provide readers with an easy to follow, step-by-step guide to forecasting balance sheet items on a financial model in Excel, including property, plant, and equipment (PP&E), other non-current operating assets, and various components of working capital.

In this four-step guide article, we forecast the income statement by forecasting revenues down to EBIT or operating profit. We will forecast the balance sheet by learning how to model operating assets, such as PP&E, accounts receivable, inventories, and accounts payable.

 

Forecasting Balance Sheet Items

 

Forecasting Balance Sheet Items

At this stage, we are not aiming to forecast all the items on a balance sheet. Having forecast the revenues and operating costs, the next stage is to forecast the operating assets required to generate them. For now, we will exclude the financing items on the balance sheet and only forecast operating (non-current) assets, accounts receivable, inventories, and accounts payable.

 

Forecasting Other Balance Sheet Items

 

Forecasting Property, Plant, and Equipment (PP&E)

Before we begin to forecast, it is important to remind ourselves of the first principles approach and the “quick and dirty” approach. Applying the first principles approach in forecasting balance sheet items will provide high levels of detail and precision in the model, even though it is more challenging to follow and audit. On the other hand, the “quick and dirty” approach will allow us to build a model in a much more straightforward way with the benefit that our model will be smaller, easier to follow and audit. The other benefit related to the latter approach is that linking PP&E to revenues ensures as revenues grow, PP&E also grows.

 

First Principles Approach vs Quick and Dirty Approach

 

Capital Asset (PP&E) Turnover Ratio

The capital asset turnover ratio is often used to link capital asset forecasting directly to revenue. This first formula defines the capital asset turnover ratio:

 

Capital Asset Turnover Formula

 

The second formula shows how we can use forecast sales and capital asset turnover to forecast capital assets. The capital asset turnover ratio is often used to link capital asset forecasting directly to revenue. This first formula defines the capital asset turnover ratio:

 

Forecast Capital Assets Formula

 

Forecasting PP&E Acquisitions and Disposals

In a more complex forecast, we may need to breakdown PP&E into further detailed items. In order to do this easily within a model, the best approach is to put the PP&E breakdown in a separate note or schedule. In our financial modeling exercise, we put all our supporting schedules in another section to keep our building blocks for inputs, processing and outputs separate. Breaking down the calculations will allow us to identify forecast acquisitions and disposals, which are necessary to complete a cash flow forecast.

 

Supporting Schedules

 

When forecasting PP&E from first principles, we typically start by forecasting acquisitions and disposals and then work down to PP&E net book value.

When forecasting PP&E using the “quick and dirty” approach, we do the reverse and start from PP&E net book value and work upwards to acquisitions and disposals.

 

Forecasting PP&E - “Quick and dirty” approach

 

Disposals can be forecast based on the historical relationship between the gross cost of opening PP&E and the gross cost of disposals.

 

Forecasting Working Capital

The first-principles approach to forecasting working capital typically involves forecasting individual current assets and current liabilities using various working capital ratios, such as receivable days, inventory days, and payable days. Alternatively, we can calculate working capital as one item in a “quick and dirty” way based on historic trends.

 

Forecasting Working Capital

 

Cash, although often considered part of the working capital, will be forecast as a financing item later.

 

Working Capital Equations

The first working capital item that we will forecast is accounts receivable. The receivable days ratio is often used to link forecast receivables to revenue. The first formula defines the account receivable days ratio:

 

Accounts Receivable Days Formula

 

The second formula shows how we can use forecast sales and receivable days to forecast receivables:

 

Forecast Accounts Receivable Formula

 

After forecasting receivables, we can then forecast accounts payable. In a similar manner, the accounts payable days ratio can be used to link forecast payables to cost of sales. If the cost of sales data is not available, it can be replaced with revenues. The first formula defines the accounts payable days ratio:

 

Accounts Payable Days Formula

 

The second formula shows how we can use forecast cost of sales/revenues and payable days to forecast accounts payable:

 

Forecast Accounts Payable Formula

 

The last working capital item to forecast is inventories. The inventory days ratio can be used to forecast inventory to cost of sales. Once again, if cost of sales is not available, revenues can be used instead. The first formula defines the inventory days ratio:

 

Inventory Days Formula

 

The second formula shows how we can use forecast cost of sales/revenues and inventory days to forecast inventories.

 

Forecast Inventory Formula

 

Conclusion

The key lessons from this article include:

  1. Consider forecasting PP&E using the capital asset turnover ratio.
    • This approach enables you to forecast PP&E quickly and ensures that PP&E grows as revenues grow.
  2. Build your PP&E from first principles based on known capital plans and budgets. Consider using ratios such as receivable days, payable days, and inventory days to forecast working capital

 

Additional Resources

CFI is the official provider of the global Financial Modeling & Valuation Analyst (FMVA)™ certification program, designed to help anyone become a world-class financial analyst. To keep advancing your career, the additional resources below will be useful:

Financial Modeling Certification

Become a certified Financial Modeling and Valuation Analyst (FMVA)® by completing CFI’s online financial modeling classes!