calculate-loan

01 Loan payments

How to Create a Balance Sheet Projection

Guide 01-23207 · Updated · Step by step

This guide walks you through building a balance sheet projection, a forward-looking statement that estimates your company’s assets, liabilities, and equity at a future date. You’ll learn what data to gather, how to structure the projection, and how to check it for accuracy. Start with your most recent balance sheet and your projected income statement and cash flow, then follow the steps below to create a 12-month or multi-year projection.

What You’ll Need

  • Most recent actual balance sheet (as your starting point)
  • Projected income statement (revenue, expenses, net income) for the period
  • Projected cash flow statement or cash flow assumptions (collections, payments, capital expenditures)
  • Assumptions for changes in working capital (e.g., days sales outstanding, inventory turnover, days payable outstanding)
  • Planned capital expenditures and asset sales
  • Debt schedule: existing loans, principal repayments, and any new borrowing
  • Equity transactions: planned investments, dividends, or share buybacks
  • Spreadsheet software (Excel, Google Sheets) or a financial modeling tool

Step-by-Step

  1. Set your projection period and base date. Decide whether you’re projecting monthly for the next 12 months or annually for 3–5 years. Use your most recent actual balance sheet as the starting point (e.g., December 31, 2024).
  2. Copy the actual balance sheet into a new worksheet. List all asset, liability, and equity accounts as rows, with columns for each future period. Keep the same account structure you use in your actual statements for consistency.
  3. Project cash and cash equivalents. Use your projected cash flow statement—or build a simple cash flow projection—to determine the ending cash balance for each period. If you don’t have a full cash flow projection, estimate cash collections from sales and cash payments for expenses, then add/subtract investing and financing cash flows.
  4. Project accounts receivable. Estimate the percentage of sales you expect to collect within the period. A common method: use days sales outstanding (DSO) to calculate ending receivables = (DSO / 365) × projected annual sales. For monthly projections, use the monthly sales and adjust for collection lag.
  5. Project inventory. Use inventory turnover or days inventory outstanding (DIO). Ending inventory = (DIO / 365) × projected cost of goods sold (COGS). If you have a production plan, adjust for planned purchases and usage.
  6. Project other current assets. Prepaid expenses, deposits, and other short-term assets usually stay relatively stable. Project them as a percentage of sales or hold them constant unless you have specific plans.
  7. Project fixed assets and depreciation. Start with the current net book value. Add planned capital expenditures (CAPEX), subtract planned asset sales, and deduct projected depreciation expense for each period. The formula: Ending net PP&E = Beginning net PP&E + CAPEX – Depreciation – Book value of disposals.
  8. Project other long-term assets. Intangibles, goodwill, and long-term investments. Only change these if you have planned acquisitions or amortization schedules.
  9. Project accounts payable. Use days payable outstanding (DPO): Ending AP = (DPO / 365) × projected COGS (or purchases, if available). If you pay suppliers faster or slower, adjust accordingly.
  10. Project accrued expenses and other current liabilities. These often track operating expenses. Estimate as a percentage of the related expense line, or hold constant if they are not significant.
  11. Project short-term and long-term debt. Use your debt schedule: list each loan’s beginning balance, add new borrowings, subtract scheduled principal repayments, and carry the ending balance to the balance sheet. Do not include interest here—it goes to the income statement and cash flow.
  12. Project equity. Start with the current equity balance. Add projected net income (from your income statement) each period, subtract dividends or owner distributions, and add any new capital contributions. The formula: Ending equity = Beginning equity + Net income – Dividends + Share issuances.
  13. Check that the balance sheet balances. Total assets must equal total liabilities plus equity. If they don’t, the most common cause is a missing link between your income statement, cash flow, and balance sheet. Review your retained earnings calculation and cash flow projections.
  14. Refine and sanity-check. Review each line for reasonableness. Compare key ratios (current ratio, debt-to-equity) to historical trends. Adjust assumptions if something looks unrealistic (e.g., receivables growing faster than sales).

Common Mistakes

  • Forgetting the link between net income and retained earnings. Net income from the income statement must flow into equity (retained earnings). If you forget this, your balance sheet won’t balance.
  • Using the income statement’s net income but not adjusting for non-cash items. Depreciation and amortization reduce net income but do not affect cash. Your cash projection must add these back.
  • Treating all sales as cash sales. If you sell on credit, you must project receivables and collections. Ignoring this inflates your cash balance.
  • Mixing up DSO, DIO, and DPO. These days-based metrics must be calculated consistently. DSO uses sales, DIO uses COGS, and DPO uses purchases (or COGS as a proxy). Using the wrong base will distort your projections.
  • Forgetting to include principal repayments on debt. Principal payments reduce cash and debt but do not appear on the income statement. If you only model interest, your debt balance stays too high.
  • Rounding errors. When you round each line to the nearest dollar, the balance sheet may be off by a few dollars. Add a rounding check and adjust the cash or retained earnings line by the difference.

Tips

  • Start with a simple model. If you’re new to this, project only the major lines: cash, receivables, inventory, fixed assets, payables, debt, and equity. You can add detail later.
  • Use formulas in your spreadsheet. Link your balance sheet to your income statement and cash flow projections using cell references. This ensures changes flow through automatically.
  • Document your assumptions. Write down every assumption (DSO, DIO, DPO, growth rates, CAPEX plans) in a separate tab. This makes it easier to audit and adjust later.
  • Compare to actuals. When you have actual results, compare your projections to what happened. This helps you refine your assumptions for future projections.
  • Use scenario analysis. Create a base case, a downside case, and an upside case. This helps you understand how sensitive your balance sheet is to changes in sales or expenses.
  • Keep it flexible. Avoid hard-coding numbers in formulas. Use input cells for assumptions so you can easily change them.

Troubleshooting

  • Balance sheet doesn’t balance. First, check that net income flows into retained earnings. Then verify that all cash inflows and outflows are reflected in the cash balance. Common missing items: depreciation add-back, principal repayments, and dividends.
  • Cash balance is negative. This means your projected cash outflows exceed inflows. Review your collection assumptions, expense timing, and capital expenditure plans. You may need to arrange additional financing or delay purchases.
  • Receivables or inventory grow too fast. Check your DSO and DIO assumptions. If they are increasing, you’re assuming slower collections or slower inventory turnover. Compare to historical averages and industry benchmarks.
  • Equity is negative. This happens when accumulated losses exceed capital. If this occurs, review your income statement projections and consider whether your business will need additional capital injections.
  • Discrepancy between cash flow and balance sheet cash. Your cash flow statement’s ending cash must equal the cash line on your balance sheet. If they differ, check that you’ve included all non-cash items (depreciation) and all financing flows (debt, equity).
  • Projection looks too good to be true. Re-examine your sales growth and expense assumptions. Are you being overly optimistic? Stress-test with a lower growth rate to see if the balance sheet still works.

Frequently Asked Questions

How far ahead should I project?

Most businesses project 12 months ahead on a monthly basis, and 3–5 years on an annual basis. The length depends on your planning needs—lenders may want a 3-year projection, while internal budgeting often uses 12 months.

What if I don’t have historical data for DSO, DIO, or DPO?

Use industry averages or your best estimate based on your payment terms. For example, if you give customers 30 days to pay, use a DSO of 30. If you pay suppliers in 45 days, use a DPO of 45. Refine as you get actual data.

How do I handle seasonal fluctuations?

Project each month separately, using seasonal patterns from prior years. For instance, if sales peak in December, your receivables and inventory will be higher in that month. Your cash flow projection should reflect these peaks and troughs.

Do I need to project the balance sheet if I already have a cash flow projection?

Yes. The cash flow projection only shows cash changes. The balance sheet projection shows the full picture of assets, liabilities, and equity, which is essential for understanding liquidity, leverage, and financial health.

What’s the difference between a projected balance sheet and a pro forma balance sheet?

They are often used interchangeably. Both refer to a forward-looking balance sheet based on assumptions. Some use “pro forma” to describe a projection that includes hypothetical transactions (like a merger or loan).

How often should I update my balance sheet projection?

Update it at least quarterly, or whenever there is a significant change in your business (new financing, large purchase, change in sales trend). Regular updates keep the projection relevant for decision-making.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this section

The whole Loan payments section →