Financial Modelling in Excel: Build Your First 3-Statement Model (Step-by-Step)

What is Required to Build a 3-Statement Model in Excel

To build a 3 statement model in financial modelling, you need a few skills, a few early decisions and clean source data. Sort these out first, as it will save your multiple hours later.

1. Skills Required

• Accounting matters most. You must know how the three statements connect and what each line item means. Without that, you can copy a template but you cannot fix it when it breaks.
• Excel comes second. Basic formulas, cell references and a few keyboard shortcuts are enough for a first model.

2. Where to Find Financial Statements

Use the company’s annual report. Listed companies also file results with the stock exchange. For Indian companies, check the BSE and NSE websites and the company’s investor relations page.

Get at least three years of history, but five is better. Growth rates, margins and working capital ratios come from it.

3. Formatting Best Practices

Good formatting makes a model easy to read and audit. Follow these rules for 3-statement model practice:

• Blue for hardcoded inputs.
• Black for formulas.
• Green for links to other sheets.
• Keep one unit scale throughout, such as ₹ crore.
• Keep inputs on a separate assumptions sheet.
• Never mix a hardcoded number inside a formula.

Must read: Financial Statement Analysis: Methods, Types, Ratios, and Examples

How to Build a 3-Statement Model in Excel: 9 Steps

Building a 3-statement financial model is easier when you follow a fixed order. Each step uses the output of the one before it. Skip ahead and the links break.

Step 1: Enter the Historical Financial Statements

Open a new workbook. Make three sheets: Income Statement, Balance Sheet and Cash Flow. Enter three to five years of data from the annual report. Put the oldest year on the left and the latest on the right. Annual reports often show them the other way round, so swap them.

Follow a few rules while entering the data:

• Show revenue as positive and expenses as negative. Totals are then easier to check.
• Combine small line items. Aim for about five to ten lines on each side of the balance sheet.
• Keep the cash flow statement to three sections, with only a few lines in each.
• Use one unit throughout, such as ₹ crore.

Check your work as you go. Sum each section and compare it with the totals in the report. On the balance sheet, assets must equal liabilities plus equity every year. If they don’t, fix it now. A small error here will carry into every forecast year.

Step 2: Build the Assumptions Sheet

Every number that drives the forecast goes on one separate sheet. Call it “Assumptions”. Do not type growth rates or margins inside formulas. Every forecast formula should point to this sheet.

First, calculate the historical ratios from Step 1. Then use them to set the forecast.

• Revenue growth: year-on-year change in revenue.
• Margins: COGS and operating expenses as a percentage of revenue.
• Working capital days: receivable days (receivables ÷ revenue × 365), inventory days (inventory ÷ COGS × 365) and payable days (payables ÷ COGS × 365).
• Capex and depreciation: each as a percentage of revenue.
• Tax rate: tax paid ÷ profit before tax.
• Dividend payout ratio: dividends ÷ net income.

Look at the trend before you pick a number. If margins have been stable for four years, keep them stable. If they have moved sharply, find out why. Don’t simply repeat last year’s figure.

Mark every input in blue. Anyone opening the file should see at once which cells are inputs and which are formulas.

Step 3: Forecast the Income Statement

Now you can start forecasting. The income statement is the first to build, because the other two statements depend on it.

Go top to bottom, one line at a time.

• Revenue. Take last year’s revenue and multiply it by one plus the growth rate from the Assumptions sheet. Growth rate is the simplest method. Analysts also build revenue from units sold and price, but that comes later.
• Costs. Forecast COGS as a percentage of revenue. Do the same for operating expenses. Gross profit and EBITDA follow from these.
• Depreciation. For now, use a percentage of revenue. In Step 4, you will replace it with a link to the fixed asset schedule.
• Interest. Leave this line empty for now. It depends on the debt schedule, which you will build in Step 7. Put a placeholder in so the layout is ready.
• Tax. Multiply profit before tax by the tax rate. If profit is negative, tax should be zero.
• Net income. This is profit after tax. It flows into retained earnings on the balance sheet and starts the cash flow statement. If the company is listed, divide it by the share count to get earnings per share.

Read: Top 22 Tools of Financial Statement Analysis

Step 4: Forecast the Balance Sheet

The balance sheet is a snapshot at the end of each year. Most of it is driven by the income statement you just built. Revenue and costs decide how much working capital and how many fixed assets the business needs.

1. Working Capital Items

Use the day's assumptions from Step 2. They turn income statement numbers into balance sheet numbers.

• Receivables: revenue × receivable days ÷ 365.
• Inventory: COGS × inventory days ÷ 365.
• Payables: COGS × payable days ÷ 365.

2. Fixed Assets Roll-Forward (PP&E)

Build a small roll-forward schedule: opening PP&E, plus capex, minus depreciation, equals closing PP&E. Capex comes from the Assumptions sheet. Link the depreciation back to the income statement, so both sheets use the same number. 

3. Debt and Equity

For now, hold debt flat. You will build the full schedule in Step 7. Share capital stays flat too, unless the company issues or buys back shares.

Retained earnings need a formula. Take last year’s balance, add net income and subtract dividends. Dividends are net income × payout ratio.

Leave cash alone. It comes from the cash flow statement, which is the next step.

Want to go beyond building a 3-statement model and lead financial decisions? Explore upGrad’s MBA from Liverpool John Moores University with IIM Udaipur Certification and choose a Finance specialisation covering financial modelling and corporate finance.

 Step 5: Build the Cash Flow Statement

The cash flow statement is different from the other two. You don’t forecast it. You build it from the changes in the income statement and balance sheet. It is a reconciliation.

That is why every line must be a link. Never type a number here. A single hardcode will break the balance sheet, and it is hard to spot.

• Operating activities. Start with net income. Add back depreciation, since no cash left the business. Then adjust for working capital. An increase in receivables or inventory uses cash, so subtract it. An increase in payables brings cash in, so add it. The result is cash flow from operations.
• Investing activities. For a simple model, this is capex. Show it as a negative number. If the company buys or sells investments or businesses, those go here too.
• Financing activities. Include the change in debt, any share issue or buyback, and dividends paid. Debt raised is positive. Dividends and repayments are negative.

Step 6: Link the Three Statements

On the asset side, start with last year’s balance and subtract the matching cash flow line. Outflows are negative on the cash flow statement, so subtracting them increases the asset. On the liabilities and equity side, add the cash flow line to last year’s balance.

Apply it line by line:

• Cash: last year’s cash plus the net change in cash.
• PP&E: last year’s balance plus capex, minus depreciation. This is the roll-forward from Step 4.
• Debt: last year’s balance plus the change in debt from the financing section.
• Retained earnings: last year’s balance plus net income, minus dividends.
• Share capital: last year’s balance plus any share issue.

Link cash last. Everything else on the balance sheet should be done first.

Also read: Asset Liability Management: Meaning, Process, and Role in Banks

Step 7: Add Debt and Interest

So far, debt has been flat and interest has been a placeholder. Now you replace both with a proper schedule. This is also where the model gets harder, so build it slowly.

1. Simple Debt Schedule

Use a roll-forward, like PP&E: opening debt, plus new borrowing, minus repayment, equals closing debt. Enter repayments as inputs based on the loan terms. New borrowing is usually zero unless the model needs more funding.

For interest, calculate the historical rate (interest expense ÷ average debt) and set the forecast rate on the Assumptions sheet. Interest expense is rate × debt balance. Link it to the income statement to replace the placeholder.

2. Cash and Revolver as Plugs

A good model covers its own funding gaps. Set a minimum cash balance on the Assumptions sheet. If closing cash falls below it, a revolver draws the shortfall. If cash is above it, the surplus builds up on the balance sheet.

Revolver draw = the larger of zero and (minimum cash minus cash before the revolver). Add the draw to financing activities on the cash flow statement, and add the revolver balance to debt on the balance sheet.

3. Handling Circular References

Plugs create a loop. Interest depends on debt, debt depends on cash flow, and cash flow depends on net income, which depends on interest. The 3-statement model excel calls this a circular reference and shows a warning.

The simple fix is to calculate interest on the opening debt balance, not the average. The loop disappears, and the small loss in accuracy is fine for a first model.

Step 8: Check the Model

A model that looks right can still be wrong. Test it before you trust any output in financial modelling.

• Add a row at the bottom of the balance sheet. The formula is total assets minus total liabilities and equity. It should show zero every year, both historical and forecast.
• Make it visible. Add conditional formatting so the cell turns red if the result is anything other than zero. Round the formula to two decimals, so tiny floating-point differences don’t trigger false alarms.

Two more tests catch what the balance check misses. First, change an assumption, such as revenue growth from 10% to 5%. Every number downstream should move. If something stays frozen, it is hardcoded. Second, check the direction. Raise receivable days and receivables should rise, while operating cash flow should fall. If a number moves the wrong way, a formula is broken.

Common Reasons the Balance Sheet Doesn’t Balance

When the check fails, the cause is usually one of these:

• A hardcoded cash flow line. It doesn’t update when the balance sheet changes.
• A sign error. An asset increase was added instead of subtracted.
• A missing link. Depreciation, dividends or a debt change is on one statement but not the others.
• A wrong opening balance. Year one of the forecast doesn’t match the last historical year.
• A broken subtotal. A sum range skips a row.

Also read: Capital Budgeting Techniques: Types, Formula, Examples, Advantages, and Comparison

Step 9: Add Scenarios and Sensitivities

Once the model balances, you can do a 3-statement model test, how it behaves under different assumptions. Two tools do this in financial modelling.

• Scenarios. Build three sets of assumptions on the Assumptions sheet: base, best and worst. Each has its own revenue growth, margins and capex. Add a drop-down cell that picks the active case. Use CHOOSE or INDEX to pull the selected values into the model. One click then changes the whole forecast.
• Sensitivities. A sensitivity table shows how one output changes with two inputs. For example, you could test net income against revenue growth and gross margin. Use Excel’s Data Table feature under What-If Analysis. Link one input to the row and the other to the column.

Common Mistakes Beginners Make

Most first models fail for the same few reasons. None of them are hard to fix once you know what to look for.

• Trusting a balanced model. A balance sheet that balances only proves the links work. It does not prove the forecast makes sense. If revenue grows 40% a year with flat margins, the model will balance and still be wrong. Always ask whether the numbers are realistic.
• Forecasting without looking at history. Picking a growth rate from a guess, not from three to five years of data, is one of the most common errors. Check the trend first.
• Adding complexity too soon. Multiple products, tax adjustments and debt tranches multiply the places where an error can hide. Get a simple version to balance first. Then add detail.

Also read: Financial Management: Objectives, Scope, and Importance

Conclusion

Your first 3 statement model will take time. Expect to spend most of it finding why the balance sheet is off. That is normal, it is also where you learn the most. Start with a simple company and annual numbers. Get the balance check to zero before adding anything else. Then add debt, scenarios and detail, one piece at a time.

Once you can build this model, you can build the ones that sit on top of it. A DCF valuation is the natural next step. After that come LBO and M&A models. All of them depend on the foundation you built here.

The best way to learn is to build. Pick a listed company, download its annual report and start from a blank sheet.

Book a one-on-one consultation with upGrad counsellors to get personalized guidance on selecting the best course for your career.