Exercise 1: Analyzing Emergency Student Loans The Student Assistance office at your school provides emergency loans at simple interest. The data obtained from six types of loans and the desired report format arc shown in Table 2-9. The required formulas are shown in Table 2-10. Use the concepts and techniques presented in this chapter to create and format the worksheet. Include total, average, maximum, and minimum values for Principal, Interest, and Amount Due. Use conditional formatting to change the formatting to white font on a red background in any cell in of the Amount Due that contains a value greater than 20000. Create a 3-D Clustered Bar chart with the Amount Due of each Loan Type. Table 2-9 Emergency Student Loan Data and Worksheet Layout Time in Years Rate 7,5% 12% 33 Loan Type Academic Supplies Medical Emergency Personal Emergency Room and Board Travel Expenses Tuition Reimbursement Principal $40,000 $25,500 $12.750 $27,000 $4,550 $107,000 200 Table 2-10 Emergency Student Loan Formulas Interest = Principal X Rate x Time Amount Due = Principal + interest Average = AVERAGE function Minimum = MIN function Maximum - MAX function Amount Due = Principal + Interest Change the name of the sheet tab to M2-Exercis Excel Practical Exercise Module 2 Note: Open a workbook and save as EX-PE2-YourName. Each exercise in a worksheet Tab. Exercise 1: Analyzing Emergency Student Loans The Student Assistance office at your school provides emergency loans at simple interest. The data obtained from six types of loans and the desired report format arc shown in Table 2-9. The required formulas are shown in Table 2-10. Use the concepts and techniques presented in this chapter to create and format the worksheet. Include total, average, maximum, and minimum values for Principal, Interest, and Amount Due. Use conditional formatting to change the formatting to white font on a red background in any cell in of the Amount Due that contains a value greater than 20000. Create a 3-D Clustered Bar chart with the Amount Due of each Loan Type. Table 2-9 Emergency Student Loan Data and Worksheet Layout Loan Type Principal Rate Time in Years 12% 33 Academic Supplies Medical Emergency Personal Emergency Room and Board Travel Expenses Tuition Reimbursement 540,000 $25,500 $12,750 $27,000 $4,550 $107,000 6.5% Table 2-10 Emergency Student Loan Formulas Interest = Principal X Rate x Time Amount Due = Principal + interest Average = AVERAGE function Minimum = MN function Maximum - MAX function Change the name of the sheet tab to M2-Exercise Exercise 1: Analyzing Emergency Student Loans The Student Assistance office at your school provides emergency loans at simple interest. The data obtained from six types of loans and the desired report format arc shown in Table 2-9. The required formulas are shown in Table 2-10. Use the concepts and techniques presented in this chapter to create and format the worksheet. Include total, average, maximum, and minimum values for Principal, Interest, and Amount Due. Use conditional formatting to change the formatting to white font on a red background in any cell in of the Amount Due that contains a value greater than 20000. Create a 3-D Clustered Bar chart with the Amount Due of each Loan Type. Table 2-9 Emergency Student Loan Data and Worksheet Layout Time in Years Rate 7,5% 12% 33 Loan Type Academic Supplies Medical Emergency Personal Emergency Room and Board Travel Expenses Tuition Reimbursement Principal $40,000 $25,500 $12.750 $27,000 $4,550 $107,000 200 Table 2-10 Emergency Student Loan Formulas Interest = Principal X Rate x Time Amount Due = Principal + interest Average = AVERAGE function Minimum = MIN function Maximum - MAX function Amount Due = Principal + Interest Change the name of the sheet tab to M2-Exercis Excel Practical Exercise Module 2 Note: Open a workbook and save as EX-PE2-YourName. Each exercise in a worksheet Tab. Exercise 1: Analyzing Emergency Student Loans The Student Assistance office at your school provides emergency loans at simple interest. The data obtained from six types of loans and the desired report format arc shown in Table 2-9. The required formulas are shown in Table 2-10. Use the concepts and techniques presented in this chapter to create and format the worksheet. Include total, average, maximum, and minimum values for Principal, Interest, and Amount Due. Use conditional formatting to change the formatting to white font on a red background in any cell in of the Amount Due that contains a value greater than 20000. Create a 3-D Clustered Bar chart with the Amount Due of each Loan Type. Table 2-9 Emergency Student Loan Data and Worksheet Layout Loan Type Principal Rate Time in Years 12% 33 Academic Supplies Medical Emergency Personal Emergency Room and Board Travel Expenses Tuition Reimbursement 540,000 $25,500 $12,750 $27,000 $4,550 $107,000 6.5% Table 2-10 Emergency Student Loan Formulas Interest = Principal X Rate x Time Amount Due = Principal + interest Average = AVERAGE function Minimum = MN function Maximum - MAX function Change the name of the sheet tab to M2-Exercise