Grading Rubric for Case Study Assignments Criteria Good 20 Fair 15 Unacceptable 0 Score Substance 25% of grade Answers are meaningful and reflect insight and careful analysis of the issues posed; contains rich and fully developed ideas which are extremely well written and presented. Answers are meaningful and reflect insight and analysis of the issues posed; contains fully developed ideas which are well written and presented. Answers are vague and reflect sloppy analysis of the issues posed; needs further development. Answers lack any worthwhile analysis. Results & Predictions 25% of grade Mathematical results are correct and clearly stated and explained. Intelligent predictions are made with full support of the correct data results. Proper graphs used, which are accurate for given scenario. Neat and easy to read. Includes all applicable labels and characteristics of a good graph. Correct mathematical results are stated and partially explained. Predictions are made with some support of the correct data results. Some incorrect mathematical results. Predictions made with incorrect mathematical results of the data. No mathematical results are discussed. No intelligent predictions are made based on the data results. Proper graphs used, which are accurate for given scenario. Neat and easy to read. Some omitted applicable labels and characteristic of a good graph. Improper graph used for given scenario; omitted labels and characteristic of a good graph. No appropriate graphs used. Writing 25% of grade The usage of grammar, sentence structure, and spelling, are excellent. Citations (if any) are properly cited using proper APA formatting. The usage of grammar, sentence structure and spelling contain minimal errors. Citations (if any) are properly cited using proper APA formatting. Prose and grammar need significant revisions. Grammar, sentence structure, spelling and citations (if any) are unacceptable. Total The usage of grammar, sentence structure, and spelling are improper and needs some revision. Improper citation (if any) formatting. Graphs 25% of grade Excellent 25 Forecasting Restaurant Sales The C'mon Back Restaurant in Puerto Rico is owned and operated by Theotis Jones. The restaurant just completed its third year of operation. During that time, Theotis sought to establish a reputation for the restaurant as a high-quality dining establishment that specializes in fresh seafood. Through the efforts of Theotis and his staff, his restaurant has become one of the best and fastest-growing restaurants on the island. To better plan for future growth of the restaurant, Theotis needs to develop a system that will enable him to forecast food and beverage sales by month for up to one year in advance. The following table shows the value of food and beverage sales ($1,000s) for the first three years of operation (the data are also in the file \"Ch5 Forecasting\"): Managerial Report Perform an analysis of the sales data for the restaurant. Prepare a report for Theotis that summarizes your findings, forecasts, and recommendations. Include the following: 1. A time series plot. Comment on the underlying pattern in the time series. 2. Using the dummy variable approach, forecast sales for January through December of the fourth year. How would you explain this model to Theotis? Assume that January sales for the fourth year turn out to be $295,000. What was your forecast error? If this error is large, Theotis may be puzzled about the difference between your forecast and the actual sales value. What can you do to resolve his uncertainty about the forecasting procedure? Month 1st Year 2nd Year 3rd Year January 248 270 289 February 240 243 261 March 237 252 271 April 184 199 212 May 190 200 217 June 146 154 166 July 151 163 172 August 157 166 180 September 117 129 132 October 137 136 153 November 159 173 180 December 213 236 241 VINTAGE RESTAURANT CASE Sales (in $1000s) MONTH First Year Second Year Third Year January 242 263 282 February 235 238 255 March 232 247 265 April 178 193 205 May 184 193 210 June 140 149 160 July 145 157 166 August 152 161 174 September 110 122 126 October 130 130 148 November 152 167 173 December 206 230 235 Vint age Rest aurant 300 250 200 150 First Year Second Year Third Year 100 50 0 300 250 200 Sales($1000s) Linear Trend Line 150 Linear Trend Line with Seasonal Adj. Reseasonal Linear Trend Line based on deseasonal data 100 50 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 VINTAGE RESTAURANT CASE MONTH 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Sales($1000s) 242 235 232 178 184 140 145 152 110 130 152 206 263 238 247 193 193 149 157 161 122 130 167 230 282 255 265 205 210 160 166 174 126 148 173 235 Linear Absolute Trend Percentage Line Error 187.8438 22.4% 187.8321 20.1% 187.8204 19.0% 187.8087 5.5% 187.797 2.1% 187.7853 34.1% 187.7736 29.5% 187.7619 23.5% 187.7502 70.7% 187.7384 44.4% 187.7267 23.5% 187.715 8.9% 187.7033 28.6% 187.6916 21.1% 187.6799 24.0% 187.6682 2.8% 187.6565 2.8% 187.6447 25.9% 187.633 19.5% 187.6213 16.5% 187.6096 53.8% 187.5979 44.3% 187.5862 12.3% 187.5745 18.4% 187.5628 33.5% 187.5511 26.5% 187.5393 29.2% 187.5276 8.5% 187.5159 10.7% 187.5042 17.2% 187.4925 12.9% 187.4808 7.7% 187.4691 48.8% 187.4574 26.7% 187.4456 8.4% 187.4339 20.2% MAPE: 22.9% SUMMARY OUTPUT Regression Statistics Multiple R 0.0026162891 R Square 0.000006845 Adjusted R Square -0.0294047184 Standard Error 47.850801417 Observations 36 lleon: This model does not explain any of the variation in Vintage's demand. ANOVA df Regression Residual Total Intercept X Variable 1 SS MS F Significance F 1 0.532883 0.53288288 0.000233 0.987918 34 77849.77 2289.6992 35 77850.31 Coefficients Standard Error t Stat P-value Lower 95%Upper 95% Lower 95.0%pper 95.0% U 187.85555556 16.28847 11.5330366 2.68E-013 154.7534 220.9577 154.7534 220.9577 lleon: -0.0117117117 0.767704 -0.0152555 0.987918 -1.571874 1.54845 -1.571874 1.54845 This model suggests a decreasing trend for Vintage which does not agree with the behavior we identified from the graph. Linear Trend Model with Multiplicative Seasonal Indices Calendar Month # MONTH 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Sales($1000s) 242 235 232 178 184 140 145 152 110 130 152 206 263 238 247 193 193 149 157 161 122 130 167 230 282 255 265 205 210 160 166 174 126 148 173 235 Linear Trend Line 187.8438 187.8321 187.8204 187.8087 187.797 187.7853 187.7736 187.7619 187.7502 187.7384 187.7267 187.715 187.7033 187.6916 187.6799 187.6682 187.6565 187.6447 187.633 187.6213 187.6096 187.5979 187.5862 187.5745 187.5628 187.5511 187.5393 187.5276 187.5159 187.5042 187.4925 187.4808 187.4691 187.4574 187.4456 187.4339 MAPE: Seasonal Indices 1.288304 1.251117 1.235222 0.947773 0.979781 0.745532 0.772207 0.809536 0.585885 0.692453 0.809688 1.097408 1.401147 1.268038 1.316071 1.028411 1.028475 0.794054 0.83674 0.858111 0.650287 0.692972 0.890257 1.22618 1.503497 1.35963 1.413037 1.093172 1.119905 0.853314 0.885369 0.928095 0.672111 0.789513 0.922934 1.253775 Linear Trend Line Absolute Adjusted for Percentage Seasonality Error 262.5398408 8.5% 242.853459 3.3% 248.1940464 7.0% 192.1506012 7.9% 195.8197755 6.4% 149.7838157 7.0% 156.1221521 7.7% 162.4604898 6.9% 119.4267698 8.6% 136.1064359 4.7% 164.1281789 8.0% 223.8415801 8.7% 262.3434144 0.2% 242.6717501 2.0% 248.00833 0.4% 192.0068115 0.5% 195.673231 1.4% 149.6717159 0.5% 156.0053013 0.6% 162.3388875 0.8% 119.3373727 2.2% 136.004547 4.6% 164.0053052 1.8% 223.673992 2.8% 262.146988 7.0% 242.4900413 4.9% 247.8226137 6.5% 191.8630218 6.4% 195.5266865 6.9% 149.5596161 6.5% 155.8884505 6.1% 162.2172851 6.8% 119.2479757 5.4% 135.902658 8.2% 163.8824316 5.3% 223.5064038 4.9% 4.9% Month 1 2 3 4 5 6 7 8 9 10 11 12 Average Seasonal Index 1.397649 1.292928 1.321443 1.023119 1.04272 0.797633 0.831438 0.865248 0.636094 0.724979 0.874293 1.192454 Average: 1 SUMMARY OUTPUT Regression Statistics Multiple R 0.0026162891 R Square 0.000006845 Adjusted R S -0.0294047184 Standard Err 47.850801417 Observations 36 ANOVA df Regression Residual Total Intercept X Variable 1 SS 1 0.532883 34 77849.77 35 77850.31 Coefficients Standard Error 187.85555556 16.28847 -0.0117117117 0.767704 MS F Significance F 0.53288288 0.00023 0.987918 2289.6992 t Stat P-value Lower 95%Upper 95% Lower 95.0%pper 95.0% U 11.5330366 3E-013 154.7534 220.9577 154.7534 220.9577 -0.0152555 0.98792 -1.571873 1.548449 -1.571873 1.548449 Recalculated Linear Trend Model with Multiplicative Seasonal Indices lleon: This will be your Y variable Linear new linear trend for the Trend line model where the month # in is your Trend forcolumn B Linear X variable. Calendar Month # MONTH 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Sales($1000s) 242 235 232 178 184 140 145 152 110 130 152 206 263 238 247 193 193 149 157 161 122 130 167 230 282 255 265 205 210 160 166 174 126 148 173 235 Deseason alized Data 173.1479 181.758 175.5656 173.9779 176.4615 175.5192 174.3966 175.6723 172.9304 179.3155 173.8547 172.7529 188.1731 184.0783 186.9168 188.6389 185.0927 186.8026 188.8294 186.0739 191.7955 179.3155 191.0115 192.8795 201.7673 197.2267 200.5383 200.3678 201.3963 200.5934 199.654 201.0985 198.0839 204.1438 197.8741 197.0725 MAPE: deseason alized data 171.7384 172.6466 173.5549 174.4631 175.3714 176.2796 177.1879 178.0961 179.0043 179.9126 180.8208 181.7291 182.6373 183.5456 184.4538 185.3621 186.2703 187.1786 188.0868 188.9951 189.9033 190.8116 191.7198 192.628 193.5363 194.4445 195.3528 196.261 197.1693 198.0775 198.9858 199.894 200.8023 201.7105 202.6188 203.527 Line Absolute Adjusted for Percentage Seasonality Error 240.0300451 0.8% 223.2196875 5.0% 229.3429305 1.1% 178.496477 0.3% 182.8633132 0.6% 140.6065051 0.4% 147.3207811 1.6% 154.0972161 1.4% 113.8636175 3.5% 130.4328644 0.3% 158.0904126 4.0% 216.7036376 5.2% 255.2629684 2.9% 237.3112584 0.3% 243.7452869 1.3% 189.6474053 1.7% 194.2278806 0.6% 149.2998785 0.2% 156.3825934 0.4% 163.5275132 1.6% 120.7963812 1.0% 138.3343812 6.4% 167.6192967 0.4% 229.7001478 0.1% 270.4958917 4.1% 251.4028293 1.4% 258.1476434 2.6% 200.7983337 2.0% 205.592448 2.1% 157.9932519 1.3% 165.4444057 0.3% 172.9578104 0.6% 127.7291449 1.4% 146.2358979 1.2% 177.1481809 2.4% 242.6966581 3.3% 1.8% Month 1 2 3 4 5 6 7 8 9 10 11 12 Average: Average Seasonal Index 1.397649 1.292928 1.321443 1.023119 1.04272 0.797633 0.831438 0.865248 0.636094 0.724979 0.874293 1.192454 1 lleon: This model is off less than 2% per month on average, which is better than when we did not deseasonalize the data before calculating the trendline. SUMMARY OUTPUT Regression Statistics Multiple R 0.9072761913 R Square 0.8231500872 Adjusted R S 0.8179486192 Standard Err 4.5001057712 Observations 36 lleon: This model is explaining 82% of the variation in the deseasonalized demand which is pretty good. ANOVA df Regression Residual Total Intercept X Variable 1 SS MS F Significance F 1 3204.782 3204.782 158.2534168 2.410E-014 34 688.5324 20.25095 35 3893.315 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0%pper 95.0% U 170.830131 1.531842 111.5194 3.4565E-045 167.717054 173.943 167.7171 173.9432 lleon: 0.9082465521 0.072198 12.57988 2.4099E-014 0.76152187 1.05497 0.761522 1.054971 This model has an upward trend which supports the behavior we saw in the graph. Recalculated Linear Trend Model with Multiplicative Seasonal Indices MONTH 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Sales($1000s) 242 235 232 178 184 140 145 152 110 130 152 206 263 238 247 193 193 149 157 161 122 130 167 230 282 255 265 205 210 160 166 174 126 148 173 235 Deseason alized Data 173.1479 181.758 175.5656 173.9779 176.4615 175.5192 174.3966 175.6723 172.9304 179.3155 173.8547 172.7529 188.1731 184.0783 186.9168 188.6389 185.0927 186.8026 188.8294 186.0739 191.7955 179.3155 191.0115 192.8795 201.7673 197.2267 200.5383 200.3678 201.3963 200.5934 199.654 201.0985 198.0839 204.1438 197.8741 197.0725 MAPE: Linear Trend line for deseason alized data 171.7384 172.6466 173.5549 174.4631 175.3714 176.2796 177.1879 178.0961 179.0043 179.9126 180.8208 181.7291 182.6373 183.5456 184.4538 185.3621 186.2703 187.1786 188.0868 188.9951 189.9033 190.8116 191.7198 192.628 193.5363 194.4445 195.3528 196.261 197.1693 198.0775 198.9858 199.894 200.8023 201.7105 202.6188 203.527 Linear Trend Line Absolute Adjusted for Percentage Seasonality Error 240.0300451 0.8% 223.2196875 5.0% 229.3429305 1.1% 178.496477 0.3% 182.8633132 0.6% 140.6065051 0.4% 147.3207811 1.6% 154.0972161 1.4% 113.8636175 3.5% 130.4328644 0.3% 158.0904126 4.0% 216.7036376 5.2% 255.2629684 2.9% 237.3112584 0.3% 243.7452869 1.3% 189.6474053 1.7% 194.2278806 0.6% 149.2998785 0.2% 156.3825934 0.4% 163.5275132 1.6% 120.7963812 1.0% 138.3343812 6.4% 167.6192967 0.4% 229.7001478 0.1% 270.4958917 4.1% 251.4028293 1.4% 258.1476434 2.6% 200.7983337 2.0% 205.592448 2.1% 157.9932519 1.3% 165.4444057 0.3% 172.9578104 0.6% 127.7291449 1.4% 146.2358979 1.2% 177.1481809 2.4% 242.6966581 3.3% 1.8% Average Seasonal Month Index January 1.397649 February 1.292928 March 1.321443 April 1.023119 May 1.04272 June 0.797633 July 0.831438 August 0.865248 September 0.636094 October 0.724979 November 0.874293 December 1.192454 Average: 1 SUMMARY OUTPUT Regression Statistics Multiple R 0.9072761913 R Square 0.8231500872 Adjusted R S 0.8179486192 Standard Err 4.5001057712 Observations 36 ANOVA df Regression Residual Total Intercept X Variable 1 SS MS F Significance F 1 3204.782 3204.782 158.2534168 2.410E-014 34 688.5324 20.25095 35 3893.315 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0%pper 95.0% U Lower 95.0%pper 95.0% U 170.830131 1.531842 111.5194 3.4565E-045 167.717056 173.943 167.7171 173.9432 154.7534 220.9577 0.9082465521 0.072198 12.57988 2.4099E-014 0.76152197 1.05497 0.761522 1.054971 -1.571873 1.548449 VINTAGE RESTAURANT CASE Sales (in $1000s) MONTH First Year Second Year Third Year January 242 263 282 February 235 238 255 March 232 247 265 April 178 193 205 May 184 193 210 June 140 149 160 July 145 157 166 August 152 161 174 September 110 122 126 October 130 130 148 November 152 167 173 December 206 230 235 Vint age Rest aurant 300 250 200 150 First Year Second Year Third Year 100 50 0 300 250 200 Sales($1000s) Linear Trend Line 150 Linear Trend Line with Seasonal Adj. Reseasonal Linear Trend Line based on deseasonal data 100 50 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 VINTAGE RESTAURANT CASE MONTH 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Sales($1000s) 242 235 232 178 184 140 145 152 110 130 152 206 263 238 247 193 193 149 157 161 122 130 167 230 282 255 265 205 210 160 166 174 126 148 173 235 Linear Absolute Trend Percentage Line Error 187.8438 22.4% 187.8321 20.1% 187.8204 19.0% 187.8087 5.5% 187.797 2.1% 187.7853 34.1% 187.7736 29.5% 187.7619 23.5% 187.7502 70.7% 187.7384 44.4% 187.7267 23.5% 187.715 8.9% 187.7033 28.6% 187.6916 21.1% 187.6799 24.0% 187.6682 2.8% 187.6565 2.8% 187.6447 25.9% 187.633 19.5% 187.6213 16.5% 187.6096 53.8% 187.5979 44.3% 187.5862 12.3% 187.5745 18.4% 187.5628 33.5% 187.5511 26.5% 187.5393 29.2% 187.5276 8.5% 187.5159 10.7% 187.5042 17.2% 187.4925 12.9% 187.4808 7.7% 187.4691 48.8% 187.4574 26.7% 187.4456 8.4% 187.4339 20.2% MAPE: 22.9% SUMMARY OUTPUT Regression Statistics Multiple R 0.0026162891 R Square 0.000006845 Adjusted R Square -0.0294047184 Standard Error 47.850801417 Observations 36 lleon: This model does not explain any of the variation in Vintage's demand. ANOVA df Regression Residual Total Intercept X Variable 1 SS MS F Significance F 1 0.532883 0.53288288 0.000233 0.987918 34 77849.77 2289.6992 35 77850.31 Coefficients Standard Error t Stat P-value Lower 95%Upper 95% Lower 95.0%pper 95.0% U 187.85555556 16.28847 11.5330366 2.68E-013 154.7534 220.9577 154.7534 220.9577 lleon: -0.0117117117 0.767704 -0.0152555 0.987918 -1.571874 1.54845 -1.571874 1.54845 This model suggests a decreasing trend for Vintage which does not agree with the behavior we identified from the graph. Linear Trend Model with Multiplicative Seasonal Indices Calendar Month # MONTH 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Sales($1000s) 242 235 232 178 184 140 145 152 110 130 152 206 263 238 247 193 193 149 157 161 122 130 167 230 282 255 265 205 210 160 166 174 126 148 173 235 Linear Trend Line 187.8438 187.8321 187.8204 187.8087 187.797 187.7853 187.7736 187.7619 187.7502 187.7384 187.7267 187.715 187.7033 187.6916 187.6799 187.6682 187.6565 187.6447 187.633 187.6213 187.6096 187.5979 187.5862 187.5745 187.5628 187.5511 187.5393 187.5276 187.5159 187.5042 187.4925 187.4808 187.4691 187.4574 187.4456 187.4339 MAPE: Seasonal Indices 1.288304 1.251117 1.235222 0.947773 0.979781 0.745532 0.772207 0.809536 0.585885 0.692453 0.809688 1.097408 1.401147 1.268038 1.316071 1.028411 1.028475 0.794054 0.83674 0.858111 0.650287 0.692972 0.890257 1.22618 1.503497 1.35963 1.413037 1.093172 1.119905 0.853314 0.885369 0.928095 0.672111 0.789513 0.922934 1.253775 Linear Trend Line Absolute Adjusted for Percentage Seasonality Error 262.5398408 8.5% 242.853459 3.3% 248.1940464 7.0% 192.1506012 7.9% 195.8197755 6.4% 149.7838157 7.0% 156.1221521 7.7% 162.4604898 6.9% 119.4267698 8.6% 136.1064359 4.7% 164.1281789 8.0% 223.8415801 8.7% 262.3434144 0.2% 242.6717501 2.0% 248.00833 0.4% 192.0068115 0.5% 195.673231 1.4% 149.6717159 0.5% 156.0053013 0.6% 162.3388875 0.8% 119.3373727 2.2% 136.004547 4.6% 164.0053052 1.8% 223.673992 2.8% 262.146988 7.0% 242.4900413 4.9% 247.8226137 6.5% 191.8630218 6.4% 195.5266865 6.9% 149.5596161 6.5% 155.8884505 6.1% 162.2172851 6.8% 119.2479757 5.4% 135.902658 8.2% 163.8824316 5.3% 223.5064038 4.9% 4.9% Month 1 2 3 4 5 6 7 8 9 10 11 12 Average Seasonal Index 1.397649 1.292928 1.321443 1.023119 1.04272 0.797633 0.831438 0.865248 0.636094 0.724979 0.874293 1.192454 Average: 1 SUMMARY OUTPUT Regression Statistics Multiple R 0.0026162891 R Square 0.000006845 Adjusted R S -0.0294047184 Standard Err 47.850801417 Observations 36 ANOVA df Regression Residual Total Intercept X Variable 1 SS 1 0.532883 34 77849.77 35 77850.31 Coefficients Standard Error 187.85555556 16.28847 -0.0117117117 0.767704 MS F Significance F 0.53288288 0.00023 0.987918 2289.6992 t Stat P-value Lower 95%Upper 95% Lower 95.0%pper 95.0% U 11.5330366 3E-013 154.7534 220.9577 154.7534 220.9577 -0.0152555 0.98792 -1.571873 1.548449 -1.571873 1.548449 Recalculated Linear Trend Model with Multiplicative Seasonal Indices lleon: This will be your Y variable Linear new linear trend for the Trend line model where the month # in is your Trend forcolumn B Linear X variable. Calendar Month # MONTH 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Sales($1000s) 242 235 232 178 184 140 145 152 110 130 152 206 263 238 247 193 193 149 157 161 122 130 167 230 282 255 265 205 210 160 166 174 126 148 173 235 Deseason alized Data 173.1479 181.758 175.5656 173.9779 176.4615 175.5192 174.3966 175.6723 172.9304 179.3155 173.8547 172.7529 188.1731 184.0783 186.9168 188.6389 185.0927 186.8026 188.8294 186.0739 191.7955 179.3155 191.0115 192.8795 201.7673 197.2267 200.5383 200.3678 201.3963 200.5934 199.654 201.0985 198.0839 204.1438 197.8741 197.0725 MAPE: deseason alized data 171.7384 172.6466 173.5549 174.4631 175.3714 176.2796 177.1879 178.0961 179.0043 179.9126 180.8208 181.7291 182.6373 183.5456 184.4538 185.3621 186.2703 187.1786 188.0868 188.9951 189.9033 190.8116 191.7198 192.628 193.5363 194.4445 195.3528 196.261 197.1693 198.0775 198.9858 199.894 200.8023 201.7105 202.6188 203.527 Line Absolute Adjusted for Percentage Seasonality Error 240.0300451 0.8% 223.2196875 5.0% 229.3429305 1.1% 178.496477 0.3% 182.8633132 0.6% 140.6065051 0.4% 147.3207811 1.6% 154.0972161 1.4% 113.8636175 3.5% 130.4328644 0.3% 158.0904126 4.0% 216.7036376 5.2% 255.2629684 2.9% 237.3112584 0.3% 243.7452869 1.3% 189.6474053 1.7% 194.2278806 0.6% 149.2998785 0.2% 156.3825934 0.4% 163.5275132 1.6% 120.7963812 1.0% 138.3343812 6.4% 167.6192967 0.4% 229.7001478 0.1% 270.4958917 4.1% 251.4028293 1.4% 258.1476434 2.6% 200.7983337 2.0% 205.592448 2.1% 157.9932519 1.3% 165.4444057 0.3% 172.9578104 0.6% 127.7291449 1.4% 146.2358979 1.2% 177.1481809 2.4% 242.6966581 3.3% 1.8% Month 1 2 3 4 5 6 7 8 9 10 11 12 Average: Average Seasonal Index 1.397649 1.292928 1.321443 1.023119 1.04272 0.797633 0.831438 0.865248 0.636094 0.724979 0.874293 1.192454 1 lleon: This model is off less than 2% per month on average, which is better than when we did not deseasonalize the data before calculating the trendline. SUMMARY OUTPUT Regression Statistics Multiple R 0.9072761913 R Square 0.8231500872 Adjusted R S 0.8179486192 Standard Err 4.5001057712 Observations 36 lleon: This model is explaining 82% of the variation in the deseasonalized demand which is pretty good. ANOVA df Regression Residual Total Intercept X Variable 1 SS MS F Significance F 1 3204.782 3204.782 158.2534168 2.410E-014 34 688.5324 20.25095 35 3893.315 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0%pper 95.0% U 170.830131 1.531842 111.5194 3.4565E-045 167.717054 173.943 167.7171 173.9432 lleon: 0.9082465521 0.072198 12.57988 2.4099E-014 0.76152187 1.05497 0.761522 1.054971 This model has an upward trend which supports the behavior we saw in the graph. Recalculated Linear Trend Model with Multiplicative Seasonal Indices MONTH 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 Sales($1000s) 242 235 232 178 184 140 145 152 110 130 152 206 263 238 247 193 193 149 157 161 122 130 167 230 282 255 265 205 210 160 166 174 126 148 173 235 Deseason alized Data 173.1479 181.758 175.5656 173.9779 176.4615 175.5192 174.3966 175.6723 172.9304 179.3155 173.8547 172.7529 188.1731 184.0783 186.9168 188.6389 185.0927 186.8026 188.8294 186.0739 191.7955 179.3155 191.0115 192.8795 201.7673 197.2267 200.5383 200.3678 201.3963 200.5934 199.654 201.0985 198.0839 204.1438 197.8741 197.0725 MAPE: Linear Trend line for deseason alized data 171.7384 172.6466 173.5549 174.4631 175.3714 176.2796 177.1879 178.0961 179.0043 179.9126 180.8208 181.7291 182.6373 183.5456 184.4538 185.3621 186.2703 187.1786 188.0868 188.9951 189.9033 190.8116 191.7198 192.628 193.5363 194.4445 195.3528 196.261 197.1693 198.0775 198.9858 199.894 200.8023 201.7105 202.6188 203.527 Linear Trend Line Absolute Adjusted for Percentage Seasonality Error 240.0300451 0.8% 223.2196875 5.0% 229.3429305 1.1% 178.496477 0.3% 182.8633132 0.6% 140.6065051 0.4% 147.3207811 1.6% 154.0972161 1.4% 113.8636175 3.5% 130.4328644 0.3% 158.0904126 4.0% 216.7036376 5.2% 255.2629684 2.9% 237.3112584 0.3% 243.7452869 1.3% 189.6474053 1.7% 194.2278806 0.6% 149.2998785 0.2% 156.3825934 0.4% 163.5275132 1.6% 120.7963812 1.0% 138.3343812 6.4% 167.6192967 0.4% 229.7001478 0.1% 270.4958917 4.1% 251.4028293 1.4% 258.1476434 2.6% 200.7983337 2.0% 205.592448 2.1% 157.9932519 1.3% 165.4444057 0.3% 172.9578104 0.6% 127.7291449 1.4% 146.2358979 1.2% 177.1481809 2.4% 242.6966581 3.3% 1.8% Average Seasonal Month Index January 1.397649 February 1.292928 March 1.321443 April 1.023119 May 1.04272 June 0.797633 July 0.831438 August 0.865248 September 0.636094 October 0.724979 November 0.874293 December 1.192454 Average: 1 SUMMARY OUTPUT Regression Statistics Multiple R 0.9072761913 R Square 0.8231500872 Adjusted R S 0.8179486192 Standard Err 4.5001057712 Observations 36 ANOVA df Regression Residual Total Intercept X Variable 1 SS MS F Significance F 1 3204.782 3204.782 158.2534168 2.410E-014 34 688.5324 20.25095 35 3893.315 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0%pper 95.0% U Lower 95.0%pper 95.0% U 170.830131 1.531842 111.5194 3.4565E-045 167.717056 173.943 167.7171 173.9432 154.7534 220.9577 0.9082465521 0.072198 12.57988 2.4099E-014 0.76152197 1.05497 0.761522 1.054971 -1.571873 1.548449