U V H W X Z AA AB AC AD Additional Money Owed Due Missed Interest Amount Owed % 7 $ 0 0 100 3% 1,000 5% 5.000 7% 10% 10,000 50,000 15% Actual Savings Goal Plan 1 Plan 2 Missed Savings Missed Savings Goal Savings Goal Plan 1 Savings Goal Income 1 $ 86,300 8,235 $ 10,954 2 $ 53,003 6,423 6,400 3 $ 87,925 6,384 16,589 4 $ 525,000 93,225 130,415 5 $ 65,488 9,402 6,412 6 $ 193.000 12,345 48,750 7 $ 29,200 2,310 259 8 $ 52,700 7,200 4.955 9 $ 96,400 18,000 19,192 10 $ 86,200 10,703 8,725 0 0 0 0 0 0 0 0 27 The Boston Savings Group assists clients in retirement planning based on income. Clients that make more money will be saving more. Your task is to analyze the total savings needed based on two savings plans, Plan 1 and Plan 2. 29 If clients do not save enough they will also miss out on interest so there will be more money owed. Clients will pay an additional percentage of the missed savings. 30 You will compare the amount owed and additional savings needed for each plan. 32 1. Open Boston Savings Group and save it as Boston Savings Group (with your name at the end). 33 2. In cell G12, write a formula to calculate the total savings amount for Plan 1 on the income in cell B12. Use the HLOOKUP function to determine the Plan 1 rate for the income in cell B12 and then multiply by the income to get the dollar amount owed. 34 3. Make sure you have used absolute references appropriately and then autofill down. 35 4. In cell H12, write a formula to calculate the total savings amount for Plan 2 on the income in cell B12. Use the HLOOKUP function to determine the Plan 2 rate for the income in cell B12 and then multiply by the income to get the dollar amount owed. 36 5. Make sure you have used absolute references appropriately and then autofill down. 37 6. In cell 112, write a formula to calculate the amount of Missed Savings based on the Actual Savings Goal (column F) and the Actual Savings (column D). This is the difference between column F and column D. 38 7. In cell J12, write a formula to calculate the amount of Missed Savings based on the Plan 1 Savings Goal (column G) and the Actual Savings (column D). This is the difference between column G and column D. 39 8. In cell K12, write a formula to calculate the amount of Missed Savings based on the Plan 2 Savings Goal (column H) and the Actual Savings (column D). This is the difference between column H and column D. 40 9. Clients that did not save enough also missed out on interest, so they will pay a percentage of the missed savings. Money in cells H3:18. Use IF, VLOOKUP and then multiply by the Missed Savings in column I. 41 In cell L12, write a formula to calculate the Actual Additional Money Needed IF the Missed Savings in 112 is >0. You can look up the Additional 42 10. Autofill L12 across to N12 and down to N21. 43 11. Go to the Customer Addresses sheet. 44 12. Type a customer ID (1-10) in cell B3. 45 13. In cell C3, use INDEX combined with MATCH to return the street for the ID entered in B3. 46 14. Make sure you have used absolute references appropriately. Since you are not copying this formula down you do not need any absolute references on the row references. Now, autofill from C3 to F3. A 1 2 Base Income $ 3 Plan 1 4 Plan 2 5 6 7 8 9 10 11 =22***+*2850******885 : 12 13 14 15 16 17 18 19 21 23 24 26 31 ID # B D E Savings Rate for Plan 1 and Plan 2 F 0 $ 30,000 $ 50,000 $80,000 $ 180,000 0.0% 5.0% 9.0% 14.0% 22.0% 26.0% 0.0% 6.0% 10.0% 16.0% Actual Savings $ G J K Plan 2 Missed Savings Goal L M N Actual Additiona| Additiona| Additiona I Mones I Money I Money Needed Plan 1 Plan 2 0 P Q R S T