You are hired as a consultant at a distribution center (DC) which supplies several retailers in Miami with I-Absorb baby diapers. All retailers issue their orders to the distribution center at the beginning of each week. Based on past sales it is known that the total weekly order from all retailers is distributed normally with a mean of 1500 packages and a standard deviation of 450. The distribution is steady throughout the year and no seasonality exists. All diaper packages are same in size. It is noted that, the aforementioned demand distribution is not itemized for different sizes of the diapers as the ratio of demand for each diaper size to the overall demand seems to stay constant. Hence, we only concentrate on the overall demand. At any time if a demand cannot be filled by the distribution center, it is transferred as a backorder to the next week. A backordered item (package) costs $2.00 a week. On the other hand carrying 1 unit of item as a stock to the next week costs $1.50. At the distribution center demand is reviewed every week and orders are issued to the manufacturer if necessary. The fixed order-cost is too little and hence is always ignored. Once an order is issued it takes 4 weeks for it to arrive at the DC. One of the tasks that you need to carry out is to determine the optimal replenishment policy for the DC. The managers in the DC are not sure how much to order each week when they order from their supplier (manufacturer). They want you to develop a cost-efficient replenishment policy for them. Being a brilliant IE, you think that a base-stock policy could be the answer. However, you still need to suggest a way that helps the center choosing replenishment amounts each week. Under base-stock policy, the DC orders a sufficient amount to raise its inventory position plus outstanding orders to the so called base-stock level, say, s. Inventory position (IP) is given by the current stocks plus in-transit inventory. Thus, every week DC needs to order s minus the current inventory position plus the amount of backorders (B) so as to raise its inventory position to s. That is, every week, the order quantity is s - IP + B Hence what remains is to compute the optimal value for the base-stock level, s. To have a better understanding of the base-stock policy consider the example in the following table where the base-stock level is 4000 and the lead time is fixed at 4 weeks. Suppose we are at the beginning of week 6. Observe that in week 2, we ordered 968 packages from the manufacturer. Since the lead time is 4 days, it arrives and gets into our stocks at the beginning of week 6. Also note that the ending inventory of the previous week is 270. Hence, our current stocks are equal to 968+270=1,238 as of Week 6. Week 6 demand is 1,496. Since we only have 1,238, we will have a backorder of 258 units. Now, our current stocks will be 0. Notice that we have replenishments on their way. Specifically, our replenishment orders from weeks 3, 4, and 5 are still to arrive. The total of these are 1102 +789+871=2762 (check values under Order Quantity for weeks 3 thru 5). 2 Consequently, our inventory position is our current stocks, which is 0, plus our in-transit orders, which is 2762. Then, the order quantity for week 6 is s IP B 4000 2762 258 1, 496 You are expected to build a simulation model to optimize the base-stock level using MS Excel and VBA. To complete the task, answer the questions in part A thru E. A. Determine the optimal base-stock level through simulation. To achieve this you need to (1) create a Simulation Table including 52 weeks of simulated data on an Excel Sheet (assume you have 4000 packages in stock at the beginning), (2) build a macro using VBA to automate the simulation replications. Your simulation model should replicate the Monte Carlo observations as many times as needed for reliable outcome. (3) The macro should implement the whole process of simulation for various values of base-stock values for comparison. Total cost is the summation of holding costs and backorder costs. Present your suggestions. B. You may recall from Production and Inventory Control Course that in fact the basestock level that minimizes the expected cost in the foregoing problem can be computed analytically. Let h and b denote the unit holding and backorder costs per week respectively. Also let L be the average demand during the lead-time period, L. With Normal distribution, average demand over lead-time - which is 4 weeks in our case - is 4*1500=6000 (Also note from statistics, the standard deviation over the lead time is SQRT(4*4502 )=900 ). The density and cumulative distribution function of the demand over lead-time (4 weeks) is denoted by fL(x) and FL(x) respectively. Then, the expected cost function can be written as follows: 0 [ ] ( ) () ( ) () s LL L s E C h h s x f x dx b x s f x dx Since the cost function is strictly convex, the first order optimality conditions can be used to optimize the expected costs. Taking first order derivative with respect to s and setting it equal to 0 we can find that for optimal s* the following equality must hold; Week Beginning Inventory Random Digits Demand Ending Inventory Backorders Order Quantity Holding Cost 1 4000 0.29 781 3219 0 781 2 3,219 0.47 968 2251 0 968 $3,219.00 3 2,251 0.60 1,102 1149 0 1,102 $2,251.00 4 1,149 0.30 789 360 0 789 $1,149.00 5 1,141 0.37 871 270 0 871 $360.00 6 1,238 0.89 1,496 0 258 1,496 $270.00 7 1,102 0.14 825 277 0 567 8 1,066 0.80 1,340 0 274 1,340 $277.00 9 871 0.89 1,757 0 886 1,483 10 1,496 0.13 1,428 68 0 5423 ( *) L b F s h b Note that we can easily calculate the right hand side of the equation. Using NormInv() function in Excel, determine the theoretically optimal base-stock value and compare both solutions. Is the optimal base-stock value suggested by your optimizer (simulation model) equal to this value? If not how and why do you think it is different from the analytical solution? C. Now consider the case where rather than minimizing inventory related costs, you would like to maximize your profits. Suppose that you pay $3 to the manufacturer for each package of diapers and sell them for $7 a package. Determine the optimal basestock value that maximizes your net gains after purchasing, holding, and backorder costs. Has the optimal base-stock value changed? Now try $12 for the selling price. Has the optimal base-stock level changed again or is it still the same value? Explain the intuition and/or logic behind the change (or no-change). D. Repeat your analysis for Part A now assuming that the lead time is uniformly distributed between 1 and 7. What is the optimal base stock value now? Explain the difference, if there is any. E. Repeat your analysis for Part A now assuming that all missed sales are lost and no backlogging is allowed. How do the optimal cost and optimal base stock value change under this policy? Also how would your answer to Part C change? You are asked to carry out the foregoing analysis within a single MS Excel file and submit a soft copy. Create a front worksheet where you present your report and your answers to all questions. Your assignment will not be graded without the front worksheet. Your report should include detailed discussions of your results and their interpretations. Also include in the report your assumptions that you use in your study whenever necessary in your Excel file. Include a separate worksheet for each question. Submit your files through your group's file exchange folder on the Blackboard.