02 23:27 3:27 Determine loan decision: Use functions we have used in the tutorials (2 mark) The loun decision must be "Approved if the approval category is "Low risk". "Medium risk or "Caution", otherwise, the loan decision must be "Declined". Jackie's approval category is "Low risk (3) therefore 13 should display "Approved Write a formula that will display the correct loan decision Interest rate: 03 L3:27 You must use VLOOKUP Jackie is "Low risk" (K3). therefore her interest rate is 6% (2 marks) The interest rates for the corresponding category (c.g., "Low risk) are listed in the Interest rates table (09:R12). Define 09:R12 as a Named Range called "Interest rates and use this in your VLOOKUP Based on the approval category from column K. determine the correct interest rate 04 Where there is no interest rate, the field should be empty M3 M27 Monthly interest payable: Refers to the amount of interest payable on the loan monthly Use functions we have used in the tutorials (1 marks) Jackie's Purchase price is $339,18 and her deposit is $230,280 and her Interest rate is 6% After dividing by 12 months, her monthly interest is S64452 It is a formula that involves the Purchase price (F3) minus the deposit (G3), which is multiplied by the Interest rate (L3). This should also be adjusted to be a monthly figure. QS Loans approved: NA Use functions we have used in the Tutorials (! mark) In R16, write a formula that displays how many loans have the decision of "Approved In R17 write a formula that displays the percentage of the total loan applications that are "Low risk Ensure that this formula will be automatically updated if data elsewhere in the workbook changes (i.e. use the appropriate functions to do the calculation) B D G H M O L Interest rate AppliD Monthly interest 1 2 3 4 5 6 7 8 R CRITERIA Category-> Low risk Medium risk Coutio Minimum age 18 18 TE 65 Minimum credit score 900 800 700 N.B.: Approximate repayment (monthly) must be less than 50% of applicants' monthly incomes for all approved applicants Interest rates Category Low risk Medium risk Coution Rate 6% 7% 8% 9 10 11 4 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 12 DOB ncome (monthly Credit score Name Jackie 26/11/1965 5 9,035.75 973 S John 06/04/1992 $ 12.372.50 796 $ Suz 20/06/1959 $ 9.163.83 923 5 Xiao 05/01/1982 S 9,774.92 794 5 Peter 28/02/1987 $ 10,950.08 875 $ Frank 21/04/1991 S 12,252.58 881 Naomi 11/10/1980 $ 9,141.50 929 $ Murtry 09/01/1981 $ 10.114.58 968 $ Amos 07/10/2004 $ 10,794.17 870 $ Julie 04/11/1953 S 8,560,00 742 S Cornelius 17/04/1982 $ 10,325.17 850 S Morrier 21/08/2006 $ 8,715.08 807 S Franklin 21/03/1994 S 9,237.67 866 S James 21/02/1992 $ 21/02/1992 $ 10,076.83 817 s Manson 14/10/1991 $ 12,308.67 891 S Lacy 15/03/1978 $ 10,386.42 732 $ Melissa 16/02/2006 S 6,884.25 718 S Naomi 28/06/2006 $ 9,333.50 808 5 Jackson 17/07/1990 $ 9,820.92 995 $ Jo 16/10/1993 $ 10,669.25 813 5 Anna 04/05/1974 $ 12.240.92 823 S Jose 18/01/1960 $ 8,639.08 915 $ Keith 27/06/2007 $ 6,968.17 841 S Jerry 17/09/2006 $ 10.542.33 738 S Elaine 11/07/1995 $ 7465.75 1000 $ N Monthly repet $4,159.21 $3,829.91 $3,275.71 $4,004.15 $3,894.61 $3,804.94 $2,831.93 $3,899.67 $3,851.07 $4,233.56 $4,542.79 $3,656.17 $4,008.00 $3,349.11 $4,302.54 $ $4,664.05 $4,807,28 $4,427.21 $4,569.51 $5,414.54 $3,331.90 $2,552.45 $3,303.12 $3,904.54 $4,305.29 Purchase price Deposit Employment Hired Finished toan decision Approval category 359,183.00 $ 230,280.00 21/03/2017 Low risk 469,192.00 $ 73,600.00 12/05/2014 471,269.00 $ 42,980.00 28/09/2008 18/08/2018 Low risk 421,626.00 $ 145,860.00 09/10/2007 454,682.00 $ 97,280.00 28/11/2013 13/11/2019 Medium risk 381,844.00 $ 157,410.00 18/07/2006 12/10/2021 Medium risk 308,874.00 $ 92,480.00 14/04/2014 09/06/2018 Low risk 323,880.00 $ 228,800.00 04/10/2007 Low risk 346,291.00 $ 199,500.00 26/08/2013 400,000.00 $ 200,000.00 21/03/2015 Caution 448,225.00 $ 195,600.00 25/02/2017 Medium risk 365,920.00 $ 152,250.00 18/11/2007 321,072.00 $ 246,960.00 10/01/2008 Medium risk 411,471.00 $ 63,180.00 02/04/2008 Medium risk 384,816.00 $ 224,960.00 19/12/2020 Medium risk 467,811.00 $ 193,200.00 13/03/2019 487,300.00 $ 194,010.00 10/09/2012 347,445.00 $ 280,000.00 12/03/2017 399,452.00 S 248,160.00 27/09/2010 Low risk 496,533.00 S 270,840.00 09/07/2012 28/03/2014 Medium risk 349,852.00 $ 122,360.00 25/08/2014 Medium risk 304,025.00 $ 57,720.00 10/11/2018 Low risk 422,384.00 $ 45,750.00 20/04/2012 466,069.00 $ 87,300.00 $ 87,300.00 28/05/2019 10/11/2015 404,715.00 $ 205,450.00 23/05/2013 24/05/2018 Low risk 13 14 STATISTICS Loans approved Total Low risk applications (%) 15 16 17 18 19 20 21 22 23 24 25 26 27 02 23:27 3:27 Determine loan decision: Use functions we have used in the tutorials (2 mark) The loun decision must be "Approved if the approval category is "Low risk". "Medium risk or "Caution", otherwise, the loan decision must be "Declined". Jackie's approval category is "Low risk (3) therefore 13 should display "Approved Write a formula that will display the correct loan decision Interest rate: 03 L3:27 You must use VLOOKUP Jackie is "Low risk" (K3). therefore her interest rate is 6% (2 marks) The interest rates for the corresponding category (c.g., "Low risk) are listed in the Interest rates table (09:R12). Define 09:R12 as a Named Range called "Interest rates and use this in your VLOOKUP Based on the approval category from column K. determine the correct interest rate 04 Where there is no interest rate, the field should be empty M3 M27 Monthly interest payable: Refers to the amount of interest payable on the loan monthly Use functions we have used in the tutorials (1 marks) Jackie's Purchase price is $339,18 and her deposit is $230,280 and her Interest rate is 6% After dividing by 12 months, her monthly interest is S64452 It is a formula that involves the Purchase price (F3) minus the deposit (G3), which is multiplied by the Interest rate (L3). This should also be adjusted to be a monthly figure. QS Loans approved: NA Use functions we have used in the Tutorials (! mark) In R16, write a formula that displays how many loans have the decision of "Approved In R17 write a formula that displays the percentage of the total loan applications that are "Low risk Ensure that this formula will be automatically updated if data elsewhere in the workbook changes (i.e. use the appropriate functions to do the calculation) B D G H M O L Interest rate AppliD Monthly interest 1 2 3 4 5 6 7 8 R CRITERIA Category-> Low risk Medium risk Coutio Minimum age 18 18 TE 65 Minimum credit score 900 800 700 N.B.: Approximate repayment (monthly) must be less than 50% of applicants' monthly incomes for all approved applicants Interest rates Category Low risk Medium risk Coution Rate 6% 7% 8% 9 10 11 4 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 12 DOB ncome (monthly Credit score Name Jackie 26/11/1965 5 9,035.75 973 S John 06/04/1992 $ 12.372.50 796 $ Suz 20/06/1959 $ 9.163.83 923 5 Xiao 05/01/1982 S 9,774.92 794 5 Peter 28/02/1987 $ 10,950.08 875 $ Frank 21/04/1991 S 12,252.58 881 Naomi 11/10/1980 $ 9,141.50 929 $ Murtry 09/01/1981 $ 10.114.58 968 $ Amos 07/10/2004 $ 10,794.17 870 $ Julie 04/11/1953 S 8,560,00 742 S Cornelius 17/04/1982 $ 10,325.17 850 S Morrier 21/08/2006 $ 8,715.08 807 S Franklin 21/03/1994 S 9,237.67 866 S James 21/02/1992 $ 21/02/1992 $ 10,076.83 817 s Manson 14/10/1991 $ 12,308.67 891 S Lacy 15/03/1978 $ 10,386.42 732 $ Melissa 16/02/2006 S 6,884.25 718 S Naomi 28/06/2006 $ 9,333.50 808 5 Jackson 17/07/1990 $ 9,820.92 995 $ Jo 16/10/1993 $ 10,669.25 813 5 Anna 04/05/1974 $ 12.240.92 823 S Jose 18/01/1960 $ 8,639.08 915 $ Keith 27/06/2007 $ 6,968.17 841 S Jerry 17/09/2006 $ 10.542.33 738 S Elaine 11/07/1995 $ 7465.75 1000 $ N Monthly repet $4,159.21 $3,829.91 $3,275.71 $4,004.15 $3,894.61 $3,804.94 $2,831.93 $3,899.67 $3,851.07 $4,233.56 $4,542.79 $3,656.17 $4,008.00 $3,349.11 $4,302.54 $ $4,664.05 $4,807,28 $4,427.21 $4,569.51 $5,414.54 $3,331.90 $2,552.45 $3,303.12 $3,904.54 $4,305.29 Purchase price Deposit Employment Hired Finished toan decision Approval category 359,183.00 $ 230,280.00 21/03/2017 Low risk 469,192.00 $ 73,600.00 12/05/2014 471,269.00 $ 42,980.00 28/09/2008 18/08/2018 Low risk 421,626.00 $ 145,860.00 09/10/2007 454,682.00 $ 97,280.00 28/11/2013 13/11/2019 Medium risk 381,844.00 $ 157,410.00 18/07/2006 12/10/2021 Medium risk 308,874.00 $ 92,480.00 14/04/2014 09/06/2018 Low risk 323,880.00 $ 228,800.00 04/10/2007 Low risk 346,291.00 $ 199,500.00 26/08/2013 400,000.00 $ 200,000.00 21/03/2015 Caution 448,225.00 $ 195,600.00 25/02/2017 Medium risk 365,920.00 $ 152,250.00 18/11/2007 321,072.00 $ 246,960.00 10/01/2008 Medium risk 411,471.00 $ 63,180.00 02/04/2008 Medium risk 384,816.00 $ 224,960.00 19/12/2020 Medium risk 467,811.00 $ 193,200.00 13/03/2019 487,300.00 $ 194,010.00 10/09/2012 347,445.00 $ 280,000.00 12/03/2017 399,452.00 S 248,160.00 27/09/2010 Low risk 496,533.00 S 270,840.00 09/07/2012 28/03/2014 Medium risk 349,852.00 $ 122,360.00 25/08/2014 Medium risk 304,025.00 $ 57,720.00 10/11/2018 Low risk 422,384.00 $ 45,750.00 20/04/2012 466,069.00 $ 87,300.00 $ 87,300.00 28/05/2019 10/11/2015 404,715.00 $ 205,450.00 23/05/2013 24/05/2018 Low risk 13 14 STATISTICS Loans approved Total Low risk applications (%) 15 16 17 18 19 20 21 22 23 24 25 26 27