Home Insert Draw Page Layout Formulas Data Review Vie

EXAM $-$ FORECASTING USING EXCEL

PART ONE $-90$ PONTS

Your boss has asigned you a fore casting project at work. She wants you to forecast demand for the company and has given you a file with data, which is on the worksheets of the spreadsheet for your converience.

She believes future sales are related to either past sales or two associated variables. Complaint Costs and Advertising Impressions. You have been instrumental in creating your company's online strategy and recormmended several process improvements that are working well.

She asks you to develop sales forecasts for the organization and you show your OM prowness stating that you will:

Peform sales forecasts using the follow ing methods.

a$)$ Moving average $($use a four period MA$)$

b$)$ Weighted moving average $($five penod WMA with weights of $1,2,3,4,5$ Use these weights onty.$)$

Exponential smoothing $($ e $=0\mathrm{.}35$

d$)$ Multiple regression using Ad Impressions, and Complaint Costs as the predictors of sales. Hint: Both variables should be used to form one regression model.

Note$-$ separate works heets have been set up in the exam file to facilitate your development. Use the common stricture of these sheets fo your advantage and speed your progress, no graphing needed.

Evaluate each of the above forecasting methods using the error measures... a$)$ Average bias $($Bias$)$

b$)$ MAD

c$)$ MSE

d$)$ RMSE

Please use the works heet fitled "Compare" to compare the methods.

Choose the best method using information from $(1)$ and $(2)$ to do so$...$

Place your discussion on the worksheet labeled "Comp are" and include specific coverage on why you chose the method you did. Knowing how important it is to get a "fair" comparison, you know to do a companson of all the forecast methods over the same time honizon.

Note that part of what I am grading in $(3)$ is do you know how to make this decision and what to base it

Directions

WMA

MA

Compare

Exp. Smooth.

Ready

Accessibility: Investigate

Search

Esc

F$1$

$$

F$2$

$3$

$0+$

F$4$

F$5$

F$6$

$!$

@

#

$

$\%$

$1$

$2$

$3$

$4$

$5$

Tab

$A=B$

C

D

E

F

Week

Sales $($$$1000)$

Ad Impressions

complaint costs

$($thousands$)$

$($$$1000)$

WMA$($s$)$

$1$

$5137$

$167$

$242$

$266$

$185$

$233$

$236$

$255$

$239$

$244$

$261$

$249$

$244$

$242$

$252$

$265$

$262$

$245$

$251$

$253$

$259$

$6446$

$6878$

$7079$

$6828$

$281$

$271$

$277$

$24$

$7385$

$267$

Directions WMA

MA Compare

Exp. Smooth.