# PROJECT 2: WHAT-IF ANALYSIS OBJECTIVE The purpose of this project

PROJECT 2: WHAT-IF ANALYSIS

OBJECTIVE
The purpose of this project is to demonstrate your understanding of the following course concepts:

1. Analyzing data with Goal Seek.
2. Using What-If Analysis tools.
3. Using Solver.
4. Using Scenarios.
5. Summary Reports.

PROJECT DESCRIPTION
PART 1
The automatic recalculation feature of Excel is a powerful tool that can be used to analyze worksheet data. What-if analysis is a decision-making tool in which changing input values recalculate formulas, in order to predict various possible outcomes. When new data is entered, Goal seek is a problem-solving method in which you specify a solution and then find the input value that produces the answer you want.

You are assisting Tony Sanchez, the office manager at Riverwalk Medical Clinic, with a disaster recovery plan for the medical records at the clinic. As part of that plan you are looking at two options for storing backup medical records. The first option is to purchase a storage facility, and the second is to use a third-party backup storage company that offers off-site small, medium, and large storage areas. In both cases, you want to keep your monthly payments below \$17,000. You decide to use Goal Seek to look at various interest rates for purchasing a storage facility and to use Solver to help find the best possible combination of third-party storage areas. You will be working with the attached file: EX_2.1.xlsx.

1. Open the fie EX_2.1xlsx and save it as Project2.1_Records.xlsx.
2. With the Purchase Sheet active, use Goal Seek to find the payment for an interest rate of 5.75% on a Loan amount of \$750,000 for 60 months. Use an additional loan amount of your choice with an interest rate of 6% to find the monthly payment for 48 months.
3.  Activate the Rental Sheet. Open Solver, then use the Set Objective To option to maximize the storage amount in cell B12. (Hint: Select Max in the Set the Objective To area.).
4. In Solver, use the quantity, cells B6:D6, as the changing cells.
5. Add a constraint to Solver specifying the quantity in cells B6:D6 must be integers.
6. Add a constraint specifying that the total monthly payment amount in cell B11 is less than or equal to 17,000.
7. Generate a solution using Solver and accept Solver’s solution.
8. Save the workbook and submit to the Assignment Folder.

PART 2
As a senior financial analyst at North Shore CPA Services, you are researching various options for financing a \$250,000 loan for the purchase of a new estate-planning facility. You haven’t decided whether to finance the project for three, four, or five years. Each loan term carries a different interest rate. To help with the comparison, you summarize these options using a Scenario Summary. You will be working with the file EX_2.2.xls.

1. Open the fie EX_2.2.xlsx and save it as Project2.2_Loans.xlsx.
2. Create cell names for cells B4:B11 based on the labels in cells A4:A11, using the Create Names from Selection dialog box.
3. Use Scenario Manager to create scenarios that calculate the monthly payment on a loan amount of your choice under the three sets of loan possibilities listed below in Figure 1. For instance, in Figure 2 below, the loan amount of \$250,000 was used. Note: You cannot use the loan amount of \$250,000. (Hint: Create three scenarios, using cells B5:B6 as the changing cells.

FIGURE 1: SCENARIO, INTEREST RATES & TERMS

SCENARIO NAME

INTEREST RATE

TERMS (MONTHS)

5% 5 year

5

60

4% 4 year

4

48

3% 3 year

3

36

FIGURE 2: NORTH SHORE CPA SERVICES

NORTH SHORE CPA SERVICES

Financing Options

Loan Amount

\$250,000.00

Annual Interest Rate

5.00%

Term in Months

60

Monthly Payment:

\$4,717.81

Total Payments:

\$283,068.50

Total Interest:

\$33,068.50

4. View each scenario and confirm that it performs as intended, then display the 5% 5 Year scenario.
5. Generate a scenario summary titled Finance Options. Use cells B9:B11 as the Result cells.
6. Format the range B9:B11 as currency with two decimal places.
7. Delete the Current Values column in the report and the notes at the bottom of the report, Row 1, and column A.
8. Rename the sheet Estate Planning Project and assign a color of your choice.
9. Enter your name in the center footer section of both worksheets.

Pages (275 words)
Standard price: \$0.00

### Latest Reviews

Impressed with the sample above? Wait there is more

Related Questions

### Read “Ending HIV? Sangamo Therapeutics and Gene Editing” on pages

Read “Ending HIV? Sangamo Therapeutics and Gene Editing” on pages 167-175. Then, Answer the following questions (with at least eight [8] peer-reviewed sources): What were

### Why are we in need of different sources of energy for generating electricity in the UAE?

Question 1: Why are we in need of different sources of energy for generating electricity in the UAE? Question 2: What are the key consideration

### Probation sentences are used more often than any other sentencing

Probation sentences are used more often than any other sentencing option in the United States for correctional purposes. As a result, there are benefits and

### Read the following Clinical Case: Jason is 11 years old

Read the following Clinical Case: Jason is 11 years old and has Type 1 Diabetes. He was diagnosed with Diabetes when he was 5

### As the chief diversity officer of your organization, you have

As the chief diversity officer of your organization, you have been tasked to deliver a presentation to executive leadership regarding the implications of a global

### Clinical Trials at National Institute of Mental Health find four

Clinical Trials at National Institute of Mental Health  find four research studies in different topic areas in the National Institute of Mental Health (NIMH) website.

### Examine the industry in which Walmart operates.

Use any or all of the following resources to conduct research on the company: Company website Public filings from the Securities and Exchange Commission EDGAR

### In order to begin your research project, you will need

In order to begin your research project, you will need to do some background reading and identify a research question/proposed correlation that addresses a

### Restoration Pamphlet Select a scenario from the list below: There

Restoration Pamphlet Select a scenario from the list below: There is a species of fish (with low numbers) that is having trouble traveling up and

### Tyler Tumminia

Directions – post your paper on the Essay Paper link using Turnitin. Turnitin provides you feedback on spelling, grammar , and punctuation mistakes, so that

### HSM 260 Week 2 Assignment Personal Balance Statement Appendix C Nursing Assignment Help

This pack of HSM 260 Week 2 Assignment Personal Balance Statement Appendix C consists of: Resources: Appendixes B & CDue Date: Day 7 [Individual] forumRead

In a paragraph form, post your initial response after reading the passage below: The client comes to the physician’s office complaining of shortness of

New questions

### MBA 671 CCU Porters Five Forces Analysis Discussion Nursing Assignment Help

I’m working on a health & medical discussion question and need the explanation and answer to help me learn. Review the Porter 5 Forces video

### NVCC Health & Medical Fentanyl Test Strips Policy Proposal Nursing Assignment Help

What Other Health Policy Issue are You Concerned About?  Steps to Determining the Health Policy Solution to Your Problem.  1. 1.  Identify a health care

### Part 1 Case 1 A 5-year-old girl has had a cough and runny Nursing Assignment Help

Part 1 Case 1 A 5-year-old girl has had a cough and runny nose (yellowish discharge) for the past 3 days. Her mother brought her

### Write an introduction and discussion for a biology lab repor… Nursing Assignment Help

Write an introduction and discussion for a biology lab report about DNA barcoding  introduction should be 1.5-2 pages  discussion should be 1-1.5 pages  use and