Assignment:
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 revenuefrom 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.