$4.$ Insert a Vlookup function in cell C$5$ to display the ring cost for the first student.

$5.$ Copy the function from cell C$5$ down through C$11$ to complete column C$.$ Ensure the appropriate cells are locked or else the formula will not autofill properly. Glance over the information that has been filled in range C$5$ to C$11$ to ensure it appropriately corresponds with base metal displayed in the range A$15$:B$19.$

You will calculate the total due for each student's order. The total is the base price of the ring plus an additional charge for personalization if applicable.

$6.$ Insert an IF function in cell E$5$ to calculate the total due. If the student has chosen to personlize the ring $($as indicated in column D$),$ there is an additional charge of $6\%$ located in cell B$21$ that must be applied; if not, the student only pays the base price. Note that when entering text in your formulation you must put quotation marks before and after it $($i$.$e "Yes"$).$ Use appropriate relative and absolute cell references.

$7.$ Copy the function from cell E$5$ down through E$11.$

The next step is to calulate the periodic payment for each student's account. The payments are based on the years financed in column F and the annual intereste rate in cell B$22.$ All accounts are paid on a monthly basis.

$8.$ Insert a PMT function in cell G$5$ to calculate the first student's monthly payment, using appropriate relative and absolute cell reference. Ensure that all information is provided monthly under "Monthly Payment" as the interest rate and the years financed are provided in a yearly format.

$9.$ Copy the formula down the column.

$10.$ Calculate the total in cell C$12,$ E$12,$ and G$12.$

$11.$ Apply the accounting number format to decimal places for column C$,$ E$,$ and G$.$

$12.$ In cells C$12,$ E$12,$ and G$12$ apply a "Top and Double Bottom Border" for the totals.

$13.$ In cell G$16$ apply a statistical function calculating the average monthly payment Per Customer for Inland Jewlers.

$14.$ In cell G$17$ apply a statistical function calculating the minimum monthly payment Per Customer for Inland Jewlers.

$15.$ In cell G$18$ apply a statistical function calculating the maximum monthly payment Per Customer for Inland Jewlers.

$16.$ In cell G$19$ apply a statistical function calculating the median monthly payment Per Customer for Inland Jewlers.

$17.$ Save your file and insert your last and first name at the end of the file name. Submit your completed file to your instructor.