3 statement financial model case study

Join 307,012+ Monthly Readers

Get Free and Instant Access To The Banker Blueprint : 57 Pages Of Career Boosting Advice Already Downloaded By 115,341+ Industry Peers.

• Break Into Investment Banking
• Write A Resume or Cover Letter
• Win Investment Banking Interviews
• Ace Your Investment Banking Interviews
• Win Investment Banking Internships
• Master Financial Modeling
• Get Into Private Equity
• Get A Job At A Hedge Fund
• Recent Posts
• Articles By Category

The 3-Statement Model: Full Tutorial for a Timed 90-Minute Modeling Test

If you're new here, please click here to get my FREE 57-page investment banking recruiting guide - plus, get weekly updates so that you can break into investment banking . Thanks for visiting!

A long time ago, we received one complaint/criticism more than any other:

“All your models start from templates ! What about case studies where I have to start from a blank Excel sheet and do not get any data, formatting, or schedules?”

We did this mostly to save time : entering the historical information and setting up the formatting, layout, etc., usually takes at least 30 minutes and sometimes up to several hours.

But there is also value in learning how to build a full model from scratch .

We’ll cover a 90-minute 3-statement modeling test here and explain how to use the company’s financials, 10-K, and investor presentation to do everything.

WARNING: You must have a decent-to-high Excel proficiency to follow along and finish this in the allotted time.

The video walkthrough below has captions for some of the Excel shortcuts, but it’s not a full Excel tutorial, and we assume you already know the basics.

What is a 3-Statement Model?

In financial modeling , the “3 statements” refer to the Income Statement, Balance Sheet, and Cash Flow Statement.

Collectively, these show you a company’s revenue, expenses, cash, debt, equity, and cash flow over time, and you can use them to determine why these items have changed.

In a 3-statement model, you input the historical versions of these statements and then project them over a ~5-year period.

In real life, you do this to value companies, model transactions, and determine whether the company’s expected growth, margins, and cash flow metrics are plausible.

For example, if the company claims it will generate $5 billion of Free Cash Flow and use it to repay $1 billion of Debt and issue $4 billion in Dividends, is that realistic?

Will the company generate more or less cash flow? Might it need outside financing? How do the numbers change if market conditions worsen?

Banks like to test this topic because it’s a quick way to assess who’s proficient in Excel, accounting, and financial modeling.

If you cannot read or interpret a company’s historical financial statements, you won’t be working on complex deals anytime soon.

Types of 3-Statement Modeling Tests

Most 3-statement models and case studies fall into one of three categories:

• Blank Sheet / Strict Time Limit: These are more about working quickly, knowing the Excel shortcuts, simplifying, and making decisions under pressure.
• Template / Strict Time Limit: These tests are more about entering the correct formulas, justifying your assumptions, and answering questions based on your model’s output.
• No Strict Time Limit: These case studies are more about using outside research and data to justify your assumptions for the revenue, expenses, cash flow, etc. You might also have to give a presentation based on your findings.

The “strict time limit” could be anything from 30 minutes to 3-4 hours, and the complexity increases as the time limit increases.

The “no strict time limit” type might give you several days or even 1 week+.

There is still a deadline, but you don’t need to rush around like a madman to finish.

The 90-Minute 3-Statement Model from a Blank Sheet

For this tutorial, I picked an example where you start from a blank sheet and review the company’s filings and presentations .

So, you must demonstrate Excel proficiency and the ability to interpret data and make reasonable assumptions.

You can get the case study prompt, the company documents, and the completed Excel file below:

• 90-Minute 3-Statement Model – Case Study Prompt (PDF)
• 90-Minute 3-Statement Model – Completed Excel File (XL)
• Overview of Main Points in 90-Minute 3-Statement Modeling Test – Slides (PDF)
• Otis – 10-K (PDF)
• Otis – 10-K in Excel Format – Raw (XL)
• Otis – User-Friendly 10-K in Excel with Swapped Columns (XL)
• Otis – Investor Presentation (PDF)

There is no “blank” or “beginning” file because we create a new sheet in Excel and enter everything from scratch in this tutorial.

You can get the video version of this entire tutorial below:

Table of Contents:

• 2:35: What is a 3-Statement Modeling Test?
• 5:54: Part 1: Inputting the Historical Financial Statements
• 15:31: Balance Sheet Entry
• 24:14: Cash Flow Statement Entry
• 35:11: Part 2: Income Statement Projections
• 50:12: Part 3: Balance Sheet Projections
• 57:51: Part 4: Cash Flow Statement Projections
• 1:07:12: Part 5: Linking the Statements
• 1:10:59: Part 6: Debt and Stock Repurchases
• 1:19:16: Part 7: Model Checks, Review, and Final Comments
• 1:22:35 : Recap and Summary

This example is not taken from our courses – it’s new for this article – but it is similar to some of the case studies in our Core Financial Modeling course :

Core Financial Modeling

Learn accounting, 3-statement modeling, valuation/DCF analysis, M&A and merger models, and LBOs and leveraged buyout models with 10+ global case studies.

The full course has 3-statement models with and without templates for additional practice. If you want more advanced 3-statement models with additional schedules, the Advanced Financial Modeling course might be more appropriate since it goes into far more depth in each case study:

Advanced Financial Modeling

Learn more complex "on the job" investment banking models and complete private equity, hedge fund, and credit case studies to win buy-side job offers.

3-Statement Model, Part 1: Inputting the Historical Statements

You could attempt to input the data by copying and pasting from the PDFs, but it’s far more efficient to link directly to the Excel or CSV files.

A few tips:

• Swap the Excel columns, so they go from oldest to newest (see below).

• On the Income Statement , use positives for revenue and other income sources and negatives for all expenses and outflows, as it will be easier to check your work that way.

• Consolidate smaller line items as much as possible; you ideally want ~5 items on each side of the Balance Sheet (maybe 10 at the most) and only a few items in each section of the Cash Flow Statement.

As you proceed, you can check your work by summing up the sections and comparing the totals to the company’s numbers.

3-Statement Model, Part 2: Income Statement Projections

The case study document says that we need to use “something more complicated” than a simple percentage growth rate for Revenue:

But the investor presentation and 10-K do not make it easy to find unit-by-unit data.

We’d ideally like to project new escalators and elevators sold, forecast the average prices, and assume a certain percentage of these new units go into “Service Units,” generating Services revenue in future periods.

It would also be helpful to know about something like the degree of operating leverage , so we could better forecast different expenses.

But we can’t find enough solid data to do this within the strict time limit, so we simplify and use Market Share and Market Size to project the New Equipment Revenue, with the Services Revenue based on the company’s estimates for the growth in Service Units:

The Cost of Products, Cost of Services, and Operating Expenses are simple percentages of Revenue, and the Taxes and NCI Net Income are based on average historical percentages:

3-Statement Model, Part 3: Balance Sheet Projections

In this part, we focus on projecting the Working Capital line items , such as Accounts Receivable (AR), Inventory, and Accounts Payable.

With more time/information, we might also use metrics like the Days Sales Outstanding or Cash Conversion Cycle to forecast some of these items.

The key point is that the absolute numbers do not matter .

What matter is the Change in Working Capital on the Cash Flow Statement since that affects the company’s cash flow and ability to repay Debt and repurchase Stock.

If the Change in WC has been positive as the company has grown, it should stay positive and in the same range in the future (and vice versa if it has been negative or near-0).

We make most of these items simple percentages of Income Statement lines such as Revenue, COGS , or Total Expenses:

Once we do this and set up the projections, we can calculate the Change in Working Capital on the Cash Flow Statement to check our work:

We also simplify the Operating Leases here by making the Operating Lease Assets a percentage of Operating Expenses and assuming the Operating Lease Liabilities change by the same amount each year.

Lease accounting is more complicated in real life and under IFRS, but this approach is fine for a U.S.-based company.

3-Statement Model, Part 4: Cash Flow Statement Projections

Most of the key line items here, such as CapEx and Depreciation & Amortization, are simple percentages of Revenue:

A few line items, such as the ones for Pension Contributions and Noncontrolling Interests , are more complex to project “correctly,” but we don’t have time to do so here.

One exception to these simple rules is the Dividends line, which we forecast based on the Dividend Payout Ratio (i.e., Dividends / Net Income) (for more, see our tutorial on the dividend yield ).

In this case, the company provides specific guidance on the Dividend Payout Ratio, so we increase it slightly over the period to match their targets (see below).

The bolt-on acquisitions are also a bit different because the company estimates $50 – $100 million per year in acquisition spending in its investor presentation , so we pick the middle of the range and assume $75 million each year:

Strangely, CapEx is below D&A in each projected year, but it’s not necessarily “wrong” for a low-growth company like this one.

We would examine this point and refine these projections if we had several hours or days to complete this case study.

3-Statement Model, Part 5: Linking the Statements

We already have the Working Capital items and the Operating Lease Assets and Liabilities linked on the Balance Sheet, so there are only a few items left to complete.

The main rules are:

• Assets Side – When linking an Asset to a line on the CFS, you start with the old Asset on the Balance Sheet and subtract the matching line on the CFS. This is because cash outflows represent increases in Assets, and cash inflows represent decreases in Assets .
• Liabilities & Equity Side – It’s the opposite: add the line items on the CFS to the old numbers on the Balance Sheet.

Here’s what we do for the remaining line items:

• Cash: Old Cash Balance + Net Change in Cash on the CFS.
• PP&E/Goodwill/Intangibles: This simplified/consolidated line item equals the old balance minus CapEx minus D&A. Due to the signs on the CFS, CapEx increases this number, and D&A decreases it.
• Other Assets: Start with the old number and subtract Acquisitions and the “Pensions/Other” line.
• Total Debt: Old Debt Balance + Change in Debt from the CFS.
• Noncontrolling Interests (NCI) : Old NCI Balance + NCI Net Income from the CFS + NCI Dividends from the CFS.
• Common Shareholders’ Equity (CSE): Old CSE + Net Income + Dividends + Stock Repurchases + Other Items + FX Rate Effects.

This last one is a “catch-all” for everything else on the CFS that has not yet been reflected on the Balance Sheet, and it’s sometimes also known as the Statement of Owner’s Equity .

Our Balance Sheet balances after completing these links, which is a good sign:

But we’re not done because the Change in Debt, Stock Repurchases, and Interest Expense lines are still blank.

3-Statement Model, Part 6: Debt and Stock Repurchases

The case study document tells us to “follow company guidance” for these last few line items.

On slide 41 of their investor presentation , Otis provides an estimated percentage split of its Free Cash Flows over the next 3 years:

We already have the Dividends and Acquisitions, so we’ll use a simple logical check for the Debt and Stock Repurchases based on the $3 billion Minimum Cash from the case document :

• Step 1: Does the company have Excess Cash Flow in this period? In other words, is its Beginning Cash + Net Change in Cash – Minimum Cash a positive number? If so, it can use that cash flow to repay Debt principal and repurchase Stock.
• Step 2: Based on the chart above, we assume an 85% / 15% split between Stock Repurchases and Debt Principal Repayments.
• Step 3: If the company has a Cash Flow Deficit , i.e., Beginning Cash + Net Change in Cash – Minimum Cash is negative, it must issue additional Debt to fund its operations.

You can see the logic below:

With these formulas, we can now add these links to the Cash Flow Statement and set the “Other” line item in Cash Flow from Financing to ~2% of Debt Issuances to represent the issuance fees.

The last line item is the Interest Expense on the Income Statement.

We can calculate the average interest rate on Debt in the previous years, but we don’t know how it will change in the future.

Interest rates were rising at the time of this case study, but if the company’s Debt has fixed rates and matures far into the future, it may not matter.

We can search for “long-term debt” in the 10-K and get a quick answer:

Since most of this company’s Debt matures after the 5-year projection period, the average rate probably won’t increase by that much in this period.

But there are ~$3.4 billion of maturities in the next 5 years, so we increase the average interest rate from 2.0% to 3.5% and use these numbers to calculate the Interest Expense:

To avoid circular references, we can use the Beginning Debt balance to calculate the interest expense as well (for more, see our tutorial on how to find circular reference in Excel ).

3-Statement Model, Part 7: Model Checks, Reviews, and Final Comments

At a high level, this model confirms that most of the company’s claims are reasonable.

For example, Otis generates just over $5 billion in FCF over the next 3 years, and it spends the expected amounts on Dividends, Acquisitions, and Stock Repurchases:

Its Free Cash Flow Conversion, which the company defines as FCF / Net Income, also stays well above 100%.

We’ve completed the model and met the requirements within the 90-minute time limit, so this attempt was successful.

However, there are some issues that we would fix with more time and resources:

• Formatting – It’s not pretty right now. We must clean up the number formats, add input boxes for the projections, fix the color coding, add headers/footers, etc.
• Revenue, Expense, and Cash Flow Detail – It’s better to project Revenue based on individual units sold and link the Product and Service segments to each other, such that New Units Sold drives Service Revenue in future periods; items like Operating Expenses should be linked to the Employee Count, and CapEx should be linked to the company’s production capacity.
• Scenarios – Finally, we always evaluate companies across multiple scenarios in real life. What happens if the market growth changes? What if the company’s market share falls? What if its expenses rise? This model is not robust enough to support these scenarios or sensitivities.

How to Master the 3-Statement Model

This example is more difficult than the average 3-statement modeling test.

If you don’t have moderate-to-high Excel proficiency, you could easily spend an entire day (or more) on this.

But if you can finish in 2-3 hours, you’re at the level where you can improve your times with repeated practice and eventually do this in 90 minutes or less.

You don’t need to score 100% to “pass” these tests; the median scores tend to be very low.

Your goal should be to finish the model , and if you can’t complete everything, simplify so that you can answer at least the main questions by the end.

If you have an upcoming 3-statement modeling test, get as many examples as possible and complete them.

If you can’t find good examples, pick companies you follow, download their statements and investor presentations, and do what we did here: start from scratch and give yourself a few hours to build a simple 3-statement model.

If you improve over time and find it interesting to pick apart companies and business models, great.

If not… well, maybe the finance industry is not for you.

Further Learning

You might be also interested in this tutorial on balance sheet forecasting .

About the Author

Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street . In his spare time, he enjoys lifting weights, running, traveling, obsessively watching TV shows, and defeating Sauron.

Free Exclusive Report: 57-page guide with the action plan you need to break into investment banking - how to tell your story, network, craft a winning resume, and dominate your interviews

Learn Valuation and Financial Modeling

Get a crash course on accounting, 3-statement modeling, valuation, and M&A and LBO modeling with 10+ global case studies.

• Recently Active
• Top Discussions
• Best Content

By Industry

• Investment Banking
• Private Equity
• Hedge Funds
• Real Estate
• Venture Capital
• Asset Management
• Equity Research
• Investing, Markets Forum
• Business School
• Fashion Advice
• Financial Modeling Resources

3 Statement Model

A free guide on three statement models.

Matthew started his finance career working as an investment banking analyst for Falcon Capital Partners, a healthcare IT boutique, before moving on to  work for Raymond James  Financial, Inc in their specialty finance coverage group in Atlanta. Matthew then started in a role in corporate development at Babcock & Wilcox before moving to a corporate development associate role with Caesars Entertainment Corporation where he currently is. Matthew provides support to Caesars'  M&A  processes including evaluating inbound teasers/ CIMs  to identify possible acquisition targets, due diligence, constructing  financial models , corporate valuation, and interacting with potential acquisition targets.

Matthew has a Bachelor of Science in Accounting and Business Administration and a Bachelor of Arts in German from University of North Carolina.

Prior to becoming our CEO & Founder at Wall Street Oasis, Patrick spent three years as a Private Equity  Associate for Tailwind Capital  in New York and two years as an Investment Banking Analyst at Rothschild.

Patrick has an  MBA  in Entrepreneurial Management from The Wharton School and a BA in Economics from Williams College.

• What Is A Three-Statement Model?

3 Core Elements Of A Three-Statement Finance Model

• Understanding A Three-Statement Model
• How To Build A Three-Statement Model?

Free WSO Financial Modeling Templates

What Is a Three-Statement Model?

A three-statement model is a dynamically integrated financial model developed by linking together a company’s three primary statements. This is one of the most important models as it serves as a base for other complex models, such as the Leveraged Buyout (LBO) Model or the Discounted Cash Flow (DCF) Model .

It uses the numbers from the company’s historical financial statements as base inputs, makes some assumptions about the company’s operations in the future, and forecasts the figures in the three statements based on these inputs and assumptions.

The purpose of this model is to project what the company’s financial health might look like if certain decisions are made or if certain assumptions materialize in the future.

In this article, we will explain what the three statements are, what a three-statement model is, and how to build it.

Key Takeaways

• A three-statement model integrates a company's income statement, balance sheet, and cash flow statement. It serves as a foundation for complex financial models, offering insights into a company's financial health based on historical data and future assumptions.
• The income statement reflects earnings and profitability, the balance sheet showcases the financial position at a specific time, and the cash flow statement tracks cash movement over a period. These statements, interconnected, provide a holistic view of a company's performance.
• Utilizing templates and financial modeling tools simplifies the process. Proper formatting, attention to detail, and understanding the relationships among statements are essential skills for anyone involved in financial analysis and decision-making.

The three financial statements are the income statement , the balance sheet , and the cash flow statement . The information in each of these statements is linked to the information in the other two statements.

These three statements are interconnected and changes in one can affect the others. They provide a comprehensive view of a company's financial health and performance.

The three statements are vital to gaining a complete understanding of a company’s performance. First, the income statement provides an insight into income and expenses. The balance sheet focuses on managing capital. Finally, the cash flow statement illustrates how cash is generated and invested.

The top-performing companies are efficient in all components of the three statements – their operations, capital allocation, and cash management. Efficiency in each of these areas is significant due to the inter-connectedness of the three statements. 

Data from the financial statements is used to conduct further analyses and create forecasts to help make decisions for the future. Users may also create pro forma financial statements based on the analyses to see how various choices can affect the financial statements.

Income statement

The income statement presents the earnings and the profitability of a company for a given period, such as a year or a quarter. It is also known as “statement of operations,” or “profit and loss account,” or “P&L account.”

It is generally prepared using the accrual system of accounting. It starts with the revenue in the first line, and after deducting various direct and indirect expenses, arrives at the company’s net income . It is often the first place an analyst or investor looks at to gauge the performance of a business. 

Balance sheet

Also known as the “statement of financial position,” the company’s balance sheet illustrates its financial position at a particular point in time. Unlike the income statement, it is not drawn for a period.

Instead, it shows the company’s sources of funds and its utilization of those funds at a particular point in time. The total funds raised from various sources (i.e., capital and liabilities) must always match the utilization of those funds (i.e., assets).

It displays the account balances of capital, liabilities, and assets at the end of a period so users can assess the changes in those balances against previous periods. The net earnings (loss) from the income statement are added in (subtracted from) retained earnings , which is part of equity/capital.

Cash flow statement

It is also known as “statement of cash flows” or “funds flow statement.” Like the income statement, the cash flow statement is prepared for a period. However, unlike the income statement, it is prepared using the cash system of accounting .

Since the income statement is prepared using the accrual system , it does not tell us how the cash moves in or out of the business.

The purpose of the cash flow statement is to tell us about the inflows and outflows of cash over a period. It breaks down the inflows and outflows into three categories: cash flow from operating activities , investing activities , and financing activities .

The net cash flows for the period must equal the difference in the opening and the closing cash balances.

Understanding a three-statement model

A three-statement model takes a company’s financial statements – the balance sheet, the income statement, and the cash flow statement – and combines them into a single dynamically linked financial model .

Its purpose is to project what the financial statements may look like if the company makes certain decisions, given certain assumptions. Since the statements are dynamically linked in a 3-statement model, changes in one statement are automatically reflected in the other two statements.

It is the base on which other more complex financial models are constructed, such as discounted cash flow (DCF) models, leveraged buyout models, and merger models, among numerous others.

Most of what might seem to be complicated in a three-statement model are basic mathematics. Building and understanding the model becomes easier if the model developers and the users are familiar with the fundamental relationships among the three statements.

Moreover, while simpler financial models use only one of these statements (the income statement or the cash flow statement), they often fail to show the entire picture.

The main benefit of using as comprehensive a financial model as the three-statement model is that users can use tools like what-if analysis and scenario analysis and get a bird’s eye view of how various decisions can affect the financial statements. This is crucial to making informed decisions.

The following video gives a brief overview of why the three statements are so important and why building financial models is a core skill in any high-paying job in finance.

How to build a three-statement model?

To put together a three-statement financial model, we begin with the income statement and the balance sheet. First, we start with the actual numbers from the previous period. Then, we move on to building forecasts based on some calculated model drivers (assumptions).

After building a forecasted income statement and a balance sheet, we create the cash flow statement. After these steps, we work towards linking the three statements. 

The steps to build a three-statement model are listed in detail below. Although you will be all set to construct a three-statement model with these steps, we firmly believe that a more hands-on approach will help you better understand the topic.

Hence, our finance experts have created a three-statement model template for you to experiment with.

Download WSO’s free three-statement model template below along with other financial modeling templates! This template allows you to create your own 3 statement model for a company – specifically, the balance sheet, income statement, and statement of cash flows.

The template is plug-and-play, and you can enter your numbers or formulas to auto-populate output numbers. The template also includes other tabs for other elements of a financial model.

Sign up to receive a FREE swipe file containing a collection of quality financial modeling templates to help your finance skills and prepare for interviews.

Here we explain the steps to building a three-statement model. Before starting to build a 3-statement model, please ensure that the Iterations setting is disabled in Excel. It is to deal with the unavoidable circularity (when the output of a computation is also an input for it) in the model.

On the Windows version of Excel, users can go to File > Options > Formulas and deselect the “Enable iterative calculation” checkbox while Mac users can go to Preferences > Calculation and then disable the “Use iterative calculation” option.

Please check out this article by Microsoft on removing or allowing a circular reference .

1. Input historical data

First and foremost, input the actual numbers for the statement of operations and the statement of financial position. This task becomes easier if the data can be downloaded or copied from another source. Please note the tips below, which can make the process easier.

• Some formatting is highly recommended to ensure following the best practices for financial modeling. It makes data easy to follow for our eyes. In WSO’s guide to the best practices , we recommend following the widely used color palette – blue for hard-coded inputs and assumptions, black for formulas and referencing in the same sheet, green for formulas and referencing to other sheets, and red for external links to other files. 
• Use comments where necessary. They are generally inserted in the cell to the side of the relevant line item and are italicized.
• Use shortcuts if possible. One of the most used functions is Sum. Users can press “Alt + =” right under a list of numbers to calculate their sum.
• Ensure that the input is correctly entered. Users can run “balance checks” to verify the net income figure and whether the balance sheet tallies. Remember the fundamental equation: Equity (Capital) + Liabilities = Assets.

With the advent of Python and other programming languages in financial modeling, the use of Stocks APIs to retrieve data quickly is becoming increasingly common.  

2. Analyze historical data

Historical data is analyzed by evaluating trends, computing ratios, and statistical information. Model drivers are based on the results of these analyses. Generally used metrics include:

• Year-on-year (YoY) growth rates for revenue, gross profit , operating profit or earnings before interest and taxes (EBIT) , and net profit.
• Margins and ratios that affect them, like direct costs, financing costs, and non-cash expenses ( depreciation and amortization ).
• Balance sheet ratios like current ratio , receivables outstanding, inventory turnover, accounts payables days , operating cycle, and cash conversion cycle . In addition, users may also calculate ratios related to the company’s capital structure .

3. Determining model drivers

Model drivers (assumptions) guide the forecasts further. They are established on the results of the analyses of the historical data.

For example, users may use the ratios calculated in the previous step or assume how they might change in the coming years. Here are a few examples of critical assumptions that are generally used:

• Revenue growth rate. If a company’s revenue has been growing at high rates in recent years, it may grow at declining rates going forward due to increase in competition.
• Costs and margins. It is reasonable to assume that a company will achieve economies of scale as it grows, so direct costs might decline, leading to improved gross margins. In addition, with increased revenues and reduced direct costs, the company may be able to allocate an increasing sum for research and development (R&D) expenses each year.
• Other indirect expenses may be taken as a percentage of sales. Although the capital expenditure ( CapEx ) requirements are usually forecasted using complex calculations, users may assume a fixed percentage of sales as a simple estimate. Unless changes in tax law are anticipated, historical effective tax rates are typically expected to apply in the future.

4. Projecting income statement

Once the inputs are in place and assumptions are determined, users have all they need to begin forecasting. The entire forecasting exercise starts with the income statement , starting from the sales and down to the EBITDA .

Although we have the inputs and the assumptions at this stage, building the income statement still requires supporting schedules for line items such as depreciation, taxes, and interest expense .

However, net financing costs (interest expense) are not linked to the income statement at this stage but rather at the end. It is because financing costs are connected to the other two statements, so incorporating them in the income statement at this stage is bound to produce circularity in the model. 

5. Forecasting capital assets and financing activity

Next, users must build supporting schedules to forecast metrics related to capital assets. The closing balance for capital assets can be calculated by a simple formula which is Opening balance + Capital expenditures – Depreciation.

There are multiple ways to account for depreciation, i.e., straight-line method, declining balance method, etc.

Users must also build the debt schedule to determine financing cost payments and principal repayments. We can use this simple formula to arrive at the closing figures: Opening balance + (-) increases (decreases) in the principal.

The interest may be calculated in various ways, i.e., based on the closing balance of debt, the opening balance, or an average of the two. Using an average is ideal, as there might be principal repayments throughout the year.

For a more intensive calculation, users may build a separate supporting schedule for financing costs altogether.

6. Projecting balance sheet

The balance sheet is slightly tougher to build. It can be built without cash, equity, and debt at this stage. These line items will be derived from the cash flow statement later.

For other items, average ratios from recent years are used (with or without adjustments based on assumptions) to determine closing balances during forecasting. These ratios may include days accounts payable , inventory days, accounts receivable days, etc.

For instance, days payable may be computed for each of the recent years as average accounts payable ÷ cost of goods sold ( COGS ) which can there be used to calculate the average of days accounts payable for the recent years. This number may be adjusted before it is used in the forecasted statements if necessary.

For example, to predict accounts payables for the coming years, use this formula: (adjusted) days accounts payable x expected COGS ÷ 365 days. Here are a few tips to help users at this stage.

• Unless users are expecting M&A transactions, goodwill should be unchanged going forward.
• Determining the assumptions first makes building the model and the error checking process easy.
• The Trace Precedents tool in Excel can be beneficial in checking whether the formulas have been entered using the correct dependencies.

7. Building cash flow statement

With the partially complete balance sheet and the income statement, users can move on to create the cash flow statement. There are three sections in a cash flow statement, each of which must be completed by linking the line items to the ones already calculated in the model.

We start with the net earnings for the period and make all the adjustments necessary to convert it from the accrual system to the cash system of accounting to determine the cash flows from operating activities. We also adjust for items that might belong in investing or financing activities.

Here are some critical adjustments required to be made to net earnings to ascertain the cash flows from operating, investing, and financing activities.

• Depreciation and amortization are non-cash expenses deducted from income to calculate net earnings. Therefore, these and other non-cash costs must be added back to the net income.
• An increase in current assets implies cash utilization, which needs to be deducted from operating activities. Conversely, a decrease in current assets means an increase in liquidity as the cash is no longer tied up in those assets. The opposite applies to current liabilities – an increase is due to not using up cash to pay them off, while a decrease is due to utilizing cash to pay them.
• Debt-related items like issuances and repayments can be derived from the debt schedule . While preparing the balance sheet, expected stock issuances and buybacks must also have been factored in beforehand. These are financing activities and will not affect cash flows from operating and investing activities.
• The net cash flows for the period are calculated as the sum of cash flows from operating, investing, and financing activities.

At this stage, the users will have completed projecting the three financial statements .

8. Linking the statements

This is the concluding stage of building the three statement model. A few items in the balance sheet were intentionally left out. Users can now build on them and link the three statements.

• Debt: Prior long-term debt is adjusted for expected issuances and repayments. It is worth noting that long-term debt becomes short-term debt as it nears its expected repayment.
• Equity: Shareholders’ equity is computed as - Opening balance + Expected issuances or buybacks (from the cash flow statement) + Net income (from the income statement) – Dividends (from the cash flow statement). 
• Cash: The closing balance for cash is calculated as - Opening balance + Net cash flow for the period.

Once these items are calculated, they will be plugged into the balance sheet. Please ensure that the balance checks do not indicate any errors.

9. Net interest expense

The puzzle is almost complete. The interest expense (or income) is the single item left to be plugged. Plugging in the net finance costs in the income statement will change the net earnings, further impacting the balance sheet through retained earnings and the cash flow statement through cash flow from operating activities.

Thus, financing costs affect all three statements, and this produces circularity. It is also known as iterative calculation. Excel (or other spreadsheet software) runs different numbers through the calculations to find the values which satisfy each of such analyses.

As the final piece of the puzzle, Iterations must be enabled in Excel. On the Windows version of Excel, users can go to File > Options > Formulas and select the “Enable iterative calculation” checkbox.

Users with the Mac version of Excel can go to Preferences > Calculation and enable “Use iterative calculation.” The net earnings figure should get updated in the income statement now, which should flow into the other two statements.

At the end of the above steps, your three-statement model should be ready to help you make more informed decisions.

Check this page out for another  3 Statement model example .

Everything You Need To Master Financial Statement Modeling

To Help you Thrive in the Most Prestigious Jobs on Wall Street.

More about financial modeling

To continue learning and advancing your career, check out these additional helpful WSO resources:

• What is Financial Modeling?
• Free Financial Modeling Guide
• What Makes a Good Financial Model?
• Top Financial Modeling Courses
• Valuation Modeling in Excel

Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.

or Want to Sign up with your social account?

How to Build a 3-statement Model: Best Practices for Valuations and Projections

To value or build projections for a company accurately, you have to factor in working capital projections—and you can’t do that without investing in a three-statement financial model.

By Abdullah Karayumak, CFA

Abdullah is a financial modeling, valuations, and capital raising expert and Chartered Financial Analyst. He’s advised clients ranging from early-stage startups to multibillion-dollar enterprises, most recently for KPMG.

Previous Role

PREVIOUSLY AT

Knowledge—correct, data-based, properly interpreted information—is indeed power, and the lack of it can cost businesses millions. In particular, accurate company valuations and projections of free cash flow available to equity holders are crucial, not just during mergers and acquisitions , but at all times if leaders and prospective investors are to understand a company’s current and future financial standing. Many companies use only a projecting income statement to forecast what’s coming, but that can lead to dramatically inaccurate projections and valuations. That's why I recommend using the gold standard financial model : a three-statement model incorporating the balance sheet , cash flow statement and income statement .

Why Accurate Financial Projections Are Essential

Financial projections are useful for business leaders who are planning and budgeting for the near term and forecasting their company’s performance under a variety of conditions. They also help identify investment needs and assist in valuing the business. For example, projected surpluses suggest new opportunities for reinvestment, where projected shortfalls may indicate a need for retrenchment or course changes. Conversely, investors use financial projections to challenge the assumptions behind a prospectus or business forecast.

Projections are also essential inputs to valuation formulas . Valuations are crucial for mergers and acquisitions, as well as for developing contingencies and to aid in decision analysis. When leaders are considering a major investment decision or change of direction, modeling the effect of those choices on future valuation may help guide the choice. Once executed, major enterprise decisions require accurate before-and-after valuations to assess the value of those changes.

The most common valuation technique is discounted cash flow (DCF). When DCF is used with just an income statement, as it often is, it uses income as a proxy for cash flow . This works well enough when working capital —the metric of liquidity that represents the difference between the company’s current assets and current liabilities—is neutral or its absolute value is small compared to cash flow based on income. But when working capital is high relative to income, this method can fail to identify significant cash inflows or outflows.

That’s because working capital can have significant effects on cash flow that the income statement doesn’t capture. As a valuation advisor for KPMG , I’ve created three-statement financial models for clients seeking to raise funds and value acquisition targets. My experience has taught me that performing DCF using all three statements provides the most accurate results and will serve you best, no matter what your goal is.

What the Income Statement Leaves Out

The biggest pitfall for financial analysts performing projections is treating the income statement as if it represents cash flow.

The income statement focuses solely on profit and loss. While you naturally tend to think the more profitable a company is, the better, there’s more to valuation than an isolated dollar value. First, not all sales are collected as cash. Some sales are made on credit and recorded as accounts receivable. Second, not all cost of goods sold (COGS) is cash outflow. Some of these costs will be covered by purchases that the business makes on credit. Because these transactions (changes in accounts receivable and accounts payable) aren’t recorded on the income statement, a forecast that relies only on this statement doesn’t give the full picture of the cash inflows and outflows of a business.

Three-statement models incorporate all the important facets of a business’s operations. These models project—along with other balance sheet items—forecasted balances of working capital elements such as accounts receivables, inventory, and prepaid expenses. Together, these influence the free cash flows available for the business’s operations. This matters because a company with high working capital demands can seem profitable on the surface but actually be in the red once the cash flows are laid bare.

How Building a 3-statement Financial Model Benefits Business Leaders

A company’s value isn’t always readily apparent to its leaders. I was once asked to build a three-statement financial model and valuation study for a greenfield aluminum recycling facility. The CEO was certain the facility was profitable, given the significant EBITDA margins reflected in the income statement. She was surprised to see that the valuation result came back below her expectations.

The facility was turning a high sales volume: Scrap aluminum was purchased with cash, recycled within the facility, and sold on two-month terms. Consequently, it took a lot of cash outflow to fund the working capital needs of the operations. This information didn’t appear in the income statement and could only be found in the cash flow statement , which details the movement of cash and cash equivalents in and out of a business.

Focusing only on the income statement and profitability would have resulted in the CEO missing this important aspect of her business. Drawing on the insights of a three-statement model, she was able to prepare for working capital cash outflows in the future by adding the startup working capital requirement as a project cost when asking for financing.

The implications here are clear: Even when leaders aren’t considering an exit, they need to know exactly how much their businesses are worth, how much they’re going to earn, and how much it will cost to operate them. Had I used a single-statement-based valuation that confirmed her biases, the CEO might have been in for a nasty shock down the road.

How Building a 3-statement Financial Model Benefits Investors and Buyers

Three-statement models are also useful for potential investors or purchasers doing due diligence . These models allow investors to look past profitability and assess the cash yield of a potential investment. Even when a company is profitable and growing, it may lose cash because of high working capital requirements. This is especially true for companies with low profit margins, high sales volume, and positive working capital projections. The most common example of this is industrial companies, since they often have large sums of cash tied up in working capital.

The opposite situation is also possible. A company may be earning a very slim margin but have negative working capital, in which case sales growth brings cash flow into the business. This is often the case for businesses in the retail sector and for utilities.

In both cases, it’s critical for investors to run working capital sensitivity analyses using the elements of the cash conversion cycle —days sales outstanding (DSO), days payable outstanding (DPO), and days inventory outstanding (DIO)—on the projections. These findings can lead to improvements in working capital management, which then allows the business to free up cash for investments. However, these analyses are only possible when the underlying financial model has the capacity to calculate future balance sheet items—in other words, a three-statement model.

I once conducted financial due diligence on an Eastern European building-chemicals company in an acquisition deal. Riding on the back of the booming construction sector, this target company was highly profitable. A one-statement model probably would have produced a healthy valuation. But when I built a three-statement model for the business, it became clear that it was polishing its profitability by loosening its trade terms—selling its products at a higher price, but giving customers more time to pay.

Although this tactic increased the company’s profitability, it forced the business to tie up a significant amount of cash in working capital, reducing liquidity. The cash outflow required to fund the working capital devalued the company significantly. This problem wasn’t identified until we created a three-statement model and looked closely at cash movements in the projections.

A 3-statement Model Example

To illustrate how single-statement DCF can lead to a significantly inaccurate result, I’ve created two alternative projections for a fictional company called Vermont Telecom. Telecom companies typically have high working capital requirements: They collect plan payments monthly or yearly, and those add up to billions of dollars and cover operational and capital expenditures. Any fluctuation in their operating cash cycle has a significant effect on their valuation and cash position. They usually borrow short-term funding to cover gaps in their cash cycle.

These excerpts from a discounted cash flow analysis show the dramatic difference between projecting present value of free cash flow in DCF using just an income statement and using a three-statement model that includes working capital.

Discounted Cash Flow Analysis: Present Value of Free Cash Flow Using Income Statement Only

As you can see, the single-statement approach shows no information for the increase in net working capital. But for Vermont Telecom, with its high working capital needs, that value is actually quite significant. Here’s what it looks like when you incorporate the working capital requirements of the business:

Discounted Cash Flow Analysis: Present Value of Free Cash Flow Using 3-statement Model

The increase in net working capital is -$98.5 million in 2022, which results in a multimillion-dollar difference in the final value of free cash flow in the three-statement model compared to the single-statement analysis.

Moving on to valuation, as you can see here, a single-statement DCF without working capital substantially overvalues the enterprise with a central projection value of $602 million based on weighted average cost of capital (WACC) at 15% and a terminal growth rate of 2.5%.

Enterprise Value Using Income Statement Only

When working capital is factored into the equation, the same assumptions result in a valuation of only $483 million—a substantial 22% difference.

Enterprise Value Using 3-statement Model

Best Practices for Handling Working Capital in a 3-statement Model

There are a variety of methods you can use to ensure that your financial model accurately projects the working capital lines. The approach I prefer is also the most common one: using days working capital —how many days it takes to convert working capital into revenue. Standard practice suggests we take the average of historical days working capital or use peer group averages .

Let's say you have the sales and cost of goods sold projected and you chose to use average days working capital. Combing this data, you can calculate 2023 working capital values in the balance sheet as follows:

• 2023 Accounts Receivable Balance Forecast = (DSO / 365) * (2023 Sales Forecast)
• 2023 Accounts Payable Balance Forecast = (DPO / 365) * (2023 COGS Forecast)
• 2023 Inventory Forecast = (DIO / 365) * (2023 COGS Forecast)

The sum of these three lines will let you arrive at your working capital projections for the coming year.

The Power of 3

As I’ve demonstrated, performing DCF valuation using just the income statement to approximate cash flow can lead to serious problems when the value of working capital, whether positive or negative, is significant. Developing a three-statement model rectifies these problems, and it provides a more nuanced and accurate view of the enterprise.

But there are additional benefits as well. Three-statement modeling enables other more advanced forms of analysis. For example, Harvard Business Review recommends combining DCF with real option analysis to achieve a median value when major investment decisions are being evaluated. Three-statement modeling also serves as the basis for models used for M&A, scenario planning, and sensitivity analysis .

It’s always empowering to have a full picture of your business, whether you’re seeking an exit or an investment, or you simply want to make informed choices about your company’s growth. Three-statement modeling is more time-consuming and requires more expertise to build, but in the long run, it’s well worth it.

Further Reading on the Toptal Blog:

• How to Build Your Startup’s Financial Model to Grab Investor Interest

Understanding the basics

What is a three-statement model.

A three-statement model is a financial model derived from a company’s balance sheet, cash flow statement, and income statement. It allows a business to track and project its performance over time. It frequently serves as the foundation upon which other analytical models are built.

Why is a company’s valuation important?

A valuation estimates the current and future worth of an asset—or in this case, a company. A company’s valuation comes into play when the business is facing a merger or acquisition, courting investors, or engaging in any other transaction that depends on how much the company is worth.

How does the income statement relate to cash flow?

The income statement records revenue, but it does not differentiate between cash and credit. For greater insight into a company’s cash position, it’s essential to refer to the cash flow statement.

Abdullah Karayumak, CFA

Istanbul, Turkey

Member since January 26, 2022

About the author

World-class articles, delivered weekly.

Subscription implies consent to our privacy policy

Toptal Finance Experts

• Blockchain Consultants
• Business Management Consultants
• Business Plan Consultants
• Business Process Optimization Consultants
• Certified Public Accountants (CPA)
• Economic Development Consultants
• Equity Research Analysts
• Excel Experts
• Financial Benchmarking Consultants
• Financial Forecasting Experts
• Financial Modeling Consultants
• Financial Writers
• Fintech Consultants
• FP&A Consultants
• Fractional CFOs
• Fundraising Consultants
• FX Consultants
• Growth Strategy Consultants
• Integrated Business Planning Consultants
• Interim CFOs
• Investment Managers
• Investment Thesis Consultants
• Investor Relations Consultants
• M&A Consultants
• Market Sizing Experts
• Pitch Deck Consultants
• Private Equity Consultants
• Procurement Consultants
• Profitability Analysis Experts
• Real Estate Experts
• Restructuring Consultants
• Risk Management Consultants
• Small Business Consultants
• Supply Chain Management Consultants
• Valuation Specialists
• Venture Capital Consultants
• Virtual CFOs
• Xero Experts
• View More Freelance Finance Experts

Join the Toptal ® community.

S T R E E T OF W A L L S

Three statement financial modeling.

In this Three-Statement Financial Modeling chapter we will cover four key topics:

Three-Statement Financial Modeling Overview

Model design & layouts, six steps to simple financial modeling, final checklist.

Investment banking analysts and associates are expected to be able to build three-statement operating models as part of their day-to-day responsibilities. In fact, in most cases, analysts and associates will spend as much time performing this task as any other . Therefore, it is extremely important that any investment banking professional or candidate be well versed in how to build a three-statement operating model to completion.

Building from the Basic Model

In the previous chapter, Financial Modeling , we discussed the basics behind modeling the future financial performance for the company you, as an analyst, are evaluating. However, that chapter only covers the beginning portion: how to model assumptions and build calculations for the Income Statement. While it is important to understand all of the considerations in building that prediction as accurately as possible, it is also important to note that building a complete financial model for a company does not end there. The projected Balance Sheet and Statement of Cash Flows must also be built, and the three statements must be integrated correctly.

Now that we have an understanding of how to model Revenue and Expenses, this chapter will build on that understanding. We will walk through each key step in building and forecasting a three-statement operating model for a company. The model will begin by using historical data and ratios, and forecasted ratios and projections—topics that we have already begun to discuss previously—and will tie in the building of the rest of the complete operating model.

A Quick Review

Building from the previous chapter, we saw that the basic financial model revolved around the Income Statement, and the Model Drivers (which we can call Assumptions ) that are used to project future figures on the Income Statement.

We start off with historical financials (typically, around 3 years worth), calculate key ratios based on those financials, and use them as part of a process to determine which drivers (assumptions) to use in projecting those financials going forward:

In short, historical financial raw data, and ratios calculated from them, are used as an integral part of the process to derive forward-looking assumptions that drive financial data projections. We should start by building a model designed to reflect that.

Also, as a reminder from the Discounted Cash Flow chapter (as well as the previous chapter), use the mnemonic “C.V.S.” to avoid common errors and help ensure that the outputs of your model will be reasonable:

• C onfirm historical financials for accuracy.
• V alidate key assumptions for projections.
• S ensitize variables driving projections to build a valuation range.

Three-statement financial models can be built in a variety of different layouts and designs. For example, the Income Statement, Balance Sheet, and Statement of Cash Flows can be combined on one excel tab, or each of the three financial statements can occur on separate tabs (i.e.,  worksheets within a single workbook). The Assumptions can be listed on a separate worksheet, or they can be listed below or beside the Income Statement.

Conceptual vs. Physical Layout

Importantly, however, the analyst must keep in mind the conceptual design of the model . Assumptions are derived from historical financial data as well as logic and external analysis; these assumptions drive the values projected in the Financial Statements (primarily the Income Statement, but as we shall see, the other statements as well). Thus they can be physically on the same worksheet as the statements, but they are always logically separate :

Laying out Assumptions

Likewise, the Assumptions themselves can be built in a variety of layouts. Income Statement drivers and assumptions can be built on a separate tab or as part of the same tab as the Income Statement. If combined on the Income Statement tab, drivers and assumptions can be integrated into the Income Statement or directly underneath the statement line items:

Numeric Display

The presentation of the financial statements can be in either a positive-only , or positive/negative , perspective. In other words, numbers that subtract from others (such as Expense items, which subtract from Revenue on the path to calculating Net Income) can be displayed either as positive numbers that are subtracted, or negative numbers that are added. Mathematically, they are equivalent.

In Chapter 4, Discounted Cash Flow , we walked you through the basic concepts in projecting Free Cash Flow. In Chapter 7, Financial Modeling , we outlined the key steps in building a complete financial model, and began discussing those steps. Here, we will complete discussing those steps:

• Input historical Financial Statements (Income Statement, Balance Sheet).
• Calculate key ratios on historical financials (e.g., Gross Margin, Net Income Margin, Accounts Receivable/Payable Days, etc.).
• Make forward-looking assumptions for projecting the Income Statement and Balance Sheet based on these historical ratios and any additional considerations.
• Build a Statement of Cash Flows (tying together Net Income from the Income Statement and Cash from the Balance Sheet).
• Tie Ending Cash Balance from the Statement of Cash Flows into the Balance Sheet, and Balance the Balance Sheet.
• Calculate Interest Expense and tie this into the Income Statement.

Because we already went into detail on Steps 1-3 in this process in Chapter 7, we will be expanding on those steps in this chapter. For more detail on Steps 1-3, please revisit that chapter on the Introduction to Financial Modeling .

Step 1: Input Historical Financial Data

The first step in building a financial operating model is to input the historical Financial Statements (Income Statement and Balance Sheet).

Here are some notes to make this process easier:

• Color code your cells so that formulas are a different color from directly input data. Standard practice is to make the text color blue for input data and black for formulas .
• Put any comments about the line items to the side of each item; standard practice is to make comments italic.
• Use the “Alt + =” shortcut to subtotal the income statement sections. For example, once you input the Operating Expenses items, type “Alt + =” in the cell directly underneath to calculate the subtotal for 2003 of $23,500.
• Double-check that the input data correctly matches the source, and that you end up with the same net income each year as the historical input source. You can code this as a “Balance Check” formula directly in the spreadsheet.
• Follow the same steps for the Balance Sheet by making sure that the input data and formulas match the source. Also, run a “balance check” to make sure the data your input Balance Sheets actually balance. For the Balance Sheet, remember the all-important Accounting Identity: Total Assets = Total Liabilities + Shareholders’ Equity

Step 2: Calculate Ratios

Once you’ve finished inputting the historical data on the Income Statement and Balance Sheet, you can calculate key historical financial ratios. Make sure to use the relevant ratio when calculating each assumption, which will be used to drive future projections.

Here is a list of ratios typically required, at minimum, to build out Income Statement financial projections:

Year-over-Year Growth Rates: Income Statement

• Revenue: (Year 2 Revenue ÷ Year 1 Revenue) – 1
• Gross Profit: (Year 2 Gross Profit ÷ Year 1 Gross Profit) – 1
• EBIT: (Year 2 EBIT ÷ Year 1 EBIT) – 1
• Net Income: (Year 2 Net Income ÷ Year 1 Net Income) – 1

Margin and Ratio Analysis: Income Statement

• Cost of Goods Sold (COGS): (Year 1 COGS ÷ Year 1 Revenue)
• Gross Margin: (Year 1 Gross Profit ÷ Year 1 Revenue)
• Selling, General & Administrative (SG&A): (Year 1 SG&A ÷ Year 1 Revenue)
• Depreciation & Amortization (D&A): (Year 1 D&A ÷ Year 1 Revenue)
• Effective Tax Rate: (Tax Expense ÷ Earnings Before Tax, a.k.a. EBT )

Compute these for all years in the historical financial data range, noting that growth rate calculations can only begin in Year 2, not Year 1:

Ratio Analysis: Balance Sheet

Next, we must compute ratios on key Balance Sheet line items for each year. Many Balance Sheet ratios are expressed either in terms of units of time ( Days ) , or frequency per unit of time ( Turns ) . Here, we have expressed these ratios as units of time (Days):

• Accounts Receivable Days: 365 days × (Year 1 Accounts Receivable ÷ Year 1 Revenue)
• Inventory Days: 365 days × (Year 1 Inventory ÷ Year 1 COGS)
• Prepaid Expenses: (Year 1 Prepaid Expenses ÷ Year 1 Revenue)
• Accounts Payable Days: 365 days × (Year 1 Accounts Payable ÷ Year 1 COGS)
• Accrued Expenses: (Year 1 Accrued Expenses ÷ Year 1 Revenue)

The use of these historical financial ratios will help you make your assumptions to the financial projections in the future years of the operating model. In this way, you will be able to spot relevant trends in these keys ratios when you project them, and help you reconcile results from the past with the results you are projecting.

Step 3: Assumptions & Forecasts

The next step includes building out the projected years (Years 4 to 6) by making assumptions based on the historical data. Typically, you should either use the historical ratios themselves or, where prudent, make assumptions about how these ratios will change over time. To start, let’s look at assumptions for the Income Statement:

Here are key notes about the assumptions made in red. Please note, for the following discussion, that Sales and Revenue are synonymous:

Year-over-Year Growth Rate Assumptions

• Revenue: The company grew by very high rates in Years 2 and 3. We therefore assume that revenue growth will slow down in upcoming years, but still be strong. The 25%, 15% and 10% are independent variables, or assumptions—they drive the model’s revenue projections, rather than being calculated from revenue. To calculate Year 4 Revenue, we simply take (1 + 25%) × Year 3 Revenue. If this formula is built correctly, we can copy the formula for each year following.

Margin and Ratio Assumptions

• COGS: When a company grows, it is standard to assume that, all other things being equal, Gross Margin will improve (that is, COGS as a percentage of Revenue will decline). The reason for this, as discussed in the previous chapter, is operating leverage. Typically, we assume an incremental improvement each year, such as a 1% decline in COGS as a percentage of Sales. To calculate Year 4 COGS/Sales, we simply take Year 3 COGS/Sales and subtract 1%. Then, Year 4 COGS = (Year 4 COGS/Sales Assumption × Year 4 Sales), where “Year 4 COGS/Sales Assumption” = (Year 3 COGS/Sales) – 1%. We then subtract 1% each year from COGS/Sales in Years 5 and 6 and copy the COGS formula over. In this way, we are modeling a 1% incremental improvement in Gross Margin each year.
• R&D: Again, we can keep R&D/Sales constant, or use an incremental improvement number each year. Here, we have assumed a 0.5% annual reduction in R&D as a percentage of Sales. To calculate Year 4 R&D/Sales, we take Year 3 R&D/Sales and subtract 0.5%. Then, Year 4 R&D Expense = (Year 4 R&D/Sales Assumption × Year 4 Sales), where “Year 4 R&D/Sales Assumption” = (Year 3 R&D/Sales) – 0.5%. We then subtract 0.5% each year from R&D/Sales in Years 5 and 6 and copy the R&D Expenses formula over. In this way, we are modeling a 0.5% incremental reduction in R&D Expenditures as a percentage of Sales in each year.
• SG&A: These forward-looking ratios are modeled exactly the same way. In this model, we have assumed 0.5% annual reductions in both Sales & Marketing (S&M) and General & Administrative (G&A) as a percentage of Sales, thereby reducing total SG&A Expenses as a percentage of Sales by 1% in each year.
• D&A: For Depreciation and Amortization, we make the simplifying assumption that D&A will remain constant as a percentage of Sales. (A more in-depth operating model would forecast Capital Expenditures (CapEx) in each year, and then make assumptions about how much each year’s CapEx would be depreciated in subsequent years.) To calculate this simple version, take the average D&A of Years 1 through 3 (5.4% of Sales) and keep that ratio constant going forward. In modeling D&A this way, we assume that D&A expenses will increase as a constant percentage of Sales, and therefore grow by the same annual percentage that Revenue grows.
• Tax Rate: Typically we estimate historical tax rates and project them as a constant percentage going forward. However, sometimes we can project that tax rates will change, for any of a number of reasons. To illustrate this concept, in this model we’ve kept the Year 3 Tax Rate (as a percentage of Earnings Before Tax, or EBT) of 33% constant in Year 4, but have reduced it to 30% in Years 5 and 6. We might model it this way if we knew that favorable tax treatment was coming a year down the line, perhaps from a business shift to lower-taxation markets, or an anticipated change in statutory tax rates.

Once the key Income Statement assumptions have been made, we can build out the income statement line items as described to compute projected Income Statement results all the way down to Net Income, with one important exception (for now). We leave the “Net Interest Income (Expense)” line item empty, which we will only link into the model as a last step . This is because Net Interest Expense links to the other financial statements, which creates circular calculation dependencies in the model (in a nutshell, circular dependencies occur when A is used to calculate B, which is used to calculate C, which is then used to calculate A). If we make a mistake in this link, it can cause a serious error in the spreadsheet which can become very difficult to fix. Therefore, in the EBT line item, add the Net Interest Income (Expense) line item to the Operating Income (EBIT), but leave the Net Interest Income (Expense) line item completely blank at first . We will link it into the final model later.

Next, let’s move on to assumptions used to drive projections in the Balance Sheet:

Here are key notes about the assumptions made in red. Because Balance Sheet ratios are typically more difficult to project, we often refrain from changing the assumptions driving these financial results, and instead defer to historical results. Also these assumptions tend to have less of an impact on Valuation results than do Income Statement assumptions. Please note in this discussion that Sales and Revenue are synonymous:

Balance Sheet Ratio Assumptions

• Accounts Receivable Days: Take the average of Accounts Receivable Days from Years 1 through 3, which is 28 days, and keep that number constant in the forecasted years. We can also make assumptions about changes in this ratio if we want, but for the purposes of this model, we have assumed that the ratio stays constant.
• Inventory Days: Take the average of Inventory Days from Years 1 through 3, which is 27 days, and keep that number constant in the forecasted years. (Again, we can add increments or decrements to this number in future years if we feel it is appropriate.)
• Prepaid Expenses: Take the average of Prepaid Expenses/Sales from Years 1 through 3, which is 1.7%, and keep that percentage constant in the forecasted years. (Again, we can add increments or decrements to this number in future years if we feel it is appropriate.)
• Days Accounts Payable: Take the average of Accounts Payable from Years 1 through 3, which is 51 days, and keep that number constant in the forecasted years. (Again, we can add increments or decrements to this number in future years if we feel it is appropriate.)
• Accrued Expenses: Take the average of Accrued Expenses/Sales from Years 1 through 3, which is 8%, and keep that percentage constant in the forecasted years. (Again, we can add increments or decrements to this number in future years if we feel it is appropriate.)

Once we have created the Balance Sheet assumptions, we can fill in the Balance Sheet line items while leaving out the Cash, Net PP&E, Debt and Shareholders’ Equity line items. We will derive those Balance Sheet line items later, from the Statement of Cash Flows.

Use the following formulas to build out the relevant Balance Sheet line items:

• Accounts Receivable: (Year 4 Accounts Receivable Days × Year 4 Sales) ÷ 365 days
• Inventory: (Year 4 Inventory Days × Year 4 COGS) ÷ 365 days
• Prepaid Expenses: (Year 4 Prepaid Expenses/Sales × Year 4 Sales)
• Accounts Payable: (Year 4 Accounts Payable Days × Year 4 COGS) ÷ 365 days
• Accrued Expenses: (Year 4 Accrued Expenses/Sales × Year 4 Sales)

Here are a couple of helpful hints at this stage:

• Keep Goodwill and Intangible Assets line items constant going forward. Unless we are modeling potential changes caused by M&A activity, these line items should not be projected to change.
• Calculate all the assumption values before creating the formulas to calculate each line item in the Financial Statements. Creating the assumption first will allow for easy building of the operating model, and easy checking that the operating model is working correctly.
• Once you are finished, you can check that each line item formula is based off the appropriate assumptions. To check the source of the assumptions used in a formula, highlight the cell containing that formula and press “Ctrl + [”. This will highlight the cells that the formula is dependent upon . For example, put the cursor on the Year 4 Revenue line and press the shortcut. If you have built your formula correctly, Excel will now highlight the Year 3 Revenue cell, as well as the Year 4 Revenue growth assumption cell.

Step 4: Building the Statement of Cash Flows

Once we have the majority of the line items projected out for the Income Statement and Balance Sheet, we are ready to build out the Statement of Cash Flows (SCF). The SCF should look something like this:

The first line item in the SCF is Net Income from the Income Statement. From this, we take into account all differences between Net Income and the change in the company’s Cash position. There are many—here are the summary points:

• Depreciation and Amortization are generally part of COGS, but they are never actually Cash expenditures—they represent a portion of Capital Expenditures from the past that are being recognized as expenses in the current year. Since they are expenditures in the current year that are not paid for as Cash in the current year, they must be added back to Net Income to account for a company’s change in Cash.
• When the line items of operating assets (things like Accounts Receivable and Inventory) increase, Cash will go down on the SCF. This is because the company has, in effect, spent Cash to build an asset, but hasn’t yet received the Cash back from these assets. For example, from Year 3 to Year 4, the Accounts Receivable line item increases by $16,131; therefore the SCF shows a negative amount of $16,131—the difference between Year 3 Accounts Receivable and Year 4 Accounts Receivable.
• When the line items of operating liabilities (things like Accounts Payable and Accrued Expenses) increase, the opposite happens—Cash will go up on the SCF. This is because the company has, in effect, earned and recorded income from its operations, and thereby accrued liabilities, but hasn’t paid off those liabilities in Cash yet. For example, from Year 3 to Year 4, the Accounts Payable line item increases by $15,792; therefore the SCF shows a positive amount of $15,792—the difference between Year 3 Accounts Payable and Year 4 Accounts Payable.
• Capital Expenditures and Depreciation will dictate the change in Net PP&E. For example, Year 4 Net PP&E = Year 3 Net PP&E + Year 4 Capital Expenditures – Year 4 Depreciation. (Note that in this model, Capital Expenditures have been manually input in the SCF each year, as negative numbers, because they constitute a use of Cash that is not yet reflected in the Income Statement.)
• Net Income and Dividends will dictate the change of Retained Earnings on the Balance Sheet. For example, Year 4 Retained Earnings = Year 3 Retained Earnings + Year 4 Net Income – Year 4 Dividends. (Note that in this model, Shareholders’ Equity is modeled as a single line item in the Balance Sheet – therefore the change in Retained Earnings will be reflected in the change in Shareholder’s Equity.)
• Debt Issuances/(Repayments) can be placed in the cells highlighted in blue based on your assumptions about future changes in debt levels. The same holds for Stock Issuances/(Buybacks). In each case, these Cash inflows/outflows are financing-related , rather than operations-related , and therefore will not be reflected whatsoever in the Net Income calculation. However, they will result in changes to a company’s Cash balance, and therefore must be recorded in the SCF.
• The company’s Change in Cash will equal the sum of the Change in Cash from Operating, Investing and Financing activities. For example, Year 4 Ending Cash Balance = Year 3 Ending Cash Balance + Year 4 Cash from Operations +  Year 4 Cash from Investing Activities + Year 4 Cash from Financing Activities.

Step 5: Linking the Three Statements

Now that we have built most of the three financial statements, we can build the final formulas that link, or integrate, all three statements.

• Calculate Net PP&E as Year Prior PP&E + Capital Expenditures (from the SCF) – Depreciation (from the SCF).
• Calculate Long-Term Debt as Year Prior Long-Term Debt + Issuance/(Buyback) of Long-Term Debt (from the SCF). (Note that in this model, a distinction is made between Long-Term Debt and Short-Term Debt, and that the entire Debt balance converts from Long-Term Debt to Short-Term Debt in Year 5 before being repaid in Year 6.)
• Calculate Shareholders’ Equity as Year Prior Shareholders’ Equity + Issuance/(Buyback) of Stock (from the SCF) + Current Year Net Income – Current Year Dividends (from the SCF). (Note that in this model, no Dividends were modeled in the SCF, so the assumed Dividends payment to Shareholders in each year equals $0.)
• Link the Beginning Cash Balance and Ending Cash Balance formulas between the SCF and the Balance Sheet. For example, in Year 4, the Beginning Cash Balance in the SCF should be set equal to the Cash line item for Year 3 on the Balance Sheet. The Cash line item for Year 4 on the Balance Sheet should be set equal to the Year 4 Ending Cash Balance on the SCF. Follow this logic for all years in the financial model. Once you have linked the Cash and remaining line items such as Debt and Shareholders’ Equity, then the Balance Sheet should balance (Total Assets = Total Liabilities + Shareholders’ Equity).

Step 6: Interest Expense (BE CAREFUL!)

At this point the financial model should balance and all balance checks should be affirmed. We have only one piece left to complete: calculating Net Interest Income/(Expense).

As we have discussed before, this calculation flows through all three Financial Statements in the model in what is referred to as a circular calculation (or iterative calculation ). When a circular calculation is built, the outputs of a series of calculations become inputs into the same calculation. It is the task of the spreadsheet software to change the values of the different components of the calculation to find a set of values that makes all of the calculations work.

At this stage it is very easy to introduce a mistake into the calculation, and generate an error. Because the calculations are now iterative, any such error will flow through ALL of the calculations, and the entire model will become populated with error messages. (Many an investment banker has gone a sleepless night trying to resolve an accidental error in a circular calculation, so be very careful!)

In order to minimize the chance that you make this mistake, follow these steps exactly:

Avoiding a Circular Calculation Error

• Turn Iterations in the workbook OFF . This can be done through the following Excel menu command: Tools → Options → Calculations .
• Check every formula and make sure the Balance Sheet balances in each projection year.
• Save your workbook. If the following steps introduce an error into your workbook, you should strongly consider closing it without saving it , and re-opening the saved version.
• Calculate Net Interest Income/(Expense) for the first projection year in your model. In the example in this chapter, this would be Year 4. Net Interest Income/(Expense) is calculated as follows:
• If you have built this formula correctly, Excel should complain . It will introduce a dialog box informing you that the calculation is circular. When that box is closed, it will likely open a Help file explaining iterative formulas to you. Close both of these boxes and return to the spreadsheet.
• Turn Iterations in the workbook BACK ON . This can be done through the following Excel menu command: Tools → Options → Calculations .
• If you have computed this correctly, Net Income should have increased/(decreased) by the Net Interest Income/(Expense) amount, and several Balance Sheet items should change slightly (both in the current year and in future years).
• Check every formula and make sure Balance Sheet for the first projection year still balances .
• If everything still looks good, copy the Net Interest Income/(Expense) formula from the first projection year into the remaining, future projection years. Once again, make sure no errors get introduced, and check to make sure the Balance Sheet still balances in all years. (If an error gets introduced, strongly consider closing it without saving it , and trying again with the saved file.)
• If your Balance Sheet still balances and the Interest calculations appear correct, then SUCCESS!

Congratulations—you are done building your integrated, three-statement financial model! Here are just a few things to consider and check before considering the model 100% complete.

• Never hardcode any number in a formula. Check formula cells to make sure there are no hardcoded values or assumptions in them.
• Make sure that, where applicable, assumptions are the first inputs in each calculation formula.
• Check to see if the trend of each important line item is gradually increasing or decreasing rather than exponentially increasing or decreasing . If you have exponential patterns, you should probably change the assumptions driving them.
• Insert any relevant comments (you can use the shortcut “Shift + F2”) to help other teammates understand your model and assumptions.
• Press “Ctrl + Home” on each worksheet in the Excel workbook so that cursor is on cell A1 in each sheet, before sending the model to your teammates.

The 3 Statement Financial Model: Income, Balance Sheet, and Cash Flow

As a startup founder, you know that a robust financial model is the Swiss Army knife in your toolkit. It's not just about keeping the lights on; it's about illuminating the path ahead, helping you navigate the twists and turns of business growth with precision and confidence. 

Whether you're trying to make sense of the numbers, pitching your vision to investors, or strategizing your next capital raise, your financial model is your north star. Yet, the journey from spreadsheet novice to financial maestro is fraught with complexity and time-consuming challenges.

This blog post aims to demystify the 3-statement model , breaking down its components and getting to the root of how it can be a game-changer for startup founders looking to steer their companies with confidence and clarity.

Table of Contents

The essence of the 3-statement model, financial modeling at the pre-revenue stages, financial modeling at the post-revenue stages, the path forward.

• Getting the Support You Need

The 3-statement model is a combination of three key financial statements: the income statement, balance sheet, and cash flow statement. Each statement offers a unique lens through which to view your business's financial status, and together, they provide a powerful holistic financial overview.

These aren't just reports; they're a narrative, a detailed story of your business's financial health and operational efficiency.

The Income Statement: This statement is the storyteller of your business's operational performance, detailing revenues, expenses, and ultimately, profit or loss over a specific period. It answers the question, "Is the business profitable?"

The Balance Sheet: Acting as a financial snapshot at a point in time, the balance sheet reflects what your company owns (assets), owes (liabilities), and the equity built by the owners. It's a testament to your company's stability and financial resilience.

The Cash Flow Statement: Cash management is the linchpin of business operations. This statement tracks the flow of cash in and out of your business, categorizing cash movements into operations, investing, and financing activities. The cash flow statement is crucial for understanding liquidity and ensuring the business can meet its short-term obligations.

The Synergy of the Three Statements

The real power of the 3-statement model lies in its interconnectedness. Changes in one statement impact the others, creating a dynamic view of your business's financial reality. For instance, revenue growth boosts profits on the income statement, which affects cash in the bank (on the balance sheet) and is reflected in the cash flow from operations. This synergy allows founders to see the ripple effects of business decisions across the entire organization.

Income Statement Without revenue, the focus shifts to planning for future income and managing expenses. Estimate potential revenues using industry benchmarks and market analysis and create assumptions around growth. Closely monitor and categorize all operational expenses to maintain control over cash flow and understand your burn rate. Highlight any initial funding or investments as indicators of financial support and confidence in the business model.

Balance Sheet Concentrate on listing your current assets, including cash reserves and any capital equipment or technology investments. Adjust liabilities to reflect any loans or financial obligations. This revision ensures a clear view of your company's financial standing, emphasizing assets over immediate revenue generation.

Pre-Revenue Cash Flow Document cash outflows related to operational expenses and initial investments, focusing on maintaining a clear record of where capital is being allocated. Include any financing inflows, such as investor capital or loans, which are crucial for sustaining operations before revenue streams are developed.

Income Statement Evolution As revenue begins to flow, closely monitoring COGS and gross margin becomes crucial for understanding product profitability and managing operating expenses to ensure profitability scales with sales.

Balance Sheet Management With expanded operations, the balance sheet requires detailed attention to things like inventory, receivables, and payables to optimize cash flow, balancing debt and equity to maintain financial health while possibly introducing more complex financial strategies .

Cash Flow Dynamics Revenue activation transforms the cash flow statement, emphasizing the importance of operating cash flow, investment in growth, and financing strategies to enhance the cash conversion cycle for financial sustainability.

Strategic Forecasting and Scenario Planning A richer financial model enables the art and science of forecasting and scenario planning , allowing for proactive management and strategic decision-making to navigate startup growth effectively.

Integrating Advanced Models Growing business complexity necessitates the use of advanced models like the Consolidation Model , providing deep insights into various business segments for detailed profitability and cost analysis, ensuring the financial model accurately captures the nuances of the business.

Understanding and implementing the 3-statement model can be a complex process, but it's essential for making informed business decisions. This model not only helps you manage your current financial health but also aids in forecasting future performance. By closely monitoring and adjusting your financial model, you can identify growth opportunities, manage risks, and build a resilient business poised for long-term success.

As you embark on this financial journey, remember that the goal is not just to manage your company's finances but to harness them in a way that propels your business forward. Embrace the complexity, revel in the learning, and let your financial model be the compass that guides you to entrepreneurial success.

Real-World Applications

Theory often serves as the foundation, but it is the application of this theory that propels startups to success. The following case studies illustrate how startups across different sectors have effectively implemented the 3-statement model, navigating through various financial landscapes to achieve their goals.

Navigating Market Uncertainty: Science On Call's Strategic Financial Overhaul

Launching just before the pandemic hit, Science On Call , co-founded by Luisa Castellanos, faced immediate and unprecedented challenges as the restaurant industry, their primary customer base, was thrust into turmoil. Initially relying on a basic financial model, the team quickly realized the need for a more sophisticated approach to navigate the volatile market. By upgrading to Forecastr, Science On Call enhanced its financial planning, enabling a level of collaboration and strategic decision-making that was previously out of reach. This shift was pivotal not only for internal management but also played a critical role in their fundraising efforts, leading to a successful $1.1 million pre-seed SAFE round. The ability to adapt its financial model and strategies in real-time allowed Science On Call to stabilize and position itself for growth despite the uncertain market conditions, showcasing the importance of flexible financial modeling in responding to external shocks and securing investor confidence.

Pivoting with Precision: Local Sports Network's Strategic Model Transformation

Local Sports Network (LSN) , led by Dustin D. McMahon, faced a pivotal challenge early in its journey of connecting high school sports fans through live streaming, social networking, and fan apparel. Initially adopting a subscription-based model, LSN discovered through feedback that an advertising model would better serve their long-term goals. This required a complete overhaul of their revenue strategy, a daunting task given the team's lack of experience with ads-based models. Enter Forecastr, introduced to Dustin during a Techstars accelerator program, which provided the tools and expertise to develop a new financial model tailored to this shift. Forecastr's collaborative approach and ability to translate complex financial scenarios into understandable terms enabled LSN to confidently present its vision to investors, securing its position for future growth. This partnership not only facilitated a seamless transition to an advertising revenue model but also prepared LSN for scalable success and future fundraising efforts, highlighting the critical role of adaptive financial modeling in navigating startup evolution.

How HyperTrader Modeled Its Way to Success in the Volatile Cryptocurrency Markets

In the face of fluctuating customer acquisition rates and rapidly changing product relevance within the cryptocurrency market, HyperTrader , under the leadership of Amar Gautam, embarked on a critical pivot. Initially planning to bootstrap to Series A funding, the team quickly realized the necessity of revising their revenue strategy and enhancing their financial modeling to secure funding. By partnering with Forecastr, HyperTrader was able to refine its financial model, enabling data-driven decisions that facilitated an updated product launch, immediate revenue generation, and ultimately, the successful raise of $1.55 million in seed funding. This case study exemplifies the power of adaptive financial strategy and precise modeling in navigating the complexities of startup growth and market volatility.

Need more support?

We understand that the leap from understanding these concepts to implementing them in a dynamic, integrated financial model can be daunting. 

Forecastr illuminates the path to 3-Statement Model with a user-friendly online platform that transforms the complex web of your financial data into a coherent, actionable 3-statement model . We've stripped away the guesswork and technical hurdles, presenting you with a streamlined interface that makes financial modeling accessible, regardless of your background in finance.

But we didn't stop there. Recognizing that every startup has unique needs, we offer a bespoke onboarding experience. Imagine having not one, but two financial analysts, joining forces with you to tailor your financial model. Leveraging your data, assumptions, and aspirations, we craft a model that's not just a tool but a strategic asset. It gives you insights into cash flow dynamics, growth trajectories, and the financial levers at your disposal.

We're not just a platform or a service; we're your finance partner. Empowering you to approach finance with confidence, to navigate the complexities of startup growth with ease, and to carve out a future that's not only envisioned but realized. Welcome to the future of financial modeling.

Mastering Startup Financial Models: A Comprehensive Guide for Founders

Some founders don’t think about creating a startup financial model until it’s too late. It’s easy to do. You’ve got your nose to the grindstone,...

How Many Revenue Forecasts Can You Fit into One Financial Model?

In our experience helping founders build great financial models, we’ve worked with startups that focus on SaaS, ecommerce, import/export,...

12 min read

Building a Solid Financial Model for Startups: The Key to Success

Stepping into the startup world feels like diving headfirst into a sea of uncertainties. But, crafting a solid financial model for startups is your...

• Video Tutorials
• Knowledge Base
• Group Licenses
• Why Choose Us?
• Certificates

• 3-Statement Model Tutorials

Knowledge Base: 3-Statement Model Tutorials

A 3-statement model links the income statement, balance sheet, and cash flow statement into one dynamically connected financial model.

Balance Sheet Forecast: How to Project the Balance Sheet (19:03)

Subscription revenue model (netflix) (17:37).

Get 40% off all Felix plans  

• The Investment Banker Micro-degree
• The Project Financier Micro-degree
• The Private Equity Associate Micro-degree
• The Research Analyst Micro-degree
• The Portfolio Manager Micro-degree
• The Restructurer Micro-degree
• Fundamental Series
• Asset Management
• Markets and Products
• Corporate Finance
• Mergers & Acquisitions
• Financial Statement Analysis
• Private Equity
• Financial Modeling
• Try for free
• Pricing Full access for individuals and teams
• View all plans
• Public Courses
• Investment Banking
• Investment Research
• Equity Research
• Professional Development for Finance
• Commercial Banking
• Data Analysis
• Team Training
• Felix Continued education, eLearning, and financial data analysis all in one subscription
• Learn more about felix
• Publications
• Online Courses
• Classroom Courses
• My Store Account
• Learning with Financial Edge
• Certification
• Masters in Investment Banking MSc
• Student Discount
• Find out more
• Diversity and Inclusion
• The Investment banker
• The Private Equity
• The Portfolio manager
• The real estate analyst
• The credit analyst
• Felix: Learn online
• Masters Degree
• Public courses

3-Statement Model

By Deborah Taylor |

September 18, 2023

What is a 3-Statement Model?  

A 3-statement model forecasts a company’s income statement , balance sheet , and cash flow statement by linking them. The aim of a financial model is to predict a company’s profitability, financial position, and cash generation; building a 3-statement model improves the accuracy of forecasting because a change in one financial statement will result in adjustments to the others. These adjustments act as a check on the validity of the assumptions and forecasts.

3 statement models are built in Excel and typically the income statement is created first, followed by the balance sheet and then the cash flow statement. The cash flow statement helps forecast cash and short-term borrowings; this is an important step in ensuring that the model links correctly. The final step is to calculate interest expenses and include them in the income statement.

Key Learning Points

• 3-statement modeling links the forecast income statement, balance sheet, and cash flow statement.
• A change in one financial statement will flow through to the others, acting as a check on the validity of the forecasts.
• A 3-statement model usually starts with the income statement, then the balance sheet, and finally the cash flow statement.
• The cash flow statement helps forecast cash and short-term borrowings and is an important step in linking the three statements.
• Short-term borrowings are often referred to as a “revolver” – an abbreviation for a revolving credit facility.

9 Steps in Building a 3-Statement Model

The nine steps in building a 3-statement model are:

• Input the historical data: Start by inputting historical data (referred to as “actuals”) for the income statement and balance sheet.
• Calculate ratios and statistics: These are calculated using the historical data to help understand historical performance and business drivers.
• Decide on forecast assumptions: Use the ratios and statistics to create forecast assumptions. For example, understanding historical performance could help us predict that sales will increase by 5% each year during the forecast period.
• Build forecast income statement except for interest: Forecast each income statement using the assumptions – this will include sales increasing by 5% each year. However, we can’t forecast interest income and expense at this point as the cash and debt balances haven’t yet been forecast.
• Build the forecast balance sheet except for cash and revolver: Forecast each balance sheet item using the assumptions. However, we can’t forecast cash and the revolver at this point as this relies on the forecast cash flow statement.
• Build the cash flow statement using the rules of cash: Forecast the cash flow statement using the forecast income statement and balance sheet and the rules of cash. We calculate ‘cash net of revolver’ at the bottom of the cash flow statement – effectively treating a revolver as a negative cash balance.
• Use max/min to fill in balance sheet cash and revolver: A positive ‘cash net of revolver’ balance is included in the balance sheet as cash (with a zero-revolver balance) while a negative ‘cash net of revolver’ balance is included in the balance sheet as a revolver (with a zero cash balance). We use the Excel max and min function for this as follows: a) Cash balance = MAX (0, ending cash net of revolver) b) Revolver balance = -MIN (0, ending cash net of revolver)
•  Build the interest calculations: Interest income is calculated using the forecast cash balance and interest expense is calculated using the forecast revolver and long-term debt balances. Interest income and expense are typically calculated using the average of opening and closing balances.
• Link interest into the income statement and deal with circular references: Interest can now be linked into the income statement and any circular references that arise will need to be resolved. The interest will lead to some changes in the net income, which will, in turn, affect the cash flow statement and cash on the balance sheet. Assuming we built our model correctly, this should all balance.

Example – Forecasting the Cash Flow Statement, Cash, and Revolver

Given below are extracts of a model for The Hershey Company. We will use these to show how to build a forecast cash flow statement and to forecast cash and the revolver. We will build our calculations only in the first forecast year.

Forecast net income (excluding interest) for Year 1 is $1,793.1m. A part-complete forecast balance sheet and calculations are provided below

At this stage, it’s important to note that the balance sheet doesn’t balance because we have not yet forecasted cash or the revolver.

Our rules of cash are:

• An increase in assets results in a cash outflow
• A decrease in assets results in a cash inflow
• An increase in liabilities or equity results in a cash inflow
• A decrease in liabilities or equity results in a cash outflow

Applying these rules to the forecast balance sheet allows us to build the forecast cash flow statement as follows:

The balance at the bottom of the cash flow statement is ($475.1m), which is effectively a negative cash balance. This is a liability and needs to be included as a revolver balance of $475.1m in our forecasts, while the forecast cash balance will be zero.

We use the MAX and MIN functions to forecast the cash and revolver balance as shown below – note that we include a minus sign before the MIN function to ensure that the revolver balance is shown as a positive number in our model.

At this point the balance sheet should now be balanced – this is an important way to check the integrity and accuracy of a 3-statement model.

The final steps to complete the model are to build the interest calculations using average cash and debt balances and then to include interest in the income statement.

Before sharing your model with your colleagues, it is important to do a final check of the following:

Download the accompanying Excel exercise sheets to practice 3-statement modeling and to access the completed model for the Hershey Company. Take our online financial modeling course & learn how to build financial models from Wall Street instructors.

A 3-statement model forecasts a company’s income statement, balance sheet, and cash flow statement by linking them. A change in one financial statement will flow through to the others, acting as a check on the validity of the forecasts. The model usually starts with the income statement, then the balance sheet, and finally the cash flow statement. The cash flow statement helps forecast cash and short-term borrowings and is an important step in linking the three statements. Short-term borrowings are often referred to as a “revolver” which is an abbreviation for a revolving credit facility.

Additional Resources

Complete Investment Banking Course

Linking Three Financial Statements

What Makes a Good Financial Model?

DCF Modeling Guide

Share this article

3 statement model excel template.

Sign up to access your free download and get new article notifications, exclusive offers and more.

Featured Online Course

Monthly Three Statement Model

• Peter Lynch

This post will focus on a few changes to keep in mind as you transition from building three statement models with annual periods to three-statement models with monthly periods. Download Monthly Three-Statement Model .

If you are new to building models, or lack experience with three statement models, we recommend getting started with an annual three-statement model before proceeding with this content (please see the Integrated Financial Statement Model video series for an introduction).

Revenue Growth in a Monthly Financial Model

In a financial model with annual periods, revenue is projected as a change over the prior period. For this reason some analysts moving to monthly projections attempt to project revenue in a similar fashion. But in most cases, especially for established businesses, growth should be projected on a Year-Over-Year (YOY) basis.

A YOY calculation compares each month in the projected period with the same month in the previous year. By way of example, revenue in January of 20X2 should be calculated as (revenue in January 20X1)*(1 + growth rate). This rule is not without exception, but it applies in most cases. If this concept remains unclear, the video that follows provides a simple introduction with visuals.

Projecting Expenses on the Income Statement

For an explanation of the formulas used to project expenses on the income statement please see the link that follows: Cost Structure: Fixed, Variable and Semi-Variable .

Working Capital Accounts: Days Used to Calculate DSO, DIO and DPO

The number of days used to calculate accounts receivable, inventory and accounts payable must match the number of days in the period used. Whereas we previously used 365 days in a model with annual periods, now we need to divide both Revenue and COGS by 30 or the number of days in the month.

For a little more detail on why this is required, let’s revisit the diagram from the Introduction to Financial Statements video series, which gives us a superficial view of how the three financial statements relate to each other.

What’s important to emphasize is that the balance sheet is always the financial position of the company. It represents the business in its entirety at any given moment. The income statement and cash flow statement, however, deal with the passage of time and represent the economic activity of the business over the interval that has elapsed between two balance sheets. For this reason the number of days needs to be adjusted to evaluate these three working capital accounts on the basis of revenue per day and COGS per day.

Working Capital Accounts: Projected on a YOY Basis

Much like revenue growth in a monthly model, the formulas used to calculate working capital balances in the projected period should pull from the same month in the prior year. To smooth out any unusual spikes in working capital, it can also be a good idea to take an average of three months by including the periods before and after the month being projected. This is visible in the projected period of the template available for download.

Accruals: Example Using Bonus Payments

You may recall from the Introduction to Financial Statements video series that on an accrual basis of accounting, revenue is recognized when earned and expenses are recognized when incurred. If an accounting period has a journal entry recording revenue or an expense that does not coincide with a cash transaction in the same period an accrual is required.

The model associated with this post includes several accruals, but the most intuitive is related to bonus compensation. A bonus is earned over the course of the year, but paid in the year that follows. This expense runs through the income statement, and builds on the balance sheet as a liability until it is paid out. For an example please see the three statement model available for download. The bonus calculations can be found under row 75.

Interest Expense vs Interest Paid

Interest expense is a period expense, so it appears in each period on your income statement in a financial model. Per most credit agreements, however, interest is only paid on a quarterly basis. To reconcile this timing difference another accrual must be included on the balance sheet. Please see this link for an explanation: Interest Expense in a Monthly Model .

Growth Rates

Working with monthly data may require converting annual growth rates to monthly growth rates. This is a calculation that is frequently done incorrectly. For a short video explaining the proper approach please see the video below. Alternatively, a dedicated post is available at the following link: Converting an Annual Growth Rate to a Monthly Growth Rate .

Download links are sent directly to your inbox! Input your email address below and we will send you an email with the information requested.

Subscribers, please log in to download documents directly.

You will only need to provide your email address the first time. All future downloads will be sent to the same email address.

A link to this file will be sent to the following email address:

If you would like to send this to a different email address, Please click here then click on the link again.

Simon Lundgren

• IFRS 16 lease excel model
• SIE4 to Excel
• Excel to SIE
• Rocket add-in for Excel

Three-statement financial model and DCF - Apple

I have developed a complete and comprehensive three-statement model of Apple Inc. using various supporting schedules. I have connected the three-statement model to a discounted cash flow model (“DCF model”), which is a type of financial model that values a company by forecasting its’ cash flows and discounting the cash flows to arrive at a current, present value.

The complete Excel workbook can be downloaded from my GitHub repository .

Screenshots:

Related Posts

November 17, 2022

How to Calculate Beta In Excel – All 3 Methods (Regression, Slope & Variance/Covariance)

by Simon Lundgren

April 11, 2021

Price-Volume-Mix analysis on Apple

The Price-Volume-Mix analysis, let us…

February 22, 2021

Automatic collection of exchange rates from Riksbanken to Excel

VBA script for automatic collection of…

Privacy Preference Center

Privacy preferences, privacy overview.

IB Case Studies: 3-Statement Modeling Test

by johnswan | Aug 10, 2020

Get the files and resources here:

Investment Banking Case Studies: What to Expect, and How Not to Choke

https://samples-breakingintowallstreet-com.s3.amazonaws.com/IBIG-06-01-Three-Statements-30-Minutes-Blank.xlsx

https://samples-breakingintowallstreet-com.s3.amazonaws.com/IBIG-06-01-Three-Statements-30-Minutes-Complete.xlsx

Table of Contents:

3:34 Step 1: Fill Out All the Assumptions (if possible)

11:34 Step 2: Fill Out the Entire Income Statement

13:49 Step 3: Fill Out What You Can of the Balance Sheet

16:03 Step 4: Fill Out the Entire Cash Flow Statement

20:35 Step 5: Finish Linking the Balance Sheet

23:25 Step 6: Check Your Work and Answer the Questions

• Study Guides
• Homework Questions

ACCT 572 - Netflix Case Study Solution