|
|
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.
|
|
0 comments