Answer To: MKTG 462AA8 – Basics of Forecasting In Excel – 100 pointsNOTE FOR EXPERT:· Please watch...
Rochak answered on Nov 26 2022
MKTG 462
AA8 – Basics of Forecasting In Excel – 100 points
NOTE FOR EXPERT:
· Please watch ‘Lecture Video.mp4 (Click Here)’where professor goes over this assignment and see how the solution by answering the following questions with any appropriate screenshots.
· Please have solution in American-English spelling
The data you have access to is the weekly sales across all the department of a single WalMart store over little more than 2 years. Use the Excel sheet “Walmart_Store_1_Data – POST.xlsx” to answer the following questions.
Question 1 (20 pts)
Q1a. Calculate and report the sum, mean and standard deviation of weekly sales for department 79, 72, and 35 in a single table and report it below.
Department 79
Department 72
Department 35
Sum
48,58,395.39
67,76,862.22
3,62,609.52
Mean
33,974.79
47,390.64
2,535.73
Standard Deviation
6,611.64
12,605.78
586.83
Q1b. Plot a line graph for the weekly sales over time for departments 79, 72, and 35, one plot per department on the same Excel sheet as its data. Label each plot and copy/paste each into the space below.
Question 2 (20 pts)
Create a forecast ending 7/5/2013 for departments 79, 72, and 35, one forecast per department. Label each forecasted Excel sheet and corresponding forecast plot, then copy/paste each into the space below, and briefly answer the following questions.
· What patterns (“seasonality”) do you notice for each department?
· What type of products do you think are sold in each department? (hint: look at how sales dip or spike at certain times of the year)
The patterns (“seasonality”) that can be noticed for each department are:
· Department 79: Weekly Seasonality
· Department 72: Annual Seasonality
· Department 35: Monthly Seasonality
Types of products sold
· Department 79: Perishable products
· Department 72: Cold products
· Department 35: Warm products
Question 3 (20 pts)
Create a NEW forecast for Department 35 ending on 8/1/2014, label and copy/paste the new line plot into the space below. Describe what happens to the “confidence intervals” of the forecast as time goes on. What does this reflect?
The confidence intervals, i.e., the lower limit goes below 0. This reflects that the sales can be 0 too at some time point in the future.
Question 4 (20 pts)
Pick ANOTHER department of your choice (e.g. not 12, 79, 72, or 35), and do the following
· Create a forecast ending 7/5/2013
· Copy/paste the labeled line plot below
· Describe what you see. What do you think might be sold in this department? How accurate does the forecast appear, for how long into the future, and why?
The department must be selling seasonal products. The forecast according to me does not seem accurate because of the volatility it has.
Question 5 (20 pts)
Pick ANOTHER department of your choice (e.g. not 12, 79, 72, 35, or the one you just chose), and do the following
· Create a forecast ending 7/5/2013
· Copy/paste the labeled line plot below
· Describe what you see. What do you think might be sold in this department? How accurate does the forecast appear, for how long into the future, and why?
· Compare this new department to the one in you chose in Question 4. Describe any similarities or differences.
· Presume you get to pick one of these departments to have a job where you bonus is tied to how well the department does. Which one would you rather be “the boss?” Why?
· The department must be selling essential products and that is the reason there is no volatility. Also, the forecast appears to be accurate.
· The difference is that department 1 sales were very volatile while department 2 has fairly consistent sales
· I will choose Department 2 as here the bonus will be consistent
Department 79
Weekly Sales
Weekly Sales 40214 40221 40228 40235 40242 40249 40256 40263 40270 40277 40284 40291 40298 40305 40312 40319 40326 40333 40340 40347 40354 40361 40368 40375 40382 40389 40396 40403 40410 40417 40424 40431 40438 40445 40452 40459 40466 40473 40480 40487 40494 40501 40508 40515 40522 40529 40536 40543 40550 40557 40564 40571 40578 40585 40592 40599 40606 40613 40620 40627 40634 40641 40648 40655 40662 40669 40676 40683 40690 40697 40704 40711 40718 40725 40732 40739 40746 40753 40760 40767 40774 40781 40788 40795 40802 40809 40816 40823 40830 40837 40844 40851 40858 40865 40872 40879 40886 40893 40900 40907 40914 40921 40928 40935 40942 40949 40956 40963 40970 40977 40984 40991 40998 41005 41012 41019 41026 41033 41040 41047 41054 41061 41068 41075 41082 41089 41096 41103 41110 41117 41124 41131 41138 41145 41152 41159 41166 41173 41180 41187 41194 41201 41208 46729.77 36080.86 34141.620000000003 33309.85 42931.77 31669.24 32604.49 29223.39 40910.94 37815.5 32135.69 28230.720000000001 30450.45 44039.92 30719.18 30190.720000000001 28195.06 42732.59 34564.68 29943.17 29521.94 37745.99 35320.410000000003 30200.62 29353.14 29275.56 43224.38 30376.79 28801.37 28990.87 39538.080000000002 34512.69 31291.01 27292.080000000002 31074.81 37770.25 29799.61 28439.7 25548.97 39589.08 29531.38 28073.27 29147.200000000001 32861.33 33269.22 31340.51 33497.1 24738.86 39984.6 30646.95 28181.68 27962.54 42065.78 36163.65 32462.04 30624.720000000001 45717.53 35519.370000000003 32251.01 28503.22 35814.980000000003 38752 31150.62 30833.66 28255.73 44271.49 33007.29 29087.29 28296.720000000001 42896.37 35023.199999999997 29656.36 29394.35 35335.800000000003 36448.879999999997 29505.119999999999 28530.68 28715.75 45837.83 29878.13 32156.49 29392.95 40331.25 34786.75 32427.96 29161.56 28876.959999999999 45059.56 30011.57 30190.3 28364.34 42665.35 31898.63 30928.23 33533.15 38806.559999999998 37340.339999999997 33041.49 34196.74 27676.43 42842.53 31338.33 31186 28937.93 47522.61 39360.339999999997 36855.68 31907.99 47896.34 39196.18 32723.279999999999 30097.93 30834.51 45342.21 30752.29 32581.33 30866.19 47342.76 34432.74 33905.440000000002 29874.35 37942.129999999997 41003.18 34184.660000000003 30684.35 30442.57 47794.94 32163.5 30048.55 29295.119999999999 45517.07 35436.11 32431.81 29424.5 30371.45 46501.08 33317.120000000003 29389.5 28492.34 48373.55 34139.980000000003 27982.85 29294.29 Week
Sales
Department 72
Weekly Sales
Weekly Sales 40214 40221 40228 40235 40242 40249 40256 40263 40270 40277 40284 40291 40298 40305 40312 40319 40326 40333 40340 40347 40354 40361 40368 40375 40382 40389 40396 40403 40410 40417 40424 40431 40438 40445 40452 40459 40466 40473 40480 40487 40494 40501 40508 40515 40522 40529 40536 40543 40550 40557 40564 40571 40578 40585 40592 40599 40606 40613 40620 40627 40634 40641 40648 40655 40662 40669 40676 40683 40690 40697 40704 40711 40718 40725 40732 40739 40746 40753 40760 40767 40774 40781 40788 40795 40802 40809 40816 40823 40830 40837 40844 40851 40858 40865 40872 40879 40886 40893 40900 40907 40914 40921 40928 40935 40942 40949 40956 40963 40970 40977 40984 40991 40998 41005 41012 41019 41026 41033 41040 41047 41054 41061 41068 41075 41082 41089 41096 41103 41110 41117 41124 41131 41138 41145 41152 41159 41166 41173 41180 41187 41194 41201 41208 98499.12 79636.320000000007 72377.789999999994 55590.73 65525.74 45046.81 41069.550000000003 40796.33 50079.67 43346.92 38711.22 42222.53 38131.53 39104.1 35355.339999999997 30922.75 31378.880000000001 38638.6 41297.839999999997 43244.02 42363.96 36242.120000000003 34523.839999999997 32796.050000000003 33638.519999999997 36106.410000000003 39070.699999999997 42051.15 44878.45 36690.76 43630.1 44555.16 43582.89 39528.86 39017.879999999997 30051.47 32775.14 34301.019999999997 34596.86 38960.78 44777.94 32072.93 196810.42 54268.03 44605.69 67832.13 121470.12 47448.91 42581.15 35873.69 41051.86 39918.870000000003 62891.35 71013.56 67900.899999999994 50577.46 54450.94 43535.72 43128.73 42627.73 36258.07 36512.94 39638.92 41104.42 33941.57 43167.81 41898.67 32615.01 34073.97 34638.69 30561.01 36041.85 36991.99 37493.58 32577.03 37570.26 35464.49 36132.57 41315.33 39234.129999999997 42481.55 49944.34 47054.71 38861.379999999997 44656.76 33809.019999999997 39319.17 38528.36 44370.76 56909.42 47743.78 63620.54 42060.480000000003 43050.78 203670.47 55188.08 60494.84 62424.9 94243.47 59732.61 39235.39 37248.54 37526.800000000003 43660.6 62524.58 98104.8 81287.05 57718.19 57117.62 48275.27 57135.91 44132.05 45350.26 40174.720000000001 40241.21 43176.95 33229.49 36683.629999999997 39483.03 35718.85 39801.69 39791.94 42900.32 44313.56 43726.7 37125.370000000003 38528.400000000001 44166.9 32363.17 34394.82 40143.72 40663.949999999997 40176.269999999997 39729.78 39515.269999999997 40231.040000000001 40346.480000000003 61883.75 50209.37 54480.13 42221.07 36267.08 35282.730000000003 Week
Sales
Department 35
Weekly Sales
Weekly Sales 40214 40221 40228 40235 40242 40249 40256 40263 40270 40277 40284 40291 40298 40305 40312 40319 40326 40333 40340 40347 40354 40361 40368 40375 40382 40389 40396 40403 40410 40417 40424 40431 40438 40445 40452 40459 40466 40473 40480 40487 40494 40501 40508 40515 40522 40529 40536 40543 40550 40557 40564 40571 40578 40585 40592 40599 40606 40613 40620 40627 40634 40641 40648 40655 40662 40669 40676 40683 40690 40697 40704 40711 40718 40725 40732 40739 40746 40753 40760 40767 40774 40781 40788 40795 40802 40809 40816 40823 40830 40837 40844 40851 40858 40865 40872 40879 40886 40893 40900 40907 40914 40921 40928 40935 40942 40949 40956 40963 40970 40977 40984 40991 40998 41005 41012 41019 41026 41033 41040 41047 41054 41061 41068 41075 41082 41089 41096 41103 41110 41117 41124 41131 41138 41145 41152 41159 41166 41173 41180 41187 41194 41201 41208 2770 2420 2176 2029.9 2752 2926 2861 1939 2275 3942 3204 3047 2454 3639 4173 2713 2932 3409 2798.5 2324 2873 2799.04 2788.5 2134 2742 1999 2639 2079 2449 2135 2273 1751 1842 2375 1816 3287 1874 2312 2483 3199 2229 2605 2870 3023 2735 3670 4279 1957.5 2029.75 1865 1828.57 1937 3111.97 3080.82 2901.88 2338.8200000000002 2709.82 2724.85 2936.44 3231.41 2610 2631.94 2859 3208.5 3631 3533.94 4090 2928.85 2780 2335.9699999999998 2970.4 2035.54 2143 2535.58 2552.52 2332.9499999999998 2523.9899999999998 1879.13 2590.31 1806.76 2666.68 2285.84 2081.31 2216.89 1771.64 2165.59 2210.41 1928.39 1953.39 2882.4 2179.0300000000002 2848.99 2576.34 2635.81 3126.99 2450.12 3023.41 3045.08 3532.72 2631.24 2013.82 2018.5 1852.48 1410.49 2571.73 3367.66 2394.5100000000002 2557.91 2801.79 2268.29 2934.74 2513.16 3174.24 3737.76 3320.38 2311.41 2578.9699999999998 2763.83 2768.22 2818.63 2473.4699999999998 3356.38 2727.51 2686.18 2293.3200000000002 2773.38 2531.92 1874.74 2044.01 2118.15 2138.88 2018.51 1842.08 1894.91 1623.92 1578.57 1547.07 1341.83 1773.32 1658.09 2228.94 1555.93 1459.47 Week
Sales
Department 79 Weekly Sales Forecast to 7/5/2013
Department 79 Weekly Sales 40214 40221 40228 40235 40242 40249 40256 40263 40270 40277 40284 40291 40298 40305 40312 40319 40326 40333 40340 40347 40354 40361 40368 40375 40382 40389 40396 40403 40410 40417 40424 40431 40438 40445 40452 40459 40466 40473 40480 40487 40494 40501 40508 40515 40522 40529 40536 40543 40550 40557 40564 40571 40578 40585 40592 40599 40606 40613 40620 40627 40634 40641 40648 40655 40662 40669 40676 40683 40690 40697 40704 40711 40718 40725 40732 40739 40746 40753 40760 40767 40774 40781 40788 40795 40802 40809 40816 40823 40830 40837 40844 40851 40858 40865 40872 40879 40886 40893 40900 40907 40914 40921 40928 40935 40942 40949 40956 40963 40970 40977 40984 40991 40998 41005 41012 41019 41026 41033 41040 41047 41054 41061 41068 41075 41082 41089 41096 41103 41110 41117 41124 41131 41138 41145 41152 41159 41166 41173 41180 41187 41194 41201 41208 41215 41222 41229 41236 41243 41250 41257 41264 41271 41278 41285 41292 41299 41306 41313 41320 41327 41334 41341 41348 41355 41362 41369 41376 41383 41390 41397 41404 41411 41418 41425 41432 41439 41446 41453 41460 46729.77 36080.86 34141.620000000003 33309.85 42931.77 31669.24 32604.49 29223.39 40910.94 37815.5 32135.69 28230.720000000001 30450.45 44039.92 30719.18 30190.720000000001 28195.06 42732.59 34564.68 29943.17 29521.94 37745.99 35320.410000000003 30200.62 29353.14 29275.56 43224.38 30376.79 28801.37 28990.87 39538.080000000002 34512.69 31291.01 27292.080000000002 31074.81 37770.25 29799.61 28439.7 25548.97 39589.08 29531.38 28073.27 29147.200000000001 32861.33 33269.22 31340.51 33497.1 24738.86 39984.6 30646.95 28181.68 27962.54 42065.78 36163.65 32462.04 30624.720000000001 45717.53 35519.370000000003 32251.01 28503.22 35814.980000000003 38752 31150.62 30833.66 28255.73 44271.49 33007.29 29087.29 28296.720000000001 42896.37 35023.199999999997 29656.36 29394.35 35335.800000000003 36448.879999999997 29505.119999999999 28530.68 28715.75 45837.83 29878.13 32156.49 29392.95 40331.25 34786.75 32427.96 29161.56 28876.959999999999 45059.56 30011.57 30190.3 28364.34 42665.35 31898.63 30928.23 33533.15 38806.559999999998 37340.339999999997 33041.49 34196.74 27676.43 42842.53 31338.33 31186 28937.93 47522.61 39360.339999999997 36855.68 31907.99 47896.34 39196.18 32723.279999999999 30097.93 30834.51 45342.21 30752.29 32581.33 30866.19 47342.76 34432.74 33905.440000000002 29874.35 37942.129999999997 41003.18 34184.660000000003 30684.35 30442.57 47794.94 32163.5 30048.55 29295.119999999999 45517.07 35436.11 32431.81 29424.5 30371.45 46501.08 33317.120000000003 29389.5 28492.34 48373.55 34139.980000000003 27982.85 29294.29 47115.320022792657 33776.647282127102 28276.693148350536 29378.069738495549 47064.269402155478 33725.596661489923 28225.642527713357 29327.01911785837 47013.218781518299 33674.546040852743 28174.591907076177 29275.968497221183 46962.16816088112 33623.495420215557 28123.541286438998 29224.917876584004 46911.11754024394 33572.444799578378 28072.490665801819 29173.867255946825 46860.066919606761 33521.394178941198 28021.440045164632 29122.816635309646 46809.016298969575 33470.343558304019 27970.389424527453 29071.766014672467 46757.965678332395 33419.29293766684 27919.338803890274 29020.715394035287 46706.915057695216 33368.242317029653 27868.288183253095 28969.664773398101 Lower 95CI 40214 40221 40228 40235 40242 40249 40256 40263 40270 40277 40284 40291 40298 40305 40312 40319 40326 40333 40340 40347 40354 40361 40368 40375 40382 40389 40396 40403 40410 40417 40424 40431 40438 40445 40452 40459 40466 40473 40480 40487 40494 40501 40508 40515 40522 40529 40536 40543 40550 40557 40564 40571 40578 40585 40592 40599 40606 40613 40620 40627 40634 40641 40648 40655 40662 40669 40676 40683 40690 40697 40704 40711 40718 40725 40732 40739 40746 40753 40760 40767 40774 40781 40788 40795 40802 40809 40816 40823 40830 40837 40844 40851 40858 40865 40872 40879 40886 40893 40900 40907 40914 40921 40928 40935 40942 40949 40956 40963 40970 40977 40984 40991 40998 41005 41012 41019 41026 41033 41040 41047 41054 41061 41068 41075 41082 41089 41096 41103 41110 41117 41124 41131 41138 41145 41152 41159 41166 41173 41180 41187 41194 41201 41208 41215 41222 41229 41236 41243 41250 41257 41264 41271 41278 41285 41292 41299 41306 41313 41320 41327 41334 41341 41348 41355 41362 41369 41376 41383 41390 41397 41404 41411 41418 41425 41432 41439 41446 41453 41460 37229.286601615808 23890.569373899954 18390.536152528261 19491.789169572483 34670.849907804681 21331.983963324074 15831.777486243313 16932.834711759904 32519.53852898319 19180.457830992855 13680.018209247759 14780.825046058673 30617.262893122235 17277.91747739588 11777.198022548599 12877.710680009061 28886.777343751775 15547.125901733889 10046.087396383053 11146.268542464146 27283.499010716034 13943.505830197868 8442.1140408768588 9541.9307943247622 25778.943716173657 12438.577092467749 6936.8014315004439 8036.2242373427798 24353.609524545762 11012.840815344887 5510.6535239709228 6609.6554475103512 22993.455030030796 9652.2580403182219 4149.6336465294153 5248.1898949549759 Upper...