Get Coursework Help In Your Essays, Assignments, Homeworks, Dissertation Or Thesis

Professional And Experienced Writers - 24/7 Online Support

5-2 project two milestone: comparison analysis

New Perspectives Excel 2013| Tutorial 10: SAM Project 1a

New Perspectives Excel 2013

Tutorial 10: SAM Project 1a

Firestone Clock Company

WHAT-IF ANALYSES AND SCENARIOS

Project Goal

M Project Name

Project Goal

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:

· NP_Excel2013_T10_P1a_FirstLastName_1.xlsx

· Open the file you just downloaded and save it with the name:

· NP_Excel2013_T10_P1a_FirstLastName_2.xlsx

· 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.

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

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

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

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

· Follow any remaining prompts to install Solver.

PROJECT STEPS
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 $ -)

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.

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

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

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:

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

g. Remove the chart title from the chart.

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

i. 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.

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

k. 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).

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

Copyright © 2014 Cengage Learning. All Rights Reserved.

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:

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

m. Remove the chart title from the chart.

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

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

p. 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.

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

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

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

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

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

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

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

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:

w. F5=5500

x. F11 <=560000

y. C5:E5 <=3500

z. 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.

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%.

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:

aa. Remove the Filter field from the PivotTable.

ab. 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).

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

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.

Final Figure 2: Wall Units Worksheet

Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 3: Custom Clocks Worksheet

Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 4: Scenario Summary Worksheet

Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 5: Scenario PivotTable Worksheet

Copyright © 2014 Cengage Learning. All Rights Reserved.

Final Figure 6: All Products Worksheet

Copyright © 2014 Cengage Learning. All Rights Reserved.

2

Get Coursework Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework

Professional And Experienced Writers - 24/7 Online Support

Similar Questions

5-2 final project milestone two completed workbook

CHAPTER 5 The Five Generic Competitive Strategies Copyright © McGraw-Hill Education. Permission required for reproduction or display. LEARNING OBJECTIVES THIS CHAPTER WILL HELP YOU UNDERSTAND: What distinguishes each of the five generic strategies and why some of these strategies work better in certain kinds of competitive conditions than in others The major avenues for achieving a competitive advantage based on lower costs The major avenues to a competitive advantage based on differentiating a company’s product or service offering from the offerings of rivals The attributes of a best-cost provider strategy—a hybrid of low-cost provider and differentiation strategies © McGraw-Hill Education. …

4-1 final project milestone two literature analysis

Running head: PROJECT MILESTONE TWO 1. PROJECT MILESTONE TWO 2. Running head: FINAL PROJECT MILESTONE 3. Final Project Milestone One Southern New Hampshire University January 6th, 2019 Research question: “Does self-disclosure of the therapist improve eating disorder treatment.” Hypothesis: Self-disclosure of the therapist improves eating disorder treatment. The key variables for this research are self-disclosure of the therapist and eating disorder treatment. This research will focus on online research whereby participants will be recruited from an eating disorder charity database. The participants will be asked about the status of their condition and how they feel about having the disorder. The …

5-1 final project milestone two: technology solutions and recommendations

5-1 final project milestone two: technology solutions and recommendations

Qso 300 final project milestone two

qso 300 final project milestone two

6-1 final project milestone three: ratio analysis report draft

TThhee BBhhaaggaavvaadd GGiittaa Translation by Shri Purohit Swami. A NOTE ABOUT THE TRANSLATOR Shri Purohit Swami was born into a religious and wealthy family in Badners, India, in 1882. He studied philosophy and law, received his LL.B. from Decan College, Poona, married and had three children. However, he did not practice law, and instead spent his entire life in spiritual devotion. He wrote in his native Marathi, in Hindi, Sanskrit and English – poems, songs, a play, a novel, a commentary on The Bhagavad Gita and an autobiography. He left India in 1930 at the suggestion of his Master to …

2-2 final project milestone one introduction

Geol 1304 Assignment 1A Surface Air Pressure Patterns and Winds "1. On Figure 1A-1, the lowest plotted pressure is 1006 mb and the highest plotted value is ___1035___ mb." "2. By U.S. convention, isobars on surface weather maps are drawn with the same interval as that described for the Figure 1A-1 map. This isobar interval is ______ mb. The isobar interval is selected to provide the most useful resolution of the field of data; too small an interval (for example, 1 mb) would clutter the map and too great an interval (for example, 10 mb) would not adequately define the …

2-2 final project milestone one: managing operations

2-2 final project milestone one: managing operations

7-2 final project part two submission multimedia presentation

7-2 final project part two submission multimedia presentation

It 140 project two text based game

519 C h a p t e r 12 Meetings, Conventions, and expositions L E A r N i N G o B J E C T i V E S after reading and studying this chapter, you should be able to: • List the major players in the convention industry. • Describe destination management companies. • Describe the different aspects of being a meeting planner. • explain the different types of meetings, conventions, and expositions. • List the various venues for meetings, conventions, and expositions. M12_WALK2761_07_SE_C12.indd 519 09/24/16 7:49 AM 520 Part iv assemblies, events, attractions, Leadership, and Management …

Acc 202 milestone 3 - variance analysis

This is the grading rubric that will be used to assess your response to others: Student address peer by name. Student displays understanding of peer’s post. Student contributes in an academic manner. Student uses college level English skills. Student reply should be no less than Five sentences for each reply, it could be more that's fine. POST ONE. What were the significant contributions of the founders of sociology listed in Chapter One? Auguste Comte was one of the founders of sociology and coined the term sociology, he also believed that sociology could unite all sciences and improve society. Comte was …

Fin 320 project two financial analyst report

CHAPTER 6 Supply, Demand, and Government Policies Economists have two roles. As scientists, they develop and test theories to explain the world around them. As policy advisers, they use their theories to help change the world for the better. The focus of the preceding two chapters has been scientific. We have seen how supply and demand determine the price of a good and the quantity of the good sold. We have also seen how various events shift supply and demand and thereby change the equilibrium price and quantity. And we have developed the concept of elasticity to gauge the size …

On her loving two equally analysis

1. Which of the following is true regarding the sampling distribution of the mean for a large sample size? a. It has the same shape, mean, and standard deviation as the population. b. It has a normal distribution with the same mean and standard deviation as the population. c. It has the same shape and mean as the population, but has a smaller standard deviation. d. It has a normal distribution with the same mean as the population but with a smaller standard deviation. Q2. According to a survey of American households, the probability that the residents own 2 cars …

System analysis and design final project

system analysis and design final project

6-1 activity systems thinking project milestone

Knowledgebank: JOB DESIGN Job design is an important method managers can use to enhance employee performance. Job design is how organizations define and structure jobs. As we will see, properly designed jobs can have a positive impact on the motivation, performance, and job satisfaction of those who perform them. On the other hand, poorly designed jobs can impair motivation, performance, and job satisfaction. Until the nineteenth century, many families grew the things they needed, especially food. General craft jobs arose as people ceased or reduced their own food production, used their labor to produce other goods such as clothing and …

7-2 project company accounting workbook and summary report

7-2 project company accounting workbook and summary report
Top Grade Tutor

ONLINE

Top Grade Writer

10374 Orders Completed

Top Academic Guru

ONLINE

Top Academic Guru

9345 Orders Completed

Top Essay Writer

ONLINE

Top Essay Writer

8379 Orders Completed

A-Grade Writer

ONLINE

A-Grade Writer

7812 Orders Completed

Get Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework