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.
