Giovanni Food Products produces and sells frozen pizzas to public schools throughout the eastern United States. Using a very aggressive marketing strategy they have been able to increase their annual revenue by approximately $10 million over the past 10 years. But increased competition has slowed their growth rate in the past few years. The annual revenue, in millions of dollars, for the previous 10 years is contained in the Excel Online file below. Construct a spreadsheet to answer the following questions.
Develop a quadratic trend equation that can be used to forecast revenue (to 4 decimals).
Tt = b0 + b1t + b2t^2
b0 : (____)
b1 : (____)
b2 : (____)
Using the trend equation developed in part (a),
forecast revenue in year 11 (to 2 decimals).
Year | Year2 | Revenue ($ millions) |
1 | 8.21 | |
2 | 10.91 | |
3 | 13.41 | |
4 | 14.01 | |
5 | 16.76 | |
6 | 17.69 | |
7 | 18.65 | |
8 | 18.17 | |
9 | 18.54 | |
10 | 18.49 |
In order to get results for both the question mentioned above, we make use of MS-Excel by following the steps given below :
Step 1 : Enter the data in MS-Excel.
Step 2 : Create a scatter plot by selecting the
complete data set.
Step 3 : Now click on the '+' sign available on
the right hand above corner of the graph, and then click on the
arrow besides the trendline option and select the more
option.
Step 4 : In the further screen, select the type of
regression as polynomial with order as 2 and then select the
"Display Regression equation on graph" option avialable at the
bottom of the box. Thus. the result obtained is as below :
Thus,
b0 = 5.3097
b1 = 3.1295
b2 = -0.1828.
And Tt = 5.3097 + 3.1295 * t - 0.1828* t2
Hope this answers your query!
Giovanni Food Products produces and sells frozen pizzas to public schools throughout the eastern United States....