Case Study

Tablet Computer ProfitabilityA 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.

Kindly display a screenshot of all tables with formulas.