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: In cell B27, use Goal Seek to set cell B27 to a value of 0.
Change the number of units sold in cell B26 to break even, or reach a Gross Profit of $0. (Hint: The number format applied to cell B27 will display the value of $0 as $ -.)
Create a one-variable data table to calculate sales, expenses, and profits based on the number of wireless headphones sold as follows:
In cell D5, enter a formula that references cell B4, which is the expected units sold for this product.
In cell E5, enter a formula that references cell B19, which is the expected total sales for this product.
In cell F5, enter a formula that references cell B20, which is the expected total expenses for this product.
In cell G5, enter a formula that references cell B21, which is the expected gross profit for this product.
Create a two-variable data table to calculate the gross profit based on the number of wireless headphones sold and the price per unit:
For the range D14:K19, create a two-variable data table using the price per unit (cell B5) as the Row input cell.
se the units sold (cell B4) as the Column input cell.
Apply a custom format to cell D14 to display the text Units Sold/Price in place of the cell value.
Switch to the Noise-Cancelling worksheet. Create a Scatter with Straight Lines chart based on range D4:F14 in the data table Noise-Cancelling – Break-Even Analysis.
Modify the new chart as follows:
Resize and reposition the chart so that it covers the range D15:H32.
Add Sales and Expenses as the vertical axis title and Units Sold as the horizontal axis title.
Change the Bounds Axis Options as follows:
Change the Minimum Bounds of the vertical axis to 1750000 and the Maximum Bounds to 2650000.
Change the number format of the vertical axis to Currency with 0 decimal places and $ as the symbol.
Change the Minimum Bounds of the horizontal axis to 8500. (Hint: The Maximum Bounds should automatically change to 12,500.)
Create two scenarios to compare the costs for standard components and green components in the noise-cancelling headphones as follows:
In the Scenario Manager, add two scenarios using the data shown in bold in Table 1 below.
The changing cells for both scenarios are the nonadjacent cells B11 and B14.
Close the Scenario Manager without showing any of the scenarios.