Microsoft Office Tutorials and References
In Depth Information
Getting the trendline values
Figure 5-9: Using a trendline to forecast sales for three
Getting the trendline values
The preceding example leads, of course, to the question, What are the actual
forecasted sales values? As described previously, you can use the slope and y-intercept
values to calculate the predicted y value for a given value of x. It’s a fairly simple
exercise to create formulas to perform these calculations.
One approach is to copy the slope and y-intercept values displayed in the
trendline’s equation, and use these values to calculate the predicted y values (this is the
method used earlier in this chapter to predict a person’s weight, based on his or her
height). For increased accuracy, you can calculate the slope and y-intercept and use
these values to calculate the predicted values (see the sidebar, “Calculating the slope
and y-intercept”). The simplest approach (for linear trendlines only) is to use Excel’s
FORECAST function.
Figure 5-10 shows the data from the sales forecast chart. Column A contains the
month names (for reference only), and column B contains consecutive month
numbers. The actual sales figures are in column C. Column D contains formulas that
return the predicted y values displayed in the trendline.
The formula in cell D2 is:
=FORECAST(B2,\$C\$2:\$C\$25,\$B\$2:\$B\$25)
This formula was copied to the 24 cells below. As you can see, values for the
final three months are forecast, based on the trend for the first 21 months.
The calculated values in column D, if plotted on a chart, would display a line
that’s identical to the linear trendline.
Search JabSto ::

Custom Search