Purchase Solution

Forecast

Not what you're looking for?

Ask Custom Question

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.

Purchase this Solution

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.

Solution provided by:
Education
  • MSc, Osmania University
  • MSc, Indian Institute of Technology - Roorkee (I.I.T.-ROORKEE)
  • BSc, Banaras Hindu University
Recent Feedback
  • "Very fast turnaround. Excellent communication! Much appreciated!"
  • "excellent"
  • "excellent"
  • "excellent work"
  • "Fantastic..great solution and very well explained!!!!"
Purchase this Solution


Free BrainMass Quizzes
Terms and Definitions for Statistics

This quiz covers basic terms and definitions of statistics.

Know Your Statistical Concepts

Each question is a choice-summary multiple choice question that presents you with a statistical concept and then 4 numbered statements. You must decide which (if any) of the numbered statements is/are true as they relate to the statistical concept.

Measures of Central Tendency

This quiz evaluates the students understanding of the measures of central tendency seen in statistics. This quiz is specifically designed to incorporate the measures of central tendency as they relate to psychological research.

Measures of Central Tendency

Tests knowledge of the three main measures of central tendency, including some simple calculation questions.