A profit maximizing company is reviewing the market demand and profit contributions from one of its products. The company economist has suggested the following revenue and cost structures:
P=1500 ' 5q
TC = q / 3 ' 53 q2 + 3000 q + 500
Where p = unit price
Q = quantity per year
TC is Total Cost
Develop an Excel Sheet spreadsheet model to model this revenue / Cost Structure and hence determine the following characteristics of the profit maximizing position : -
i. Annual quantity ( if unsure, round to the nearest 5 units)
ii. Unit Price
iii. Total annual revenue and total cost
iv. Annual Contribution this product makes towards profit
Answer:
Q = (1500 ' P) / 5
П = P(Q)*Q-TC(Q)
= (1500- 5Q) Q ' (q3 / 3 ' 53 q2 + 3000 q + 500)
= 1500 q ' 5 q2 - q3 / 3 + 53 q2 - 3000 q ' 500
= - q3 / 3 + 48 q2 ' 1500 q ' 500
d П / dQ = - 3q2 + 96 q ' 1500
= q2 - 32 q + 500
The company’s economist has suggested that this product could be selectively priced by using “budget” and “premium” pricing based on the following demand structures to improve its contribution to company profits “
Sub-sector 1 : q1 = 155- ( 2/25) p1
Sub-sector 2 : q2 = 145- (3/25) p2
Develop your spreadsheet model to deal with this segmented market and briefly explain how Excel tool SOLVER can be used to determine the profit maximizing position and hence determine the following for this optimum condition:
i. Unit price and annual quantity sold in each market segment under price discrimination
ii. Annual revenue generated by the segmented market
iii. Increase in profits resulting from price discrimination
iv. If the market segmentation involves incremental annual fixed costs of £ 7000, would you recommend implementation of the segmented pricing? Give your reasons.