Forecasting the balance sheet is an essential part of any 3-statement financial model as the balance sheet, income statement, and cash flow statement are all integrated and need to flow. While the balance sheet is not as flashy as the income statement to investors, balance sheet projections form a critical role in developing the cash flow statement which investors care deeply about. A balance sheet forecast can also help investors analyze whether their net income projections are realistic by allowing them to measure profitability ratios such as return on equity and return on invested capital.

For investors interested in a pre-built financial model where they can punch in the financial data of any company of interest, they can check out our financial model and valuation template!

Using Historical Data to form Relationships

Projections need a solid base in reality and this is done by using historical financial data to form relationships which can then be carried forward into future years.

If any metric has been steadily changing in a certain direction, it would be most wise to take the latest level. As always, using more years of historical data can help form a more accurate average.

A good model will tell users the average level but will also allow users to override and adjust the historical figure if the analyst has good reason to believe the metric will change from its historic average.

Side Note on Turnover, Days, and Ratios: A “turnover” rate is how many times an account’s average balance can be divided into, or “turned over” another account. A “days” metric will convert this same turnover metric into the average number of days the account represents. Knowing a turnover or days metric is one and the same as an analyst just needs to divide 365 days by the turnover rate. A simple average “ratio” of one account to another, whether in decimal form or percentage form, can also establish the relationship.

The Major Balance Sheet Items

While every balance sheet looks a little different, there are a few major line items that will be consistent across every business. As we forecast out assets and liabilities, we have to think about what are the “drivers” behind each specific item. Some will be driven by sales and others will be driven by cost of goods sold. The income statement should be forecasted first which then drives a lot of the information on the balance sheet.

As we go through the formulae for calculating each major balance sheet item, the term “ending” refers to the amount that will be shown on the balance sheet for the current year and the term “beginning” refers to the closing amount on the balance sheet from the previous year.

ASSETS

Cash – As mentioned previously, the various parts of the financial statements are all interrelated and cash is the perfect example of this. In real life, cash should always tie to the bank statement, and a proper forecast will have a beginning and ending cash balance flowing neatly in the statement of cash flows. Cash on the balance sheet for any given year will be determined by the projected cash flow statement.

A certain amount of cash always needs to be kept on hand to pay suppliers, employees and the like. After this minimum level of cash is determined, short-term credit facilities can act as the “plug” in the model to keep cash at a healthy level.

Ending Cash -> from Cash Flow Statement

Accounts Receivable – The main driver behind accounts receivable would be sales. A certain amount of sales will be in cash, and a certain amount may remain outstanding. The common metric to refer to accounts receivable is days sales outstanding as is shown below.

Days Sales Outstanding = (Accounts Receivables / Sales) x 365

This historic formula can then be rearranged to solve for the year-end balance of accounts receivable.

Accounts Receivable = (Sales x Accounts Receivable Days) / 365

Inventory – The main driver for inventory is cost of goods sold (COGS) which is itself driven by sales. The common metric to refer to inventory is days on hand as is shown below.

Days Inventory on Hand = (Inventory / COGS) x 365

This historic formula can then be rearranged to solve for the year-end amount of inventory.

Inventory = (COGS x Days Inventory on Hand) / 365

Fixed Assets – The category of fixed assets includes items such as property plant & equipment (PP&E), intangible assets, and other long-term assets. Each type of fixed asset could be forecasted individually or to simplify things, they can be forecasted as a group. The main driver for fixed assets is sales and the common metric to analyze fixed assets is a turnover ratio as shown below.

Fixed Asset Turnover = Sales / Average Fixed Assets

This historic turnover ratio can rearranged to solve for the year-end balance of fixed assets.

Fixed Assets = Sales x Fixed Asset Turnover

Also of critical importance to a 3-statement model, the amount of fixed assets on the balance sheet must also flow with depreciation on the income statement and capital expenditures on the cash flow statement. As such, it is best practice to create a separate fixed asset schedule that starts with the previous year’s fixed assets, subtracts depreciation (based on historical depreciation rates as a percent of fixed assets), and then adds the capital expenditures that are needed in order to keep the fixed asset turnover ratio at the historical average.

Ending Fixed Assets = Beginning Fixed Assets + Capital Expenditures – Depreciation

LIABILITIES

Accounts Payable – The main driver behind accounts payable would be COGS as the number is being driven by the amount of time the company takes to pay suppliers for inventory. As such, the common metric to refer to accounts payable is days outstanding as is shown below.

Days Payable Outstanding = (Accounts Payable / COGS) x 365

This historic formula can then be rearranged to solve for the year-end balance of accounts payable.

Accounts Payable = (COGS x Days Payable Outstanding) / 365

Debt – The amount of debt in a company’s capital structure can greatly influence return on equity as analyzed through the Du Pont Method. The debt-to-assets ratio (simply debt divided by assets) can be used to measure the historic capital structure of the business. Unless an analyst has good reason to believe a company’s capital structure will change from historic norms, it is probably best to assume a constant capital structure. For forecasting the balance sheet, this means that the amount of debt in the business will continue to be proportional to the amount of assets based on the historical debt-to-asset ratio as shown below.

Debt = Assets x Historic Debt-to-Asset Ratio (%)

Also of note is that issuances and repayments of debt will flow from the cash flow statement’s section on financing activities.

Ending Debt = Beginning Debt + Debt Issuances – Debt Repayments

Side Note: If analysts are particularly concerned about the debt load and liquidity situation, they can create a separate debt schedule that incorporates the maturities of a company’s existing debt which can be found in the notes to the financial statements.

EQUITY

Retained Earnings – Net income that is retained in the business adds to equity through retained earnings while dividends (shown on the cash flow statement’s section on financing activities) will subtract from retained earnings.

Ending Retained Earnings = Beginning Retained Earnings + Net Income – Dividends

Share Capital – When a company issues or repurchases their own shares, these amounts will flow through share capital. Both share issuances and repurchases will flow from the cash flow statement’s section on financing activities.

Ending Share Capital = Beginning Share Capital + Share Issuances – Share Repurchases

Again, with this balance sheet analysis in place, investors can utilize a balance sheet forecast as a sanity check on other important balance sheet and income statement sourced calculations, such as Return on Equity (ROE) and Return on Invested Capital (ROIC).