Case Study – Tablet Computer Profitability
A tablet computer manufacturer has three models in its product line:
The Mini model costs 5 to produce and sells for $499. 60,000 were sold.
The Standard model costs $390 to produce and sells for $699. 30,000 were sold.
The Pro model costs $435 to produce and sells for $899. 15,000 were sold.
The company plans to spend $300,000 to advertise the Mini, $500,000 to advertise the Standard, and $400,000 to advertise the Pro. These costs will be incurred regardless of how many units are sold.
Using Excel, put the above information into a spreadsheet, and develop a financial model which does the following:
Using selling prices and production costs, calculates per-unit gross margin (profit) dollars and percent of selling price.
Uses selling prices and units sold to calculate total revenue from each model and the total for all three models
Calculates total production costs from each model and the total for all three models
Calculates gross profit of each model and the total for all three models
Calculates net profit of each model and the total for all three models by deducting advertising expenses
Displays the net profit of the three models in a chart which conveys the relative profit contribution of each model.
Sheet1
Mini Standard Pro
Selling Price 499 699 899
(each model)
Mini Standard Pro
Cost 375 390 435
Units Sold 60,000 30,000 15,000
(for each model)
Mini Standard Pro
Mini Standard Pro
Gross Profit 7440000 9270000 6960000
500000
Net Profit
[VALUE]
[PERCENTAGE]
[VALUE]
[PERCENTAGE]
[VALUE]
[PERCENTAGE]
Mini Standard Pro 7140000 8770000 6560000