The management of Madeira Manufacturing Company is considering the introduction

Question

The management of Madeira Manufacturing Company is considering the introduction of a new product. The fixed cost to begin the production of the product is $35,000. The variable cost for the product is uniformly distributed between $17 and $23 per unit. The product will sell for $55 per unit. Demand for the product is best described by a normal probability distribution with a mean of 1,300 units and a standard deviation of 300 units. Develop an Excel worksheetsimulation for this problem. Use 500 simulation trials to answer the following questions:

What is the mean profit for the simulation? Round your answer to the nearest dollar.

Mean profit = $   

What is the probability that the project will result in a loss? Recalculate the numerical value of probability in percent and then round your answer to the nearest whole number.

Probability of Loss =  %

What is your recommendation concerning the introduction of the product?

Explanation / Answer

Procedure of simulation in excel:

Step 1) generate random sample from normal distribution by using the following command

Data>>>Data Analysis>>>Random Number Generation

Number of variable : 1

Number of random Numbers : 500

Distribution: Normal (click on down arrow and select Normal

Mean = 1300

Standard deviation = 300

Then select output range (for example " $A$2"

Then click on OK

so we get 500 random numbers from normal distribution with mean 1300 and standard deviation 300

In the next column multiply columns of random numbers by 55 and drag the 2nd column then we get profit column.

Step 2) now we need to select samples from uniform distribution for finding cost per unit

generate random sample from uniform distribution by using the following command

Data>>>Data Analysis>>>Random Number Generation

Number of variable : 1

Number of random Numbers : 500

Distribution: Uniform(click on down arrow and select Normal

Between 17 and 23

Output range $C$2

Then click on OK

So we get cost per unit

Multiply column of random sample from normal to the random number from uniform the resulting column is nothing

but cost of the units.

Take the average of 2nd and 4th column which are the mean profit and mean cost on the products

Add the initial cost of production (35000) in the average cost and then subtract it from average profit then we get

net profit.

When I do the above process of simulation then I get

Average profit = 72350.77 - (26205.76 + 35000) = $11145.01

Next for this question we want to find probability of loss

Amount of profit = 1300*58 - (20*1300+35000) = 14400

Where 1300 is the mean of unit produced

20 is the mean of cost per unit so total cost = 20*1300+35000 = 61000

So that if profit is less than 61000 then there is loss

mean profit = 1300* 58 = 75400

standard deviation of profit =300*58 = 17400

Probability of loss = P(X < 61000) = "=NORMDIST(61000,75400,17400,1)" excel command

So we get Probability of loss = P(X < 61000) = 0.2040

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.