IE 3302: Production & Inventory Control Fall 2020

For this assignment, you will develop an Excel-based simulation tool using VBA macros to explore the effect of demand variability on system performance. There are two components to this project: 1) the simulation tool and 2) a report that describes experimentation and results. This project will be worth 16% of your overall course grade, with the simulation tool and the report each contributing 8%, respectively.

Part 1: Simulation Tool

This tool will simulate daily demand, demand fulfillment, and inventory replenishment of a single item for 365 simulated days using the EOQ method of inventory control. Assume the following parameter values as inputs to your tool:

The economic order quantity (EOQ) method of inventory control assumes that demand occurs continuously. For this assignment, you will approximate continuous demand by assuming a daily time- step, in which the following events occur in each simulated day:

• Start of the day: Any replenishment orders that are due from the vendor will arrive at the start of the day and will be added to available inventory. This inventory is assumed to be available to fill orders on the same day that it arrives. If there are backorders from the previous day (i.e., negative inventory), those orders will be filled
• End of the day: Available inventory will be drawn down by the number of units demanded during the day. You will then check to see if a replenishment order needs to be placed; if so, the order is placed with the vendor, and the vendor’s lead time clock starts running (i.e., at the start of the next day, the lead time will be reduced by 1 day).

The tool will be used to simulate three different scenarios, each of which must be run for 10 replications of 365 simulated days. For all three scenarios, you will capture (at a minimum):

• One state variable – current inventory level – at the start and end of each simulated day
• Two system performance metrics – cost and service level – at the end of each 365-day simulation replication

The three scenarios are as follows:

1. Start by assuming that there is no demand variability (i.e., the system is deterministic). Determine the optimal order quantity (Q*) and reorder point (r*) for this system, and use these values to control inventory replenishment. Assuming that you start at time 0 with Q* units in inventory, run the simulation, and collect relevant output
2. Now, assume that daily demand exhibits randomness (i.e., the system is stochastic) and follows a normal distribution. Again, use Q* and r* to control inventory replenishment. Assuming that you start at time 0 with Q* units in inventory, run the simulation for the following three daily demand distributions (mean, standard deviation):
   • Low demand variability: N(10, 2)
   • Medium demand variability: N(10, 5)
   • High demand variability: N(10, 10)

3. Finally, assume that you can buffer against demand variability using safety stock. Assume you start at time 0 with Q* + ss units in inventory, and run the simulation for the three demand distributions described above (i.e., low, medium, high). Experiment with different levels of safety stock: What level of safety stock gives you the lowest total cost and/or service level on average (over 10 replications)? How does safety stock impact system performance across each of the 10 simulation replications?

IE 3302: Production & Inventory Control Fall 2020

Simulation Tool Guidelines

For full credit, your simulation tool should:

• Be programmed using VBA macros in Excel
• Produce correct results for all specified scenarios
• Be easy to use and understand

Part 2: Report

Your mini-project report must be no more than 5 pages long (single-spaced, font size = 12, 1-inch margins), including figures/tables. You do not need to include a title page. The report should contain the following four sections:

1. Introduction & Motivation
   • Describe the issue/question that you are investigating with the simulation tool that you created
   • Briefly describe the approach that you used to answer this question (i.e., stochastic simulation)
   • Explain why this approach is useful for answering the question of interest

2. Experimentation and Results
   • Present and explain your results for the three experimental scenarios described above (no demand variability; three levels of demand variability but no safety stock; three levels of demand variability with safety stock)
     • It is a good idea to use summary statistics and figures to present your results
     • Keep in mind that random inputs into the simulation model will produce random outputs. Therefore, you will need to account for this randomness when you present your results. For example, use error bars, standard deviations,

3. Discussion of Results
   • Provide a discussion of the results that you presented in the previous section. Be sure to compare results from all three scenarios. How does demand variability impact system performance? How well does the EOQ method work when demand is variable? As industrial engineers, how can we manage demand variability and its impacts?

4. Conclusion

• What are the main takeaways from this study?
• What other experiments could/should be undertaken to explore this issue?
• How might the results of this study be useful for a real-world production system? Specifically, who might benefit from this tool, and how?

DETAILED ASSIGNMENT

20200916175735mrj_assigment