ISE553 MODELLING AND ANALYSIS OF SUPPLY CHAINS
Spring 2014
Dr. Reha Uzsoy
Homework 2 - due 11:55PM, Tuesday January 27, 2015
Use the Excel workbook Spring2012HW2Data.xlsx to solve the following problems: Problem 1: Demand for a seasonal inventory item is estimated to follow a Gamma distribution with mean 150 units and standard deviation 50 units. Due to its seasonal nature and long delivery lead time, the item must be ordered before demand can be observed, and any inventory remaining at the end of the season must be disposed of at a discount. The product sells for $100 per unit, and is purchased for $40 per unit.
Excess inventory will be sold at the end of the season at a discounted price of $10 per unit. a) Compute the parameters of the Gamma distribution that defines the demand, showing your work in a text box on the title page of your submission per the Instructions for Submission at the end of this assignment.
b) Compute the order quantity that minimizes the expected cost of operating this inventory system by inserting the necessary formulas in the indicated area of the
Problem1 worksheet. Use the More Functions button in the Formulas tab to locate the formulas you need for the Gamma distribution.
Problem 2: a) Develop an Excel simulation in the indicated area of the Problem2 worksheet that will simulate 1000 demand observations, computing the quantities indicated in the headings in column H through O.
b) In a separate worksheet named Problem2Histogram, create a histogram of the realized profit observed in your simulation. Use bin sizes from 1000 to 12000 in increments of 250, and explain the behavior observed at the high end of the histogram what is causing this pattern to occur in the histogram?

Problem 3: Now assume that the demand for the inventory item in Problem 1 is given by the sum of three Gamma random variables, each having the parameters calculated in Problem 1a).
a) Create a worksheet named Problem 3, and develop an Excel simulation to determine the critical fractile of the demand distribution based on 1000 replications of the random variable of interest. Do NOT simulate the inventory system seeking the order quantity that minimized expected profit; your task is to use simulation to compute the value of the critical fractile. You should set the Calculation Options to Manual while doing this problem, since Excel will generate new random numbers each