In: Accounting
National Scan, Inc., sells radio frequency inventory tags. Monthly sales for a seven-month period were as follows: |
Month | Sales (000)Units |
Feb. | 20 |
Mar. | 17 |
Apr. | 13 |
May. | 28 |
Jun. | 17 |
Jul. | 23 |
Aug. | 26 |
b. | Forecast September sales volume using each of the following: |
(1) |
A linear trend equation.(Round your intermediate calculations and final answer to 2 decimal places.) |
Yt | thousands |
(2) |
A five-month moving average. (Round your answer to 2 decimal places. |
Moving average | thousands |
(3) |
Exponential smoothing with a smoothing constant equal to .30, assuming a March forecast of 19(000). (Round your intermediate forecast values and final answer to 2 decimal places) |
Forecast | thousands |
(4) | The naive approach |
Naive approach | thousands |
(5) |
A weighted average using .60 for August, .10 for July, and .30 for June. (Round your answer to 2 decimal places.) |
Weighted average | thousands |
Ans
Given
Month |
Sales (000) units |
Feb | 20 |
March | 17 |
April | 13 |
May | 28 |
June | 17 |
July | 23 |
Aug | 26 |
b.
(1)
The linear trend equation is as below:-
y = a + bt
y = Forecast for peroid t
t = Specified number of time period
a = Value of Ft at t = 0
b = Slope of the line
Now,
b = nƩ(ty) - ƩtƩy / nƩt² - (Ʃt)²
a = Ʃy - bƩt / n
t Month |
t² |
y Sales (000 units) |
ty |
2 (Feb) | 4 | 20 | 40 |
3 (Mar) | 9 | 17 | 51 |
4 (April) | 16 | 13 | 52 |
5 (May) | 25 | 28 | 140 |
6 (June) | 36 | 17 | 102 |
7 (July) | 49 | 23 | 161 |
8 (Aug) | 64 | 26 | 182 |
Ʃt = 35 | Ʃt² = 203 | Ʃy = 144 | Ʃty = 728 |
(Ʃt)² = 1225 |
n = number of month = 7
t = 9 (Sept)
Now
b = 7 * 728 - 35 * 144 / 7 * 203 - 1225
= 5096 - 5040 / 1421 - 1225
= 56 / 196
= 0.29
a = 144 - 0.29 * 35 / 7
= 144 - 10.15 / 7 = 133.85 / 7
= 19.12
Now
y = a + bt
= 19.12 + 0.29 * 9 = 19.12 + 2.61
= 21.73
Hence, as per linear trend equation
Sales Forecast for month of Sept = 21.73 (000 units)
(2)
A five-month moving average is calculated by averaging Feb to June (a) , then March to July (b) & April to Aug (c).
These data points are
a = 20 + 17 + 13 + 28 + 17 / 5
= 95 / 5
= 19
b = 17 + 13 + 28 + 17 + 23 / 5
= 98 / 5
= 19.6
c = 13 + 28 + 17 + 23 + 26 / 5
= 107 / 5
= 21.4
So the Sept Sales Forecast = 21.40 thousand units
(4)
The naive approach takes the last period's actuals as the forecast for the next month
As Aug sales was 26 thousand units (as per given table)
Hence,
Naive Forecast for Sept = 26.00 thousand units
(5)
The weighted average calculation Sep has weights of 1 for each month except Aug 0.60
Weighted | ||||
(000) units | Weighted | Average | ||
Weight | Month | Sales | Sales | Forecast |
1.00 | Feb | 20 | 20 | |
1.00 | March | 17 | 17 | |
1.00 | April | 13 | 13 | |
1.00 | May | 28 | 28 | |
1.00 | June | 17 | 17 | |
1.00 | July | 23 | 23 | |
0.60 | Aug | 26 | 15.60 | |
Sept | 19.09 |
Hence,
Weighted Average of Sept = 19.09 Thousand Units