Excel Scenarios, management homework help

User Generated

qsqs44

Business Finance

Description

look at the attached file

Unformatted Attachment Preview

GSB 622 Assignment – What if Scenario Analysis Using What-If Scenario Analysis This assignment involves using Scenario Analysis (Tools  Scenarios) to analyze what the result will be if employees are given a percentage increase in their hourly rate. We wish to analyze different scenarios corresponding to different rates. Create this spreadsheet: DO NOT LOSE POINTS! - - NOTE THE FOLLOWING (failure to do exactly what is required will result in loss of points): ➢ Cell C9 is to be computed using a formula that can be copied to all of the cells in subsequent rows 10 through 20 in column C. Current Weekly Pay is computed as Current Hourly Pay Rate * Hours per Week. Note that in order to copy this formula and a few others in the spreadsheet you must fix a cell address. In this case, you must fix the cell address for Hours per Week! (absolute cell reference) ➢ Cell C22 is to contain a formula that computes the sum of the values in column C ➢ Cell D9 is to be computed using a formula that can be copied to all of the cells in subsequent rows 10 through 20 in column D. Proposed Hourly Pay Rate is computed as the product of the Current Hourly Pay Rate and the % of Pay Increase found in the right side portion of the spreadsheet. ➢ Cell E9 is to be computed using a formula that can be copied to all of the cells in subsequent rows 10 through 20 in column C. Proposed Weekly Pay is computed as Proposed Hourly Pay Rate * Hours per Week. ➢ Cell E22 is to contain a formula that computes the sum of the values in column E. ➢ The Total Cost of the Raise in cell H14 is to be computed as the different in the two Weekly Pay Totals. ➢ The Yearly Cost of the Raise in cell H20 is to be computed using the Total Cost of the Raise multiplied by 52 (52 weeks in the year). ➢ Format the cells with a currency format wherever appropriate. The results of these calculations should display as follows. Again make certain that you have used copy-able formulas! Scenario Analysis Page 2 Now, we are ready to do Scenario Analysis, or what is often referred to as what if analysis. Using the Scenario Manager (Tools  Scenario), create the following scenarios: Scenario Analysis Page 3 (a) Scenario for a 3% Hourly Pay rate (b) Scenario for a 5% Hourly Pay rate increase increase (c) Scenario for a 6% Hourly Pay Rate increase (d) Scenario for a 7% Hourly Pay Rate increase Then produce a Scenario Summary that selects the cells C22, E22, H14 and H20 for display. The Scenario Summary worksheet produced should look like this: Scenario Analysis Page 4 The Questions Below will appear in Blackboard as part of this assignment. Write the answers obtained by this analysis below. 1. With a 7% raise, what is the total weekly cost of the raise? _____________ 2. With a 7% raise, what would the annual cost be? ______________ 3. What is the annual cost at 5% if we lower the hours per week input to 30? _________ 4. Compromise with the employees and offer an 5% raise, but cut the per-week hours to 36. a. What is the weekly cost to the company?_______________ b. What is the annual cost?__________________ c. What is Frank Lawson’s proposed weekly pay under this scenario? ____________ Scenario Analysis Page 5
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Hello dfdf44,I have attached the completed assignment, as well as a summary (included below), Please review the files and let me know if any modifications need to be made.Assignment – What if Scenario Analysis - Overview There are two files included in the response: Assignment – What if Scenario Analysis.xlsxIt has the requested spreadsheet with the appropriate calculations.It also includes a second spreadsheet with the requested Scenario Analysis.Assignment – What if Scenario Analysis.docxThis document contains the questions and answers for the final items indicated in the assignment instructions.

Scenario Summary
Current Values:

3% Increase in Pay Rate

Changing Cells:
$H$12
5%
3%
Result Cells:
$C$22 $
8,690.00 $
8,690.00
$E$22 $
9,124.50 $
8,950.70
$H$14 $
434.50 $
260.70
$H$20 $
22,594.00 $
13,556.40
...


Anonymous
Just the thing I needed, saved me a lot of time.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags