Question

Let's say you are a business analyst for a firm that is introducing a new t-shirt into the market. Your boss asked you to estimate the profit for this new product. The calculation for profit is: Profit = [Units Sold * (Price - Unit Cost)] - Fixed Cost. However, we do not know how the market is going to receive your new product. You analyzed historical data, looked into industry reports, and came up with the following market scenarios in Excel. Market Scenario Low volume Medium volume High volume Average Probability 30% 40% 30% Units sold 50,000 70,000 90,000 70,000 Price $14 $12 $10 $12 Unit Cost: Your VP of production told you that they are not yet certain about the final production cost, but she says it's likely to be between $5 and $7. Fixed cost: Assume it to be $42,000. Scenario 1: Your boss comes and says, "I need a number for profit, and I need it in 15 minutes. Give me the number." What will you do? Just use the average numbers for every variable and suggest a solution. Include this part in your Worksheet 1. Name the worksheet "Problem1_simple". Now use appropriate probability distributions to simulate 1000 values of profit in Excel for the above problem. You should show the simulation in Worksheet 2. Call it "Problem1_simulation". Your Worksheet 3 (call it "Problem1_analysis") should contain answers to the following: Mean Profit Standard Deviation of Profit 95% Confidence Interval for Mean of Profit Probability of Loss Histogram of profits Your interpretation of the results. Specifically, your interpretation must clearly specify the risk involved in bringing this product to market.

Let's say you are a business analyst for a firm that is introducing a new t-shirt into the market. Your boss asked you to estimate the profit for this new product. The calculation for profit is: Profit = [Units Sold * (Price - Unit Cost)] - Fixed Cost. However, we do not know how the market is going to receive your new product. You analyzed historical data, looked into industry reports, and came up with the following market scenarios in Excel.

Market Scenario
Low volume
Medium volume
High volume
Average
Probability
30%
40%
30%
Units sold
50,000
70,000
90,000
70,000
Price
$14
$12
$10
$12
Unit Cost: Your VP of production told you that they are not yet certain about the final production cost, but she says it's likely to be between $5 and $7. Fixed cost: Assume it to be $42,000.

Scenario 1: Your boss comes and says, "I need a number for profit, and I need it in 15 minutes. Give me the number." What will you do? Just use the average numbers for every variable and suggest a solution. Include this part in your Worksheet 1. Name the worksheet "Problem1_simple".

Now use appropriate probability distributions to simulate 1000 values of profit in Excel for the above problem. You should show the simulation in Worksheet 2. Call it "Problem1_simulation".

Your Worksheet 3 (call it "Problem1_analysis") should contain answers to the following:

Mean Profit
Standard Deviation of Profit
95% Confidence Interval for Mean of Profit
Probability of Loss
Histogram of profits

Your interpretation of the results. Specifically, your interpretation must clearly specify the risk involved in bringing this product to market.

Added by Patricia D.