Hi, this is Wayne again with a topic “The Excel FORECAST Function”.
In this tutorial, i will show you how to use the excel forecast function, and this is just one way you can use it, but i think it’ll help you get a sense of what it’s for. You can see the workbook that i have here. It consists of two spreadsheets one on a home appraised value and the other is savings. It’S like the savings account in a bank. Those will be our two examples, so here on the home appraised value spreadsheet, you can see, i have a list of years.
Let’S say i bought a house in 2001 and this is what it was worth at the time and then maybe every year or two or three, the house got appraised. But each of these years has a different home value for the home, and these years are all in the past, of course. But what about the future? Is there a way to kind of forecast what the value of this home will be in 2035? Now, before i show you how to do this in excel, just know that this is limited obviously excel, doesn’t know that much about home values. It doesn’t know how real estate appreciates, and it doesn’t know the neighborhood that i live in, or things like that. There’S all sorts of variables, but using the information that we do know excel, can make an educated guess about the future.
So here in this blank cell, i’m just going to click and then type the equals sign and i’ll type in the function forecast now you’ll notice. There are six different forecast functions, they’re all pretty similar, but if you know that what you’re looking at is a linear model, you could choose forecast linear. If you know that what you’re looking at is an exponential model, you could use forecast.ets and there’s all of these other slightly different versions of the forecast function, if you’re not sure which one to use just stick with basic forecast.
So that’s what i’m going to do after typing in forecast i’ll just put a left parenthesis and you can see what excel is looking for. It’S looking for an x number and then comma and a known y, comma and then a known x. So this can be really confusing at first, but basically the forecast.
I want it to be based on the year 2035, so i’m going to click on 2035 that will become the x. So i click on 2035. Now i didn’t really click on the number.
I actually clicked on the cell cell b13, so anything that i type in b13 that will become the x now, i type comma and the known y okay. So since this is x, the y must be this information here, and it is so. This is the information that we know. We know what the past appraisal values have been, so i just clicked and dragged to select all of the past appraisal amounts and now i’ll put a comma in and now it wants the known x’s. So this is x. What are the known, x’s? Well, all of these years, these past years.
So i click and drag to highlight all of the past years, and then i should put in a right parenthesis and tap enter on the keyboard and it calculates what excel thinks my home will be worth in 2035, based on past results. Okay, let’s try that again. This time i’m going to go to the savings spreadsheet, and this again is limited and there’s going to be some flaws in this excel doesn’t know my saving habits and things like that.
But let’s say in 1995 i had 10 dollars in the bank in 2005. I had three thousand dollars. I would follow the same pattern as before.
I would go here to this cell and click and type equals forecast left parenthesis what’s the x. What is this forecast based on? Well, it’s based on the year 2030. So i click on that cell b11 type, the comma. What’S the known y of this data, if this is x, then this is y, so i’ll click here to select all of the y comma and now the known x. Well, here are the known x’s. So i click and drag to select those put in the right parenthesis.
Tap, enter and apparently that’s what i would have in 2030.. That’S not a lot of savings for 10 years and, like i said this forecast ability is pretty limited, but excel is doing its best to figure out okay, given these numbers and their relationship to these numbers. This is what excel expects for 2030. now notice that you can go in here and change from 2030.
I could put 2055 tap enter and the model updates the forecast updates, and this is now the expected amount. I can do the same thing with home appraised value. So i could change this from 2035. What about 20 55 i’ll tap enter, and i can see.
Oh, that is now the expected amount that my home will be worth in 2055.. So, even though forecast is limited, it is kind of fun and it’s a way to quickly get an estimate based on past results thanks for watching. I hope you found this tutorial to be helpful. If you did please like follow and subscribe and when you do click the bell so you’ll be notified.
Whenever i post another video, if you’d like to support my channel, you can do that through my patreon account and you’ll see a link to that in the description below speaking of patreon. I want to give a quick shout out to my five dollar patreon supporters. Thank you so much for all you do to support my channel.
.