Any help would be appreciated.
PROJECT STEPS
1.Sophia Sabatini owns Cortona Coffee Makers, a company in Nashville, Tennessee that manufactures coffee makers for the consumer market. As an intern at the company, you are developing a workbook that includes the financial details of each product line. Sophia has asked you to use the data to analyze scenarios that involve cutting expenses and raising prices. She wants you to find the most profitable mix of products using the most cost-effective means of production.
Switch to the Single Cup worksheet. Perform a break-even analysis for single cup coffee makers as follows:
2.Create a one-variable data table to calculate sales, expenses, and profits based on the number of single cup coffee makers sold as follows:
3.Create a two-variable data table to calculate the gross profit based on the number of single cup coffee makers sold and the price per unit:
4.Apply a custom format to cell D14 to display the text Units Sold/Price in place of the cell value.
5.Switch to the Automatic Drip worksheet. Create a Scatter with Straight Lines and Markers chart based on range D4:F14 in the data table Automatic Drip – Break-Even Analysis.
6.Modify the new chart as follows:
7.Change the Bounds Axis Options as follows:
8.Create two scenarios to compare the costs for stainless steel components and standard acrylic components in the automatic drip coffee makers as follows:
Table 1: Automatic Drip Coffee Maker Scenario Values
|
Values
|
Scenario 1
|
Scenario 2
|
|
Scenario Name
|
Standard
|
Stainless_Steel
|
|
Auto_Drip_Variable_Cost (B11)
|
52.00
|
65.50
|
|
Auto_Drip_Fixed_Cost (B14)
|
260000
|
360000
|
9.Switch to the French Press worksheet. Create a Scatter with Straight Lines and Markers chart based on range D6:I14 in the data table French Press – Net Income Analysis.
10.Modify the new chart as follows:
11.Change the Bounds Axis Options for the new chart as follows:
12.Edit the chart series names as follows:
13.Sophia wants to determine whether subcontracting the manufacture of a new line of espresso makers to suppliers would reduce the costs of the machines. Switch to the Suppliers worksheet, and then run Solver to solve this problem as follows:
14.Switch to the All Products worksheet. Use the Scenario Manager to create a Scenario Summary report that summarizes the effect of the No Change, Subcontract, and Raise Prices 10% scenarios. Use cells B17:D17 as the result cells. Delete column D of the Scenario Summary worksheet because it repeats data already shown.
15.Switch back to the All Products worksheet. Use the Scenario Manager as follows to compare the profit per unit in each scenario:
16.Add a PivotChart to the Scenario PivotTable worksheet as follows:
Get Answer Over WhatsApp
Order Paper Now
