Projecting Balance Sheet Line Items
Designing a forecasted balance sheet
Designing a forecasted balance sheet
Projecting balance sheet line items is typically done in conjunction with projecting income statement line items. Both these skills are necessary when mastering the art of financial modeling. This guide will break down step-by-step how to calculate and then forecast each of the line items necessary to forecast a complete balance sheet and build a 3 statement financial model.
The above screenshot is from CFI’s financial modeling course.
The following are the main accounts we need to cover when projecting balance sheet line items:
These are the main line items that make for a functioning balance sheet.
Accounts Receivables, Inventory, and Accounts Payables are unique in that they have a very specific method of forecasting. Because these accounts are all involved in the operating and cash cycle, it is useful to forecast days outstanding for all of these accounts. Using the formula for their respective days outstanding, we can forecast future accounts receivables, inventory and accounts payables.
The following are the formulas for annual days outstanding:
After finding historical values for days outstanding, we can use these trends and reverse engineer the days outstanding formulas to find the accounts receivables, inventory or accounts payables for that specific period.
Let us take an example of accounts receivables. In the previous year, accounts receivables days was 120. If sales revenue was $100,000 for the year, then accounts receivables is found by:
Accounts Receivables = 120 x $100,000 / 365 = $32,876
We can forecast other current assets as a single line item, or break them out as individual items. Projecting balance sheet line items through the latter method is a bit more involved, but will allow for more granularity and dynamism in the model.
The quick and dirty method of project balance sheet line items for current assets is to simply use a whole dollar value prediction for these accounts in the future, or follow the trend that already exists.
Projecting PP&E is different from projecting other current assets and long-term assets. This projection requires building out a depreciation schedule for each class of PP&E. The balance displayed on the balance sheet is the closing balance.
Closing balance = Opening Balance + Capital Expenditures – Depreciation Expense
As you can see, the use of the depreciation schedule is tied to both the balance sheet and income statement. We use the closing balance on the balance sheet and the depreciation expense in the income statement.
Similarly to PP&E with its depreciation schedule, Long-term debt is forecasted using the debt schedule. This schedule outlines each class of borrowings and lays out the interest expense for each period. The balance displayed on the balance sheet is also the closing balance of long-term debt, or the sum of all the closing balances of individual debt.
Closing balance = Opening Balance + Interest Expense – Repayments
It’s important to note that here, interest expense is added back to the opening balance. In contrast, depreciation expense is deducted from the opening balance under PP&E. Keep this in mind, and don’t forget to use the appropriate signs.
Shareholder capital can be one of the simplest tasks when projecting balance sheet line items. More often than not, shareholder capital remains constant throughout periods, so forecasts will generally just be set to equal the latest known period.
Closing balance = Opening Balance + New Capital Issued – Capital Repurchased
Forecasting retained earnings actually involves projecting net income and dividends, rather than retained earnings itself. This means that to finishing projecting balance sheet line items, it’s handy to first finish projecting income statement line items so as to have net income readily available. As always, the balance that is displayed on the balance sheet is the closing balance.
Closing balance = Opening Balance + Net Income – Dividends
Because we need certain items from the income statement, this is the best way of projecting balance sheet line items:
Thank you for reading this guide to creating a balance sheet forecast. 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 continue learning and advancing your career, these resources will be very helpful:
Learn how to build a financial model in Excel with CFI's Financial Modeling Courses! Build a model from scratch the easy way with step-by-step instruction.
Become a certified Financial Modeling and Valuation Analyst (FMVA)™ by completing CFI’s online financial modeling classes!