How to Build a Financial Model in Excel – Tutorial | Corporate Finance Institute

How to Build a Financial Model in Excel – Tutorial | Corporate Finance Institute

welcome to building a financial model part 1 by the end of this session you will be able to apply a structured approach to financial modeling in Excel outline the hallmarks of good financial models and explain the importance of planning your model structure you will also forecast an income statement from operating revenue down to operating profit let's get going let's start with the importance of applying a structured approach to model building if you break apart a robust financial model you will typically see the same core building blocks robust models clearly separate inputs processing and output financial models should have inputs clearly identified and separated and allow for inputs to only be entered once the processing of inputs into outputs should be transparent and broken down into steps that are easy to follow output should be organized so that users can quickly access the outputs they wish to see or use because of their importance we'll explore each of these fundamental building blocks in more detail before we examine the building blocks it's important to stress that model building is not an iterative process models builds on the fly typically contain errors in order to minimize errors when building your financial models consider the following five steps first clarify the business problem what problem is this financial model meant to solve who are the end-users what do users need to be able to do with this model second think about all inputs and outputs needed try to keep the model as simple as possible ask yourself what are the minimum number of inputs and outputs that we can get away with and still have a useful model third plan your model structure including how the inputs processing and outputs will be laid out in your model if possible try to keep your inputs all in one place fourth once you've created your model consider Excel tools that will help protect the integrity of your model such as data validation and conditional formatting finally consider using test data to check that your model works as expected the second point we refer to on the previous screen related to simplicity think about a financial model that you have recently built or used how large is the model is it less than one megabyte or more than 10 megabytes there is an inherent tension in model building between making your model realistic and keeping it simple and robust large models may appear to offer users a high degree of detail and precision but by their very nature are harder to follow an audit small models are generally easier to follow an audit yet lack the degree of precision necessary for decision making the best financial models work to reconcile these opposing forces keeping things as simple as possible while still providing sufficient detail for decision making you now let's look at those financial modeling building blocks the inputs processing and outputs in more detail when building the input section of your financial model it's important to consider how your model ensures that inputs are accurate inputs remain within reasonable data ranges and that inputs are easy to use understand and update your model should be structured so that data is only ever entered once your inputs also should be clearly highlighted with color so that they are easily identified yellow shading or blue font color are often used for model inputs the models you will see in this course always have inputs shaded in yellow finally consider using Excel tools such as data validation conditional formatting and comments to help you maintain the integrity of your model inputs let's explore how excels data validation tool can be used to enhance the integrity of your financial models in Excel 2007 and later versions data validation is found on the data ribbon under data tools the data validation window has three tabs settings input message and error alert in this screenshot the gross margin input range has been set to between 0 and 90 percent although you cannot see the error alert tab it has been set to give the user a warning message if gross margins are set outside this prescribed range in Excel 2003 and previous versions data validation is found on the data menu if you put data validation on your model inputs you can check if a user has set inputs to numbers outside the preset data validation ranges – circle invalid data in Excel 2007 and later versions go to the data ribbon data tools data validation and then circle invalid data in Excel 2003 and previous versions go to view toolbars and then formula auditing okay let's see data validation first hand in Excel for this demonstration and throughout this course we use Excel 2007 in 2007 data validation is found on the data ribbon in 2003 data validation is found on the data menu before selecting data validation use your cursor to select the cell that has the gross margin input on the data validation window go to the Settings tab here the validation criteria will be set to allow a decimal we then set the data box to between the minimum to zero and the maximum to 0.9 we then go to the input message tab in the title box we will type please note in the input message box we will type please input your assumption for gross margins historically gross margins are between 0 and 90 percent on the error alert tab we will set the style to warning we'll set the title to warning and we will put an error message that reads you have input a gross margin assumption that is not between 0 and 90 percent once finished click OK to test whether the error message is working properly we can type in 95% into the gross margin input when the error message appears click yes to continue we then know it is working correctly now we can use the circle invalid data tool to find the input that is set outside the prescribed ranges in Excel 2007 this tool is found right next to data validation on the data ribbon in Excel 2003 you will need to make the formula auditing toolbar visible as explained previously now it's your turn click on the attachment link entitled data validation exercise with instructions once you've had a go check your attempt with the attached data validation solution good luck now that we've covered inputs let's consider how to translate inputs into outputs do you try to put all your processing calculations into as few cells as possible do you hide your processing cells or worksheets if you do then you may already know that hiding calculation cells or putting too many calculations into a cell makes your financial model harder to maintain and also harder to audit ideally you want your processing calculations to be easy to maintain structure to ensure accurate processing and above all else transparent so that users understand how the model works where possible break down complex calculations into several steps use comments to explain how your financial model works use formatting to ensure that formulas are not accidentally over typed and finally calculate final figures on your processing worksheets and then link these figures to your final output sheets this will make your model easier to follow and audit you ideally you want to make output cells or worksheets are easy to understand and unambiguous consider making your outputs modular so that the end-user can choose which outputs he or she wants to review for example put your income statement forecast balance sheet forecast and cash flow forecast on separate worksheets rather than all on the same worksheet consider creating a summary output sheet that allows users to review the key model outputs without having to go through the entire model use color to inform the user that your output cells are formulas and should not be touched finally consider protecting your output cells and worksheets in order to maintain model integrity let's talk about how to protect sales and worksheets the first thing you should know is that the default setting in Excel is for individual cells to be locked when a worksheet is protected this default setting means that all cells will be locked down although your models output cells should be locked input cells need to be unlocked before protecting a worksheet the Excel shortcut to unlock cells as well as format cells is control one the format cells window has a separate protection tab where you can lock and unlock individual cells in Excel 2007 and later versions once you've unlocked your input cells you then protect your worksheet by going to the review ribbon changes and then protect sheet in Excel 2003 and prior versions you can protect your worksheet by going to tools protection protect sheet now that we've explained the context let's demonstrate how locking and unlocking cells and protecting worksheets actually works in Excel although we are using Excel 2007 the shortcut for formatting cells is the same in all versions of Excel it is control one first we need to highlight the cells to unlock these are the three input cells highlighted in yellow then by pressing control one we access the format cells window we need to toggle to the protection tab we need to ensure that the locked button is deselected if it is selected make sure you deselect it and then click OK to return to the spreadsheet now we need to lock down the worksheet in Excel 2007 we go to the review ribbon changes and find protect sheet in Excel 2003 remember to go to the tools pulldown menu for this example we will leave all the default settings and not set a password passwords are optional once ok is selected all cells should now be locked except for the 3 input cells that were unlocked it's your turn again click on the attachment link entitled protecting data exercise with instructions and once you've had a go check your attempt with the attached protecting data solution enjoy now that we've covered the building blocks let's talk about actually building a financial forecast model over the next four sessions we will build a complete set of financial statement forecasts starting with the income statement in this module we're going to work on forecasting the income statement down to operating profit or EBIT once we have completed this step we will move to the balance sheet where we will forecast operating assets we will then forecast finance which has implications for both the income statement and balance sheet finally we will derive a cash flow forecast the first step in our model building case study is to forecast revenues down to operating profit or E bit we will stop at this point because after it becomes financing costs which will depend in part on what we are forecasting for our operating assets you earlier in the session we talked about the inherent tension in model building between making your model realistic and keeping it simple and robust the first principles approach outlined here identifies how you would go about modeling revenues if you need a high degree of detail and precision the quick and dirty approach outlines how you can model revenues in a much more straightforward way with the benefit that your model will be smaller as well as easier to follow an audit for our case study we are going to use the quick and dirty approach using historic figures and trends to predict future revenue growth after forecasting next one to forecast gross margins gross margins are usually forecast as a percent of revenues again you can use historic figures or trends to quickly forecast future margins however you may need to consider a more detailed approach which considers the cost of inputs as well as factors such as economies of scale and learning effects this second approach will make your model more realistic with the trade-off being that it's more complex and somewhat harder to follow the next step would be to forecast overhead costs forecasting overhead costs such as selling general and administrative anews for forecasts over shorter periods like weeks or months using revenues to help forecast sgna may not be appropriate some models forecast gross and operating profit margins and leave SGA as the balancing figure okay it's time to demonstrate how building the income statement forecast can be done in Excel the first thing to note is that all the forecast inputs are on the same worksheet entitled ratio drivers by keeping the inputs on this separate page we keep the model simple and easy to update our inputs should only be ever entered once this model also has separate output worksheets for the income statement balance sheet and cash flow statement there is also a notes worksheet where detailed processing calculations for PP&E and equity are broken down in order to make the model easier to follow and audit in this demonstration we are only working on the ratio drivers and income statement worksheets starting with the ratio drivers worksheet you will note that all of the income statement input assumptions down to EBIT can be found in rows 8 through 14 all of the expenses are being forecasted as a percent of revenues only sales is being forecast based on growth over the previous year the inputs on the ratio drivers worksheet are also ordered in the way they will appear on the income statement now let's move to the income statement worksheet we are going to work in column D and work downwards to forecast revenues we take the previous year and grow it by the Assumption on the ratio drivers page the formula in d6 should read equals see six times open bracket 1 plus ratio drivers d8 close bracket we can now move on to cost of goods sold this is going to be forecasted as a percent of our forecast revenues the formula in cell d7 should read equals minus T six times open bracket 1 minus ratio drivers d9 or forecasted revenues close bracket we can then sum the two to get gross profit a shortcut for summing is alt equals we will then move down to the other operating expenses which are also forecasted as a percent of forecast revenues the formula in cell d9 reads equals dollar sign D dollar sign six times ratio drivers d-10 the dollar signs are there to lock the referencing of forecast revenues so that it is an absolute rather than relative reference because we have done this we can then copy the formula down a quick way to copy formulas down is to highlight the area where you want to copy to and then just go ctrl D the final thing we need to do is sum up gross profit and all the operating expenses we can do that with alt equals and we are done the number you should have for operating profit or E bit is six five one eight now you have a go click on the attachment link entitled forecast part 1 exercise once you've finished check your result with the attached forecast part 1 solution have fun

5 thoughts on “How to Build a Financial Model in Excel – Tutorial | Corporate Finance Institute

  1. Hands-on skills in Financial Modelling can be an asset whenever you eye a job in the field of M&A, Project FInance, Valuation, FP&A, Equity Research or Investment Banking 

    Anurag Singal , MBA from IIM Ahmedabad, CA and a renowned YouTuber on "Careers in Commerce" has created this course that encapsulates crucial leanings on this topic, that he derived from 10+ years in leading MNCs 

    Learn how to build a financial model brick-by-brick and then go through case studies of Alibaba IPO, Facebook IPO, Uber Valuation, Startup Funding, Hotel Project Appraisal

    Avail discount using this link

Leave a Reply

Your email address will not be published. Required fields are marked *