Projecting Balance Sheet Line Items

Projecting Balance Sheet Line Items

Projecting balance sheet line items is not very different from projecting income statement line items. Both these skills are necessary when mastering the art of financial modelling. Most line items are not difficult to forecast.

 

 

Main Line Items to Project

The following are the main accounts we need to cover when projecting balance sheet line items:

  • Assets
    • Accounts Receivables
    • Inventory
    • Other Current Assets
    • PP&E
    • Other Long-Term Assets
  • Liabilities
    • Accounts Payables
    • Long-term Debt
  • Equity
    • Shareholder Capital
    • Retained Earnings

These are the main line items that make for a functioning balance sheet.

 

 

Cash Cycle Line Items

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.

 

Accounts Receivable Days = Average AR / Sales Revenue x 365
Inventory Days = Average Inventory / Cost of Goods Sold x 365
Accounts Payable Days = Average AP / Cost of Goods Sold (or Purchases) x 365

 

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.

 

 

Example

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

 

 

Other Current Assets and Long Term Assets

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.

 

 

Property, Plant and Equipment

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 plus Capital Expenditures less 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 in the balance sheet, and the depreciation expense in the income statement.

 

 

Long-term Debt

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 sum of all the closing balances of individual debt.

 

Closing balance = Opening balance plus Interest Expense less 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

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.

 

 

Retained Earnings

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 plus net income less dividends

 

 

The Ideal Order of Projecting Balance Sheet Line Items

Because we need certain items under the income statement, this is the best way of projecting balance sheet line items:

  • Project income statement leading up to depreciation expense and interest expense
  • Project balance sheet all the way through to retained earnings
  • Finish projecting income statement by completing depreciation, interest and tax expense
  • Finish projecting balance sheet by completing retained earnings