Excel Video 102 Forecasting Part 2





The interactive transcript could not be loaded.



Rating is available when the video has been rented.
This feature is not available right now. Please try again later.
Uploaded on Nov 2, 2011

Excel Video 102 takes you back to your algebra days to forecast billed charges. Remember y=mx+b, the equation to chart a straight line? That equation will come in handy in Excel Video 102.

To forecast billed charges, we'll use the y=mx+b equation to project future billed charges based on our historical data. To use y=mx+b, we need to know the slope, represented by m, and the y-intercept, represented by b. The y-intercept is the point at which the line crosses the y-axis. Excel has a SLOPE function to calculate slope and an INTERCEPT function to calculate the y-intercept. Both functions require you to input the known y values, a comma, and then the known x values. Known values means entering the historical data we'll use to forecast those values in the future. Once Excel's calculated m and b, use the current value of x to calculate y for each month.

Excel also has an RSQ function, which calculates the r-squared value. The r-square value is a number between -1 and 1 that measures the correlation of your y=mx+b equation to your actual historical data. A value of -1 means a perfect negative correlation. If x goes up 10%, y goes down 10%. A value of 1 means a perfect positive correlation. If x goes up 10%, y goes up 10%. A value of 85.85 means our equation, and therefore our forecast, comes very close to approximating the historical data.

This is much more of an Excel Video than a statistics lesson, but I hope you find these Excel functions useful in your practice.

  • Category

  • License

    • Standard YouTube License


When autoplay is enabled, a suggested video will automatically play next.

Up next

to add this to Watch Later

Add to

Loading playlists...