Designed for working professionals and students, it helps users upskill with expert-led, short-format courses. Finalize your model by assembling a statement of cash flows to understand how cash is generated and consumed. In a 3-statement model, you input the historical versions of these statements and then project them over a ~5-year period. If you have an upcoming excel for finance: building a three-statement operating model videos 3-statement modeling test, get as many examples as possible and complete them. Banks like to test this topic because it’s a quick way to assess who’s proficient in Excel, accounting, and financial modeling.

Types of 3-Statement Modeling Tests

With the income statement projected (purple-shaded line items excluded), the next step is to project the balance sheet. Five items will need to be shaded purple on the balance sheet for the same reason outlined above. For this exercise two years of historical financial data are provided to build the model. To complete this step you will need to link the information contained on these two worksheets to the template available on a separate worksheet. 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.

LinkedIn Learning

At a high level, this model confirms that most of the company’s claims are reasonable. 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. The case study document tells us to “follow company guidance” for these last few line items. 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.

Download courses and learn on the go

Learn how to build a dynamic financial operating model in Excel from scratch just like the investment bankers do it. We make it super simple by taking you step by step through each line using the example of a lemonade stand. If you cannot read or interpret a company’s historical financial statements, you won’t be working on complex deals anytime soon. In financial modeling, the “3 statements” refer to the Income Statement, Balance Sheet, and Cash Flow Statement. The Juno School is a learning platform that offers free courses in marketing, Excel, data analysis, AI tools, communication, business, and career development.

Statement Model, Part 1: Inputting the Historical Statements

Juno School workshops are led by industry professionals and experts with years of experience in their respective fields, including marketing, AI, data, and sales. Nihaar has built a career at the intersection of finance, strategy, entrepreneurship, and Media & Entertainment. His experience includes working as Head of New Business Development for Sean ‘Diddy’ Combs, in the Content Strategy & Analysis Group at Netflix, and in investment banking.

Statement Model, Part 2: Income Statement Projections

Finalize your income statement by connecting the operating expenses section, identifying capital expenditures and filling out the the depreciation and tax lines. With the balance sheet projected, the next step is to project the cash flow statement. Learn to build a comprehensive 3-statement model in Excel and make data-driven decisions. Begin assembling your income statement by connecting it your revenue and COGS assumptions. Whether you’re a finance professional at an NGO, a small-business CFO, or an aspiring analyst, mastering a robust Excel-based forecasting framework is essential. Though our demo is denominated in Nigerian Naira (₦), all techniques apply globally.

Financial Wellness: Managing Personal Cash Flow

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 full course has 3-statement models with and without templates for additional practice. Each workshop typically includes a 30–60 minute video, real-world examples, downloadable resources, and a certificate on completion. This course is delivered through on-demand video lessons, allowing you to learn at your own pace. Practical examples and Excel templates are provided to reinforce your understanding. 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.

• There is no “blank” or “beginning” file because we create a new sheet in Excel and enter everything from scratch in this tutorial.
• A three-statement model links the income statement, the balance sheet and the cash flow statement of a company, providing a dynamic framework to help evaluate different scenarios.
• Once the historical data has been included in the template, the next step is to project the income statement.
• We would examine this point and refine these projections if we had several hours or days to complete this case study.
• Whether you’re a finance professional at an NGO, a small-business CFO, or an aspiring analyst, mastering a robust Excel-based forecasting framework is essential.

Follow along with Chris Reilly, financial modeling expert and experienced Excel trainer, as he shows you how to build a three-statement model in Excel. Chris explains how to clean up source data, build the cash flow statement, build EBITDA, and forecast the income statement, balance sheet, debt schedule, and more. In this video you will learn to build a fully functional and dynamic three-statement financial model in Excel. A three-statement model links the income statement, the balance sheet and the cash flow statement of a company, providing a dynamic framework to help evaluate different scenarios. Once the historical data has been included in the template, the next step is to project the income statement.

• It would also be helpful to know about something like the degree of operating leverage, so we could better forecast different expenses.
• As these schedules are built the items shaded in purple can be appropriately linked to complete the model.
• 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.

Understand what a financial operating model is and what attributes make for a good one. This video will follow the procedure outlined in the previous video titled Overview of the Process, but the model built will be far more thorough. Get a crash course on accounting, 3-statement modeling, valuation, and M&A and LBO modeling with 10+ global case studies.

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. 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). We would examine this point and refine these projections if we had several hours or days to complete this case study. 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). Lease accounting is more complicated in real life and under IFRS, but this approach is fine for a U.S.-based company. In this part, we focus on projecting the Working Capital line items, such as Accounts Receivable (AR), Inventory, and Accounts Payable.