Grader. Instructions Excel 2019 Project Excel Capstone Part 2 v21.3 Project Description: Developed by for PC and Mac Steps to Perform: Step Instructions 1 Whenever using Excel, you should strive for the best possible formulas. This means only using brackets when required and using absolute cell references only when necessary, typically when making copies of a formula, filling right or filling down. If your formula produces the correct answer, but you don't receive full marks, use View Submissions to check your formulas and change them or be prepared to argue why your formula is as good or better than the one used in MyLab Start Excel and open the downloaded document excel_capstone_part_2.xlsx On the Table sheet, format the Salary column as Currency with no decimal places. 2 Convert all of the data to a table with headers. 3 4 Add the Total Row to the table (salaries will automatically be totalled). In the Total Row use the best technique to create an average for the Age column. Points Possible 2 Perform a two-level sort, first by Last Name A to Z and then by First Name A to 4 2 5 Adjust all columns to the ideal widths. Extra step for Macs only: Change the width of column C to 21.33 and change the width of column G to 14.50. 6 Filter the table so that only people from Alberta and British Columbia are displayed. You should see six records and totals on row 32. This is the last step for the Table sheet and you can now move on to the next sheet. 6 On the Vlookup sheet, select cells CII through E17 and name the cells discount 3 In D2, using the range you named in the previous question, insert a vlookup formula that will display the Discount % based on the Amount Spent. Use the fill handle to fill down to D9. In E2 create a formula that displays Phil's discount as a dollar amount, $6.25, for 12 example. Fill the formula down to E9. In F2 create a formula that subtracts the discount amount from the amount spent. Phil's will be about $119. Fill the formula down to F9. In G2 insert a vlookup formula that will display the customer status (Gold, Silver, Bronze, etc.) based on the original Amount Spent. Use the fill handle to fill down to G9. Then move on to the next sheet. 10 On the Pmt sheet, create a formula in B4 to calculate the monthly loan payment 4 based on the cells B1, B2, and B3. Make sure to add a minus sign after the equal sign so that the result displays as a positive number. The result should be about $700. In cells B6 through j23 build a two-variable Data Table. 12 Format cells C7 through 37 as Accounting with two decimal places. Format cells 2 C8 through 123 as Comma with two decimal places. Move on to the next sheet. 13 On the Goal Seek sheet, Carmen is currently investing $125 per week. Her goal 4 is to retire in 45 years with one million dollars in the bank. Use Goal Seek to determine how much Carmen will have to invest per week to achieve her goal. Move on to the next sheet. 14 On the Loan sheet, in cell D10 use PMT to calculate the weekly payment. Add a 4 minus sign after the equal sign so that the result displays as a positive number. 15 In E10 create a formula that calculates the weekly interest. Add a minus sign 4 after the equal sign so that the result displays as a positive number. 16 In F10 create a formula that calculates the weekly principal. Make sure that the 3 result displays as a positive number.