Question

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 1

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:

Price Demand 2,597 1,899 1,757 1,614 1,608 1,262 1,241 1,082 Demand $149 $159 $169 $179 $189 $199 $209 $219 3,000 2,500 2,000 1,500 Demand 500 0 0 4 6 10

.

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:

Trendline Options Trendline Options Trend/Regression Type Line Color Line Style Shadow Glow and Soft Edges Exponential Linear ! 0 Logarithmic Polynomial Order: 2 Power Moving Average Period: 2 Trendline Name O Automatic: Custom: Poly. (Demand) Forecast Forward: 0.0 Backward: 0.0 periods periods Set Intercept0.0 Display Equation on chart Display R-squared value on chart Close

.

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

.

1 Demand 3,000 2,500 y 22.179x2-382.77x+2789.4 R2 0.9258 1,500 1,000 500 0 0 2 3 4 6

.

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