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

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:

1. Blank Sheet / Strict Time Limit: These are more about working quickly, knowing the Excel shortcuts, simplifying, and making decisions under pressure.
2. 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.
3. 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:

The full course has 3-statement models with and without templates for additional practice.

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 Year-Over-Year (YoY) 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.