Loading...

Messages

Proposals

Stuck in your homework and missing deadline? Get urgent help in $10/Page with 24 hours deadline

Get Urgent Writing Help In Your Essays, Assignments, Homeworks, Dissertation, Thesis Or Coursework & Achieve A+ Grades.

Privacy Guaranteed - 100% Plagiarism Free Writing - Free Turnitin Report - Professional And Experienced Writers - 24/7 Online Support

Create a scenario summary report excel 2013

20/10/2021 Client: muhammad11 Deadline: 2 Day

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

Homework is Completed By:

Writer Writer Name Amount Client Comments & Rating
Instant Homework Helper

ONLINE

Instant Homework Helper

$36

She helped me in last minute in a very reasonable price. She is a lifesaver, I got A+ grade in my homework, I will surely hire her again for my next assignments, Thumbs Up!

Order & Get This Solution Within 3 Hours in $25/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 3 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 6 Hours in $20/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 6 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

Order & Get This Solution Within 12 Hours in $15/Page

Custom Original Solution And Get A+ Grades

  • 100% Plagiarism Free
  • Proper APA/MLA/Harvard Referencing
  • Delivery in 12 Hours After Placing Order
  • Free Turnitin Report
  • Unlimited Revisions
  • Privacy Guaranteed

6 writers have sent their proposals to do this homework:

Assignment Helper
Unique Academic Solutions
Essay Writing Help
Quick Finance Master
Accounting & Finance Master
Professional Accountant
Writer Writer Name Offer Chat
Assignment Helper

ONLINE

Assignment Helper

I am a professional and experienced writer and I have written research reports, proposals, essays, thesis and dissertations on a variety of topics.

$23 Chat With Writer
Unique Academic Solutions

ONLINE

Unique Academic Solutions

This project is my strength and I can fulfill your requirements properly within your given deadline. I always give plagiarism-free work to my clients at very competitive prices.

$36 Chat With Writer
Essay Writing Help

ONLINE

Essay Writing Help

I have assisted scholars, business persons, startups, entrepreneurs, marketers, managers etc in their, pitches, presentations, market research, business plans etc.

$33 Chat With Writer
Quick Finance Master

ONLINE

Quick Finance Master

I am a PhD writer with 10 years of experience. I will be delivering high-quality, plagiarism-free work to you in the minimum amount of time. Waiting for your message.

$19 Chat With Writer
Accounting & Finance Master

ONLINE

Accounting & Finance Master

I have done dissertations, thesis, reports related to these topics, and I cover all the CHAPTERS accordingly and provide proper updates on the project.

$36 Chat With Writer
Professional Accountant

ONLINE

Professional Accountant

I find your project quite stimulating and related to my profession. I can surely contribute you with your project.

$16 Chat With Writer

Let our expert academic writers to help you in achieving a+ grades in your homework, assignment, quiz or exam.

Similar Homework Questions

Mentoring in workplace- assignment - Finance analyst job description - Porsche kettle and toaster - Ibm tivoli directory integrator - Strategic management rothaermel 4th edition pdf - Braille days of the week abbreviations - Impediments to problem solving - With database software users run functions to retrieve data - Briddy li net worth - Compentecy of functions of case managers - Almighty suspect twitter mentions lyrics - Foreign Corrupt Practices Act- 2nd discussion - Ssd bharat bazar / tandoori express - Two types of employee attributions are noted in the article (intentionality and deceptive intentionality), - Bio rad precast gels 4-15 - Cal thomas is the american dream over - For financial reporting clinton poultry farms - Amenable research topic to scientific study in qualitative approach - Duplicate - Ignore English 2 Page work - Sql stands for in computer - Itpm best practices - 16 university avenue macquarie university - Case study on international human resource management with solution - Just for feet inc - US History Book Essay (Answering Questions By Providing Quotes) - Tina jones shadow health musculoskeletal - Kaggle Competition - Sonic firewall price in walmart - Game design document template - Brisbane city council waste collection - Core high frequency word survey - Exploring strategy model - Chapter 8 i just want to be yours anita baker - Develop and cultivate partnership and relationship - Gordon rule essay example - Symbolism in literature powerpoint - Dutton park parking control area - Zurich company reports pretax financial income of - St croix model 488 rotating davits - Balance Sheet and Income Statement - China dumping steel in india - .79 inches to fraction - Calculus - Discussion - Accident error injury prevention ati - African American Studies_Week 2 - Nitrogen at an initial state of - 5 sources of power in management - Discussion Question NSG 4074 - 2 assignments with answers - Which conventional steering gear adjustment should be the first performed - Strategic Management Practices: An Approach Towards Reducing Financial Crimes - Me3 target jamming technology - Carlsberg strategic analysis - Is limonite metallic or nonmetallic - Sherman she acupuncture kansas city - Primary Sources - BUSN603 - Words to describe a piano player - Write a program that scores a blackjack hand - Barberry inc manufactures a product called fruta - Printable periodic table pdf - Accurate answers to the questions demonstrating knowledge of statutory interpretation - A room of one's own analysis chapter 3 - Observation of ohm's law - Presented below is an aging schedule for bosworth company - Edu 540 week 7 - Reflection and refraction lab report answers - Body parts in french - How to calculate theoretical yield - Hydrogen cooling of alternator - Www manwec my into com - Catholic church kings lynn - Mastering skimming will improve your reading speed and comprehension - Impractical jokers ill conceived menu - Brazer cutters solderers welder typical entry level education - Moodle bcu ac uk - Me talk pretty one day author's goal - Fierceemr and fiercehealthit - Write an Essay about Hui Liu the Chinese Mathematician - Ken is 63 years old and unmarried - Sims metal west gosford - Texas teachers lesson plans - Facebook inc the initial public offering case study - Case Study - Intercultural Management - Sterile supply storage guidelines - Walmart retail link login page - Interstellar 2014 movie worksheet answers - From Slavery to Freedom - Diagram of the skin - Stephanie owen and isabel sawhill should everyone go to college - Ipconfig flushdns the requested operation requires elevation - Lyrics firework katy perry meaning - V2500 engine training manual - Eston grange power station - Beck depression inventory ii scoring interpretation - Setting up your own business reading answer - Practice of leadership lululemon - How to get rid of stags bluekai on mac - Long-term strategy for vertical integration vs. outsourcing of components