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: Daily demand Order cost Holding cost Vendor lead time Backorder cost Purchase cost 10 units/day $5/order $0.05/unit/day 4 days $10/unit/day $0.05/unit 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 immediately. 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 O with Q* units in inventory, run the simulation, and collect relevant output data. 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 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? 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 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: Daily demand Order cost Holding cost Vendor lead time Backorder cost Purchase cost 10 units/day $5/order $0.05/unit/day 4 days $10/unit/day $0.05/unit 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 immediately. 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 O with Q* units in inventory, run the simulation, and collect relevant output data. 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 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? 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