For most items on the financial statements, the historical information provides sufficient data to project the future. Some items, however, must first be calculated on a different financial statement or on a supporting schedule. All such items will be shaded purple to indicate that this data will be linked later in the process.
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.
- In a 3-statement model, you input the historical versions of these statements and then project them over a ~5-year period.
- Learn why operating models are critical for making more informed business decisions.
- 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.
- The case study document tells us to “follow company guidance” for these last few line items.
- The full course has 3-statement models with and without templates for additional practice.
Become a financial modeling pro
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.
Learning
With the three primary financial statements projected, the next step is to build the supporting schedules. As these schedules are built the items shaded in purple can be appropriately linked to complete the model. 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. Learn to build a 3-statement financial model using Excel with linked assumptions, depreciation methods, working capital, and revolver schedules.
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. 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). It would also be helpful to know about something like the degree of operating leverage, so we could better forecast different expenses. For this tutorial, I picked an example where you start from a blank sheet and review the company’s filings and presentations.
Foundation of a good forecast – Microsoft Excel Tutorial
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.
Forecast Revenue & Cost of Sales
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. There is no “blank” or “beginning” file because we create a new sheet in Excel and enter everything from scratch in this tutorial. 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.
Watch the Full Tutorial
- With the income statement projected (purple-shaded line items excluded), the next step is to project the balance sheet.
- If you have an upcoming 3-statement modeling test, get as many examples as possible and complete them.
- All such items will be shaded purple to indicate that this data will be linked later in the process.
- 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).
But if you can finish in 2-3 hours, you’re at the level where you can improve excel for finance: building a three-statement operating model videos 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.
Start learning today
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. A Simple Model exists to make the skill set required to build financial models more accessible. If you improve over time and find it interesting to pick apart companies and business models, great.
Share what you’ve learned, and be a standout professional in your desired industry with a certificate showcasing your knowledge gained from the course.