13 Feb This exercise provides you the opportunity to apply several concepts you have learned in Module #3 in addition to Excel skills practice. Learning Objectives: ? Identify trends and patter
This exercise provides you the opportunity to apply several concepts you have learned in Module #3 in addition to Excel skills practice.
Learning Objectives:
· Identify trends and patterns by analyzing data in an Excel line graph
· Compare and contrast the performance and accuracy of five sales forecasting models through forecast accuracy analysis
· Develop conclusions and a forecasting recommendation for a case study issue
· Compute the labor productivity impact due to a demand shock significantly lowering demand below the forecast and make a recommendation on how to address
· Create a chart in Excel and insert the chart in a Word document
· Create a well-written, professional, grammatically correct leadership report
Required Materials:
· Redstone Foods M&M Wholesale Case Study – (Word document)
· Redstone Foods M&M Wholesale Data File – (Excel document)
· Microsoft Excel
Forecasting
Redstone Foods M&M Sales Forecasting Tool | ||||||||||||||||||||||||||||
Data Section | 2019 Total Sold (Cases) | 2020 Total Sold (Cases) | 2021 Total Sold (Cases) | 2022 Total Sold (Cases) | 2021 – 2022 Actual Growth | MODEL #1 2022 Forecast 3% Growth Over 2019 Only | MODEL #1 2022 Forecast Growth Over 2019 Only – Absolute Deviations | Squared Errors | Absolute Percent Error | MODEL #2 2022 Forecast 3-month Moving Average | MODEL #2 2022 Forecast 3-month Moving Average – Absolute Deviations | Squared Errors | Absolute Percent Error | MODEL #3 2022 Forecast 3-Month Weighted Moving Average | MODEL #3 2022 Forecast 3-Month Weighted Moving Average – Absolute Deviations | Squared Errors | Absolute Percent Error | MODEL #4 2022 Forecast Exp. Smoothing | MODEL #4 2022 Forecast Exp. Smoothing – Absolute Deviations | Squared Errors | Absolute Percent Error | MODEL #5 2022 Forecast Exp. Smoothing with Trend | MODEL #5 2022 Forecast Exp. Smoothing with Trend – Absolute Deviations | Squared Errors | Absolute Percent Error | Season Adjusment Factors | ||
January | 52598 | 57487 | 57342 | 58012 | 1.17% | 59062 | 1050 | 1102500 | 1.81% | 71352 | 13340 | 177955600 | 23.00% | 71907 | 13895 | 193071025 | 23.95% | 64183 | 6171 | 38081241 | 10.64% | 65144 | 7132 | 50865424 | 12.29% | 0.89 | ||
February | 58908 | 59513 | 58236 | 59456 | 2.09% | 59983 | 527 | 277729 | 0.89% | 67689 | 8233 | 67782289 | 13.85% | 65312 | 5856 | 34292736 | 9.85% | 66559 | 7103 | 50452609 | 11.95% | 68086 | 8630 | 74476900 | 14.51% | 0.91 | ||
March | 58800 | 62834 | 56843 | 57007 | 0.29% | 58548 | 1541 | 2374681 | 2.70% | 63473 | 6466 | 41809156 | 11.34% | 61722 | 4715 | 22231225 | 8.27% | 68477 | 11470 | 131560900 | 20.12% | 70160 | 13153 | 173001409 | 23.07% | 0.89 | ||
April | 69056 | 76115 | 72343 | 60582 | -16.26% | 74513 | 13931 | 194072761 | 23.00% | 58158 | 2424 | 5875776 | 4.00% | 57943 | 2639 | 6964321 | 4.36% | 65337 | 4755 | 22610025 | 7.85% | 65092 | 4510 | 20340100 | 7.44% | 1.13 | ||
May | 64884 | 67409 | 65875 | 58209 | -11.64% | 67851 | 9642 | 92968164 | 16.56% | 59015 | 806 | 649636 | 1.38% | 59284 | 1075 | 1155625 | 1.85% | 63573 | 5364 | 28772496 | 9.22% | 62720 | 4511 | 20349121 | 7.75% | 1.03 | ||
June | 55465 | 55863 | 59351 | 52850 | -10.95% | 61132 | 8282 | 68591524 | 15.67% | 58599 | 5749 | 33051001 | 10.88% | 58681 | 5831 | 34000561 | 11.03% | 61603 | 8753 | 76615009 | 16.56% | 60303 | 7453 | 55547209 | 14.10% | 0.93 | ||
July | 59143 | 61869 | 60050 | 53021 | -11.71% | 61852 | 8831 | 77986561 | 16.66% | 57214 | 4193 | 17581249 | 7.91% | 56004 | 2983 | 8898289 | 5.63% | 61297 | 8276 | 68492176 | 15.61% | 60394 | 7373 | 54361129 | 13.91% | 0.94 | ||
August | 61171 | 62850 | 63259 | 55895 | -11.64% | 65157 | 9262 | 85784644 | 16.57% | 54693 | 1202 | 1444804 | 2.15% | 54007 | 1888 | 3564544 | 3.38% | 60370 | 4475 | 20025625 | 8.01% | 59458 | 3563 | 12694969 | 6.37% | 0.99 | ||
September | 64173 | 63224 | 62518 | 56022 | -10.39% | 64394 | 8372 | 70090384 | 14.94% | 53922 | 2100 | 4410000 | 3.75% | 54424 | 1598 | 2553604 | 2.85% | 58114 | 2092 | 4376464 | 3.73% | 56665 | 643 | 413449 | 1.15% | 0.97 | ||
October | 68265 | 67351 | 69002 | — | 71072 | 54979 | 55384 | 56586 | 55105 | 1.08 | ||||||||||||||||||
November | 72885 | 75195 | 72102 | — | 74265 | ERROR:#N/A | ERROR:#N/A | ERROR:#N/A | ERROR:#N/A | 1.10 | ||||||||||||||||||
December | 69865 | 72488 | 72952 | — | 75141 | ERROR:#N/A | ERROR:#N/A | ERROR:#N/A | ERROR:#N/A | 1.14 | ||||||||||||||||||
Forecast Accuracy | MODEL #1 | MODEL #2 | MODEL #1 | MODEL #1 | MODEL #1 | MODEL #3 | MODEL #1 | MODEL #1 | MODEL #1 | MODEL #4 | MODEL #5 | |||||||||||||||||
Apply Seasonality Adjustment | NO | 2022 YTD Accuracy | MAD | 6826 | 4946 | 4498 | 6495 | 6330 | ||||||||||||||||||||
Forecast Growth Rate (Target): | 3.00% | MSE | 65916550 | 38951057 | 34081326 | 48998505 | 51338857 | |||||||||||||||||||||
Forecast Weights: | 20% | 30% | 50% | MAPE | 12.09% | 8.70% | 7.91% | 11.52% | 11.18% | |||||||||||||||||||
YTD Aggregate Growth Rate: | -8.05% | |||||||||||||||||||||||||||
1st Quarter 2022 Accuracy | MAD | 1039 | 9346 | 8155 | 8248 | 9638 | ||||||||||||||||||||||
MSE | 1251637 | 95849015 | 83198329 | 73364917 | 99447911 | |||||||||||||||||||||||
MAPE | 1.80% | 16.06% | 14.02% | 14.23% | 16.63% | |||||||||||||||||||||||
2nd Quarter 2022 Accuracy | MAD | 10618 | 2993 | 3182 | 6291 | 5491 | ||||||||||||||||||||||
MSE | 118544150 | 13192138 | 14040169 | 42665843 | 32078810 | |||||||||||||||||||||||
MAPE | 18.41% | 5.42% | 5.75% | 11.21% | 9.77% | |||||||||||||||||||||||
3rd Quarter 2022 Accuracy | MAD | 8822 | 2498 | 2156 | 4948 | 3860 | ||||||||||||||||||||||
MSE | 77953863 | 7812018 | 5005479 | 30964755 | 22489849 | |||||||||||||||||||||||
MAPE | 16.06% | 4.60% | 3.95% | 9.12% | 7.14% | |||||||||||||||||||||||
M&M Sales Forecasts (Cases)
2021 Total Sold (Cases) January February March April May June July August September October November December 57342 58236 56843 72343 65875 59351 60050 63259 62518 69002 72102 72952 2022 Total Sold (Cases) 58012 59456 57007 60582 58209 52850 53021 55895 56022 MODEL #1 2022 Forecast 3% Growth Over 2019 Only 59062 59983 58548 74513 67851 61132 61852 65157 64394 71072 74265 75141 MODEL #2 2022 Forecast 3-month Moving Average 71352 67689 63473 58158 59015 58599 57214 54693 53922 54979 #N/A #N/A MODEL #3 2022 Forecast 3-Month Weighted Moving Average 71907 65312 61722 57943 59284 58681 56004 54007 54424 55384 #N/A #N/A MODEL #4 2022 Forecast Exp. Smoothing 64183 66559 68477 65337 63573 61603 61297 60370 58114 56586 #N/A #N/A MODEL #5 2022 Forecast Exp. Smoothing with Trend 65144 68086 70160 65092 62720 60303 60394 59458 56665 55105 #N/A #N/A
Month
Cases Forecast
M&M Sales Trends (Cases Sold)
2021 Total Sold (Cases) January February March April May June July August September October November December 57342 58236 56843 72343 65875 59351 60050 63259 62518 69002 72102 72952 2022 Total Sold (Cases) 58012 59456 57007 60582 58209 52850 53021 55895 56022 2020 Total Sold (Cases) 57487 59513 62834 76115 67409 55863 61869 62850 63224 67351 75195 72488 2019 Total Sold (Cases) 52598 58908 58800 69056 64884 55465 59143 61171 64173 68265 72885 69865
Month
Cases Sold
Calcs
January | 57342 | Yes | January | Jan-18 | Jan-19 | ||
February | 58236 |
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline. Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax. |