Sometimes curvature in a scatterplot can be fit adequately (especially to the naked eye) by several trend lines. We discussed the exponential trend line, and the power trend line is discussed in the previous problem. Still another fairly simple trend line is the parabola, a polynomial of order 2 (also called a quadratic). For the demand-price data in the file P13_10.xlsx, fit all three of these types of trend lines to the data, and calculate the MAPE for each. Which provides the best fit? (Hint: Note that a polynomial of order 2 is still another of Excel's Trend line options.) If needed, round your answers to one decimal digit.
Price | Demand |
$149 | 2,597 |
$159 | 1,899 |
$169 | 1,757 |
$179 | 1,614 |
$189 | 1,608 |
$199 | 1,262 |
$209 | 1,241 |
$219 | 1,082 |
Answer to the
question)
Enter data in excel
use the insert tab
click on other chart types
from the drop down menu select all chart types
int he all chart type window select XY scatter plot
click on it and click ok
The following scatter plot appears on screen:
.
Now right click on the scatter point , and from the drop down menu select add trend line
Click on it , the following window appears on screen:
.
In this window one can note that all different models can be experimented and compared
the polynomial model with order 2 is selected
select the last two check boxes for the equation and R square value
Click ok
.
.
Similarly we can get the equations for rest of the models too
And compare the values for them
the strongest or the best model is: Logarithmic model with least MAPE value
Sometimes curvature in a scatterplot can be fit adequately (especially to the naked eye) by several...