Assignment:
Task:
Summer Historical Inventory Data
|
Typical Seasonal Demand for Summer Highs
|
|
|
|
|
|
|
|
|
|
Actual Demands (in units)
|
|
|
|
|
|
|
|
|
|
Month
|
Year 1
|
Year 2
|
Year 3
|
Year 4
|
Forecast
|
|
1
|
18,000
|
45,100
|
59,800
|
35,500
|
|
|
2
|
19,800
|
46,530
|
30,740
|
51,250
|
|
|
3
|
15,700
|
22,100
|
47,800
|
34,400
|
|
|
4
|
53,600
|
41,350
|
73,890
|
68,000
|
|
|
5
|
83,200
|
46,000
|
60,200
|
68,100
|
|
|
6
|
72,900
|
41,800
|
55,200
|
61,100
|
|
|
7
|
55,200
|
39,800
|
32,180
|
62,300
|
|
|
8
|
57,350
|
64,100
|
38,600
|
66,500
|
|
|
9
|
15,400
|
47,600
|
25,020
|
31,400
|
|
|
10
|
27,700
|
43,050
|
51,300
|
36,500
|
|
|
11
|
21,400
|
39,300
|
31,790
|
16,800
|
|
|
12
|
17,100
|
10,300
|
31,100
|
18,900
|
|
|
Avg.
|
|
|
|
|
|
Second Graph is based on the seasonal index for the projected Year 5
|
Y
|
Year 1
|
Year 2
|
Year 3
|
Year 4
|
Mean
|
Index
|
Year 5
|
|
Month
|
|
|
|
|
|
|
|
|
January
|
18,000
|
45,100
|
59,800
|
35,500
|
39,600
|
.94
|
39,808.02
|
|
February
|
19,800
|
46,530
|
30,740
|
51,250
|
37,080
|
.88
|
37,267.08
|
|
March
|
15,700
|
22,100
|
47,800
|
34,400
|
30,000
|
.71
|
30,067.76
|
|
April
|
53,600
|
41,350
|
73,890
|
68,000
|
59,210
|
1.40
|
59,288.54
|
|
May
|
83,200
|
46,000
|
60,200
|
68,100
|
64,375
|
1.52
|
64,370.42
|
|
June
|
72,900
|
41,800
|
55,200
|
61,100
|
57,750
|
1.36
|
57,594.59
|
|
July
|
55,200
|
39,800
|
32,180
|
62,300
|
47,370
|
1.12
|
47,430.84
|
|
August
|
57,350
|
64,100
|
38,600
|
66,500
|
56,637.5
|
1.34
|
56,747.61
|
|
September
|
15,400
|
47,600
|
25,020
|
31,400
|
29,855
|
.70
|
29,644.27
|
|
October
|
27,700
|
43,050
|
51,300
|
36,500
|
39,637.5
|
.94
|
39,808.02
|
|
November
|
21,400
|
39,300
|
31,790
|
16,800
|
27,322.5
|
.65
|
27,526.82
|
|
December
|
17,100
|
10,300
|
31,100
|
18,900
|
19,350
|
.46
|
19,480.52
|
|
Total
|
457,350
|
487,030
|
537,620
|
550,750
|
508,187.5
|
12
|
508,187.5
|
With the following Data, please provide the followings:
Construct a histogram of the inventory data using Microsoft® Excel®.
Forecast the future inventory costs using time value of money concepts.
Note-To understand a more accurate picture of the amount the inventory will be in the following year, it is necessary to evaluate the forecasted amount and calculate the future time value of the inventory. This value can be used as the interest rate in the formula FV=PV ((1+i) ^n) to calculate the estimated cost of the forecasted inventory for the following year.