# Title: Excel Lab 5 Fall 2021 Author: Kyler.Attisha Comments: location where you completed the lab i.e Home System,

 Part 1 – Goal Seek Start by creating your own original new file using Excel 2016 or 2019 with the name Kyler.Attisha_Excel_Lab5.xlsx Enter the appropriate Excel header and footer elements in the locations below. The only part you should type is your name. Add 3 document properties via the Document Properties panel. Title: Excel Lab 5 Fall 2021 Author: Kyler.Attisha Comments: location where you completed the lab i.e Home System, Work Computer cell A1 title contains Attisha Goal Seek Loan Analysis Worksheet data entry, the following cells should contain information as follows: Change the title font size in cell A1 to 18 points. Merge and Center the title in cell A1 across the data columns A – E. Set the background color of cell A1 to Accent 5 Lighter 80% or the closest light blue color. Change all column widths as follows: Set columns A, D, to a width of 18 (131 pixels) Set columns B, E to a width of 12 (89 pixels) Set columns C to a width of 4 (33 pixels) Bold text in cells A2 and D2 Merge and Center the cell A2 across the columns A – B. Merge and Center the cell D2 across the columns D – E. Format the Purchase Price, Down Payment, and Term Months as Comma with 0 decimal places Format the Yearly Rate as Percent with 2 decimal places. Your worksheet should look like this. Enter the formulas in row 9 for the Amount Financed which is Purchase Price – Down Payment Enter the formulas row 10 using the PMT function to calculate the Monthly Payment. the PMT function was covered in Chapter 2 page E2-116 Enter the formulas row 11 to calculate the Total Interest. total of all payments – Amount Financed Format cells B9 – B11 and E9 – E11 as Comma with 0 decimal places. Name the worksheet tab Goal Seek Use Goal Seek to change the Down Payment (cell E5) for a Monthly Payment of exactly \$420. Goal Seek was covered in the Simnet Goal Seek training from Chapter 9. See E9-555 Save the file with the name Kyler.Attisha_Excel_Lab5.xlsx Part 2 – Rebate Worksheet Task – Find difference between discount interest rate or normal interest rate with rebate Create a new worksheet and name it Rebate Copy Cells A4:B11 into a new worksheet into cells A3:B10 Enter Rebate in cell A1 Change the title font size in cell A1 to 18 points. Merge and Center the title in cell A1 across the data columns A – D. Set the background color of cell A1 to Accent 6 Lighter 80% or the closest light green color. Insert 3 rows between Yearly Rate and Amount Financed. Adjust column widths; A to 20 (145 pixels) and columns B, C, and D to 16 (117 pixels) Enter the text and values highlighted in yellow in the image below Indent College Grad and Financing Rebate text Bold and Center cells B2, C2, D2 Indent the College Grad and Financing Rebate text Change the cell fill color for cells A15:D15 to light gray Enter the formulas in row 11 Amount Financed which is: Purchase Price – Down Payment – All Promotions Enter the formulas row 12 use the PMT function to calculate the Monthly Payment with: Purchase Price, Down Payment Term Months, and Yearly Rate values. PMT function was covered in Chapter 2 page E2-116 Enter the formulas row 13 Total Interest which is the total of all payments – Amount Financed Enter Savings / (Cost) in cell A14 Calculate the Savings / Cost for the \$500 and \$1000 rebate columns The Savings / Cost is the difference between the Total Interest (cell B13) and the Total Interest in columns C and D Enter a formula for the Savings / Cost (cell C14) using the correct cell reference type in the formula so it can be copied to cell D14 to produce a correct result. Copy the formula from C14 to D14. Click image to view example: (your values will be different) Format the values in rows 11 – 14 as Currency with 0 decimal places using the option to display negative numbers in red with parenthesis i.e. (\$535) Bold text and values in row 14 Save and Close the workbook.