Hello, I'm professor Brian Bushee. Welcome back. In this video, we're gonna take a look at forecasting. We're gonna see how you could use a company's historical financial statements in order to forecast future balance sheets, income statements and cash flow statements. We're gonna start by putting together something called common size financial statements from the historical financial statements. We can use these common size statements along with sales growth forecasts, in order to forecast all three of the financial statements into the future. Once we have those forecasted statements, then we can tweak assumptions for individual line items and see how that would effect all of the forecasted statements. Let's get started. Before we start to work on forecasting, the first thing we need to do is talk about common size financial statements. There are a couple benefits to doing common size financial statements. One is it's gonna remove the effects of growth, which will help you better spot trends in the financial statements. So, one of the problems is that growth in assets or growth in sales drives almost all of the trends in the line items on the balance sheet and income statement respectively. So we're gonna use common sizing to take out that growth which allows to see whether certain line items on the balance sheet or income statement are growing more or less that we'd expect given the total growth in assets and sales. The second benefit is it's gonna help us put together forecasted future financial statements. What you're gonna see is we're gonna combine historical common size statements with growth forecasts of sales and assets to get future financial statements. So, we're gonna put together a common size balance sheet in which we express all numbers on the balance sheet as a percentage of total assets. A common sized income statement where we express all the numbers on the income statement as a percentage of sales. And then the cash flow statement is typically not common sized. >> I have an intense dislike for the Cash Flow Statement. I am so glad we do not have to common size it. >> But what is the rationale for doing so? >> There are a couple reasons why we don't do common sized cash flow statements. The first is that there's not an obvious number to use to common size it. I mean, we could use the change in cash, but if that was close to zero, then the common size ratios would be close to infinity. Or if the change in cash was negative and one of the line items was negative, you'd end up with a positive number. So it would be hard to interpret if we tried to common size. The second reason is we don't really need to common size the cash flow of statement to forecast it because if we forecast the income statement and forecast the balance sheet, we can use those two together to put together the cash flow statement. In a sense the cash flow statement is redundant because it's just a combination of the other two statements. If you take a look at the Woof Junction spreadsheet, there is a tab for common size finance statements. Here's the commons size balance sheet and what I've done, and you can check this with the formulas, is taken original value of each line item divided it by total assets to get the percent of total assets which is why total assets is equal to one. Same thing with total liabilities and equity because that has to equal total assets that hold balance sheet equation thing. And so what this allows you to do is look at trends and line items where you've taken out growth in the company. So we can see inventory has gone down as a percent of assets over time. For the income statement we divide all the line items by sales. So you can see sales has a value of one and everything else is a percentage of sales. And you've seen a lot of these before because these are basically the profitability sub-ratios that we looked at earlier, where we took a lot of these line items as a percent of sales. And then we don't common size the cash flow statement. Now that we've put together common size historical balance sheets and income statements, we can now forecast future financial statements. And another word for projected future financial statements is pro forma statements. So I'll use those terms interchangeably. Step one is to forecast future sales. All of the other line items on the balance sheet and the income statement are going to be a function of our future sales forecasts. >> Wait. How on earth does one forecast sales? >> I'm glad you said how on Earth do we forecast sales, because I was about to tell you how to do it on Mars. So, forecasting sales is obviously tricky, because we're trying to forecast the future. The best starting point is historical sales growth. Then adjust that for any additional information you have about future prospects. So if the company has some good growth opportunity as your plan's in place, maybe you make sales growth higher than historical, or if they seemed to have reached the limits of their growth potential or expansion, then maybe you make sales growth in the future lower than it's been historically. There's a lot more art than science to it and ultimately, what you wanna do is play around with different forecast assumptions. So in the spreadsheet, it's easy to try different sales forecast assumptions and see how it affects all of your forecasted future financial statements. Step two, we can use our forecasted sales for each year in the future to put together pro forma income statements. So all we need to do is take the forecasted sales for the year, times those forecasted common size ratios for each line item to get the value of each line item on the income statement. For example, if we wanted to forecast SG&A expense, we would take forecasted sales for the year, times the forecasted SG&A to sales ratio. Step three is we can use forecasted sales to construct a pro forma balance sheet. I'm gonna have to show you that on the next slide. And then the final step is we can use the pro forma income statement and pro forma balance sheets to construct a pro forma statement of cash flows. So let's talk about how to project the balance sheet. So we can take our forecasted sales and use the total asset turnover ratio to forecast for future total assets at the end of the year. And that's because the turnover ratio, if you remember, is a ratio of sales to assets. So the total asset turnover ratio or TATR is sales divided by average total assets. What we can do is solve for forecasted total assets in a period. As two times forecasted sales divided by turnover ratio. Minus total assets of the prior period. >> I am a certified genius for my age. But you really lost me with this TATR stuff! >> So one of the most difficult things about putting together an online course, well other than not being able to make eye contact with your students, is that you all have such variation in backgrounds and experiences, in terms of courses that you've taken in the past and in terms of your affinity for mathematics. So I'm trying to run a middle course here, where I will provide all these formulas. So if you love mathematics you can get into the formulas but if you're somebody who doesn't like math, no problem. Just ignore the formulas and the technical stuff here and just trust that everything is programmed in the spreadsheet correctly. So as long as you don't make any changes to the formulas in the spreadsheet everything will work out fine. Next, we multiply the forecasted total assets at the end of the year times the forecasted common size ratio for every asset and liability line item. For example, if we wanted to forecast inventory, we would take the forecasted total assets at the end of the year times the forecasted ratio of inventory to assets to get the forecasted inventory at the end of the year. Once we forecast all the assets and all the liabilities, then we can forecast stockholders' equity. And it's just gonna be the difference between all the forecasted assets and the forecasted liabilities because remember that balance sheet equation, assets have to equal liabilities plus stockholders' equity. Now we have to do one technical refinement that I've built into this spreadsheet. And we wanna smooth the forecasted total assets to eliminate something called the saw-tooth effect. So what we're gonna do is forecast unsmoothed total assets using the equation above. Then calculate the projected average growth rate in assets over the horizon we're forecasting. And use that average growth rate to project a smoothed asset growth series, so that we don't have the saw-tooth effect. >> You are probably expecting me to ask, do we have to forecast par value? But I am not. I would rather see an example of this saw-tooth effect. >> That's an excellent idea, why don't we spend about two minutes going through an example of how the saw-tooth effect works? And then we'll get to the Woof Junction spreadsheet. Let me give you a quick example of how this saw-tooth effect works, and how we can correct it. So let's forecast the company having 100 in sales every year. And we'll forecast that their total asset turnover ratio is 2 every year. So we can use sales and total asset turnover ratio to calculate average total assets. Cuz the total asset turnover ratio is sales over total assets. So we have 2 equals 100 divided by x, x is 50. So if we know that average total assets were 50 and the total assets at the beginning of the year, the previous ending total assets was 40, then what we can do is we can take 2 times the average minus the previous to figure out ending total assets of 60. Or the other way to look at it is sixty is the number where when we take the average, we end up with a value of fifty. In year two average, total assets is still expected to be fifty. But now that's going to bring us back down to forty to get that average, and as you can see it's gonna saw-tooth. It's gonna bounce up and down like the teeth on a saw every year because of the way we're doing the forecasting. If we have high forecasted sales growth it gets even worse. We see a bigger up and down in this saw-tooth pattern. So the way we're gonna correct it is we're gonna try to figure out what's the growth rate every year that gets us from 40 to 150 over the 5 year forecast horizon period. And we can calculate that by taking 150 divided by 40 and then raising that to the 1/5 power to get a compound growth rate of 1.3. Which means that basically, if you start at 40 and you grow assets by 30% every year, they'll get to 150 after five years. So then what we'll do when we forecast total assets is we'll start with 40, and then we'll make it grow each year by 1.3. And so what this'll do is it'll grow very smoothly from 40 up to 150, as opposed to exhibiting this saw-tooth effect. So now let's try to apply this to Woof Junction. So there's a tab in the spreadsheet called Assumptions. And what I've got here are just some basic assumptions to generate financial statements into the future, the pro forma financial statements. What you can do on your own is you can change any of these assumptions and see how the statements will change. So starting with sales growth, I just forecasted five years of their historical average growth. Then I had a drop to five percent, which is the industry average. You could put any kind of pattern in here you want, or thought was appropriate. But once you forecast sales, then you can use the historical averages from the common size to forecast all the line items. Then you forecast total asset turnover. And again, I just took the historical average as a starting point. Then we're gonna go down here to the saw-tooth calculation. So what this will do for you is it will calculate the total assets using the total assets turnover ratio. You get a little bit of saw-toothing going on, or at least uneven growth. Then we figure out what's the growth rate over this horizon, what's the growth rate over this horizon. Apply that to total assets to get a much smoother growth in total assets. Once we get a smoother growth then what I take is the historical average of the common size ratios for the balance sheet to forecast out each of the line items. And so it'll look like this. Where every line item in the balance sheet is the assumption for the cash to total assets ratio times the total assets I forecasted during the year. The total assets all come from the saw-tooth calculation and then we forecast an income statement where we start with sales growth and then each line item is the forecasted ratio of the line item to sales times whatever the sales is. Once you get the balance sheet income statement, you can go through and take a look at the formulas. But it basically allows you to put together a statement of cash flows. And also, ratios. So you can go look at these ratios into the future, to see if your pro forma financial statements makes sense. So again, this is just a base case. What you can do on your own is change anything in the assumptions tab, and it will recalculate everything automatically in the spreadsheet. There are a couple other refinements you can do in forecasting. One is to try to decompose the net increase and property plan and equipment into how much is purchases of new PP and E verses stuff that's retired either because it's sold or it wears out. And also you can decompose the change in stockholder's equity and how much is net income verses dividends and net share repurchases, and wait. Is that snoring that I hear? Okay, okay, I get the point, enough technical details. These probably are more than you really need to know but if you are interested, you can go through the details on the slide. And then more importantly, bring up the spreadsheet and I have all the calculations on the assumptions tab. Which shows you how to do these additional refinements. That wraps up our brief look at forecasting. Obviously there's a lot more that I could have done with this. But we've hit all the basics. And now I think the key thing is for you to get comfortable using that spreadsheet. So I thought about shooting more video where I played around with some of the assumptions and showed you how the financial statements change and the ratios change. But it's probably better learning experience for you to do it yourself than to watch me do it. So I strongly encourage you to get out the Woof Junction spreadsheet. Changed some of the assumptions individually and take a look at how it changes the forecasted financial statements and the forecasted ratios. After you're done playing around with all the assumptions, come back here and I'll see you next time. >> See you next video.