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. 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.

EXCEL SHEET HE HAS GIVEN US __

Probability

LOWER_LIMIT

UPPER_LIMIT

Production

Cost/Unit

0.3

0

0.3

42

42

0.2

0.3

0.5

45

0.2

0.5

0.7

49

0.15

0.7

0.85

55

0.15

0.85

1

60

Month

Random

Number

Unit

cost

1

0.229259008

42

2

0.964035358

60

3

0.957761446

60

4

0.153961281

42

5

0.064795196

42

6

0.810524553

55

7

0.15632173

42

8

0.110170957

42

9

0.493723361

45

10

0.543055377

49

11

0.271240947

42

12

0.664160286

49

13

0.560775195

49

14

0.469406057

45

15

0.052825681

42

16

0.708002469

55

17

0.563881404

49

18

0.785411008

55

19

0.635432234

49

20

0.655950005

49

Mean

for

Normal

Distribution

125

Standard

Deviation

for

Normal

30

Month

Random

Number

Demand

Integer

Demand

1

0.896697014

162.8886

162

2

0.104361983

87.28754

87

3

0.206948007

100.4883

100

4

0.339949471

112.622

112

5

0.322081205

111.1434

111

6

0.844330509

155.3725

155

7

0.07980738

82.80895

82

8

0.524897658

126.8735

126

9

0.145606185

93.33603

93

10

0.041869511

73.11825

73

11

0.183382569

97.92352

97

12

0.489325112

124.1972

124

13

0.416939803

118.7081

118

14

0.087675062

84.34365

84

15

0.494868452

124.6141

124

16

0.202807926

100.051

100

17

0.661685731

137.512

137

18

0.018655983

62.52998

62

19

0.441823281

120.6096

120

20

0.409719426

118.152

118

SIMULATING

AN

INVENTORY

POLICY

INVENTORY

ORDER

UPTO

LEVEL

Q

125

Q

Inventory

at

start

of

Month

1

SELLING

PRICE/UNIT

120

INVENTORY

COST

FOR

UNSOLD

INVENTORY

25

MONTH

#

Simulated

Demand

Units

Sold

=

Min(D,

Q)

UNSOLD

Inventory

@

Month

end

Amount

produced

this

month

Simulated

cost/unit

Total

production

cost

Inventory

cost

Total

Revenue

PROFIT

ALL

Demand

Met?

(1

=

Yes,

0

=

No)

1

162

125

0

125

42

5250

0

15000

9750

0

2

87

87

38

125

60

7500

950

10440

1990

1

3

100

100

25

87

60

5220

625

12000

6155

1

4

112

112

13

100

42

4200

325

13440

8915

1

5

111

111

14

112

42

4704

350

13320

8266

1

6

155

125

0

111

55

6105

0

15000

8895

0

7

82

82

43

125

42

5250

1075

9840

3515

1

8

126

125

0

82

42

3444

0

15000

11556

0

9

93

93

32

125

45

5625

800

11160

4735

1

10

73

73

52

93

49

4557

1300

8760

2903

1

11

97

97

28

73

42

3066

700

11640

7874

1

12

124

124

1

97

49

4753

25

14880

10102

1

13

118

118

7

124

49

6076

175

14160

7909

1

14

84

84

41

118

45

5310

1025

10080

3745

1

15

124

124

1

84

42

3528

25

14880

11327

1

16

100

100

25

124

55

6820

625

12000

4555

1

17

137

125

0

100

49

4900

0

15000

10100

0

18

62

62

63

125

55

6875

1575

7440

-1010

1

19

120

120

5

62

49

3038

125

14400

11237

1

20

118

118