+61480015851
Forecasting Sales and Analyzing Accuracy Excel-Based Analysis
Download Solution
Order New Solution
Assignment Task
1. JV Battery Distribution had the monthly sales for the past year below.
|
Month |
Sales |
|
January |
509 |
|
February |
480 |
|
March |
552 |
|
April |
486 |
|
May |
497 |
|
June |
502 |
|
July |
517 |
|
August |
540 |
|
September |
498 |
|
October |
523 |
|
November |
507 |
|
December |
546 |
1. Copy the above data into EXCEL and plot the monthly sales for last year on a line chart. (5)
2. Forecast this coming January’s sales using each of the following:
- Naïve method
- A three-month simple moving average
- A four-month weighted moving average using weights of 4,3,2,1
- A three-month weighted moving average with weights of your own choice.
- Exponential smoothing using an α = .25 and a December forecast of 540.
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 |
|
1 |
17 |
20 |
25 |
|
2 |
23 |
18 |
27 |
|
3 |
14 |
21 |
18 |
|
4 |
27 |
20 |
24 |
|
5 |
19 |
25 |
28 |
|
6 |
26 |
35 |
30 |
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. 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. 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.
3. Ontario Appliances has recorded the total sales of refrigerators for the previous twelve months.
a) Using this historical data, create a forecast from April – Dec using a three- period weighted moving average using weights of 3, 2 and 1. All answers rounded to whole numbers
b) Using this historical data, create a forecast from Apr -Dec using exponential smoothing and an opening forecast for March of 1500 units and an alpha of .3 All answers rounded to whole numbers.
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.
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.
This Business Economics has been solved by our PhD Experts at My Uni Paper.