How to Build a Forecasting Model in Excel

hi and welcome to the corporate finance Institute this is our course on 12-month rolling cash flow forecasts in this course we're going to cover several things first off I'm going to introduce myself and the content of the course then we're going to talk about modeling best practices these are tips and tricks that I've learned over years and years of building financial models we're going to build a model together from scratch in Excel we'll be building it from the bottom up and I'll take you through step by step calculations of how to build the forecast finally we'll learn how to enhance the model these are professional level tips and tricks that you can build into your model key learning objectives let's talk about what we're going to cover in this course by the end of the course you'll be able to do several things the first is apply a structured approach to monthly cash flow forecasting we'll give you tips and tricks to make your model as robust as possible next you're going to learn how to build the assumptions and formulas required to create the forecast for this business we'll look out 12 months into the future to see what this campaign is going to look like next you'll calculate the monthly cash flow that results from the assumptions and formulas above from there you'll analyze the impact of the forecast on the company's balance sheet and capitalization this will allow you to make recommendations to executive management finally you'll create charts and graphs to show your results that look extremely professional and help you stand out building your career this course is ideal for anyone who's working in or wants to work in fpn a treasury management financial reporting and financial accounting please open up the file called 12-month rolling cash flow forecast in bracket blank this is the file you downloaded at the beginning of the course as a quick recap the model is organized into groups this keeps it highly structured and easy for other people to follow and makes it easy for us to build we're going to focus on building the assumptions section right now the assumptions section contains all the inputs or drivers for our model it's going to help us fill in the income statement the balance sheet and the other sections below as we continue on as you'll see in this model I've inputted a set of assumptions you can tweak these as you like and they will drive the forecast going forward let's quickly review what we're going to build in the assumptions section here for revenue we're going to start by building a number of stores this is a retail business that is driven by number of stores new stores can be added each month or stores can be closed we have an assumption for sales force or a square foot per store and then sales per square foot then you can calculate the revenue per square foot our operating assumptions are based on a gross margin percentage SGA is a fixed cost we have a tax assumption here we have working capital assumptions in terms of number of days it takes to get paid on receivables the number of days our inventory to turn and the number of days to pay our bills we also have capital expenditure assumption the depreciation rate of 10 percent and a cost per square foot to build a new store of $100 per foot we have not assumed any equity being raised or repurchased during this period nor any dividends paid we do however have some debt that was issued a few months ago in the past and some repayment schedules for that we're going to graph here at the end our debt to equity ratio and debt service coverage ratio covenants it will be important to understand if we're on side of these covenants as we build the model so let's start at the top of the assumptions section here this is a retail operation driven by number of stores so what we're going to do is set the formula for number of stores going forward to be equal to last month's number of stores plus any new stores that were opened or closed in the month once I built that formula I can hold down the shift key in the right arrow to fill the area to the right and press control R to review tips and tricks and Excel shortcuts please take our financial modeling fundamentals course where we will review all of these in detail now that we have the number of stores in place per month let's calculate on a historical basis what the sales per square foot was in order to do this we'll set the formula equal to revenue in the month divided by and I'm going to open a bracket here the square feet per store multiplied by the number of stores however this sales per square foot figure is based on actual dollars and its annual so we need to multiply it by a thousand because our income statement units are in thousands and multiply it by 12 because typically you think of sales per square foot on an annual basis not a monthly basis with that in place I can copy and paste over here now I can see that has to work over the past few months the average was about 550 dollars per square foot of annual revenue so I'm going to be a little bit more conservative and say that we're going to do five hundred and thirty-five dollars per square foot of annual revenue going forward next up let's look at gross margins in order to calculate gross margins I'm going to set this equal to the monthly gross profit divided by the monthly revenue I can copy this and paste it to the left to get a nice average here and as I can see you know the forward assumption of 26.5% again is a little bit conservative Relic historical the sgna dollar amounts I can also fill in by linking down to the rep to the income statement I'm going to make sure I flip the sign and I'm going to copy that to the left the last thing I'm going to do here is calculate SGA as a percentage of revenue this will give me a sense of how it's doing if the business is growing and if it's keeping pace with a number that makes sense hold down the shift key on this cell use the right arrow and press ctrl R you'll see that for the forecast period we haven't built up the revenue function yet so you get this divided by zero error that's fine we'll just leave it in place for now on a historical basis we can see that SGA is a percent of revenue range between about 21 and 24 percent now let's calculate the historical working capital assumptions the first stop is receivable days I'm going to show the formula to calculate receivable days on a historical basis we're going to set it equal to the accounts receivable on the balance sheet divided by the annualized revenue so the revenue in that month times 12 times 365 days per year if you want to refresher on this formula please refer back to the previous chapter where we broke it down in detail next up is inventory I'm going to calculate inventory days in the similar way where I take the inventory on the balance sheet and I divided by this time it's the annual cost of goods sold so I'm going to flip the sign for cost of goods sold and multiply it by 12 and then multiply all of this by 365 days per year 28.8 days lastly let's do accounts payable together I'm going to set it equal to on the balance sheet accounts payable divided by the cost of goods sold the flight sign flip times 12 to make the annual and then times 365 days per year so with these in place I shall copy these using control-c select the area to the left and press control V to paste them so I now see that based on these historical days for receiving payments turning inventory and paying bills I have realistic assumptions going forward about what this business is going to do feel free to tweak any of these blue cells as you build your own model financing assumptions I have left as zeros you could put in here that this business will raise money or buy back shares or even pay dividends there is some debt that was issued in September 2015 that debt has some principal repayments that go with it and 5.75% coupon I also have filled out here that we have a debt to equity ratio covenant 0.7 times and a debt service coverage ratio of three times these formulas here are simply linking to the one reference cell I'm going to delete these using ctrl shift in the right arrow I can select all those cells as a reminder we only want any one input located in one spot in the model so for reference I set this equal to the solid the left I selected in the cell below and press control D to fill down I hold shift and the right arrow and select the cells to the right and press ctrl R to fill that right we've now filled in the entire assumptions section of this model this will allow us to forecast the income statement the balance sheet will build some supporting schedules and then the cash flow statement let's get ready to move on to the next now that we have the assumptions in place we can start filling in the income statement let's open up the assumptions section here so we've got everything ready for easy reference let's also open up the income statement section to fill in the income statement I'm going to start with revenue I'm going to set a formula that's equal to and I'm going to scroll up to the top of the assumptions section the sales per square foot multiplied by the square feet per store and multiplied by the number of stores divided by 12 because as you'll recall the sales per square foot number is an annual figure and divided by a thousand because our income statement is expressed in thousands as are all of our financial metrics other than these assumptions here on sales per square foot so we get that number there I can then calculate the cost of goods sold the cost of goods sold is going to be equal to the revenue in the month times 1 minus the gross margin and I'm going to make sure I set this to negative I can then calculate the gross profit using all equal sign which is the quick autosum function next I can pull forward SGA from the assumptions section it's going to be equal to and I'm putting a negative sign to flip it here the monthly SGA in thousands from there I can calculate yada-yada is going to be equal to the sum of gross profit and the SGA expenses now that we filled the income statement all the way down as far as we can let's select this column holding ctrl shift and down arrow I then will just the shift key on the right arrow select the space to the right and press ctrl R I've now filled in the first half of the income statement hi and welcome to the corporate finance Institute now it's time to build some charts and graphs to make a beautiful output of this model it's best practice to have a summary or dashboard section of the model that includes charts and graphs and key metrics the goal is to convey a lot of information as quickly as possible the readers of our final output may not have time to go through the model in detail so we need to show them what really matters we're going to prevent several different types of information on the screen so we're going to combine different chart types and we're going to replace any tables where charts are easier to display the information so particularly for sensitivity analysis you want to be sure to use a lot of tables for more on sensitivity analysis you can check out our course on sensitivity analysis now let's jump into the model and get going flipping back to the excel file we're going to focus on the summary charts and graphs section here what we're going to have to do is pull forward information from the model that we want to chart into a little table here this makes it much easier to link up the charts and it keeps the model very organized so let's open up all of the model by hitting number two here on the grouping this way we can easily link up first thing we need to do because we're going to graph debt-to-equity ratios is take these formulas that were in a historical period select them and then press ctrl R to fill those right these are our debt-to-equity ratios and debt service ratios I'm going to start by building an operating cash flow summary so I'm going to link up into the cash flow from operations and we're going to be starting in the period of up to ending October here as we don't have complete data for September so we're going to link up here to the cash from operating activities then we're going to link up the investing cash flow and finally the financing cash flow and as one last piece let's link the monthly cash balance there's two places to pull that from one is on the cash flow statement and the other is on the balance sheet on the next section here let's pull forward the debt service ratio and the debt service covenant this is the benchmark that we want to compare to so what I'm going to do is select this information here select the area to the right and then press control R so I've now filled in the tables that are going to populate the charts and if I look down here I've got a monthly cash flow chart just shows the operating cash flow and blue the investing cash flow and green and the financing cash flow in purple then we have our running monthly cash balance in the red line and as you can see this really highlights what we can't see as easily in the numbers of the model which is that our cash our cash balance gets very low to zero and a couple points here and fees coincide with the years that we have big investing cash flow those represent the new store openings and capital expenditures for new openings so this chart really demonstrates the impact of opening new stores on this business now let's scroll down and look at the next chart the next chart is on our debt service ratio and the debt service Rachel covenant as you can see we've graphed the covenant with a dotted red line and it remains flat as this does not change over time and then we have the observed debt service ratio or the forecasted debt service ratio and as you can see we're actually forecasting to dip below the covenant at one point here so this is something we're going to want to watch very carefully as we move forward with planning for this business and ultimately rolling up our recommendations to the CFO and the CEO when we present our results let's review what we've built in this course we've designed a very robust and dynamic financial model that forecasts 12 months of cash flow we've got some historicals here and then we've got a forecast period on the right the model is grouped by section we start with our assumptions this is where we keep only inputs and drivers of the business this business is driven by number of stores and new store openings as well as sales per square foot below that we've got our income statement the income statement flows from the assumptions as well as some supporting schedules below in the supporting schedules we've modeled our capital assets like property plant and equipment as well as our debt schedule we can take pieces of these and feed them up into the balance sheet the balance sheet also pulls from the income statement and assumptions section when the income statement and balance sheet are complete we can then do the cash flow statement with a cash flow statement we fill the three components cash from operating activities investing and financing finally with all that in place were able to create summary charts output graphs this is probably the most important part of the model where we're able to clearly display what's happening in this business over time and how we need to adjust things going forward given the forecast thanks for taking this course with the corporate finance Institute we hope you've helped you accelerate your career

Channel: Corporate Finance Institute

Views: 375,905

Rating: 4.915545 out of 5

Keywords: how to build a forecasting model in excel, how to build an excel forecasting model, building a forecasting model in excel, building an excel forecasting model, excel for financial modeling, excel training, excel tutorial, corporate finance, finance training, finance education

Id: CDl0_Udyk70

Length: 19min 23sec (1163 seconds)

Published: Tue Aug 02 2016