Statement Financial Model: Step-by-Step Guide for Beginners

The Statement financial model is a basic skill that you must learn if you want to work in corporate finance, investment banking, private equity, or financial planning and analysis (FP&A). If you are getting ready for an interview, a timed modelling test, or making a business estimate, a well-built financial model will help you the most. Mastering a Statement Financial Model is crucial for long-term career success.

The feared unbalanced model is something that every financial analyst has had to deal with right before a big presentation. It’s very important to know where to look, how to fix problems, and how to set up your model from a blank Excel sheet. Building a reliable Statement Financial Model requires a disciplined approach to structural design.

This complete, SEO-friendly guide will explain what a 3-statement financial model is, how to build one correctly, and give you step-by-step instructions on how to do it. It will also show you safe ways to make sure your balance sheet always balances within your Statement Financial Model.

What does a 3-Statement Model mean?

The income statement, balance sheet, and cash flow statement of a business can all be predicted into the future with a 3-statement model, which is a fully integrated, dynamically linked financial model in Excel. This integrated architecture forms the core of any comprehensive Statement Financial Model.

A forward-looking financial model lets us look at how a business will do in the future based on different factors, while standard accounting only shows how a company has done financially in the past. Analysts can see how different business, investment, and funding choices will affect the company’s bottom line by making predictions about these statements using a holistic Statement Financial Model.

You can’t build a more complex financial model without first building a three-statement model. It’s not possible to make correct Discounted Cash Flow (DCF), Leveraged Buyout (LBO), or Mergers and Acquisitions (M&A) accretion/dilution models without a good Statement Financial Model acting as the foundation.

Figuring out periodicity

You need to choose the “periodicity” of your model before you open Excel to design your Statement Financial Model. Once a year, three times a year, once a month, or once a week?

• Annual Models: These are usually used for DCF estimates and LBOs, which need clear predictions for at least 5 years.
• Quarterly Models: These are often used in FP&A, credit analysis, and stock research where short-term drivers are important for the Statement Financial Model.
• Monthly and Weekly Models: These are very important for project financing, bankruptcies, and restructurings (like the 13-week cash flow model), where it’s important to keep track of cash flow from month to month or week to week.

The Model’s Most Important Parts

To make a 3-Statement Financial Model, you need to know the three main types of financial statements and how they vary from each other within a Statement Financial Model.

1. The Income Report (IS)

The income statement shows how profitable a business was over a certain time period, like a quarter or a year. It starts with total sales or income as the top line, takes away the Cost of Goods Sold (COGS) to get Gross Profit, and then takes away running costs (such as SG&A) to get EBITDA and EBIT. Finally, interest and taxes are taken out to get the Net Income at the end. It’s important to remember that the IS is made on an annual basis, which means that it includes things like depreciation that can’t be paid for with cash, requiring close tracking in the Statement Financial Model.

2. How Much Money You Have (BS)

The balance sheet is a picture of the company at a certain point in time, like the end of the reporting period. The income statement, on the other hand, covers a period of time. It shows the company’s assets and how those assets are paid for (liabilities and shareholders’ equity). The most important thing to remember about the balance sheet is that assets = liabilities + equity, which maintains structural balance in the Statement Financial Model.

3. The CFS (Cash Flow Statement)

The income statement has things that aren’t cash flows, so the cash flow statement just compares the changes from one year to the next in the balance sheet. It then changes net income to show real cash flows inside the Statement Financial Model. It is split into three parts:

• Operating Activities: Net Income plus non-cash adjustments and changes in working capital, plus cash from the main business activities.
• Investing Activities: Capital expenses (CapEx) and asset sales bring in or use cash for investing activities.
• Financing Activities: This includes cash flows involving lenders and owners, such as issuing and repaying loans, issuing shares of stock, and paying dividends.

Best Practices for statement financial model and Excel Rules

It is important to follow best practices in the business before starting the step-by-step build of your Statement Financial Model. Sticking to a tight structure makes your model clear, simple to check, and much less likely to make mistakes.

The Three Rules for Excel Modelling

1. All of the statements should be on the same tab: For example, for a single company, the Income Statement, Balance Sheet, and Cash Flow Statement should all be on the same page. This makes it easy to find formulas that depend on or come before others without having to switch between tabs, creating a streamlined Statement Financial Model.
2. Drives and Past Events Should Be Kept Separated: Your ideas (inputs) should not be mixed with your past or expected outcomes. The assumptions part should have all the important inputs, ideally at the top of the model. This way, users can easily change a variable and see how it affects the whole Statement Financial Model.
3. Simplify and Combine Historical Data: When you enter data from 10-Ks or public files, combine small line items. Aim for between 5 and 10 lines on each side of the balance sheet. If four assets make up 90% of all assets, put the other small things into a category called “Other” to make your Statement Financial Model easier to use.

Styles and Formatting

• Colour-coding: This is how everything on Wall Street does it. For hard-coded numbers (inputs), use blue font. For formulas and calculations, use black font. For links to other forms, use green font. For links to external files, use red font. Adhering to this maintains standard presentation in a Statement Financial Model.
• Rules for signs: On the income statement and cash flow statement, use negative signs for all costs and cash withdrawals. This keeps you from making mistakes when adding up numbers and lets you easily add up a column to get the net total instead of making complicated methods for adding and subtracting within the Statement Financial Model.
• Do Not Hard-Code Numbers in Formulas: Do not hide a number like 300 or 0.05 inside a formula. Instead, you should use the centralised assumptions block of your Statement Financial Model.
• Group, Don’t Hide: Never “Hide” rows or columns, because that hides important information. Use Excel’s Grouping tool instead. It has a plus/minus button that makes it easy to see when data has been compressed.

A Step-by-Step Guide on How to Make a Three-Statement Model

There are steps you need to take in order to build a fully connected model. Let’s break down the assembly of a Statement Financial Model into 8 steps that you can take.

Step 1: Enter financial data from the past

The first step to build a Statement Financial Model is to get three to five years’ worth of financial information from the company’s 10-Ks, SEC EDGAR records, or press releases. You can either write this information by hand or use a plugin like Capital IQ. Set up the fields so that they go from oldest to newest (for example, 2021, 2022, and 2023). Put the past numbers into your Excel sheet. Use blue text to show data that is hard-coded. Make sure that the past financials fit the way the business will be set up legally.

Step 2: Figure out the assumptions and drivers

Once you have the past data, you can make a part called “Assumptions and Drivers” to look into the future (usually for the next 5 to 10 years). Check past numbers like year-over-year sales growth, Gross Margin, EBITDA margin, and working capital days (for example, Days Sales Outstanding for Accounts Receivable) to anchor your Statement Financial Model.

Your beliefs need to be backed up by facts. For instance, a unit-by-unit analysis (Annual Sales Volume × Net Sales Price) that takes into account cost inflation could lead to more sales, just like it does in business case studies like Henderson Manufacturing, enriching your Statement Financial Model.

Step 3: Make a guess about the income statement

Assume the following, then make a prediction for the Income Statement all the way down to EBITDA. First look at the top-line income. This is what the rest of the plan is based on. Then, look at your Cost of Goods Sold (COGS) and working Expenses (SG&A) either as a share of your income or as unit costs, such as the cost of raw materials and working labour per unit. When you get to Depreciation, Interest, and Taxes, stop because they need their own plans to go with them in the Statement Financial Model.

Step 4: Make a plan for your capital assets (PP&E and D&A Schedule).

Next, you need to make predictions about long-term assets to figure out your depreciation cost for the Statement Financial Model. Make a roll-forward plan for PP&E (Property, Plant, and Equipment). To find the ending PP&E, add up the beginning PP&E and subtract the depreciation expense. This gives you the ending PP&E. A straight line can be used to figure out depreciation over the useful life of the goods. Once you have the numbers, you can add this depreciation cost to your income statement.

Step 5: Fill out the Balance Sheet (but leave out the cash).

Now, guess what the things on the Balance Sheet that show working capital will be. The things that drive these are revenue and running costs. Project Accounts Receivable (based on days of collection), Inventory (based on days of movement), and Accounts Payable using the assumptions you’ve worked out for your Statement Financial Model.

Changes in things like working capital are very important because, even though the numbers on the balance sheet are important, it is the Change in Working Capital that affects the company’s cash balance in the end within the global Statement Financial Model.

Step 6: Make a schedule for the debt and a forecast of the financing activity

You need the Interest Expense to finish the Income Statement. To keep track of current debt amounts, required amortisation (principal repayments), and interest, you will need a Debt Schedule tied to your Statement Financial Model. A business will often have different types of debt, such as:

• A revolving credit facility, or revolver: is a short-term, flexible line of credit that is used when cash is tight.
• Senior debt: is standard bank debt that usually needs to be paid back in order to lower the lender’s risk.
• Subordinated debt: is riskier debt that is lower in the capital structure. It usually has set interest rates.

Each payment on the debt schedule should be moved up one month at a time: ending debt = beginning debt + new issues – principal repayments. Use the interest rate applied to the average debt amount to figure out how much interest you have to pay.

As a side note on circularity, Excel has a “circular reference” when you try to figure out interest on the average loan amount. Interest costs cut into Net Income, which cuts into cash flow. This changes how much debt needs to be taken on or returned, which changes interest costs again. To handle planned loops in your Statement Financial Model, you need to turn on “Iterative Calculations” in Excel (Alt + F + T > Formulas > Enable Iterative Calculation). Always make a circuit breaker (a toggle switch) so that you can quickly stop the loop when you’re looking for problems.

Step 7: Fill out the income statement

Now that Depreciation and Interest Expense have been worked out in their own supporting charts, connect them back to the Income Statement of your Statement Financial Model. Find your Earnings Before Taxes (EBT), then use the expected tax rate to find your tax liability, and finally, find your Net Income.

Step 8: Fill out the Cash Flow Statement and connect the cash flows.

The last step is to make the Cash Flow Statement, which shows how everything fits together. The great thing about the CFS is that it doesn’t require you to make any direct predictions; each line item is just a link to another part of the Statement Financial Model.

• From the Income Statement, start with Net Income.
• Include costs that can’t be paid for with cash, like depreciation and amortisation.
• Add the Changes in Operating Working Capital from the Balance Sheet. Remember that when an asset goes up in value, cash goes out, and when a debt goes up in value, cash comes in.
• Take the amount of money you spent on investing activities and subtract it from your PP&E plan.
• Show when debt is issued or repaid and when dividends are paid out in financing activities.

To find the Net Change in Cash, add up all three parts. To find the Ending Cash Balance, add this net change to the Beginning Cash Balance. Finally, connect this ending cash balance directly to the Balance Sheet’s Cash line to tie up your Statement Financial Model.

Putting the Statements Together: The Magic of Combining

A three-statement approach is most useful when it is used together. If you change one assumption, it will have an effect on all three claims. These are the exact ways that the statements fit together in a cohesive Statement Financial Model:

• Net Income moves from the bottom of the Income Statement to the top of the Cash Flow Statement (Operations) and then to Retained Earnings in the Equity part of the Balance Sheet.
• Net PP&E goes down on the Balance Sheet because of depreciation. On the Income Statement, it shows up as a cost, and on the Cash Flow Statement, it shows up as a non-cash charge.
• On the Balance Sheet, capital expenditures add to PP&E, and in the Cash Flow Statement, they show a cash loss in the Investing area.
• Changes in Working Capital are found on the Balance Sheet and sent straight to the Operating part of the Cash Flow Statement.
• When you issue and repay debt, it changes the line that says “Debt” on the Balance Sheet and shows up as cash flows in the Financing part of the Cash Flow Statement. The Interest Expense on the Income Statement is based on the amount of debt that was taken on.
• In the Cash Flow Statement, the line item for Cash Assets is set by the bottom line item for Ending Cash.

How to Make Your Balance Sheet Equal (In 3 Easy Steps)

You add up the Balance Sheet late at night, when your model is almost done, but you find that Total Assets do not equal Total Liabilities + Equity. Your Statement Financial Model doesn’t work right.

1. Don’t use fake “plugs.”

For example, don’t write a formula that says Retained Earnings must equal Assets minus Liabilities. This breaks the model’s integrity and hides problems that are really there. Instead, to find the mistake, follow these three steps that have been proven to work by experts to rescue a broken Statement Financial Model:

• Step 1: Make sure your sums and subtotals are correct. A missed line item in an SUM formula is the easiest and most common mistake people make. For instance, make sure that Inventory is included in Total Current Assets. Use the Row Differences tool in Excel to show where the sums don’t match up in your Statement Financial Model.
• Step 2: Look over the Cash Flow Statement line by line. Cash and Equivalents is the only real account that balances the Balance Sheet on its own. The Cash Flow Statement must match every thing on the Balance Sheet, whether it’s an asset, a debt, or an equity line to keep the Statement Financial Model valid.
  • Did Net Income go to Earnings Kept?
  • Did depreciation really lower PP&E?
  • How did the changes in working capital go? Did they make sense? (For example, if the BS inventory goes up, the CFS cash must go out).
• Step 3: Look for small changes in the Balance Sheet. In the event that it still doesn’t balance, there is at least one line on the Balance Sheet that changes from one period to the next without an equal change in the Cash Flow Statement. If you thought that “Other Long-Term Assets” would grow based on a percentage of sales but forgot to mark that cash loss as an investment on the Cash Flow Statement, your Statement Financial Model will never be balanced.

10 Common Reasons for Imbalance and How to Avoid Them

Keep an eye out for these common mistakes that make models lose their balance as you build and fix your Statement Financial Model:

1. There are hidden mistakes in SUM formulas that cause incorrect subtotals. These formulas don’t include all important assets or liabilities.
   
2. Changes in the Forecast Formula: Using different ways to calculate from one year to the next (for example, a hard code in Year 2 and a percentage driver in Year 3).
   
3. Unintentional Circular References: Loops that happen by accident because of wrong references, which cause calculations to go on forever. “Always look for precedents to find these!”
   
4. References are not lined up correctly; they are pointing to columns of past data instead of prediction input tabs inside the Statement Financial Model.
   
5. Hidden Rows and Columns: Important information is hidden and not included in the main totals. To make sure, always use Unhide (Alt + H + O + U).
   
6. Wrong Signs: Turning the sign on changes in working capital. Remember that when assets go up, cash flows out, and when liabilities go up, cash flows in.
   
7. Lack of Cash Flow Items: Every change in a non-cash account on the balance sheet must show up on the cash flow statement.
   
8. Artificial Plugs: As we already said, using plugs to force a balance gives you a false sense of safety. Instead, let the flexible “Cash” and “Revolver” plans handle any shortfalls in funds on their own.
   
9. Fixing mistakes in the forecast by hand: putting numbers in by hand during the projection period limits the model’s ability to adapt. To find illegal hardcodes, press F5 and then select “Special” and “Constants.”
   
10. Beginning Balance vs. Ending Balance: In cumulative accounts like Retained Earnings or PP&E, using the beginning balance instead of the ending balance is wrong.

Finally, Getting Your Model to the Next Level

At first, making a 3-statement financial model from scratch might seem hard, but in the end, it’s a smart way to keep track of where your money is going. When you have strong analysis tools like Scenario and Sensitivity Analysis ready, you can add your key statements and switch between Base Cases, Best Cases, and Worst Cases within your Statement Financial Model.

Remember that financial modelling requires a deep understanding of business drivers, a keen eye for detail, and skill with Excel. If you follow best practices like putting assumptions in one place, linking plans correctly, and not using fake plugs, you will not only pass technical modelling tests but also become a highly sought-after corporate finance master with a bulletproof Statement Financial Model. Have fun modelling!

For more detailed insights, watch the complete video below.