I need help finding what indicated criteria means or what formula I need to enter into cell D29 on the second worksheet. Also, if someone could explain the rest to me that would be so appreciated, but if not, just the first portion would be amazing!
I
CIS 105 - Excel Ch11 HW Excel Ch11 HW Your first assignment for Artichoke Bank is to analyze consumer loan data. You will display selected loans, those with a loan type of A, from the comprehensive set of loan records. Your task is to finish the partially completed worksheet and the associated Pivot Table report. a. Open Excel_Ch11HW.xlsx and save it as Excel_Ch11HW_LastNameFirstName. b. Click cell H4, the cell containing the ending date for the first loan. Enter the formula to compute the ending date, based on the starting date and the term of the loan. For the sake of simplicity, you don't have to account for leap year. To compute the ending date, multiply the term of the loan by 365 and add that result to the starting date. Be sure to format the starting and ending dates to show a date format and widen columns as necessary. C. Click cell 14 and enter the PMT function to compute the monthly payment for the first loan. Copy the formulas in cells H4 and 14 to the remaining rows in the worksheet. d. Name your database range Loan_Database (A3 through 126) and name your criteria range Loan_Criteria (A28 through 129). e. Enter the indicated criteria in cell D29. Then enter the indicated database functions into cells C32, C33, C34, H32, H33, and H34. Use the Advanced Filter command to filter the list, in-place (use Loan_Database as your list range and Loan_Criteria as your criteria range) to display only those loans that satisfy the indicated criteria as shown in the Figure below. Format the list as closely as possible to the image in the Figure. FILE DATA REVIEW VIEW DEVELOPER General HOME INSERT PAGE LAYOUT FORMULAS X Cut MS Sans Serit -10 - AA La Copy- BIU- Format Painter Clipboard Font Paste 2 Wrap Text = Merge & Center - Alignment $. % Conditional Format as Formatting Table St Styles Number A2 f A B c D E H F Analysis of Consumer Loans 1 2 2 3 Loan ID 9 L0006 10 L0007 12 L0009 14 L0011 16 L0013 23 L0020 24 L0021 27 Branch North Miami North Miami Opa Locka Kendall Kendall Kendall Opa Locke Loan Amount Loan Type $35.0001A $10.000 A $25,000 A $56.000 A $35.000 A $25.000 A $41.0001A Interest Rate 5.70% 4.00% 8.50% 6.00% 7.50% 7.00% 8.40% Term 4 3 3 3 Start Date July 26. 2004 July 28, 2004 August 4, 2004 August 6. 2004 August 14, 2004 September 18, 2004 September 21, 2004 End Date July 25. 2008 July 28, 2007 August 4, 2007 August 6. 2007 August 14, 2007 September 17, 2008 September 21.2007 Monthly Payment $817.17 $295.24 $78919 $1.703.63 $1,088.72 $598 66 $1.292.37 3 4 3 Loan Interest Rate Amount Monthly Payment Term Loan Type A End Date Start Date 28 Loan ID Branch 29 30 31 Summary Statistics 32 Number of Loans 33 Average loan amount 34 Total loan amount 35 7 $32.429 $227.000 Largest loan Average interest rate Average term $56,000 6.73% 3.29 B C E 1 Analysis of Consumer Loans 2 Term 4 3 Loan ID 9 L0006 10 L0007 12 L0009 14 L0011 16 L0013 23 L0020 24 L0021 27 Branch North Miami North Miami Opa Locka Kendall Kendall Kendall Opa Locka Loan Amount Loan Type $35.000 A $10,000 A $25,000 A $56.000 A $35.000 A $25,000 A $41.000 A 3 Interest Rate 5.70% 4.00% 8.50% 6.00% 7.50% 7.00% 8.40% Start Date July 26, 2004 July 28, 2004 August 4, 2004 August 6. 2004 August 14, 2004 September 18, 2004 September 21, 2004 Monthly End Date Payment July 25, 2008 $817.17 July 28, 2007 $295.24 August 4, 2007 $789.19 August 6. 2007 $1.703.63 August 14, 2007 $1.088.72 September 17, 2008 $598,66 September 21, 2007 $1.292.37 3 3 4 Loan Amount Interest Rate Monthly Payment Term Start Date Loan Type End Date 28 Loan ID Branch 29 30 31 Summary Statistics 32 Number of Loans 33 Average loan amount 34 Total loan amount 35 7 $32,429 $227,000 Largest loan Average interest rate Average term $56,000 6.73% 3.29 Pivot Table Loans 1 CIS 105 - Excel Ch11 HW f. Look closely at the bottom of the Figure and note the presence of a PivotTable worksheet. Create a Pivottable that has the loan type and branch in the row and column fields, respectively. Your Pivot Table is to contain two data fields, the total amount of the loans and the average interest rate. Format the PivotTable report fields as currency with O decimals and percentage with 2 decimals. Add Summary Loan Statistics as the title of the report. Rename the Sheet1 tab as Pivottable. g. Create a custom footer containing your name in the left section, the name of the worksheet in the center section, and today's date in the right section. Be sure to print gridlines and row and column headings, making sure the Loans worksheet and the PivotTable would each print on one page. Save your workbook and upload it to Canvas. e. Enter the indicated criteria in cell D29. Then enter the indicated database functions into cells C32, C33, C34, H32, H33, and H34. Use the Advanced Filter command to filter the list, in-place (use Loan_Database as your list range and Loan_Criteria as your criteria range) to display only those loans that satisfy the indicated criteria as shown in the Figure below. Format the list as closely as possible to the image in the Figure. FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER MS Sans Serif - 10-AA we Wrap Text General * Cut Ee Copy Format Painter Paste BIU- A EES E Merge & Center $ % 48: Conditional Format as Formatting Table St Styles Clipboard Font Alignment Number A2 fx A B D E F G H J 1 Analysis of Consumer Loans 2 Term 4 3 Loan ID 9 L0006 10 L0007 12 L0009 14 L0011 16 L0013 23 L0020 24 L0021 27 Branch North Miami North Miami Opa Locka Kendall Kendall Kendall Opa Locka Loan Amount Loan Type $35.000 A $10,000 A $25,000 A $56,000A $35.000 A $25.000 A $41.000 A 3 3 Interest Rate 5.70% 4.00% 8.50% 6.00% 7.50% 7.00% 8.40% Start Date July 26, 2004 July 28, 2004 August 4, 2004 August 6, 2004 August 14, 2004 September 18, 2004 September 21, 2004 Monthly End Date Payment July 25, 2008 $817.17 July 28, 2007 $295.24 August 4, 2007 $789.19 August 6. 2007 $1,703.63 August 14, 2007 $1,088.72 September 17, 2008 $598.66 September 21, 2007 $1.292.37 3 3 4 3 Loan Amount Loan Type Interest Rate Monthly Payment Term Start Date End Date 28 Loan ID Branch 29 30 31 Summary Statistics 32 Number of Loans 33 Average loan amount 34 Total loan amount 35 7 $32,429 $227,000 Largest loan Average interest rate Average term $56,000 6.73% 3.29 Pivot Table Loans + 1 B D E F G H 1 J 1 Analysis of Consumer Loans 2 3 Loan ID Branch Loan Amour Loan Type Interest Ra Term Start Date End Date Monthly Payment 4 L0001 Opa Locka 525000 M 0.05 30 38179 49129 ($2,818.31) 5 L0002 Kendall 10500 P 0.06 5 38179 40004 ($202.99) 6 L0003 North Miami 475000 M 0.054 15 38179 43654 ($3,855.99) 7 L0004 Kendall 35000 P 0.05 5 38179 40004 ($660.49) 8 L0005 Opa Locka 250000 M 0.073 30 38179 49129 ($1,713.93) 9 L0006 North Miami 35000 A 0.057 4 38179 39639 ($817.17) 10 L0007 North Miami 10000 A 0.04 3 38179 39274 ($295.24) 11 L0008 North Miami 12000 P 0.08 10 38179 41829 ($145.59) 12 L0009 Opa Locka 25000 A 0.085 3 38179 39274 ($789.19) 13 L0010 North Miami 20000 P 0.08 5 38179 40004 ($405.53) 14 L0011 Kendall 56000 A 0.06 3 38179 39274 ($1,703.63) 15 L0012 Kendall 50000 M 0.07 15 38179 43654 ($449.41) 16 L0013 Kendall 35000 A 0.075 3 38179 39274 ($1,088.72) 17 L0014 Kendall 150000 M 0.06 15 38179 43654 ($1,265.79) 18 L0015 Kendall 75000 M 0.055 30 38179 49129 ($425.84) 19 L0016 Opa Locka 200000 M 0.065 15 38179 43654 ($1,742.21) 20 L0017 Kendall 15000 P 0.065 5 38179 40004 ($293.49) 21 L0018 North Miami 350000 M 0.067 30 38179 49129 ($2,258.47) 22 L0019 Kendall 275000 M 0.077 15 38179 43654 ($2,580.64) 23 L0020 Kendall 25000 A 0.07 4 38179 39639 ($598.66) 24 L0021 Opa Locka 41000 A 0.084 3 38179 39274 ($1,292.37) 25 L0022 Kendall 75000 M 0.06 15 38179 43654 ($632.89) 26 L0023 Kendall 15000 P 0.05 3 38179 39274 ($449.56) 27 28 Loan ID Branch Loan Amour Loan Type Interest Ra Term Start Date End Date Monthly Payment 29 30 31 Summary Statistics 32 Number of Loans Largest loan 33 Average loan amount Average interest rate 34 Total loan amount Average term 35 CIS 105 - Excel Ch11 HW Excel Ch11 HW Your first assignment for Artichoke Bank is to analyze consumer loan data. You will display selected loans, those with a loan type of A, from the comprehensive set of loan records. Your task is to finish the partially completed worksheet and the associated Pivot Table report. a. Open Excel_Ch11HW.xlsx and save it as Excel_Ch11HW_LastNameFirstName. b. Click cell H4, the cell containing the ending date for the first loan. Enter the formula to compute the ending date, based on the starting date and the term of the loan. For the sake of simplicity, you don't have to account for leap year. To compute the ending date, multiply the term of the loan by 365 and add that result to the starting date. Be sure to format the starting and ending dates to show a date format and widen columns as necessary. C. Click cell 14 and enter the PMT function to compute the monthly payment for the first loan. Copy the formulas in cells H4 and 14 to the remaining rows in the worksheet. d. Name your database range Loan_Database (A3 through 126) and name your criteria range Loan_Criteria (A28 through 129). e. Enter the indicated criteria in cell D29. Then enter the indicated database functions into cells C32, C33, C34, H32, H33, and H34. Use the Advanced Filter command to filter the list, in-place (use Loan_Database as your list range and Loan_Criteria as your criteria range) to display only those loans that satisfy the indicated criteria as shown in the Figure below. Format the list as closely as possible to the image in the Figure. FILE DATA REVIEW VIEW DEVELOPER General HOME INSERT PAGE LAYOUT FORMULAS X Cut MS Sans Serit -10 - AA La Copy- BIU- Format Painter Clipboard Font Paste 2 Wrap Text = Merge & Center - Alignment $. % Conditional Format as Formatting Table St Styles Number A2 f A B c D E H F Analysis of Consumer Loans 1 2 2 3 Loan ID 9 L0006 10 L0007 12 L0009 14 L0011 16 L0013 23 L0020 24 L0021 27 Branch North Miami North Miami Opa Locka Kendall Kendall Kendall Opa Locke Loan Amount Loan Type $35.0001A $10.000 A $25,000 A $56.000 A $35.000 A $25.000 A $41.0001A Interest Rate 5.70% 4.00% 8.50% 6.00% 7.50% 7.00% 8.40% Term 4 3 3 3 Start Date July 26. 2004 July 28, 2004 August 4, 2004 August 6. 2004 August 14, 2004 September 18, 2004 September 21, 2004 End Date July 25. 2008 July 28, 2007 August 4, 2007 August 6. 2007 August 14, 2007 September 17, 2008 September 21.2007 Monthly Payment $817.17 $295.24 $78919 $1.703.63 $1,088.72 $598 66 $1.292.37 3 4 3 Loan Interest Rate Amount Monthly Payment Term Loan Type A End Date Start Date 28 Loan ID Branch 29 30 31 Summary Statistics 32 Number of Loans 33 Average loan amount 34 Total loan amount 35 7 $32.429 $227.000 Largest loan Average interest rate Average term $56,000 6.73% 3.29 B C E 1 Analysis of Consumer Loans 2 Term 4 3 Loan ID 9 L0006 10 L0007 12 L0009 14 L0011 16 L0013 23 L0020 24 L0021 27 Branch North Miami North Miami Opa Locka Kendall Kendall Kendall Opa Locka Loan Amount Loan Type $35.000 A $10,000 A $25,000 A $56.000 A $35.000 A $25,000 A $41.000 A 3 Interest Rate 5.70% 4.00% 8.50% 6.00% 7.50% 7.00% 8.40% Start Date July 26, 2004 July 28, 2004 August 4, 2004 August 6. 2004 August 14, 2004 September 18, 2004 September 21, 2004 Monthly End Date Payment July 25, 2008 $817.17 July 28, 2007 $295.24 August 4, 2007 $789.19 August 6. 2007 $1.703.63 August 14, 2007 $1.088.72 September 17, 2008 $598,66 September 21, 2007 $1.292.37 3 3 4 Loan Amount Interest Rate Monthly Payment Term Start Date Loan Type End Date 28 Loan ID Branch 29 30 31 Summary Statistics 32 Number of Loans 33 Average loan amount 34 Total loan amount 35 7 $32,429 $227,000 Largest loan Average interest rate Average term $56,000 6.73% 3.29 Pivot Table Loans 1 CIS 105 - Excel Ch11 HW f. Look closely at the bottom of the Figure and note the presence of a PivotTable worksheet. Create a Pivottable that has the loan type and branch in the row and column fields, respectively. Your Pivot Table is to contain two data fields, the total amount of the loans and the average interest rate. Format the PivotTable report fields as currency with O decimals and percentage with 2 decimals. Add Summary Loan Statistics as the title of the report. Rename the Sheet1 tab as Pivottable. g. Create a custom footer containing your name in the left section, the name of the worksheet in the center section, and today's date in the right section. Be sure to print gridlines and row and column headings, making sure the Loans worksheet and the PivotTable would each print on one page. Save your workbook and upload it to Canvas. e. Enter the indicated criteria in cell D29. Then enter the indicated database functions into cells C32, C33, C34, H32, H33, and H34. Use the Advanced Filter command to filter the list, in-place (use Loan_Database as your list range and Loan_Criteria as your criteria range) to display only those loans that satisfy the indicated criteria as shown in the Figure below. Format the list as closely as possible to the image in the Figure. FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW DEVELOPER MS Sans Serif - 10-AA we Wrap Text General * Cut Ee Copy Format Painter Paste BIU- A EES E Merge & Center $ % 48: Conditional Format as Formatting Table St Styles Clipboard Font Alignment Number A2 fx A B D E F G H J 1 Analysis of Consumer Loans 2 Term 4 3 Loan ID 9 L0006 10 L0007 12 L0009 14 L0011 16 L0013 23 L0020 24 L0021 27 Branch North Miami North Miami Opa Locka Kendall Kendall Kendall Opa Locka Loan Amount Loan Type $35.000 A $10,000 A $25,000 A $56,000A $35.000 A $25.000 A $41.000 A 3 3 Interest Rate 5.70% 4.00% 8.50% 6.00% 7.50% 7.00% 8.40% Start Date July 26, 2004 July 28, 2004 August 4, 2004 August 6, 2004 August 14, 2004 September 18, 2004 September 21, 2004 Monthly End Date Payment July 25, 2008 $817.17 July 28, 2007 $295.24 August 4, 2007 $789.19 August 6. 2007 $1,703.63 August 14, 2007 $1,088.72 September 17, 2008 $598.66 September 21, 2007 $1.292.37 3 3 4 3 Loan Amount Loan Type Interest Rate Monthly Payment Term Start Date End Date 28 Loan ID Branch 29 30 31 Summary Statistics 32 Number of Loans 33 Average loan amount 34 Total loan amount 35 7 $32,429 $227,000 Largest loan Average interest rate Average term $56,000 6.73% 3.29 Pivot Table Loans + 1 B D E F G H 1 J 1 Analysis of Consumer Loans 2 3 Loan ID Branch Loan Amour Loan Type Interest Ra Term Start Date End Date Monthly Payment 4 L0001 Opa Locka 525000 M 0.05 30 38179 49129 ($2,818.31) 5 L0002 Kendall 10500 P 0.06 5 38179 40004 ($202.99) 6 L0003 North Miami 475000 M 0.054 15 38179 43654 ($3,855.99) 7 L0004 Kendall 35000 P 0.05 5 38179 40004 ($660.49) 8 L0005 Opa Locka 250000 M 0.073 30 38179 49129 ($1,713.93) 9 L0006 North Miami 35000 A 0.057 4 38179 39639 ($817.17) 10 L0007 North Miami 10000 A 0.04 3 38179 39274 ($295.24) 11 L0008 North Miami 12000 P 0.08 10 38179 41829 ($145.59) 12 L0009 Opa Locka 25000 A 0.085 3 38179 39274 ($789.19) 13 L0010 North Miami 20000 P 0.08 5 38179 40004 ($405.53) 14 L0011 Kendall 56000 A 0.06 3 38179 39274 ($1,703.63) 15 L0012 Kendall 50000 M 0.07 15 38179 43654 ($449.41) 16 L0013 Kendall 35000 A 0.075 3 38179 39274 ($1,088.72) 17 L0014 Kendall 150000 M 0.06 15 38179 43654 ($1,265.79) 18 L0015 Kendall 75000 M 0.055 30 38179 49129 ($425.84) 19 L0016 Opa Locka 200000 M 0.065 15 38179 43654 ($1,742.21) 20 L0017 Kendall 15000 P 0.065 5 38179 40004 ($293.49) 21 L0018 North Miami 350000 M 0.067 30 38179 49129 ($2,258.47) 22 L0019 Kendall 275000 M 0.077 15 38179 43654 ($2,580.64) 23 L0020 Kendall 25000 A 0.07 4 38179 39639 ($598.66) 24 L0021 Opa Locka 41000 A 0.084 3 38179 39274 ($1,292.37) 25 L0022 Kendall 75000 M 0.06 15 38179 43654 ($632.89) 26 L0023 Kendall 15000 P 0.05 3 38179 39274 ($449.56) 27 28 Loan ID Branch Loan Amour Loan Type Interest Ra Term Start Date End Date Monthly Payment 29 30 31 Summary Statistics 32 Number of Loans Largest loan 33 Average loan amount Average interest rate 34 Total loan amount Average term 35
