could anyone please help me with this queastion? 1- Using the data provided in t
ID: 3237919 • Letter: C
Question
could anyone please help me with this queastion?
1- Using the data provided in the excel sheet that came with this HW create a combined forecast over the next four quarters using naïve and moving average using 4 periods. Using CFE, RMSE (MSE), MAD, and MAPE, which forecast gives the ‘best’ forecast? Be sure to include the point estimate and the bounds of the confidence interval for your four forecast values.
Quarter Sales Q1-1997 1807.37 Q2-1997 2355.32 Q3-1997 2591.83 Q4-1997 2236.39 Q1-1998 1549.14 Q2-1998 2105.79 Q3-1998 2041.32 Q4-1998 2021.01 Q1-1999 1870.46 Q2-1999 2390.56 Q3-1999 2198.03 Q4-1999 2046.83 Q1-2000 1934.19 Q2-2000 2406.41 Q3-2000 2249.06 Q4-2000 2211.56 Q1-2001 2237.05 Q2-2001 2856.43 Q3-2001 2799.57 Q4-2001 2645.33 Q1-2002 2563.59 Q2-2002 3146.52 Q3-2002 3196.68 Q4-2002 2930.48 Q1-2003 2878.96 Q2-2003 3687.85 Q3-2003 3608.33 Q4-2003 3288.26 Q1-2004 3178.23 Q2-2004 3939.69 Q3-2004 3680.11 Q4-2004 3516.65 Q1-2005 3354.76 Q2-2005 4490.02 Q3-2005 4678.97 Q4-2005 4148.56 Q1-2006 3995.07 Q2-2006 5178.43 Q3-2006 5010.64 Q4-2006 4453.38 Q1-2007 4306.70 Q2-2007 5321.93 Q3-2007 4888.10 Q4-2007 4554.65 Q1-2008 4176.79 Q2-2008 5125.40 Q3-2008 4962.65 Q4-2008 4917.63 Q1-2009 4542.60 Q2-2009 5284.71 Q3-2009 4817.43 Q4-2009 4634.50 Q1-2010 4431.36 Q2-2010 5602.21 Q3-2010 5349.85 Q4-2010 5036.00 Q1-2011 4534.61 Q2-2011 5836.17 Q3-2011 5818.28 Q4-2011 5070.42 Q1-2012 4497.47 Q2-2012 6075.52 Q3-2012 5868.67 Q4-2012 5432.24Explanation / Answer
Notes:-
The Forecast is done on the basis of DeS being the dependent variable and Period being the independent variable.
Year Quarter Period Quarter Sales MA CMA Ratio Avg_Ratio SI DeS Forecast Error Absolute error Squared error Absolute % error 1 1 1 Q1-1997 1807.37 0.88113256 2051.189664 1705.167391 102.2026086 102.2026086 10445.37321 6% 1 2 2 Q2-1997 2355.32 1.102834511 2135.696676 1770.272618 585.0473822 585.0473822 342280.4395 25% 1 3 3 Q3-1997 2591.83 2247.7275 2215.44875 1.169889396 1.053333333 2460.598101 1835.377844 756.4521559 756.4521559 572219.8641 29% 1 4 4 Q4-1997 2236.39 2183.17 2151.97875 1.039224946 0.964349604 2319.065607 1900.483071 335.9069295 335.9069295 112833.4653 15% 2 1 5 Q1-1998 1549.14 2120.7875 2051.97375 0.754951178 0.88113256 0.88113256 1758.123658 1965.588297 -416.4482969 416.4482969 173429.184 27% 2 2 6 Q2-1998 2105.79 1983.16 1956.2375 1.076449051 1.102834511 1.102834511 1909.434261 2030.693523 75.0964767 75.0964767 5639.480813 4% 2 3 7 Q3-1998 2041.32 1929.315 1969.48 1.036476633 1.053333333 1.053333333 1937.962025 2095.79875 -54.47874968 54.47874968 2967.934167 3% 2 4 8 Q4-1998 2021.01 2009.645 2045.24125 0.988152376 0.964349604 0.964349604 2095.723368 2160.903976 -139.8939761 139.8939761 19570.32454 7% 3 1 9 Q1-1999 1870.46 2080.8375 2100.42625 0.890514485 0.88113256 2122.790696 2226.009202 -355.5492024 355.5492024 126415.2354 19% 3 2 10 Q2-1999 2390.56 2120.015 2123.2425 1.125900598 1.102834511 2167.6507 2291.114429 99.44557117 99.44557117 9889.421625 4% 3 3 11 Q3-1999 2198.03 2126.47 2134.43625 1.029794167 1.053333333 2086.737342 2356.219655 -158.1896552 158.1896552 25023.96702 7% 3 4 12 Q4-1999 2046.83 2142.4025 2144.38375 0.954507326 0.964349604 2122.49789 2421.324882 -374.4948816 374.4948816 140246.4163 18% 4 1 13 Q1-2000 1934.19 2146.365 2152.74375 0.898476653 0.88113256 2195.118065 2486.430108 -552.240108 552.240108 304969.1369 29% 4 2 14 Q2-2000 2406.41 2159.1225 2179.71375 1.104002762 1.102834511 2182.022756 2551.535334 -145.1253344 145.1253344 21061.36267 6% 4 3 15 Q3-2000 2249.06 2200.305 2238.1625 1.004868949 1.053333333 2135.183544 2616.640561 -367.5805607 367.5805607 135115.4686 16% 4 4 16 Q4-2000 2211.56 2276.02 2332.2725 0.94824254 0.964349604 2293.317684 2681.745787 -470.1857871 470.1857871 221074.6744 21% 5 1 17 Q1-2001 2237.05 2388.525 2457.33875 0.910354749 0.88113256 2538.834792 2746.851014 -509.8010135 509.8010135 259897.0734 23% 5 2 18 Q2-2001 2856.43 2526.1525 2580.37375 1.106983048 1.102834511 2590.080353 2811.95624 44.4737601 44.4737601 1977.915338 2% 5 3 19 Q3-2001 2799.57 2634.595 2675.4125 1.046406862 1.053333333 2657.81962 2877.061466 -77.49146628 77.49146628 6004.927346 3% 5 4 20 Q4-2001 2645.33 2716.23 2752.49125 0.961067542 0.964349604 2743.123437 2942.166693 -296.8366927 296.8366927 88112.02211 11% 6 1 21 Q1-2002 2563.59 2788.7525 2838.39125 0.903184154 0.88113256 2909.426023 3007.271919 -443.681919 443.681919 196853.6453 17% 6 2 22 Q2-2002 3146.52 2888.03 2923.67375 1.076221312 1.102834511 2853.120725 3072.377145 74.14285457 74.14285457 5497.162884 2% 6 3 23 Q3-2002 3196.68 2959.3175 2998.73875 1.066008168 1.053333333 3034.822785 3137.482372 59.19762819 59.19762819 3504.359183 2% 6 4 24 Q4-2002 2930.48 3038.16 3105.82625 0.943542801 0.964349604 3038.814957 3202.587598 -272.1075982 272.1075982 74042.545 9% 7 1 25 Q1-2003 2878.96 3173.4925 3224.94875 0.892714962 0.88113256 3267.340387 3267.692825 -388.7328246 388.7328246 151113.2089 14% 7 2 26 Q2-2003 3687.85 3276.405 3321.1275 1.110421084 1.102834511 3343.974061 3332.798051 355.051949 355.051949 126061.8865 10% 7 3 27 Q3-2003 3608.33 3365.85 3403.25875 1.06025732 1.053333333 3425.629747 3397.903277 210.4267227 210.4267227 44279.40561 6% 7 4 28 Q4-2003 3288.26 3440.6675 3472.1475 0.94703926 0.964349604 3409.821486 3463.008504 -174.7485037 174.7485037 30537.03956 5% 8 1 29 Q1-2004 3178.23 3503.6275 3512.6 0.904808404 0.88113256 3606.982813 3528.11373 -349.8837301 349.8837301 122418.6246 11% 8 2 30 Q2-2004 3939.69 3521.5725 3550.12125 1.109733928 1.102834511 3572.33108 3593.218956 346.4710435 346.4710435 120042.184 9% 8 3 31 Q3-2004 3680.11 3578.67 3600.73625 1.022043756 1.053333333 3493.775318 3658.324183 21.78581712 21.78581712 474.6218276 1% 8 4 32 Q4-2004 3516.65 3622.8025 3691.59375 0.952610238 0.964349604 3646.654684 3723.429409 -206.7794093 206.7794093 42757.72409 6% 9 1 33 Q1-2005 3354.76 3760.385 3885.2425 0.863462191 0.88113256 3807.327243 3788.534636 -433.7746356 433.7746356 188160.4345 13% 9 2 34 Q2-2005 4490.02 4010.1 4089.08875 1.098049046 1.102834511 4071.345207 3853.639862 636.380138 636.380138 404979.68 14% 9 3 35 Q3-2005 4678.97 4168.0775 4248.11625 1.101422307 1.053333333 4442.060128 3918.745088 760.2249116 760.2249116 577941.9162 16% 9 4 36 Q4-2005 4148.56 4328.155 4414.20625 0.939820155 0.964349604 4301.925342 3983.850315 164.7096852 164.7096852 27129.2804 4% 10 1 37 Q1-2006 3995.07 4500.2575 4541.71625 0.879638837 0.88113256 4534.016993 4048.955541 -53.88554118 53.88554118 2903.651548 1% 10 2 38 Q2-2006 5178.43 4583.175 4621.2775 1.120562442 1.102834511 4695.563975 4114.060768 1064.369232 1064.369232 1132881.863 21% 10 3 39 Q3-2006 5010.64 4659.38 4698.33375 1.066471704 1.053333333 4756.93671 4179.165994 831.4740061 831.4740061 691349.0227 17% 10 4 40 Q4-2006 4453.38 4737.2875 4755.225 0.936523508 0.964349604 4618.014029 4244.27122 209.1087797 209.1087797 43726.48174 5% 11 1 41 Q1-2007 4306.7 4773.1625 4757.845 0.90517871 0.88113256 4887.68682 4309.376447 -2.676446711 2.676446711 7.163366999 0% 11 2 42 Q2-2007 5321.93 4742.5275 4755.18625 1.119184343 1.102834511 4825.683225 4374.481673 947.4483269 947.4483269 897658.3322 18% 11 3 43 Q3-2007 4888.1 4767.845 4751.60625 1.028725812 1.053333333 4640.601267 4439.586899 448.5131005 448.5131005 201164.0013 9% 11 4 44 Q4-2007 4554.65 4735.3675 4710.80125 0.966852507 0.964349604 4723.027812 4504.692126 49.95787414 49.95787414 2495.789188 1% 12 1 45 Q1-2008 4176.79 4686.235 4695.55375 0.889520219 0.88113256 4740.251569 4569.797352 -393.0073522 393.0073522 154454.7789 9% 12 2 46 Q2-2008 5125.4 4704.8725 4750.245 1.078975927 1.102834511 4647.478791 4634.902579 490.4974214 490.4974214 240587.7204 10% 12 3 47 Q3-2008 4962.65 4795.6175 4841.34375 1.025056318 1.053333333 4711.376584 4700.007805 262.642195 262.642195 68980.92259 5% 12 4 48 Q4-2008 4917.63 4887.07 4906.98375 1.002169612 0.964349604 5099.426577 4765.113031 152.5169686 152.5169686 23261.42571 3% 13 1 49 Q1-2009 4542.6 4926.8975 4908.745 0.925409652 0.88113256 5155.410441 4830.218258 -287.6182578 287.6182578 82724.26221 6% 13 2 50 Q2-2009 5284.71 4890.5925 4855.20125 1.088463635 1.102834511 4791.933828 4895.323484 389.3865158 389.3865158 151621.8587 7% 13 3 51 Q3-2009 4817.43 4819.81 4805.905 1.002398092 1.053333333 4573.509495 4960.428711 -142.9987105 142.9987105 20448.63122 3% 13 4 52 Q4-2009 4634.5 4792 4831.6875 0.959188689 0.964349604 4805.829733 5025.533937 -391.0339369 391.0339369 152907.5398 8% 14 1 53 Q1-2010 4431.36 4871.375 4937.9275 0.897412933 0.88113256 5029.16383 5090.639163 -659.2791633 659.2791633 434649.0152 15% 14 2 54 Q2-2010 5602.21 5004.48 5054.6675 1.108324138 1.102834511 5079.828337 5155.74439 446.4656103 446.4656103 199331.5412 8% 14 3 55 Q3-2010 5349.85 5104.855 5117.76125 1.045349663 1.053333333 5078.971521 5220.849616 129.0003839 129.0003839 16641.09905 2% 14 4 56 Q4-2010 5036 5130.6675 5159.9125 0.975985542 0.964349604 5222.172518 5285.954842 -249.9548425 249.9548425 62477.42327 5% 15 1 57 Q1-2011 4534.61 5189.1575 5247.71125 0.86411195 0.88113256 5146.342566 5351.060069 -816.4500688 816.4500688 666590.7149 18% 15 2 58 Q2-2011 5836.17 5306.265 5310.5675 1.098972944 1.102834511 5291.972587 5416.165295 420.0047048 420.0047048 176403.952 7% 15 3 59 Q3-2011 5818.28 5314.87 5310.2275 1.095674338 1.053333333 5523.683546 5481.270522 337.0094784 337.0094784 113575.3885 6% 15 4 60 Q4-2011 5070.42 5305.585 5335.50375 0.950317016 0.964349604 5257.864968 5546.375748 -475.955748 475.955748 226533.874 9% 16 1 61 Q1-2012 4497.47 5365.4225 5371.72125 0.837249327 0.88113256 5104.192268 5611.480974 -1114.010974 1114.010974 1241020.451 25% 16 2 62 Q2-2012 6075.52 5378.02 5423.2475 1.120273416 1.102834511 5509.004243 5676.586201 398.9337992 398.9337992 159148.1762 7% 16 3 63 Q3-2012 5868.67 5468.475 1.053333333 5571.522154 5741.691427 126.9785729 126.9785729 16123.55797 2% 16 4 64 Q4-2012 5432.24 0.964349604 5633.06085 5806.796654 -374.5566535 374.5566535 140292.6867 7% 2.841727546 351.2621038 187733.2614 10.12% BIAS MAD MSE MAPERelated Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.