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

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

## Be the first to reply