busi 3100

Case Study – Tablet Computer Profitability

A tablet computer manufacturer has three models in its product line:

The Mini model costs $375 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

1. Using selling prices and production costs, calculates per-unit gross margin (profit) dollars and percent of selling price. Mini Standard Pro Selling Price 499 699 899 Cost 375 390 435 Gross margin 124 309 464 Gross margin % 24.85% 44.21% 51.61% 2. Uses selling prices and units sold to calculate total revenue from each model and the total for all three models

Mini Standard Pro
Selling Price 499 699 899

Units Sold 60,000 30,000 15,000 Total Revenue

(each model) 29940000 20970000 13485000 Total Revenue (all 3 models) 64395000 3. Calculates total production costs from each model and the total for all three models

Mini Standard Pro
Cost 375 390 435
Units Sold 60,000 30,000 15,000

Total Cost

(for each model) 22

500000 11700000 6525000 Total Cost (for all models) 40725000 4. Calculates gross profit of each model and the total for all three models

Mini Standard Pro

Total Revenue 29940000 20970000 13485000
Total Cost 22500000 11700000 6525000
Gross Profit 7440000 9270000 6960000 Total Gross Profit 23670000 5. Calculates net profit of each model and the total for all three models by deducting advertising expenses

Mini Standard Pro
Gross Profit 7440000 9270000 6960000

Advertising Expenses 300000

500000

400000

Net Profit

7140000 8770000 6560000 Total net Profit 22470000 6. Displays the net profit of the three models in a chart which conveys the relative profit contribution of each model.

Net Profit

[VALUE]
[PERCENTAGE]
[VALUE]
[PERCENTAGE]
[VALUE]
[PERCENTAGE]
Mini Standard Pro 7140000 8770000 6560000