Complete financial modeling guide
A free step by step guide on financial modeling in Excel.
A free step by step guide on financial modeling in Excel.
This is designed to be the best free guide on financial modeling! In this guide, we will break down all the most important aspects of financial modeling, and provide tips and tricks about industry leading best practices.
CFI’s mission is to help anyone in the world become a world class financial analyst. With that goal in mind, we we’ve designed this guide to be extremely practical with specific takeaways that can help you improve your financial modeling skills.
This financial modeling guide will cover several important topics designed to sharpen your financial analysis. Topics in this guide include:
All of these topics are covered in more detail in our online financial modeling courses.
For anyone pursuing or advancing a career in corporate development, investment banking, financial planning and analysis FP&A, equity research, commercial banking, or other areas of corporate finance, building financial models is part of the daily routine.
Financial models are essential just tools to help make decisions. These decisions often include: whether or not to invest in a company, asset, or security; whether or not to invest in a project (project finance); whether or not to do a merger or acquisitions, and whether or not to raise money; and other corporate finance transactions.
The financial model allows decisions makers to test scenarios, observe potential outcomes, and hopefully make an informed decision. There is a lot of talk about software programs that can be used, but the truth is the vast majority of financial modeling takes place in Excel.
Excel is the main tool used by banks, corporations, and institutions to perform financial modeling. The main reason for this is Excel’s phenomenal versatility. Every company or investment opportunity is unique, and Excel is a blank canvas that can be totally customized and tailor to the situation. The flip side of this is that there are no controls or rules in place to ensure the model is accurate or error free.
Here are some of the most important Excel tips for financial modeling:
For a refresher on basic Excel functions check out our free Excel Crash Course.
When you’re ready to take your skills to the next level, our Advanced Excel Formulas course will help you stand out from the pack.
Over and above good Excel skills, analysts who really stand out at financial modeling are great at structuring and organizing their spreadsheets.
Here are our top 10 best practices for structuring a model:
The “art” of financial modeling mostly relates to making assumptions about the future performance of the business being modeled. This is the most subjective and important part in the valuation of a company.
This guide will outline various approaches to forecasting, which include:
We’ve created another detailed guide on how to link the 3 financial statements, but we will also provide a quick recap of it here. Once the forecast assumptions are in place, it’s just a bunch of basic mathematical operations to fill in the three financial statements in the model.
From a financial modeling perspective, this is the least subjective part of the process. With the assumptions clearly stated, an analyst more/less multiplies, divides, add or subtracts to produce the statements.
Step #1 – begin by calculating revenue, based on the forecasting approach used form the above section. From there fill in cost of goods sold, gross profit, operating expenses, and arrive at earnings before interest taxes depreciation and amortization EBITDA.
Step #2 – create supporting schedules for (i) capital assets (PP&E, depreciation, and capital expenditures), (ii) working capital balances (accounts receivable, accounts payable and inventory), and (iii) financing schedules to equity capital, debt balances and interest expense.
Step #3 – finish the income statement (depreciation, interest, taxes, net income) and fill in the balance sheet items except for cash, which will be the last part of the financial model to be completed.
Step #4 – build the cash flow statement, consisting of cash from operating activities, cash used in investing activities, and cash from financing activities. Combined these three sections will determine the closing cash balance, which links to the balance sheet to complete the financial model.
This is a simplified overview of the financial modeling process, or linking of the three statement, so please watch our video based courses on finical modeling if you’d like more detailed instruction.
With the baseline financial model in place it’s time to layer on whatever type of financial modeling exercise suits the situation.
We’ve published an overview of the various types of financial models, but to recap, the most common ones include:
When all of the above analysis is done, the work still not over. The last step is to develop charts, graphs, and other outputs that can be used to easily communicate the information from the model. This is where the best analysts really get to shine.
It’s one thing to build a complex model that only you understand, but it’s another thing to effective communicate the risks, rewards, and critical factors to all audiences.
As the capstone for your financial modeling training we recommend either an advanced Excel course to learn how to build all the best charts and graphs for a presentation, dashboard, or any other document you’re producing.
We hope this has been a helping guide to financial modeling in Excel, and has helped you advance your career as a financial analyst. At CFI, we pride ourselves on create the best free guides to help you get an edge.
Please check out these other resources to continue developing your skills: