A tablet computer manufacturer has three models in its product line:
• The Mini model costs $275 to produce and sells for $499. 50,000 were sold.
• The Standard model costs $350 to produce and sells for $699. 25,000 were sold.
• The Pro model costs $425 to produce and sells for $899.10,000 were sold.
The company plans to spend $500,000 to advertise the Mini, $600,000 to advertise the Standard, and $750,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.
Sample Solution