Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Financial analysts are fond of saying that past performance is no guarantee of future results. That's true. But it does make sense to see how a business's sales, expenses, or market share have progressed over time so you can make judgments about the company's prospects. If you have a data series that includes past results, you can use the Forecast function to create a formula to estimate future values if the trend stays consistent. The Forecast function has three arguments: X, which is the value often a year for which you want to project a result, known_y's, which is the set of known results such as sales, and known_ x's, which indicate when the known_y measurements were taken.
It's interesting, but the X values can be in any order. They don't have to be sorted into ascending or descending order, and you can also skip values in the series. For example, with the numbers in this worksheet, we could skip the year 2009, and still create a projection for 2010 using the Forecast function. So let's see how the forecast formula will work. Just type in =FORCAST, our X value is 2010, which is in cell A11 and that is the year for which we want to project a value.
Our known_y's are the variable measurements and in this case, those are the sales revenue figures in cells B6 through B10. And our known_x's are the years in which those measurements were taken, and those are in A6 through A10. Those are all of the arguments we need for the formula. Close the parenthesis and there we have our projection. Based on current trends, the sales for the year 2010 will be 46,265 multiplied by 1,000 because all of the values in this worksheet are expressed in terms of 1,000s, which means you can interpret this value as 46,265,000.
Excel uses linear regression techniques to project future values based on the past measurements. The math is not that difficult to work through, but you don't need to bother. Excel does all the work for you.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.