You are responsible for sales forecasting specific to the M&M candy line at Redstone Foods. Due to the disruptive events this year, your leadership has asked you to review the M&M candy sales forecast for the remainder of the year and draft a report with your current sales assessment and forecast recommendation for the final quarter of the year (October, November, and December). There are multiple steps required to complete this project. Complete each of the steps below to analyze the provided data. As you perform the analysis, keep notes concerning your conclusions so that you can create your report that you will submit to the appropriate D2L dropbox.
Included with this assignment (as a separate file) is the Redstone Foods Excel Sales Forecasting Tool. Open the spreadsheet and spend some time becoming familiar with it. The tool has several cells where data can be input, highlighted in yellow. We will be using some of these cells later in the assignment.
The Data Section of the spreadsheet includes five forecasting models for 2020. The explanation for each of these models is in your textbook and the D2L content. You do not need to understand the forecast calculations formulas, but you should understand the nature of each forecasting model. It would be best if you also understood the pros and cons of each model, as learned during this module.
The company currently projects a target growth percentage and then uses this target as the forecast for the coming year. Column H of the Data Section contains this forecast. You have decided to test alternative forecasting models and have calculated the results in columns L, P, T, and X.
In this assignment you will submit a Word document with your current assessment as discussed below adhering to the template provided in the Appendix.
Redstone Foods Excel Sales Forecasting Tool Data Elements:
Data Section Summary:
- Column B: “2017 Total Sold (Cases)” – contains the number of cases sold in each month of 2017
- Column C: “2018 Total Sold (Cases)” – contains the number of cases sold in each month of during 2018
- Column D: “2019 Total Sold (Cases)” – contains the number of cases sold in each month of during 2019
- Column E: “2020 Total Sold (Cases)” – contains the number of cases sold in each month of 2020 through August
- Column G: “2019 – 2020 Actual Growth” – contains the monthly actual percentage growth between 2019 and 2020 based on the monthly sales data for each year
- Column H: “MODEL #1 – 2020 Forecast 3% Growth Over 2019 Only” – contains the first forecasting model, based on 2019 sales multiplied by the expected growth rate in the “Forecast Growth Rate” (Cell B22) ***THIS IS THE CURRENT FORECAST USED BY THE COMPANY ***
- Column L: ” MODEL #2 – 2020 Forecast 3-Month Moving Average” – contains the second forecasting model, based on a three-month moving average
- Column P: ” MODEL #3 – 2020 Forecast 3-Month Weighted Moving Average” – contains the third forecasting model, based on a three-month weighted average using the three weights in the Forecast Weights cells (B23 – D23).
- Value in cell D23 is the weight placed on the most recent month of data
- Value in cell C23 is the weight placed on the second most recent month of data
- Value in cell B23 is the weight placed on the third most recent month of data.
- Column T: ” MODEL #4 – 2020 Forecast Exp Smoothing” – contains the fourth forecasting model based on exponential smoothing
- Column X: ” MODEL #5 – 2020 Forecast Exp Smoothing with Trend” – contains the fifth forecasting model, based on exponential smoothing with a trend consideration
- Column AB: “Season Adjustment Factors” – contains a seasonal adjustment factor computed from 2019 data
Input Cells Summary (highlighted in yellow):
- “Apply Seasonality Adjustment” (Cell B21) – this is a “Yes” or “No” value cell. When “Yes” is selected, forecast models 2 through 5 are adjusted to account for the seasonality indicator. Selecting “No” will remove the seasonality adjustment.
- “Forecast Growth Rate” (Cell B22) – this is a percentage value cell. The percentage entered will be factored in as the forecast growth rate for 2020 based on 2019 data. The value can be positive or negative.
- “Forecast Weights” (Cell B23, C23, and D23) – these cells hold the three weights for the three-month weighted moving average calculations.
Forecast Accuracy Section: There are three forecasting accuracy measurements included in this section. Calculations have been made for the 2020 year to date, 2020 first quarter, 2020 second quarter, and 2020 third quarter. While you are not required to calculate the values for each of these methods, you should understand what each metric is measuring. It would be best if you also understood the pros and cons of each measurement. This information is in your textbook and the content for this module in D2L.
- MAD – Mean Average Deviation values for the five forecasting methods
- MSE – Mean Square Error values for the five forecasting methods
- MAPE – Mean Average Percent Error values for the five forecasting methods
Graphs:
- M&M Sales Trends (Cases Sold) – this graph details the cases sold per month for each sales year
- M&M Sales Forecasts (Cases) – this graph details the forecasts from each of the five forecasting models
Case Issue:
Redstone Foods has been acquiring cases of M&Ms based on Model #1: 2020 Forecast 3% Growth Over 2019 Only forecast model (Column H). During the second quarter of 2020, the COVID-19 situation significantly impacted demand.
Material to Include in Your Current Assessment:
You should answer the questions and address the items in your current assessment document. You do not need to answer these questions is a separate document.
- Analyze Demand Patterns
- Using The M&M Sales Trends (Cases Sold) graph, determine if there are any consistent patterns that demand has followed from 2017 – 2019. These patterns could indicate some seasonality in demand (or predictable demand changes). Include a summary of the trends you have found in your report. Consider what causes these patterns, such as holidays, etc.
- Analyze 2020 demand and consider whether or not demand is following the patterns you have identified in 2020.
- Analyze Current Inventory
- The company has been purchasing cases of M&Ms based on the 3% over growth forecast for January 2020 through September 2020. This process means that the company purchases in advance the number of cases required to cover each month as listed in column H (Model #1). For example, the company purchased 59,062 cases to sell in January of 2020, 59,983 cases to sell in February 2020 and so on. Calculate the total number of cases of M&Ms purchased from January through September of 2020 and the number of cases of M&Ms sold from January through September of 2020. What is the difference between total cases purchased in the period and total cases sold in the period?
- If you continue to purchase cases of M&Ms based on the forecast, how will your end of year inventory be impacted? Include this assessment in your report.
- Analyze labor and productivity impact
- The original forecast indicates that you will have needed a total of 791,940 cases for 2020. You have labor hired to accommodate this amount of order processing at an expected productivity rate of 95.18 cases per hour based on four employees working a total of 8,320 hours during the year.
- Assuming the current demand and sales, how is productivity currently impacted, and how will it be affected through the remainder of the year? (Hint calculate the productivity rate for the sales to date (end of September) using the following data:
- The total number of cases sold January through September of 2020
- For the present, you have not furloughed or laid off any workers, so they are all reporting to work and working. Labor hours for January through September are 1,560 hours
- Provide a summary assessment of the labor and productivity situation and recommend a plan for moving forward. Your plan should be supported with evidence from your demand forecast analysis. (Hint – if you layoff any employees, consider what the company’s action will be if and when demand picks up).
Forecasting
Your next task is to develop a recommendation for forecasting the remainder of the year. Let’s look at the forecasting models.
Complete the following tasks in the Excel document. The questions will help you collect data and draw conclusions for your report.
- Confirm that the following pre-set existing values are present. If they are different then key in the values below:
- Apply Seasonality = “No” (Cell B21)
- Forecast Growth Rate = 3.00% (Cell B22)
- Forecast Weights = 20% (Cell B23); 30% (Cell C23); 50% (Cell D23)
- Determine which of the five forecast models performed the best based on MAD, MSE, and MAPE. Think about why the particular model performed the best across the first nine months of the year.
- We use seasonality adjustment factors to adjust forecasts for the presence of regular movements that relate to recurring events such as holidays. Your team has calculated seasonality adjustment factors in column AC of the Data Section in the spreadsheet.
- Change the value of Cell (B21) from “No” to “Yes” to have the four new forecasting models take seasonality into account
- Now determine which of the five forecast models performed the best based on MAD, MSE, and MAPE. Think about why the particular model performed the best across the first nine months of the year and include this assessment in your report as part of your recommendation.
- Create a graph showing MAPE for all five forecasting models across the four specific periods, 2020 YTD, 1st Quarter, 2nd Quarter, and 3rd Quarter. Include the figure in your report. Full credit for a chart is contingent on the inclusion of all of the following, a chart title, legend, x-axis title, and y-axis title. One possible example is the clustered column chart below. Charts are embedded within the text by right-clicking on the chart and selecting the option indicated below
Forecasting Recommendation:
Include the following items in your forecasting recommendation:
- Assessment of which forecasting model should be used for the remaining three months. Also, consider if there should be action beyond the forecast model, such as a judgmental component or a manual adjustment.
- Assess the impact of seasonal adjustment factors on the forecasts. Is there evidence that using seasonal adjustment factors will provide value to the forecasts?
- Consider the forecast accuracy for each quarter of the year. Discuss the impact of an unforeseen significant demand shock on the forecasting models (specifically look at the 2nd quarter and discuss model performance).
- Develop a recommendation concerning what Redstone Foods should forecast for the remaining three months of the year.
Create Your Report:
Create your report for Redstone Leadership by following the template provided in the Appendix of this assignment. Follow the template exactly.
Grading: See Rubric in D2L
Appendix
Format for the Redstone Foods 2020 4th Quarter M&M Sales Forecasting Report – do not include this line or the Appendix title in your document.
Redstone Foods 2020 4th Quarter M&M Sales Forecasting Report
Date: {today’s date}
Prepared by: {Your Name}
Executive Summary
{An executive summary is designed primarily to serve the person who, at least initially, does not intend to read the entire report. It usually states the main points of each section and emphasizes results, conclusions, and recommendations. Executive summaries are ideally suited to the needs of readers who are seeking advice about a decision or a course of action. These summaries are called executive summaries because some decisionmakers rely wholly upon their advisors to read and evaluate the rest of the report. For this project, the Executive Summary should concentrate on summarizing who you are, what the problem is, conclusions, and recommendations. You can find information on executive summaries as one of the readings in D2L for this module.}
Current Assessment
{The current assessment section should provide the reader with a summary of the current state. Consider the elements that were listed earlier in this document and reproduced here for your convenience.
- Analyze Demand Patterns
- Analyze Current Inventory
- Analyze labor and productivity impact
Include your MAPE chart embedded with the text along with appropriate discussion. End this section with a transition into the next section where you will draw your conclusions and provide your recommendation.
Forecasting Recommendation
{The forecasting recommendation section should provide the reader with a summary of your conclusions regarding forecasting and a recommendation for forecasting the remaining three months of the year. Consider the elements that were listed earlier in this document and reproduced here for your convenience.
- Assessment of whether any of the other four forecasting models would have had better results than Model #1.
- Assess the impact of seasonal adjustment factors on the forecasts. Is there evidence that using seasonal adjustment factors will provide value to the forecasts?
- Consider the forecast accuracy for each quarter of the year. Discuss the impact of an unforeseen significant demand shock on the forecasting models (specifically look at the 2nd quarter and discuss model performance.
- Develop a recommendation concerning what Redstone Foods should forecast for the remaining three months of the year.}
{End your report with a suitable closing}
Leave a reply