How to Forecast Balance Sheet in Excel

Building a clear financial forecast is the difference between guessing where your business is heading and actually steering it there. While many focus on sales projections, forecasting your balance sheet in Excel provides a complete picture of your company's future financial health. This guide will walk you through the process step-by-step, showing you how to build a dynamic model that connects to your income statement and cash flow streams.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Forecast a Balance Sheet?

Forecasting your balance sheet isn't just an accounting exercise for the finance team. It's a critical strategic tool for any business owner, marketer, or department head. A well-built forecast helps you:

• Plan for growth: Understand how much cash you'll need to support higher sales, such as funding more inventory or accounts receivable.
• Identify funding needs: See potential cash shortfalls well in advance, giving you time to secure loans or investments before it becomes an emergency.
• Manage debt: Project your debt levels and ensure you can meet your obligations by planning for principal and interest payments.
• Make informed decisions: Evaluate the financial impact of major decisions, like purchasing new equipment or launching a new product line, before you commit.

Ultimately, it provides a reality check on your goals, showing you whether your ambitions are financially sustainable.

The Three-Statement Model: Your Forecasting Foundation

A balance sheet forecast never exists in a vacuum. It's one part of an integrated financial model, famously known as the "three-statement model." To build an accurate forecast, you need to understand how the three core financial statements - the Income Statement, the Balance Sheet, and the Cash Flow Statement - work together.

• The Income Statement (P&L): Shows your revenues, expenses, and profitability over a period (e.g., a month or a year). Its bottom line, Net Income, is the crucial link to your balance sheet.
• The Balance Sheet: Provides a snapshot of your company's financial position at a single point in time. It follows the fundamental accounting equation: Assets = Liabilities + Owner's Equity.
• The Cash Flow Statement: Tracks the actual cash moving in and out of your business from operations, investing, and financing. The final Ending Cash Balance from this statement directly becomes the cash amount on your next period's balance sheet.

Net income from the P&L flows into the Retained Earnings on the balance sheet. The Cash Flow Statement reconciles that net income with the real change in cash, and that final cash number is posted back to the assets on the balance sheet. When you build your forecast, you’ll be recreating these connections in Excel.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Getting Your Data Ready in Excel

Before you start plugging in formulas, you need to organize your workspace. The best practice is to set up a three-statement model with historical data and future periods laid out side-by-side.

Here’s what you need to gather:

• Historical Income Statements: Two to three years of past data is ideal. This helps you identify trends and calculate historical averages.
• Historical Balance Sheets: The corresponding balance sheets for the same periods.

Setting Up Your Spreadsheet

1. Create a new Excel file and set up three tabs: one for your main model ("Model"), one for your assumptions ("Assumptions"), and one for reference schedules like debt and fixed assets ("Schedules").
2. On your "Model" tab, build the structure of your Income Statement, Cash Flow Statement, and Balance Sheet.
3. Create columns for your historical years (e.g., 2022, 2023) and your forecast years (e.g., 2024, 2025, 2026).
4. Input your historical data into the correct columns. This historical data will serve as the basis for all your forecast assumptions.

Your goal is to have a clean, easy-to-read layout where you can see the flow from past performance to future projections.

Forecasting Your Balance Sheet, Step by Step

Now, we’ll go line-by-line through the balance sheet. Most balance sheet items are forecasted based on "drivers" - usually items from the income statement like Sales or Cost of Goods Sold (COGS).

1. Forecasting Your Assets

Assets are what your company owns. We'll start with current assets and then move to non-current (or long-term) assets.

Current Assets

• Cash and Cash Equivalents: Leave this for last. Cash is the balancing item, or "plug," that you will calculate from your Cash Flow Statement. Place a placeholder for now.
• Accounts Receivable (A/R): This is the money your customers owe you. It's directly driven by sales. The best way to forecast it is using Days Sales Outstanding (DSO), an assumption you should place in your "Assumptions" tab.
• Inventory: This is a function of how quickly you sell goods, generally tied to COGS. We use Inventory Days for this.

Non-Current Assets

• Property, Plant, & Equipment (PP&E): This represents your long-term assets like buildings, machinery, and computers. It's more complex and requires its own mini-schedule, commonly called a "waterfall."

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

2. Forecasting Your Liabilities

Liabilities are what your company owes to others. These are also broken down into current and non-current.

Current Liabilities

• Accounts Payable (A/P): This is the money you owe your suppliers. It’s driven by the cost of goods sold. You forecast it using Days Payables Outstanding (DPO).
• Accrued Expenses: These are expenses you've incurred but haven't paid yet, like employee wages or taxes. They are often forecasted as a percentage of sales.

Non-Current Liabilities

• Long-Term Debt: Like PP&E, debt should be managed in a dedicated schedule on your "Schedules" tab.

3. Forecasting Shareholder's Equity

Equity represents the owners' stake in the company.

• Common Stock: This usually remains constant unless you plan to issue new shares or buy back existing ones. In most simple models, you link the forecast period to the previous period's value.
• Retained Earnings: This is one of the most important links in the entire model. It connects your income statement directly to your balance sheet.

Bringing It All Together: The Cash Flow Link and Balancing

At this point, you've forecasted almost every line item. If you sum up your Total Assets and your Total Liabilities & Equity, they won't match. This is expected because we haven't determined our Cash balance yet. That's where the Cash Flow Statement comes in.

Linking the Cash Flow Statement

Now, it's time to build your simple cash flow forecast to find your ending cash balance.

1. Cash from Operations (CFO): Start with Net Income. Add back non-cash expenses like Depreciation. Then, account for changes in working capital (an increase in A/R is a use of cash, while an increase in A/P is a source of cash).
2. Cash from Investing (CFI): This is primarily your Capital Expenditures (CapEx), which is a use (outflow) of cash.
3. Cash from Financing (CFF): This includes cash from new debt issued, less debt repayments and dividends paid.

Once you sum these three sections, you get the Net Change in Cash for the year. The final step is:

Ending Cash Balance = Beginning Cash Balance + Net Change in Cash

The Beginning Cash Balance is the ending cash balance from the prior year.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Making it Balance

This is the magic moment. Take the forecasted Ending Cash Balance from your Cash Flow Statement and link it back to the "Cash and Cash Equivalents" line at the very top of your balance sheet for the corresponding forecast period. Your balance sheet should now balance!

To be sure, add a check at the bottom of your balance sheet:

Balance Check = Total Assets - (Total Liabilities + Total Equity)

This cell should read 0 (or a very small number due to rounding). Use Excel's conditional formatting to make this cell turn red if the value is not zero, so you can easily spot errors.

Tips for a More Accurate Forecast

• Document Your Assumptions: Keep all your assumptions - DSO, DPO, revenue growth, etc. - in one place on your "Assumptions" tab. This makes it easy to change them and see the impact on your model.
• Use Historical Ratios: Don't just guess your assumptions. Calculate them based on the average of your last 2-3 years of historical data to get a realistic starting point.
• Perform Scenario Analysis: Create a "Best Case," "Worst Case," and "Realistic" scenario by changing key drivers. This helps you understand the range of possible outcomes.
• Be Skeptical: If the results look too good to be true (or impossibly bad), re-trace your logic and your links. An omitted minus sign or an incorrect cell reference can throw everything off.

Final Thoughts

Building an integrated three-statement forecast in Excel is a powerful skill. By connecting your income statement, cash flow statement, and balance sheet, you create a dynamic model that gives you deep insight into your business's financial future. Following these steps helps transform what seems complex into a manageable and logical process for strategic planning.

Of course, manually building and maintaining these models in spreadsheets is time-consuming, especially when your live financial data lives in platforms like QuickBooks, Salesforce, or your CRM. We built Graphed to streamline this entire process. By connecting your data sources in a few clicks, we help you analyze performance, build real-time financial dashboards, and forecast key metrics using simple natural language - turning hours of spreadsheet work into a 30-second task.