Please Provide instructions to complete tasks in Excel. Thank you

Step 3: FORMAT A ONE-VARIABLE DATA TABLE You want to format the results to show dollar signs and to display rounded values to the nearest penny. In addition, you want to add column headings to provide more detail to the data table and add custom formats to the cells to appear as column headings. Step 4: SET UP TWO-VARIABLE SUBSTITUTION VALUES AND ADD A FORMULA TO THE DATA TABLE Now you want to focus on how a combination of interest rates and different costs will affect just the monthly payment. The interest rates range from 4% to 8% in 0.25% increments. Step 5: CALCULATE RESULTS FOR A TWO-VARIABLE DATA TABLE You complete the data table, format the monthly payment results, and apply a custom number format to the cell containing the formula reference so that it displays the text APR. Enter 150000, 175000, and 200000 in the range J3:L3. Format these values with Accounting Number Format. Click Fill in the Editing group on the Home tab, select Series, and then click Columns for the series from 4% to 8% in 0.25% increments. Click cell I3, and enter the reference to monthly payment, and then press Ctrl+Enter. Select the range. Click the Data tab, click What-If Analysis in the Forecast group, and then select Data Table. You should enter the reference to the cell containing the original cost in the Row input cell box and the reference to the cell containing the original APR variable in the Column input cell box. Part (3) Summary: You want to use Goal Seek and Scenario Manager to perform additional what-if analyses with your mortgage data. Step 1: DETERMINE OPTIMAL INPUT VALUES USING GOAL SEEK Given the current interest rate with a 30-year mortgage and your planned down payment, you want to identify the most that you can afford and maintain a $600.00 monthly payment. In the What-If Analysis you should select Goal Seek in order to find the ideal home purchase price based on $600.00 monthly payment. You will use Goal Seek to work backward from your goal to identify the ideal home purchase price. To achieve a $600 monthly mortgage payment, you need to purchase a home that costs up to $135,676.74, instead of the original $150,000, assuming the other variables (down payment, interest rate, and term of loan) stay the same. Step 2: CREATE A SCENARIO Chapter 6 What-If Analysis Using Decision-Making Tools Summary of problem: - Assume you want to buy your first home. - After doing some preliminary research on prices, you developed a spreadsheet to help you calculate your monthly mortgage payment, total amount to repay the loan, and the total amount of interest you will pay. Your total budget for the home is $150,000 including taxes, closing costs, and other miscellaneous fees. - You plan to take $10,000 out of your savings account for a down payment. > You are currently investigating loan interest rates at various banks and credit unions. - You realize that you may need to find a less expensive home or increase your down payment to reach a monthly payment you can afford. - Although you can change input values to see how different values affect the monthly payment, you want to be able to see the comparisons at the same time. In addition, you want to look at your budget to review the impact of purchasing a new home on your income and expenses. - To help you make a decision, you will use several what-if analysis tools, each with specific purposes, benefits, and restrictions. Part (1) Summary: You decide to simplify the PMT function you are using to calculate your mortgage by adding range names for the Amount of Loan, Periodic Rate, and No. of Payments instead of the actual cell references. After creating the range names, you will modify the names and create a list of range names. Step 1: CREATE A RANGE NAME You want to assign a range name to the Amount of Loan, Periodic Rate, and No. of Payments and Monthly Payment. You can also use "create from selection" on the formulas to create a range name for the group of a range name. 1 A C D M Two-Variable Data Table: APR and Cost of Home Cost of home Down Payment $150,000.00 One-Variable Data Table: APR Calculated Results for Each APR APR No. Years for Loan Payments Per Year $10,000.00 4.00% 30 12 Output Area Amount of Loan $140,000.00 Periodic Rate No. Payment Periods Monthly Payment Total to Repay Loan Total Interest Paid Monthly Payment by Cost/APR Q R 17 19 20 21 23 24 25 26 27 Home Loan Part (4) Summary: Although Goal Seek and Scenario Manager were helpful in further analyzing your home purchase, you want to ensure the spreadsheet model imposes constraints on the situation. Therefore, you will continue your analysis by using Solver. Step 1: LOAD THE SOLVER ADD-IN Before you can use Solver to analyze your home loan model, you need to load Solver. If Solver is already loaded, skip Step 1 and start with Step 2. a. Click the File tab and click Options. The Excel Options dialog box opens so that you can customize Excel settings. b. Click Add-ins on the left side of the Excel Options dialog box. The Excel Options dialog box displays a list of active and inactive application add-ins. c. Check to see where Solver is listed. If Solver is listed in the Active Application Add-ins list, click Cancel, and then skip Step d. If Solver is listed in the Inactive Application Add-ins list, click the Manage arrow, select Excel Add-ins, and then click Go. The Add-Ins dialog box opens, containing a list of available add-in applications. d. Click the Solver Add-in check box in the Add-ins available list and click OK. Step 2: IDENTIFY THE OBJECTIVE CELL AND CHANGING CELLS a. You set the objective cell as the monthly payment with the value of for instance $800 or $1000. b. you should select the changing Variable Cells from the input area. Step 3: DEFINE CONSTRAINTS You define the constraints: $100,000 to $300,000 cost, $5,000 to $10,000 down payment, 4% to 6% APR, and 15- to 30-year loan. In addition, you set an integer constraint for the years so that Solver does not produce a fractional year, such as 5.71 . a. Add the constraints to the Constraint box. After you enter the last constraint, click OK in the Add Constraint dialog box. You should add your desired constraints. Here is an only example(you should use your desired constraints): - B2>=100000 - B2=5000 - B3=4% - B4=15 - B5 You are currently investigating loan interest rates at various banks and credit unions. - You realize that you may need to find a less expensive home or increase your down payment to reach a monthly payment you can afford. - Although you can change input values to see how different values affect the monthly payment, you want to be able to see the comparisons at the same time. In addition, you want to look at your budget to review the impact of purchasing a new home on your income and expenses. - To help you make a decision, you will use several what-if analysis tools, each with specific purposes, benefits, and restrictions. Part (1) Summary: You decide to simplify the PMT function you are using to calculate your mortgage by adding range names for the Amount of Loan, Periodic Rate, and No. of Payments instead of the actual cell references. After creating the range names, you will modify the names and create a list of range names. Step 1: CREATE A RANGE NAME You want to assign a range name to the Amount of Loan, Periodic Rate, and No. of Payments and Monthly Payment. You can also use "create from selection" on the formulas to create a range name for the group of a range name. 1 A C D M Two-Variable Data Table: APR and Cost of Home Cost of home Down Payment $150,000.00 One-Variable Data Table: APR Calculated Results for Each APR APR No. Years for Loan Payments Per Year $10,000.00 4.00% 30 12 Output Area Amount of Loan $140,000.00 Periodic Rate No. Payment Periods Monthly Payment Total to Repay Loan Total Interest Paid Monthly Payment by Cost/APR Q R 17 19 20 21 23 24 25 26 27 Home Loan Part (4) Summary: Although Goal Seek and Scenario Manager were helpful in further analyzing your home purchase, you want to ensure the spreadsheet model imposes constraints on the situation. Therefore, you will continue your analysis by using Solver. Step 1: LOAD THE SOLVER ADD-IN Before you can use Solver to analyze your home loan model, you need to load Solver. If Solver is already loaded, skip Step 1 and start with Step 2. a. Click the File tab and click Options. The Excel Options dialog box opens so that you can customize Excel settings. b. Click Add-ins on the left side of the Excel Options dialog box. The Excel Options dialog box displays a list of active and inactive application add-ins. c. Check to see where Solver is listed. If Solver is listed in the Active Application Add-ins list, click Cancel, and then skip Step d. If Solver is listed in the Inactive Application Add-ins list, click the Manage arrow, select Excel Add-ins, and then click Go. The Add-Ins dialog box opens, containing a list of available add-in applications. d. Click the Solver Add-in check box in the Add-ins available list and click OK. Step 2: IDENTIFY THE OBJECTIVE CELL AND CHANGING CELLS a. You set the objective cell as the monthly payment with the value of for instance $800 or $1000. b. you should select the changing Variable Cells from the input area. Step 3: DEFINE CONSTRAINTS You define the constraints: $100,000 to $300,000 cost, $5,000 to $10,000 down payment, 4% to 6% APR, and 15- to 30-year loan. In addition, you set an integer constraint for the years so that Solver does not produce a fractional year, such as 5.71 . a. Add the constraints to the Constraint box. After you enter the last constraint, click OK in the Add Constraint dialog box. You should add your desired constraints. Here is an only example(you should use your desired constraints): - B2>=100000 - B2=5000 - B3=4% - B4=15 - B5