Question

1. 
   B D H A F G Hurdy Headphones - Wireless Models 2 Wireless Headph ones-Profit Analysis Total Expenses 3 Sales Units Sold 25,000 Units Sold Total Sales Gross Profit A Price Per Unit 5 179.00 Total Sales 6 4,475,000.00 15,000 7 20.000 8 Expenses 9 Variable Expenses 25,000 30,000 35,000 10 Units Manufactured 25,000 150.00 Variable Cost per Unit Total Variable Costs S 11 12 3,750,000.00 13 Fixed Expenses Total Fixed Cost Wireless Headphones - Gross Profit Analysis 169.00 S 149.00 S 159.00 $ 179.00 $ 465,000 S 15,000 20,000 25,000 30,000 14 260,000.00 4,010,000.00 160.40 189.00 15 Expenses- Total Total Exnense Per Unit Sold $ 16 17 18 Profit Total Sales 4,475,000.00 35,000 19 Total Expenses 20 4,010,000.00 Gross Profit 21 465,000.00 22 23 Break-even Analysis 24 Price per Unit 179.00 S 25 Cost Per Unit 160.40 26 Units Sold 25,000 465,000.00 27 Gross Profit 28 29 30 31 32 33 34 35 36 37 38
   Kylie and Derek Hurdy own Hurdy Headphones, a company that manufactures wireless and noise-cancelling headphones and personalized earphones in Pittsburgh, Pennsylvania. As an intern at the company, you are developing a workbook that includes the financial details of each product line. Kylie and Derek have asked you to use the data to analyze scenarios that involve cutting expenses and raising prices. They want you to find the most profitable mix of products using the most cost-effective means of production.

Switch to the Wireless worksheet. Perform a break-even analysis for wireless headphones as follows:

1. In cell B27, use Goal Seek to set cell B27 to a value of 0.

Answer 1

C 1 Wireless Headphones Profit Analysis 2 3 Unit Sold Total Expenses Gross Profit Total Sales 26,85,000 $ (15,000 $179) (15,000*$150)+$260,000 35,80,000 $ (20,000 $179) (20,000*$150)+$260,000 44,75,000 $ (25,000 $179) (25,000*$150)+$260,000 53,70,000 $ (30,000 $179) (30,000*$150)+$260,000 62,65,000 $ (35,000 $179) (35,000*$150)+$260,000 15,000 $ 25,10,000 $ 1,75,000 5 32,60,000 $ 20,000 $ 6 3,20,000 7 40,10,000 $ 25,000 $ 4,65,000 9 47,60,000 $ 30,000 $ 10 6,10,000 11 35,000 $ 55,10,000 $ 12 7,55,000 13 LO C

15 Wireless Headphones Gross Profit Analysis 16 $ 4,65,000 $ 149 $ 169 $ 159 $ 179 $ 189 -2,75,000 $ -2,80,000 $ -2,85,000 $ 2,90,000 $ -2,95,000 $ 25,000 $ 1,20,000 $ 2,15,000 $ 3,10,000 $ 4,05,000 $ -1,25,000 $ -80,000 $ -35,000 $ 10,000 $ 55,000 $ 1,75,000 $ 3,20,000 $ 4,65,000 $ 6,10,000 $ 7,55,000 $ 17 15,000 $ 3,25,000 5,20,000 18 20,000 19 25,000 7,15,000 30,000 $ 35,000 $ 20 9,10,000 21 11,05,000 22

24 Break-even Analysis 25 Price per unit $ 26 Cost per unit $ 27 Units Sold 28 Gross Profit 179.00 160.40 $ 8,966.00 $