Construct a histogram of the inventory data

Question

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.