This guide provides a comprehensive approach to creating a balance sheet forecast, detailing how to project individual balance sheet line items and ensure the statements align in a three-statement model. A well-built balance sheet projection can help business leaders understand their company’s financial situation and make more informed business decisions based on the company’s financial performance.
How to Build a Balance Sheet Forecast in Excel
When you’re forecasting a balance sheet in Excel, the first step is to use historical data to inform future projections. After projecting the income statement with key figures like revenues, operating expenses, and net income, the next task is to focus on forecasting balance sheet items.
Use at least two years of historical data: This is critical for providing context when building a balance sheet forecast. Organizing this financial data will help you project current assets and liabilities, along with PP&E and long-term liabilities.
Tailor GAAP classifications for forecasting: When projecting a balance sheet, you may need to reclassify some items to better align with your method of forecasting. For example, some companies may present balance sheet items in a way that is not ideal for modeling.
Supporting schedules: Use detailed schedules to forecast each balance sheet line item. The final balance sheet should pull these projections to present a clear picture of the company’s future financial position.
Forecasting Working Capital and Current Assets
Working capital is a crucial measure of the company’s financial health, and forecasting it is an essential part of the balance sheet. Common working capital balance sheet line items include:
- Accounts Receivable (A/R): Typically grows with sales. When forecasting, consider using a causal forecasting method like days sales outstanding (DSO) to refine the projection.
- Inventories: These typically grow with the cost of goods sold (COGS) and can be adjusted with turnover ratios.
- Prepaid Expenses: Grow these in line with SG&A expenses or revenues if the nature of the items is unclear.
- Other Current Assets: These are often tied to revenue growth but can be straight-lined if unrelated to business operations.
- Accounts Payable: This liability usually grows with COGS or revenue but can be adjusted based on payment terms.
- Accrued Expenses: Forecast these liabilities with SG&A or revenue, depending on their nature.
- Deferred Revenue: Typically grows with sales and represents payments for products or services yet to be recognized as revenue.
- Taxes Payable: This grows in line with tax expense on the income statement.
Long-Term Assets and Liabilities Forecasting
When projecting long-term assets like PP&E and intangible assets, depreciation must be accounted for. These assets grow with the company’s operations but depreciate over time, which affects the company’s financial position.
PP&E Forecasting:
- Start with the opening balance from the previous period.
- Use equity research or historical data to forecast capital expenditures.
- Depreciation can be projected either as a percentage of capex or using a more detailed waterfall analysis.
Intangible Assets Forecasting:
- Begin with the prior period’s balance sheet value, adjusting for purchases and amortization.
- Goodwill is often straight-lined unless there’s a clear reason to forecast impairments or acquisitions.
Forecasting Equity and Long-Term Debt
Long-term debt and equity forecasts are essential for understanding the company’s future capital structure. Companies often refinance maturing debt, keeping long-term debt constant or growing slightly with net income.
Forecasting equity: Includes key line items like common stock, retained earnings, and other comprehensive income (OCI). Forecast retained earnings based on net income from the income statement, and adjust for dividends as a percentage of net income. Treasury stock forecasts should consider any potential stock buybacks.
Cash Flow and Short-Term Debt Forecasting
In most three-statement models, cash and short-term debt (such as revolving credit) serve as the plug to ensure the balance sheet balances. A cash flow statement can help in forecasting cash surpluses or deficits, which will either increase the cash balance or the need for short-term funding. Properly forecasting cash flow allows businesses to make more informed financial decisions.
Ensuring the Balance Sheet Balances
To create an accurate balance sheet, it is crucial to ensure that assets and liabilities, plus equity, balance. Errors in the cash flow statement or mislinks between line items can prevent the balance sheet from balancing. Cross-referencing the balance sheet and cash flow statement will help resolve these issues.
Benefits of Balance Sheet Forecasting
A balance sheet forecast allows business leaders to assess the future financial position of their company, improving the ability to make strategic business decisions. It offers insights into the business’s financial health, helping to project cash needs, capital expenditures, and future growth opportunities. Accurate forecasting techniques such as causal forecasting and time series forecasting ensure that the company’s balance sheet remains balanced and reliable for planning purposes.
Master Financial Forecasting and Business Strategy
To master balance sheet forecasting, consider learning more about financial planning, modeling, and business valuation. Understanding how balance sheet line items tie into other financial statements will provide a holistic view of your company’s financial situation in the future and help make informed business decisions that align with long-term goals.
This guide offers practical steps to help businesses forecast a balance sheet, improve financial health, and support business growth through accurate financial forecasts that align with business strategynds-on practice.
Do you have an inquiry? Schedule a free initial consultation