Simulation Exercise Instructions

This document will help you complete your simulation exercise. Please note that this is a

TEAM assignment. Please check under the SIMULATION module to find the spreadsheet that

contains your team assignments.

Before beginning the assignment, you should read all the assigned pages from the simulation

chapter in the book $($chapter $12).$ In particular, you should review the Inventory Simulation

discussion. The simulation that you will be performing for this assignment is a slight variant of

the Inventory Simulation discussed in the book. $($you will consider random production

cost$/$unit in your assignment$).$ After reading the book, carefully review the three simulation

videos posted under the Simulation module$/$folder$.$ First review the NORMAL distribution

simulation video, then the VLOOKUP function video $($this is a video that helps you simulate a

DISCRETE probability distribution$)$ and finally the Inventory Simulation video.

For your simulation assignment, you are going to mimic the process described in the three

videos, but with different parameters.

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.

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.

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 $=$$$\frac{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