Question

Stahlmaere Inc. is a start-up company that manufactures simple machines. It is interested in analyzing the profit from a new machine using Monte Carlo simulation. It wants to investigate the profit resulting from a selling price of $150 per unit. The setup and advertising costs are known to total $75,000. They assume that the demand for the product is normally distributed with a mean of 1500 units and a standard deviation of 100 units. The company estimates that the raw material cost per unit is uniformly distributed between $5 and $6. The labor cost per unit is assumed to follow a discrete uniform distribution from $12 to $16. A junior analyst has devised the following Excel spreadsheet that simulates a single scenario using the information given above: Selling price per unit = 150 Set up and advertising cost = 75000 Demand = =NORM.INV(RAND(),1500,100) Raw material cost per unit = =5+(6-5)*RAND() Labor cost per unit = =RANDBETWEEN(12,16) Profit = =(B1*B4)-B2-((B5+B6)*B4) Copy-and-paste the above information into cells A1:B8 of an Excel spreadsheet. Then use a data table to repeat the simulation 1000 times. From the simulation results, estimate Stahlmaere's expected mean profit. Understanding that simulation is random in nature and that your estimate is unlikely to match any of the answer choices exactly, choose the answer choice that is closest to the estimated mean profit.

A. $180,000
B. $50,000
C. $150,000
D. $90,000
E. $120,000

Answer 1

Answer:

$ 120,000

Explanation:

Formulas:

Cell        Formula

B4          =NORMINV(RAND(),1500,100)

B5          =5+(6-5)*RAND()

B6          =RANDBETWEEN(12,16)

B8          =(B1*B4)-B2-((B5+B6)*B4)

B12         =AVERAGE(F3:F1002)

Enter formula = B8 in cell E2

and =RANDBETWEEN(12,16) in E3 copy down to E1002 (this represents labor cost)

To create the data table, select range E2:F1002

click Data tab > What-If Analysis in Data Tools group > Data Table > In the resulting dialogue box, enter B6 in the Column Input cell, and B1 in the Row Input cell.

Estimated mean profit = $ 121,445 this is closest to $ 120,000

THE ANSWER IS $ 120,000