Sadaf Abbas, with over 16 years in the financial consulting realm, has showcased her expertise across diverse industries like Blockchain, Gaming, and SaaS. As a CFO for leading companies, she’s transformed complex financial scenarios into actionable strategies. Now, as the CEO of Oak Business Consultant, her leadership has driven the firm to unparalleled heights, marking it as a benchmark for excellence and innovation. Beyond her corporate achievements, Sadaf is also a revered educator, blending theoretical and practical insights to shape the future of financial analysts and consultants.
Statement Financial Modeling Excel Analysis
Similar to the section above, we take last period’s closing balance, and then add any increases or decreases in debt, and arrive at the closing balance. In this step, we take the historical financial information of the company and either download, type or paste it into Excel. Once the information is in Excel, you’ll need to do some basic formatting to make the information easy to read and to make it follow the structure you want your model to take. As you can see in the screenshot below, the historical information is entered in a blue font color under the historical time periods. Every individual line item on the cash flow statement should be referenced from elsewhere in the model (it should not be hardcoded) as it is a reconciliation. Constructing the cash flow statement correctly is critical to getting the balance sheet to balance.
Services/ Consulting Financial Model
In this lesson, we’ll model the Income Statement of a 3-statement financial model. A 3-statement financial model consists of a full Income Statement, a full Cash Flow Statement, and a full Balance Sheet. Following this, you’ll need to fill your sheet with historical financial data points and build off of those figures. Public company’s historical financial data can be found in their 10k on sec.gov or on the company’s own investor relations page. Inputting historical balance sheet data is similar to inputting data in the income statement. Cash is going to come from the very bottom right here, the ending cash balance.
Previously, we learned about the different line items on the Income Statement, Cash Flow Statement and Balance Sheet as well as how they’re interconnected to one another. This course builds on top of that knowledge and shows how to integrate the three financial statements in Excel. We’ll learn how to project many different line items that we didn’t learn before, such as PP&E, Goodwill, Retained Earnings, Treasury Stock, AOIC, etc. The Cash Flow Statement is generated from fluctuations in the figures of the balance sheet and income statement. It begins with net income derived from the income statement, and subsequently adjusts for non-cash items and alterations in working capital to compute cash from operations. Project cash flows from investing and financing activities based on the company’s strategic plans.
You could have saved some time here by not doing exactly what I did, and waiting to copy across some of these instead. So, the better solution is to fill in what you can and at each step of the way, like we’re doing here, make sure that you’ve properly filled in everything in the section and linked it. If you don’t want to build a complete model, you can always use forecasting software to model scenarios and assess possible outcomes. Use the three-statement model to identify potential risks and test strategies.
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. 3-statement models include a variety of schedules and outputs, but the core elements of a 3-statement model are, as you may have guessed, the income statement, balance sheet, and cash flow statement. With the three primary financial statements projected, the next step is to build the supporting schedules.
Before you build your first model, it’s important to understand that the three financial statements are interlinked in multiple ways. We will use these to show how to build a forecast cash flow statement and to forecast cash and the revolver. A Simple Model exists to make the skill set required to build financial models more accessible. Learn accounting, 3-statement modeling, valuation/DCF analysis, M&A and merger models, and LBOs and leveraged buyout models with 10+ global case studies.
- These include all types of expenses, including sales, marketing, and operating expenses.
- The sensitivity analysis focuses on one output of the financial model and how one or two inputs attached to it affect it—observing the EPS changes while making changes in the revenue growth or margins.
- Click here to learn how to build a sensitivity analysis into a 3-statement model.
- 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.
How Do You Build a Three-Statement Model?
Then for the Amortization, we can take the 10% and multiply by this number right here. Yes, in real life it could very well be different from this, but again, this is an example of a simplification we might make. You might be looking at this and saying, “Well, what about a minimum formula? Because in an LBO and other debt models, we have to check to make sure that we don’t amortize more than the total amount.” But look at these numbers, 10% over 5 years only adds up to 50%.
As these schedules are built the items shaded in purple can be appropriately linked to complete the model. Start by projecting revenue growth based on historical performance, market analysis, and industry trends. Then, estimate future costs of goods sold (COGS) and operating expenses, keeping in mind any expected changes or strategic initiatives that may impact these figures. It serves as an excellent resource for financial planners, analysts, and investors alike, aiming to forecast future financial scenarios and gauge the impact of financial activities over time. With its comprehensive approach, the 3 Statement Financial Model is indispensable for achieving a holistic financial analysis, fostering clarity and strategic planning in the complex world of finance. At this point, we need to forecast capital assets such as property, plant, and equipment (PP&E), before we can finish the income statement in the model.
You also want to see if you’ll be able to maintain the right interest coverage ratio and enough cash reserves. Subtract the dividend, if any, from the net income to calculate the retained earnings. These decisions will impact multiple parts of the model, including interest expense and cash position. You’ll also need to factor the effect of depreciation expense into the income statement. Days receivables and days inventory typically aren’t relevant for SaaS companies since they don’t have inventory, and in most cases, accounts receivable only result from failed credit card payments. Be mindful of these linkages so you can use accurate formulas in your model.
Initially a brief outline is provided detailing the sequence in which this model will be built. Keeping this sequence in mind as you build the model provides a good reference for progress made. Insert the capital assets and debt figures from the schedule you just created into the balance sheet. Not all changes on the balance sheet impact the income statement or cash flow. Each line item on the income statement impacts the balance sheet or cash flow in some way, which is why the income statement is the first statement you should 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. 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.
Project the revenue four to five periods out, as per your preference, using the assumed growth rate. Read on to learn how you can build your own models once you’ve had a chance to play around with an Excel template. To create the income statement, you sum up the revenue and other incomes and reduce the sum by the amount of expenses to get a net profit. Let’s take a closer look at each of the three elements in the three-statement model to understand the ways in which they’re connected. If you improve over time and find it interesting to pick apart companies and business models, great. For this tutorial, I picked an example where you start from a blank sheet and review the company’s filings and presentations.
This will typically be determined by the purpose of the 3-statement financial model. Once the information is in Excel , you’ll need to do some basic formatting to make the information easy to read and to make it follow the structure you want your model to take. All countries have mandatory regulations in place with regards to maintenance of books integrated 3-statement build of accounts. In this lesson, we’ll learn how to project Accumulated Other Comprehensive Income and Losses on the Balance Sheet. In this lesson, we’ll learn how to project Treasury Stock on the Balance Sheet. In this lesson, we’ll learn how to project Retained Earnings on the Balance Sheet.
