Requirements Use Whatif Analysis feature of Excel program to

Requirements  

Use What-if Analysis feature of Excel program to work on the following case study. You will use Scenario Manager to create three scenarios. Name your Excel workbook as P3_Scenarios

Case Information

Suppose you had a part time job that earned $18,000 in 2017. After several years of flat wage rate, you hope to see a wage increase in 2018. Table 1 below shows your estimated annual living expenses.

       Table 1. Income and Expense Budget

Income(2017)

$18,000.00

Rent

$3,600.00

Tuition

$6,500.00

Insurance

$2,000.00

Meal

$4,000.00

Gas

$1,200.00

Communication

$600.00

Total expense

$17,900.00

Left over

$100.00

To plan your finances for year 2018, you will create three scenarios (worst, most likely, and best) using Scenario Manager in Excel program and generate Scenario Summary Report (make sure you rename the changing cells and result cells). The inflation rates and wage increase under three scenarios are given in the Table 2 below. Assume the inflation rate applies to all the expenses in the table.

Rate estimates

Scenarios for Year 2018

Worst Case

Likely

Best Case

Inflation rate

8.00%

4.50%

1.00%

Wage increase

0%

4.00%

6.00%

           

                                                Table 2. Estimated Rates for Three Scenarios

You must use Scenario Manager to do this project. The output should look like the following Scenario Summary table (Table 3). When I evaluate your project, I will examine your formula in the spreadsheet as well as the Summary Report generated by Excel in your spreadsheet workbook.

   Table 3 Scenario Summary

Scenario Summary

Current Values:

best

likely

worst

Changing Cells:

infrate

0.00%

1.00%

4.50%

8.00%

wageraise

0.00%

6.00%

4.00%

0.00%

Result Cells:

Total_Income

18000

19080

18720

18000

Total_Expenses

17900

18079

18705.5

19332

NetSaving

$100.00

$1,001.00

$14.50

($1,332.00)

Notes: Current Values column represents values of changing cells at times Scenario Summary Report was created. Changing cells for each scenario are highlighted in gray.

Income(2017)	$18,000.00
Rent	$3,600.00
Tuition	$6,500.00
Insurance	$2,000.00
Meal	$4,000.00
Gas	$1,200.00
Communication	$600.00
Total expense	$17,900.00
Left over	$100.00

Solution

Scenario Summary Current Values: Worst Case Likely Scenario Best Case Scenario Changing Cells: Scenario Base Scenaio Worst Case Scenaio Likely Scenaio Best Case Scenaio Income 18,000 18,000 18,720 19,080 Rent 3600 3888 3762 3636 Tuition 6500 7020 6792.5 6565 Insurance 2000 2160 2090 2020 Meal 4000 4320 4180 4040 Gas 1200 1296 1254 1212 Communication 600 648 627 606 Result Cells: Total Expenses 17900 19332 18705.5 18079 Net Saving 100 -1,332 15 1,001