Question

Jianli Hu, regional manager of Drones, Drones, Drones, wishes to have a model to evaluate the profit impact of various pricing strategies for the firm’s newest product, the Drone3000. Monthly fixed costs for the Drone3000 are $60,000. The variable cost per unit is $50,000. Jianli’s marketing manger believes that the following monthly demand curve represents the product: D = 8,000 – 40P, where D is the monthly demand, and Pis the sales price.

a) Set up an Excel model, similar to Bill Pritchett’s clock repair shop, to compute monthly profit. Here the sales price will be the decision variable, and quantity produced will be a function of sales price. (Designate the quantity as one of the “Results.”)

b) What is the monthly profit for a sales price of $100?

c) Try a few different values for the price. Does it appear that the current price of $100 is too high or too low?

d) Use Goal Seek to find the price that would lead to a monthly profit of $161,000.

Answer 1

a. Demand D = 8000 - 40P

Monthly fixed costs	60000
Variable cost per unit	50

Variable cost per unit of 50000 in the question seems to be a typo. Have changed it to 50

The setup in excel is as shown:

A B C E F. 1 2 3 4 Demand D = 8000 - 40P D =8000-40*E3 4000 Р 100 5 60000 6 50 Monthly fixed costs Variable cost per unit Total cost = =C5+C6*D3 Profit = D3*F3 - C7 7 260000 140000 8 9

price P is a decision variable which is kept at 100

Demand D is the result variable = 8000 - 40*P

Monthly fixed costs and variable cost per unit are give

Hence total cost = fixed cost + D*variable cost per unit

Profit = Demand * Price - total fixed costs

b) For sales price of 100 as shown above,

the demand D = 8000 - 40*100 = 4000

hence total costs = 60000 + 4000*50 = 260000

Profit = 4000*100 - 260000 = 400000 - 260000 = 140000

c) Let us calculate the value of profit at price of 50 and 75 as well:

A B D E F 1 2 Demand D = 8000 - 40P D =8000-40*E3 3 6000 Р 50 4 5 60000 6 7 Monthly fixed costs Variable cost per unit Total cost = =C5+C6*D3 Profit = D3*F3 - C7 50 360000 8 -60000 9

A B C D E F. 1 2 Demand D = 8000 - 40P D =8000-40*E3 3 5000 P. 75 4 5 6 Monthly fixed costs Variable cost per unit Total cost = =C5+C6*D3 Profit = D3*F3 - C7 60000 50 310000 65000 7 8 9

So profit for P = 50 is -60000

for P = 75 is 65000

We can find the profit margin for the three cases and see how we are faring:

For P=50 obviously there is a loss so no point in calculating

For P = 75, Profit margin = profit/revenue = 65000/(5000*75) = 17.33%

For P = 100, profit margin = 140000/(4000*100) = 35%

Hence the profit margin for P = 100 seems to double that of margin at P = 75. Hence price P = 100 seems to be fine and not too high

d. Goal seek to find price for monthly profit of 161000:

A B C D E F H. 1 2 Demand D = 8000 - 40P D =8000-40*E3 3 4000 Р 100 4 Goal Seek ? X 5 60000 $C$8 6 1 Set cell: 50 Monthly fixed costs Variable cost per unit Total cost = =C5+C6*D3 Profit = D3*F3 - C7 To value: 161000 7 8 260000 140000 By changing cell: $F$3| 9 OK Cancel 10

A B с D E F. 1 2 Demand D = 8000 - 40P D =8000-40*E3 3 3,400 P 115 4 5 6 7 Monthly fixed costs Variable cost per unit Total cost = =C5+C6*D3 Profit = D3*F3 - C7 60000 50 230,000 161,000 8

Hence at P = 115, we get profit = 161000