Share
Explore BrainMass

Forecast

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.
Note- I am guessing that the histogram should simply depict the seasonal indices for each month (i.e. 0.94 for January, 0.88 for February, etc.)

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.

Attachments

Solution Summary

This solution is comprised of detailed analysis and step-by-step calculation of future inventory costs in EXCEL. The solution provides students with a clear perspective of the given problems and the related aspects of forecast analysis.

$2.19