Assignment #$1$ OPM$400$ January $2024$

$1.$ JV Battery Distribution had the monthly sales for the past year below.

Month Sales

January $940$

February $880$

March $758$

April $795$

May $913$

June $945$

July $907$

August $886$

September $779$

October $822$

November $764$

December $790$

a$)$ Copy the above data into EXCEL and plot the monthly sales for last year on a line chart. $(5)$

b$)$ Using the data copied into EXCEL, forecast this coming January$$s sales using each of the following:

i$)$ Na$$ve method $(2)$

ii$)$ A two$-$month simple moving average $(2$ marks$)$

iii$)$ A three$-$month weighted moving average using weights of $\mathrm{.}5,\mathrm{.}4,\mathrm{.}3(2$ marks$)$

iv$)$ A three$-$month weighted moving average with weights of your own choice. $(2$ marks$)$

v$)$ Exponential smoothing using an $\backslash$alpha $=\mathrm{.}15$ and a December forecast of $725.(2)$

Be sure to label each calculation and use a formula in order to receive the marks. Round all forecasts to whole numbers.

$2.$ Sales of Chevrolet$$s new hybrid minivan at a popular dealership in Edmonton is below. Also find the two forecasts that were completed in$-$house.

Forecasts

Month Actual Sales Method $1$ Method $2$

$1677065$

$2596570$

$3535563$

$4626160$

$5696362$

$6746866$

a$)$ Copy the above data into EXCEL and Calculate the Mean Absolute Deviation for both Method $1$ and Method $2.$ Round all answers to one decimal place. $(6$ marks$)$ Ensure that you show each step and that each column is labelled. Formulas must be used to obtain a grade.

b$)$ Copy the above data into EXCEL and Calculate the mean squared error for both Method $1$ and Method $2.$ Round all answers to one decimal place. $(6$ marks$)$ Ensure that you show each step and that each column is labelled. Formulas must be used to obtain a grade.

c$)$ Create a text box and include your comments on which of the two forecasts is the most accurate and why. $(2$marks$)$

$3.$ Ontario Appliances has recorded the total sales of refrigerators for the previous twelve months.

Month Sales

January $1924$

February $1860$

March $1769$

April $1595$

May $1840$

June $1730$

July $1623$

August $1698$

September $1805$

October $1737$

November $1735$

December $1873$

a$)$ Using this historical data, create a forecast from April $$ Dec using a three$-$ month weighted moving average using weights of $5,4$ and $3.$ All answers rounded to whole numbers $(2$ marks$)$

b$)$ Using this historical data, create a forecast from Apr $-$Dec using exponential smoothing and an opening forecast for March of $1600$ units and an alpha of $\mathrm{.}30$ All answers rounded to whole numbers $(2$ marks$)$

c$)$ Create a line chart and plot the actual demand as well as the April $$ Dec forecast from part a$.$ and the Apr$-$Dec forecast from b$.(5$ marks$)$

d$)$ If you were responsible to forecast these items, what factors would you take into consideration in addition to using historical data and time series analysis? Use a text box for your answer. $(3$ marks$)$

Be sure to label each column and use formulas in order to receive the marks. Be sure to read the instructions below

Instructions:

This is an EXCEL assignment. Upload ONE excel document with all the above questions on one sheet. Double check that you upload the correct document the first time.

In the top left corner, include your full name and student number.

Label your document with your firstname.lastnameAss$1.$

Ensure that every column is labelled describing the content.

Every piece of data must come from a formula to obtain the grade.

Format your tables to give a professional appearance.

Graphs must have a descriptive title, labelled X and Y axes.

Written questions must be inside a text box.

Below your question #$3,$ copy the table below.