For your simulation assignment, you are going to mimic the process described in the three videos, but with different parameters.

1. First review the NORMAL distribution simulation video. Once you have understood this material, please open the Excel TEMPLATE provided and draw 20 demand values from the Normal distribution with mean = 110 and standard deviation = 20. Copy and paste (remember to paste "values") the twenty demand numbers into the appropriate column in the master Inventory Simulation worksheet.

2. Second review the VLOOKUP video. Once you have understood this video, simulate 20 months' production cost/unit and paste these 20 values in the master worksheet. Please note that in the Template, you will have to COMPLETE the lookup table by filling in appropriate ranges for the LOWER limit and UPPER limit before you can execute the VLOOKUP simulation (if you are not sure how to complete the lookup table, please schedule a brief conference with the instructor to obtain clarification). Remember to complete the VLOOKUP table before performing the simulation.

3. Finally, move to the master Inventory Simulation spreadsheet and complete the simulation with these parameters: selling price/unit = $130 and inventory cost for unsold inventory = $50/unit. The goal of the simulation exercise is to find the BEST value for the order up to quantity Q in cell $G$3 of the master sheet. After filling in the entire spreadsheet (exactly in the manner of the Inventory simulation worksheet and as illustrated in the Excel ILLUSTRATION spreadsheet posted), you are going to EXPERIMENT with various values for Q and make a recommendation for the BEST value of Q. As you change Q in $G$3, the PROFIT, the INVENTORY cost and total PRODUCTION cost should change. Make a separate record of these costs (perhaps 2 graph these costs for various values of Q) and make a recommendation for the final BEST value of Q, which maximizes PROFIT.

Notes on computing units produced each month:

In the first month you will always produce (or order up to) a quantity Q (i.e., assume that you have no inventory coming into the 20 month period). So cell E8 should always be equal to $G$3. In months 2,...20, the amount you order (or equivalently produce) will be = {Q - Unsold inventory from the PREVIOUS month}. Review the ILLUSTRATION to make sure you understand how units produced each month is computed. Once you compute units produced, the production cost = (units produced)*(random production cost/unit). The random production cost/unit is what is simulated using the VLOOKUP function.