Monte Carlo Simulation to Get an Estimate

¶ … Monte Carlo simulation to get an estimate of the total production cost that can be expected for the company. The key to understanding this simulation is that there are two sets of information -- the probabilities and the randomly-generated number. The random number is between 1 and 100, so there is the same amount of numbers as there are probabilities. The probabilities need to be broken down on the spreadsheet to match up with a cost. So for the materials costs, there is an 18% probability that the materials costs will be $33. Thus, any random number between 1 and 18 will reflect a materials cost of $33. There is a 23% probability that the materials cost will be $34. Thus, numbers 19-41 inclusive will mean that the materials cost will be $34 on the spreadsheet. The formula for materials costs would therefore be:

=IF ($C6

This process is repeated for all of the different costs and their probabilities. The costs are then averaged out to provide an average cost of production that the company can expect, generated with 30 Monte Carlo simulations. Appendix A contains the completed spreadsheet for the expected average cost of production.

If the company wants to realize at markup of $20 for each unit sold, then the company needs to set the price at least $20 higher than the expected average cost of production. The expected average cost of production is $65.60, so the price that the firm must charge to give itself at $20 markup is at least $85.60.

Task 2

A. For nutrient, we will use N, for flavor we will use F, for color we will use C. To represent the variables. Y represents the number of cases of Y and X represents the number of cases of X in a given month.

For nutrient, the equation would be N = 4X + 4Y

For flavor, the equation would be F = 12X + 6Y

For color, the equation would be C = 6X + 15Y

Each equation has a maximum, those being N = 30; F = 72, C = 90. These can be plugged into any equation to set a maximum constraint. So to set the maximum for N, the equation would be: 30 = 4X + 4Y, where any combination of X and Y cannot result in a figure over 30. You would also have 72 = 12X + 6Y for F; and 90 = 6X + 15Y for C. All three are maximum constraints, as the limit on downside production is zero for all three additives, should the company choose not to produce any cases of food.

B. The purple objective function has a production level of 6 cases of Brand X or 8 cases of Brand Y. The contribution from this level would be:

6(40) or 8(30) = Contribution

Contribution = $240

C. In order to determine the production level that maximizes the profit while laying within the constraints, first the production constraints must be understood. For nutrients, the maximum level of production for either X or Y will be the same:

30 = 4N

N = 7.5 cases

For flavor, the maximum production of X can be:

72 = 12F

F = 6 cases

For color, the maximum production of X can be:

90 = 6C

C = 15 cases

The Y constraints are as follows:

30 = 4N

N = 7.5 cases

72 = 6F

F = 12 cases

90 = 15C

C = 6 cases

Since X is the more profitable product, producing only X would result in the production of 6 cases, the contribution would be $240, but there would be a considerable amount of unused capacity.

According to Graph 1, the point at which profit is maximized would be where the flavor and nutrient lines meet, with both lying beneath the color line. At this point, the production level is 4X and 3.5Y. The usage of N. would be 30 at this level, the usage of F. would be 72 and the usage of C. would be 72. The total contribution at this level would be $270. See Appendix B for the spreadsheet solution.

D. The total contribution at this level would be:

4.5(40) + (3)(30)

180 + 90 = $270

Task 3.

A. The economic order quantity model seeks to determine the size of order that would allow the firm to minimize its total ordering and holding costs. The EOQ formula is as follows:

source: NSCU.edu

In this formula, the variables are as follows:

A = Demand for the year

Cp = Cost of placing a single order

Ch = Cost to hold the inventory for a single year.

All of these values are given.

A=18,000

Cp = $38

Ch = 26%

First, we calculate the numerator: 2*18,000*38 = 1,368,000

Then we divide by (.26)*12 = 3.12

Then we take the square root of that number in order to yield the EOQ: 662.16

This can be rounded to 662 or 663, since a partial unit cannot be ordered.

B. The economic production lot size model helps a company to determine the optimal level of production to reduce the costs related to both production set-up and holding inventory. The formula for the economic production lot size model is:

source: Wikipedia

where K = setup cost

D = demand rate

F = holding cost

X = demand rate / production rate

We know that D = 15,000; F = 28% and K = $84

X will be 15,000 / 60,000 = .25

First, we solve the numerator: 2*84*15000 = 2,520,000

Then we can solve the denominator = (0.28)(1-.25) = 0.21*19

So we have 2,520,000 / 3.99 = 631,578.94

And then we take the square root of this to derive the EPQ = 794.72, which can be rounded down to 795 units per run.

Task 4. A. 1. PERT assumes a beta probability distribution for time estimates (NetMBA.com, 2010). This means that the probabilities are 1/6 optimistic, 2/3 probable and 1/6 pessimistic. So for Activity A, the formula would be:

((2)+(4*3)+(4))/6 = Expected time to complete

Expected time to complete = 3

The formula for variance is: [ ( Pessimistic - Optimistic ) / 6 ]2

So the variance for Activity A would be [(4-2)/6] 2 = 0.111

The table 1.1 would look as follows, given this information:

PERT/CPM Analysis

Task Detail Table 1.1

Task

Preceding Activity

Optimistic Time to Complete

(weeks)

Probable Time to Complete

(weeks)

Pessimistic Time to Complete

(weeks)

Expected Time to Complete (weeks)

Variance

(weeks)

START

A

START

2

3

4

3

0.11

B

START

5

6

13

7

1.78

C

A

3

4

8

4.5

0.69

D

B

10

11

15

11.5

0.69

E

C

4

5

6

5

0.11

F

B

8

10

12

10

0.44

G

F

4

6

11

6.5

1.36

H

D, E

8

10

18

11

2.78

I

G

3

6

12

6.5

2.25

J

H, I

2

3

7

3.5

0.69

END

A. 2. Drawing a PERT chart is not possible with normal word-processing software. A PERT chart sketched on paper or a GANTT chart produced in Excel much more efficient methods of finding the critical path. Based on the estimated times to complete each activity, the project is going to take 33.5 weeks to complete. The critical path is B-F-G-I-J, which takes 33.5 weeks.

3.a. The total length of the project is 33.5 weeks. Following the critical path we have:

B = 7 + F = 10 + G = 6.5 + I = 6.5 + J =3.5 = 33.5

3.b. The slack for project Task A is 6.5 weeks. The slack enters the critical path upon completion of Task E. There are six weeks between the estimated completion time of Task E. And the point at which the next dependent task, Task H, can begin. This is because Task H. also requires completion of Task D. Tasks B. And D. take a combined 18.5 weeks. A-C-E takes 12.5 weeks. These lead to Task H, which itself has the extra 0.5 weeks of slack.

3.c. The slack for project Task H. is 0.5 weeks. Task H. finishes after 29.5 weeks, 0.5 weeks less than the completion of Task I, which is 30 weeks.

3d. Task F. is currently scheduled to start at week 8.

3e. Task I is scheduled to finish after week 30.

4. The task is slated to be completed in 33.5 weeks, just under the 34-week threshold. This is based on the estimates from the CPM tables. If any individual task went over the expected time and reached the pessimistic time, then the project would go over 34 weeks. The percentage allotted to the pessimistic outcome was 16.7%, meaning that the odds of completing this project on time are: 1-16.7% = 83.3%

B. In order to complete this project in 30 weeks, some activities will need to be crashed. The maximum reduction in time, if all activities are crashed, would be 23.5 weeks for completion, meaning: 33.5 -- 23.5 = 10 weeks if everything is crashed. Obviously, this will not be necessary.

C. The project will need to be crashed 3.5 weeks. The most cost effective means is to crash along the critical path first, starting with the cheapest options. Task F. is the cheapest, and can be cut 3.5 weeks. This will cost (3.5)(1000) = $3,500. Task D. will need to be reduced 3 weeks as well. This will cost (3) (1778) = $5,334.

The total cost of the crash will be $3,500 + $5,334 = $8,834.

Task 5.

A. For each decision branch, the expected values are the weighted averages of the predicted gains of each option. So for the first branch, Develop New Product ? Develop Thoroughly, the expected outcome would be: