excel file

BCOR 3750 -- Operations and Supply Chain Management Analytics
Project 1A -- Forecasting
NutCo Historical Shipments from 2016 to 2020 (MM Lbs)
Year	2016	2017	2018	2019	2020	2021
Jan	1.2	1.2	1.2	1.8	1.9
Feb	0.8	0.9	1.1	1.4	1.6
Mar	1.5	1.6	1.9	2.5	2.4
Apr	0.9	0.9	1.0	1.5	1.7
May	0.8	0.7	0.9	1.1	1.3
Jun	0.9	1.0	1.1	1.6	1.7
Jul	0.8	0.7	0.8	1.2	1.1
Aug	1.9	2.2	2.2	3.0	3.0
Sep	1.7	1.8	2.0	2.9	2.6
Oct	1.6	1.9	2.1	2.9	2.8
Nov	2.0	1.9	2.1	2.7	3.0
Dec	1.3	1.4	1.9	2.3	2.3
Total	15.4	16.3	18.5	24.8	25.5

You: How do estimate your numbers now?. Diane: We try to use the previous years' monthly and annual volumes (and just add a fudge factor as needed) but we are constantly finding ourselves chasing the eight-ball. You: Do you have the sales volumes for the previous year? Diane: Yes. Here are the numbers provided by the sales manager... Table 1: NutCo Historical Shipments from 2016 to 2020 (MM Lbs) Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct 2016 1.20 0.83 1.55 0.89 0.75 0.94 0.77 1.87 1.67 1.62 Nov Dec Total 1.98 1.34 15.41 2017 1.17 0.92 1.64 0.91 0.74 1.03 0.72 2.16 1.75 1.93 1.89 1.44 16.30 2018 1.22 1.15 1.87 1.05 0.93 1.1 0.82 2.23 2.04 2.12 2.09 1.9 18.52 2019 1.76 1.37 2.50 1.47 1.15 1.58 1.19 2.99 2.92 2.87 2.70 2.35 24.85 2020 1.87 1.61 2.36 1.72 1.28 1.67 1.15 3.04 2.63 2.83 3.01 2.34 25.51 Diane: We called your company because we would like your help forecasting the monthly and annual demand for 2021. You: In order forecast the monthly and annual demand, we will perform the following: BCOR 3750 - Project 1 (Part A) Provide a summary which shows the percentage of variation the model would explain, the change in forecast demand for each subsequent year, and the relevance of the annual constant (i.e. Summary Output) (5 Points) . Calculate how many millions of lbs the model's Annual Forecast can be off (also known as the Mean Absolute Deviation) (5 Points) . Calculate what percentage off the annual forecast (also known as the Mean Absolute Percentage Deviation) (5 Points) Calculate the monthly demand for 2021 using the combination of the annual forecasted demand and the monthly seasonal index (5 Points) Create a Monthly Demand Table for 2016 2020 Actuals PLUS the 2021 monthly Forecasts (5 Points) Calculate how many millions of lbs the model's monthly forecast can be off' (also known as the "Mean Absolute Deviation) (2.5 Points) . Calculate what percentage off' the monthly forecast (also known as the Mean Absolute Percentage Deviation) (2.5 Points) . Create a chart which shows the monthly 2016 to 2021 actuals and forecasts (with all of the proper labels) (5 Points) You: How do estimate your numbers now?. Diane: We try to use the previous years' monthly and annual volumes (and just add a fudge factor as needed) but we are constantly finding ourselves chasing the eight-ball. You: Do you have the sales volumes for the previous year? Diane: Yes. Here are the numbers provided by the sales manager... Table 1: NutCo Historical Shipments from 2016 to 2020 (MM Lbs) Year Jan Feb Mar Apr May Jun Jul Aug Sep Oct 2016 1.20 0.83 1.55 0.89 0.75 0.94 0.77 1.87 1.67 1.62 Nov Dec Total 1.98 1.34 15.41 2017 1.17 0.92 1.64 0.91 0.74 1.03 0.72 2.16 1.75 1.93 1.89 1.44 16.30 2018 1.22 1.15 1.87 1.05 0.93 1.1 0.82 2.23 2.04 2.12 2.09 1.9 18.52 2019 1.76 1.37 2.50 1.47 1.15 1.58 1.19 2.99 2.92 2.87 2.70 2.35 24.85 2020 1.87 1.61 2.36 1.72 1.28 1.67 1.15 3.04 2.63 2.83 3.01 2.34 25.51 Diane: We called your company because we would like your help forecasting the monthly and annual demand for 2021. You: In order forecast the monthly and annual demand, we will perform the following: BCOR 3750 - Project 1 (Part A) Provide a summary which shows the percentage of variation the model would explain, the change in forecast demand for each subsequent year, and the relevance of the annual constant (i.e. Summary Output) (5 Points) . Calculate how many millions of lbs the model's Annual Forecast can be off (also known as the Mean Absolute Deviation) (5 Points) . Calculate what percentage off the annual forecast (also known as the Mean Absolute Percentage Deviation) (5 Points) Calculate the monthly demand for 2021 using the combination of the annual forecasted demand and the monthly seasonal index (5 Points) Create a Monthly Demand Table for 2016 2020 Actuals PLUS the 2021 monthly Forecasts (5 Points) Calculate how many millions of lbs the model's monthly forecast can be off' (also known as the "Mean Absolute Deviation) (2.5 Points) . Calculate what percentage off' the monthly forecast (also known as the Mean Absolute Percentage Deviation) (2.5 Points) . Create a chart which shows the monthly 2016 to 2021 actuals and forecasts (with all of the proper labels) (5 Points)