Excel Assignment Blackboard
andcampodonqlr
NP_EX16_10a_AndresCampodonico_11.xlsxDocumentation
| New Perspectives Excel 2016 | Module 10: SAM Project 1a | |
| Hurdy Headphones | |
| WHAT-IF ANALYSES AND SCENARIOS | |
| Author: | Andres Campodonico |
| Note: Do not edit this sheet. If your name does not appear in cell B6, please download a new copy of the file from the SAM website. | |
Wireless
| Hurdy Headphones - Wireless Models | |||||||||
| Sales | Wireless Headphones - Profit Analysis | ||||||||
| Units Sold | 25,000 | Units Sold | Total Sales | Total Expenses | Gross Profit | ||||
| Price Per Unit | $ 179.00 | ||||||||
| Total Sales | $ 4,475,000.00 | 15,000 | |||||||
| 20,000 | |||||||||
| Expenses | 25,000 | ||||||||
| Variable Expenses | 30,000 | ||||||||
| Units Manufactured | 25,000 | 35,000 | |||||||
| Variable Cost per Unit | $ 150.00 | ||||||||
| Total Variable Costs | $ 3,750,000.00 | ||||||||
| Fixed Expenses | Wireless Headphones - Gross Profit Analysis | ||||||||
| Total Fixed Cost | $ 260,000.00 | $ 465,000 | $ 149.00 | $ 159.00 | $ 169.00 | $ 179.00 | $ 189.00 | $ 199.00 | $ 209.00 |
| Expenses - Total | $ 4,010,000.00 | 15,000 | |||||||
| Total Expense Per Unit Sold | $ 160.40 | 20,000 | |||||||
| 25,000 | |||||||||
| Profit | 30,000 | ||||||||
| Total Sales | $ 4,475,000.00 | 35,000 | |||||||
| Total Expenses | $ 4,010,000.00 | ||||||||
| Gross Profit | $ 465,000.00 | ||||||||
| Break-even Analysis | |||||||||
| Price per Unit | $ 179.00 | ||||||||
| Cost Per Unit | $ 160.40 | ||||||||
| Units Sold | 25,000 | ||||||||
| Gross Profit | $ 465,000.00 |
Noise-Cancelling
| Hurdy Headphones - Noise-Cancelling Models | |||||
| Sales | Noise-Cancelling - Break-Even Analysis | ||||
| Units Sold | 10,500 | Units Sold | Total Sales | Total Expenses | Gross Profit |
| Price Per Unit | $ 209.00 | 10,500 | 2,194,500 | 2,139,500 | 55,000 |
| Total Sales | $ 2,194,500.00 | 8,000 | 1,672,000 | 1,692,000 | (20,000) |
| 8,500 | 1,776,500 | 1,781,500 | (5,000) | ||
| Expenses | 9,000 | 1,881,000 | 1,871,000 | 10,000 | |
| Variable Expenses | 9,500 | 1,985,500 | 1,960,500 | 25,000 | |
| Units Manufactured | 10,500 | 10,000 | 2,090,000 | 2,050,000 | 40,000 |
| Variable Cost per Unit | $ 179.00 | 10,500 | 2,194,500 | 2,139,500 | 55,000 |
| Total Variable Costs | $ 1,879,500.00 | 11,000 | 2,299,000 | 2,229,000 | 70,000 |
| Fixed Costs | 11,500 | 2,403,500 | 2,318,500 | 85,000 | |
| Total Fixed Cost | $ 260,000.00 | 12,000 | 2,508,000 | 2,408,000 | 100,000 |
| Expenses - Total | $ 2,139,500.00 | ||||
| Total Expense Per Unit Sold | $ 203.76 | ||||
| Profit | |||||
| Total Sales | $ 2,194,500.00 | ||||
| Total Expenses | $ 2,139,500.00 | ||||
| Gross Profit | $ 55,000.00 | ||||
| Break-even Analysis | |||||
| Price per Unit | $ 209.00 | ||||
| Cost Per Unit | $ 203.76 | ||||
| Units Sold | 10,500 | ||||
| Gross Profit | $ 55,000.00 |
Earphones
| Hurdy Headphones - Personalized Earphones | |||||||
| Sales | Personalized Earphones - Net Income Analysis | ||||||
| Units Sold | 12,500 | Average Price per Unit | |||||
| Price Per Unit | $ 99.00 | 233750.0 | $ 79.00 | $ 89.00 | $ 99.00 | $ 109.00 | $ 119.00 |
| Total Sales | $ 1,237,500.00 | 10,500 | (55,250) | 49,750 | 154,750 | 259,750 | 364,750 |
| 11,000 | (45,500) | 64,500 | 174,500 | 284,500 | 394,500 | ||
| Expenses | 11,500 | (35,750) | 79,250 | 194,250 | 309,250 | 424,250 | |
| Variable Expenses | 12,000 | (26,000) | 94,000 | 214,000 | 334,000 | 454,000 | |
| Units Manufactured | 12,500 | 12,500 | (16,250) | 108,750 | 233,750 | 358,750 | 483,750 |
| Variable Cost per Unit | $ 59.50 | 13,000 | (6,500) | 123,500 | 253,500 | 383,500 | 513,500 |
| Total Variable Costs | $ 743,750.00 | 13,500 | 3,250 | 138,250 | 273,250 | 408,250 | 543,250 |
| Fixed Costs | 14,000 | 13,000 | 153,000 | 293,000 | 433,000 | 573,000 | |
| Total Fixed Cost | $ 260,000.00 | 14,500 | 22,750 | 167,750 | 312,750 | 457,750 | 602,750 |
| Expenses - Total | $ 1,003,750.00 | ||||||
| Total Expense Per Unit Sold | $ 80.30 | ||||||
| Profit | |||||||
| Total Sales | $ 1,237,500.00 | ||||||
| Total Expenses | $ 1,003,750.00 | ||||||
| Gross Profit | $ 233,750.00 | ||||||
| Break-even Analysis | |||||||
| Price per Unit | $ 99.00 | ||||||
| Cost Per Unit | $ 80.30 | ||||||
| Units Sold | 12,500 | ||||||
| Gross Profit | $ 233,750.00 |
Earphone Suppliers
| Hurdy Headphones - Earphone Suppliers | ||||
| Prima Electronics | Claret Audio Components | Shengyin Technology | Total | |
| Units Produced | 3,300 | 4,200 | 5,000 | 12,500 |
| Fixed Costs | $ 90,000.00 | $ 100,000.00 | $ 150,000.00 | |
| Fixed Cost per Unit | 27.27 | 23.81 | 30.00 | |
| Variable Costs | 195,030.00 | 248,850.00 | 297,500.00 | |
| Variable Cost per Unit | 59.10 | 59.25 | 59.50 | |
| Total Costs | $ 285,030 | $ 348,850 | $ 447,500 | $ 1,081,380 |
| Minimum Total Cost Model |
All Products
| Hurdy Headphones - Product Mix | |||
| Wireless | Noise-Cancelling | Earphones | |
| Revenues | |||
| Units Sold | 25,000 | 10,500 | 12,500 |
| Price Per Unit | $ 179.00 | $ 209.00 | $ 99.00 |
| Total Sales | $ 4,475,000.00 | $ 2,194,500.00 | $ 1,237,500.00 |
| Cost of Goods Sold | |||
| Units Manufactured | 25,000 | 10,500 | 12,500 |
| Fixed Costs | $ 260,000.00 | $ 260,000.00 | $ 260,000.00 |
| Variable Cost per Unit | $ 150.00 | $ 179.00 | $ 59.50 |
| Variable Costs | $ 3,750,000.00 | $ 1,879,500.00 | $ 743,750.00 |
| Total Costs | $ 4,010,000.00 | $ 2,139,500.00 | $ 1,003,750.00 |
| Total Profit | $ 465,000 | $ 55,000 | $ 233,750 |
| Profit per Unit Sold | $18.60 | $5.24 | $18.70 |
