Noise-cancelling headphones and personalized earphones in Pittsburgh, Pennsylvania

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:

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

b. 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 $ -.)

1. Create a one-variable data table to calculate sales, expenses, and profits based on the number of wireless headphones sold as follows:

a. In cell D5, enter a formula that references cell B4, which is the expected units sold for this product.

b. In cell E5, enter a formula that references cell B19, which is the expected total sales for this product.

c. In cell F5, enter a formula that references cell B20, which is the expected total expenses for this product.

d. In cell G5, enter a formula that references cell B21, which is the expected gross profit for this product.

e. Select the range D5:G10 and then complete the one-variable data table, using cell B4 as the Column input cell for your data table.

1. Create a two-variable data table to calculate the gross profit based on the number of wireless headphones sold and the price per unit:

a. For the range D14:K19, create a two-variable data table using the price per unit (cell B5) as the Row input cell.

b. Use the units sold (cell B4) as the Column input cell.

1. Apply a custom format to cell D14 to display the text Units Sold/Price in place of the cell value.
2. 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.
3. Modify the new chart as follows:

a. Resize and reposition the chart so that it covers the range D15:H32.

b. Remove the chart title from the chart.

Sample Solution