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
EnterSavings / (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)
