Assignment 5

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Assignment 5 (100 points)

Part (a): 30 points      Part (b): 10 points      Part (c): 40 points      Part (d): 20 points

Please set up an Excel file to analyze the case study described below. Submit your Excel file with your added notes summarized in a Word doc file.

Profit Management Using an (R, Q) Inventory Model

You work as a business analyst for a department store that sells a high-end washing machine model. The demand estimates and the cost factors considered are listed below.

Average demand per week DA      20

Demand standard deviation DSD      5

Fixed order cost FOC      $3,000

Unit product purchase cost UC      $900

Unit selling price UP      $1,400

Holding cost per unit, per week HC      $20

Salvage value at the end of year SV      $700

Beginning inventory BI      30

You have been asked to assist the store manager to set up an efficient inventory policy for these washing machines. You plan to apply a stochastic (R, Q) inventory model (as discussed in class and lecture 2). You consider two important management criteria:

1) Maximize the expected profits of the store over a one-year time period,

2) Keep stockout probability below a certain level which is set by the manager to 5 times per year (considering weeks as the time unit).

(a) Set up a simulation model for a year (52 weeks). In your model, use the appropriately truncated and discretized normal distribution (as discussed in class and the topical lecture) to simulate stochastic weekly product demands. Record your net profits over the entire simulated time period.

(b) In order to start optimizing the selection of R and Q, assume first that we exactly know the weekly demands, using the value DA. Find the solution of the corresponding deterministic model.

(c) Since you want to optimize the store’s profits under the stated stockout probability constraint, you are interested in experimenting with the inventory model parameters in the “neighborhood” of the parameter values that you found in step (b). Your aim is to find by trial and error the “best” (R, Q) values, considering the chosen management criteria 1) and 2). This search could be formalized, but now for simplicity just experiment with ten (10) parameter settings selected by yourself. For each (R, Q) pair settings, estimate and record the profits and the stockout probability by running your 52-week simulation repeatedly 10 times.

Record and briefly explain the observed tendency of your results:

“If R increases then profits seem to … and the probability of stockout events seem to …”

“If Q increases then profits seem to … and the probability of stockout events seem to …”

(d) For the best (R, Q) pair that you found in (c), create a numerical and graphical summary of your analysis. Specifically, based on a new set of 20 repeated 52-week simulation runs, record the resulting sequence of estimated yearly profits and estimated stockout probabilities. Next, estimate the mean and standard deviation of the yearly profits, and create a histogram of the profits.