Question details

New Perspectives Excel 2013 Tutorial 10: SAM Project 1a Firestone
$ 30.00

New Perspectives Excel 2013
Tutorial 10: SAM Project 1a Firestone Clock Company
WHAT-IF ANALYSES AND SCENARIOS PROJECT DESCRIPTION
Walter Silva runs operations at Firestone Clock Company. The company currently owns and
operates its own manufacturing facilities that produce three lines of clocks, Desktop Models,
Wall Units, and Custom Clocks. Manufacturing and other costs have been rising, and profits
are being squeezed. Walter has asked you to create a workbook that details the financial
components of each product line, then analyze a number of scenarios that involve cutting
expenses and/or raising prices. You are trying to find the most profitable mix of products
using the most cost-effective means of production. GETTING STARTED Download the following file from the SAM website:
o NP_Excel2013_T10_P1a_FirstLastName_1.xlsx Open the file you just downloaded and save it with the name:
o NP_Excel2013_T10_P1a_FirstLastName_2.xlsx o Hint: If you do not see the .xlsx file extension in the Save file dialog box, do
not type it. Excel will add the file extension for you automatically. With the file NP_Excel2013_T10_P1a_FirstLastName_2.xlsx still open, ensure that
your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6
does not display your name, delete the file and download a new copy from the SAM
website. 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 available) 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 $ -) New Perspectives Excel 2013| Tutorial 10: SAM Project 1a
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 New Perspectives Excel 2013| Tutorial 10: SAM Project 1a Values Scenario 1
Standard Materials Green Materials 33.75 42.50 175000 Scenario Name Scenario 2 225000 Wall_Unit_Variable_Cost (C12)
Wall_Unit_Fixed_Cost (C15) Copyright © 2014 Cengage Learning. All Rights
Reserved. 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 New Perspectives Excel 2013| Tutorial 10: SAM Project 1a
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. Final Figure 1: Desktop Models Worksheet Microsoft product screenshot reprinted with permission
from Microsoft Incorporated. Copyright © 2014 Cengage
Learning. All Rights Reserved. New Perspectives Excel 2013| Tutorial 10: SAM Project 1a
Final Figure 2: Wall Units Worksheet Copyright © 2014 Cengage Learning. All Rights
Reserved. Final Figure 3: Custom Clocks Worksheet New Perspectives Excel 2013| Tutorial 10: SAM Project 1a Copyright © 2014 Cengage Learning. All Rights
Reserved. New Perspectives Excel 2013| Tutorial 10: SAM Project 1a
Final Figure 4: Scenario Summary Worksheet Copyright © 2014
Final Figure 5: Scenario PivotTable Cengage Learning. All Rights
Worksheet
Reserved. Copyright © 2014 Cengage Learning. All Rights
Reserved. Final Figure 6: All Products Worksheet New Perspectives Excel 2013| Tutorial 10: SAM Project 1a Copyright © 2014 Cengage Learning. All Rights
Reserved.

Available solutions