90.2K

Verified Solution

Link Copied!

Please show formulas.

Q3 ( 2 point +1 bonus point): Use the following table to answer the questions below the table The formula in C14 is -PMT(B14/12, C13, F9). The PMT(interest rate, number of payments, loan amount) formula calculates the monthly payments required for a loan. (Note that B14 is divided by 12 in the formula in order to convert the annual interest rate to a monthly rate). Q3a (1 point): Adjust the relative and absolute addressing on B14, C13, and F9 directly in the formula in C14 so that it can be filled down and to the right, while still correctly referring to the interest rate, loan term, and loan amount appropriate to that row and column. Then fill out the rest of the table by using fill down and fill right to copy the formula in C14 to the rest of the table. Q3b (1 point): Use index function and match functions to build a monthly payment lookup tool in cell I15 for the Annual Interest Rate in cell I13 and the Loan Term in cell I14. Q3c (bonus 1 point): Build the same lookup tool as Q3b, but use vlookup and match functions instead. Q4 (2 point): Use the following table to answer the questions below the table Timing of order date first last order date year month day due date (+ lead time) delivery date days late emily jennifer james steve tom plaza jones dean smith vega 8/17/20 8/30/20 10/24/20 11/10/20 12/21/20 10/15/20 lead time 15 Q4a (1 point): Use proper functions to show the year, month, and day of the order date in cells D5:F9. Use proper functions to calculate the due dae of each order, which should be calculated as 15 days after the order date. Q4b (1 point): Th actual delivery dates for all customer orders are listed in columns H. In cells 15:19, calculate the number of days each order is late, if any. Otherwise (the order is delivered before the due date), show 0. Q3 ( 2 point +1 bonus point): Use the following table to answer the questions below the table The formula in C14 is -PMT(B14/12, C13, F9). The PMT(interest rate, number of payments, loan amount) formula calculates the monthly payments required for a loan. (Note that B14 is divided by 12 in the formula in order to convert the annual interest rate to a monthly rate). Q3a (1 point): Adjust the relative and absolute addressing on B14, C13, and F9 directly in the formula in C14 so that it can be filled down and to the right, while still correctly referring to the interest rate, loan term, and loan amount appropriate to that row and column. Then fill out the rest of the table by using fill down and fill right to copy the formula in C14 to the rest of the table. Q3b (1 point): Use index function and match functions to build a monthly payment lookup tool in cell I15 for the Annual Interest Rate in cell I13 and the Loan Term in cell I14. Q3c (bonus 1 point): Build the same lookup tool as Q3b, but use vlookup and match functions instead. Q4 (2 point): Use the following table to answer the questions below the table Timing of order date first last order date year month day due date (+ lead time) delivery date days late emily jennifer james steve tom plaza jones dean smith vega 8/17/20 8/30/20 10/24/20 11/10/20 12/21/20 10/15/20 lead time 15 Q4a (1 point): Use proper functions to show the year, month, and day of the order date in cells D5:F9. Use proper functions to calculate the due dae of each order, which should be calculated as 15 days after the order date. Q4b (1 point): Th actual delivery dates for all customer orders are listed in columns H. In cells 15:19, calculate the number of days each order is late, if any. Otherwise (the order is delivered before the due date), show 0

Answer & Explanation Solved by verified expert

See Answer