1. go to the desktop models worksheet. in cell c28, use goal seek to

·         This project requires the use of the Solver add-in. If this add-in is not available in the Analysis group (or if the Analysis group is not av­ailable) on the DATA tab, install Solver by following the steps below.

o    In Excel, click the FILE tab, and then click the Options button in the left navigation bar.

o    Click the Add-Ins option in the left pane of the Excel Options window.

o    Click on the arrow next to the Manage box, click the Excel Add-Ins option, and then click the Go button.

o    In the Add-Ins window, click the check box next to the Solver Add-In option and then click the OK button.

o    Follow any remaining prompts to install Solver.

PROJECT STEPS

1.    Go to the Desktop Models worksheet. In cell C28, use Goal Seek to perform a break-even analysis for Desktop Models by calculating the number of units the company needs to sell (represented by the value in cell C27), at the price per unit listed in cell C25, in order to break even, or reach a Gross Profit of \$0. (Hint: The number format applied to cell C25 will make a value of \$0 display as \$ -)

2.    Create a one-variable data table to display values for Sales, Expenses, and Profits based on the Number of Clocks sold by completing the following actions:

a.    In cell E5, enter a formula to reference cell C5, which is the input cell to be used in the data table.

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

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

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

e.    Select the range E5:H10 and then complete the one-variable data table, using cell C5 as the Column input cell for your data table.

3.    Select range E14:L19. Create a two-variable data table to display values for gross Profit based on Units Sold and Price per Unit (Hint: Use cell C6 as the Row input cell and cell C5 as the Column input cell).

4.    Apply a custom format to cell E14 to display the text “Units Sold/Price” in place of the cell value.

5.    Go to the Wall Units worksheet. Create a Scatter with Straight Lines chart based on range E4:G14 in the data table Wall-Units – Break-Even Analysis. Modify the chart as described below:

a.    Resize and reposition the chart so the upper-left corner is in cell E15 and the lower-right corner is in cell H28.

b.    Remove the chart title from the chart.

c.    Add Sales and Expenses as the Vertical Axis title and Units Sold as the Horizontal Axis title.

d.    For the Vertical Axis, change the Minimum Bounds to 300000 and the Maximum Bounds to 550000. Change the Number format of the Vertical Axis to Currency with 0 decimal places.

e.    For the Horizontal Axis, change the Minimum Bounds to 5000 and the Maximum Bounds to 9000.

f.     Use the Change Colors option to change the color set for the chart to Color 14 (the 4th entry from the bottom in the gallery of color choices).

6.    Open the Scenario Manager and add two scenarios for the data in the Wall Units worksheet based on the data shown in Table 1. The changing cells for both scenarios are the non-adjacent cells C12, and C15. Close the Scenario Manager without showing any of the scenarios.

Table 1: Wall Unit Scenario Values

 Values Scenario 1 Scenario 2 Scenario Name Standard Materials Green Materials Wall_Unit_Variable_Cost (C12) 33.75 42.50 Wall_Unit_Fixed_Cost (C15) 175000 225000

7.    Go to Custom Clocks worksheet. Create a Scatter with Straight Lines chart based on range E6:J14 in the data table Custom Clocks – Net Income Analysis. Make the following modifications to the chart:

a.    Resize and reposition the chart so the upper-left corner is in cell E15 and the lower-right corner is in cell J28.

b.    Remove the chart title from the chart.

c.    Reposition the chart legend to the Right of the chart.

d.    Add the title Net Income as the Vertical Axis title and Units Sold as the Horizontal Axis title.

e.    For the Vertical Axis, change the Minimum Bounds to –150000 and the Maximum Bounds to 250000. Change the Number format of the Vertical Axis to Currency with 0 decimal places.

f.     For the Horizontal Axis, change the Minimum Bounds to 3000 and the Maximum Bounds to 7500.

8.    In the Scatter with Straight Lines chart created in the previous step, edit the chart series names as described below:

a.    For Series 1, set the series name to cell F5 (Hint: The series name should automatically update to =’Custom Clocks’!\$F\$5).

b.    For Series 2, set the series name to cell G5.

c.    For Series 3, set the series name to cell H5.

d.    For Series 4, set the series name to cell I5.

e.    For Series 5, set the series name to cell J5.

9.    Firestone Clocks is considering subcontracting the construction of their Custom Clock line to other woodshops in the area. Walter wants to determine if this option will reduce the costs associated with this product line.

Go to the Custom Clock – Suppliers worksheet. Run Solver to minimize the value in cell F11 (Total Cost) by adjusting number of units produced by each woodshop (Hint: Changing cells will be C5:E5) assuming the four (4) manufacturing constraints below:

a.    F5=5500

b.    F11 <=560000

c.    C5:E5 <=3500

d.    C5:E5 should be an Integer

Run Solver, keep the Solver Solution, and then return to the Solver Parameters Dialog box. Save the model to the range B15:B22. Close the Solver Parameters Dialog box.

10.Go to the All Products worksheet. Open the Scenario Manager and create a Scenario Summary report for the resultant cells C18:E18. The Scenario Summary report will summarize the impact of the following three scenarios: Status Quo, Outsource Manufacturing, Raise Prices 5%.

11.Go back to the All Products worksheet. Open the Scenario Manager and create a Scenario PivotTable report for result cells C18:E18. Format the Scenario PivotTable as described below:

a.    Remove the Filter field from the PivotTable.

b.    Change the number format of the Profit_per_Unit_Sold_Desktop, Profit_per_Unit_Sold_Wall_Units, and Profit_per_Unit_Sold_Custom fields (located in the Values box of the PivotTable Field List) to Currency (with 2 decimal places).

c.    In cell A1, enter the value All Products Scenario PivotTable and format the cell with the Title cell style.

12.Go back to the All Products worksheet. Open the Scenario Manager and view the Outsource Manufacturing scenario in the worksheet.

Your workbook should look like the Final Figure on the following page. Note that some of the outcome values have been intentionally blanked out. Save your changes, close the workbook, and exit Excel. Follow the directions on the SAM website to submit your completed project.

Calculate the price
Pages (550 words)
\$0.00
*Price with a welcome 15% discount applied.
Pro tip: If you want to save more money and pay the lowest price, you need to set a more extended deadline.
We know how difficult it is to be a student these days. That's why our prices are one of the most affordable on the market, and there are no hidden fees.

Instead, we offer bonuses, discounts, and free services to make your experience outstanding.
How it works
Receive a 100% original paper that will pass Turnitin from a top essay writing service
step 1
Fill out the order form and provide paper details. You can even attach screenshots or add additional instructions later. If something is not clear or missing, the writer will contact you for clarification.
Pro service tips
How to get the most out of your experience with Australia Assessments
One writer throughout the entire course
If you like the writer, you can hire them again. Just copy & paste their ID on the order form ("Preferred Writer's ID" field). This way, your vocabulary will be uniform, and the writer will be aware of your needs.
The same paper from different writers
You can order essay or any other work from two different writers to choose the best one or give another version to a friend. This can be done through the add-on "Same paper from another writer."
Copy of sources used by the writer
Our college essay writers work with ScienceDirect and other databases. They can send you articles or materials used in PDF or through screenshots. Just tick the "Copy of sources" field on the order form.
Testimonials
See why 20k+ students have chosen us as their sole writing assistance provider
Check out the latest reviews and opinions submitted by real customers worldwide and make an informed decision.
SEO
Good job!
Customer 463957, May 9th, 2023
Nursing
paper was not completed by due date
Customer 454371, May 25th, 2020
Military
excellent job
Customer 456821, August 3rd, 2022
It was really well written!
Customer 453975, February 28th, 2020
Education
Thank you!
Customer 463875, April 29th, 2023
Writer was very patient and completed on time
Customer 452707, October 4th, 2022
Social Work and Human Services
Good work.
Customer 462793, July 8th, 2022
GOVT 200
Plagiarism issue noted.
Customer 454799, July 11th, 2022
Social Work and Human Services
Thank you for the revision which was done timely and efficiently!
Customer 452703, January 23rd, 2020
I received a mastery on this assignment!
Customer 463143, August 31st, 2022
Finance/Acc Related
Good concept breakdown.
Customer 460073, July 9th, 2022
Nursing
i really appreciate for the writer work for redoing my assignment on my request. Hopefully i will get atleast credit in this essay.
Customer 452967, April 3rd, 2020
11,595
Customer reviews in total
96%
Current satisfaction rate
3 pages
Average paper length
37%
Customers referred by a friend
OUR GIFT TO YOU
15% OFF your first order
Use a coupon FIRST15 and enjoy expert help with any task at the most affordable price.
Claim my 15% OFF Order in Chat