$13$

Ensure that the Facilities worksheet is active. Enter a reference to the beginning loan balance in cell B$12$ and enter a reference to the payment amount in cell C$12.$

$14$

Enter a function in cell D$12,$ based on the payment and loan details, that calculates the amount of interest paid on the first payment. Be sure to use the appropriate absolute$,$ relative, or mixed cell references.

$15$

Enter a function in cell E$12,$ based on the payment and loan details, that calculates the amount of principal paid on the first payment. Be sure to use the appropriate absolute$,$ relative, or mixed cell references.

$16$

Enter a formula in cell F$12$ to calculate the remaining balance after the current payment. The remaining balance is calculated by subtracting the principal payment from the balance in column B$.$

$17$

Enter a function in cell G$12,$ based on the payment and loan details, that calculates the amount of cumulative interest paid on the first payment. Be sure to use the appropriate absolute$,$ relative, or mixed cell references.

$18$

Enter a function in cell H$12,$ based on the payment and loan details, that calculates the amount of cumulative principal paid on the first payment. Be sure to use the appropriate absolute$,$ relative, or mixed cell references.

$19$

Enter a reference to the remaining balance of payment $1$ in cell B$13.$ Use the fill handle to copy the functions created in the prior steps down to complete the amortization table.

$20$

Ensure the Sales worksheet is active. Enter a function in cell B$8$ to create a custom transaction number. The transaction number should be comprised of the item number listed in cell C$8$ combined with the quantity in cell D$8$ and the first initial of the payment type in cell E$8.$ Use Auto Fill to copy the function down, completing the data in column B$.$

$21$

Enter a nested function in cell G$8$ that displays the word Flag if the Payment Type is Credit and the Amount is greater than or equal to $$4000.$ Otherwise, the function will display a blank cell. Use Auto Fill to copy the function down, completing the data in column G$.$

$22$

Create a data validation list in cell D$5$ that displays Quantity, Payment Type, and Amount $($in that order$).$

$23$

Type the Trans# $30038$C in cell B$5,$ and select Quantity from the validation list in cell D$5.$

$24$

Enter a nested lookup function in cell F$5$ that evaluates the Trans # in cell B$5$ as well as the Category in cell D$5,$ and returns the results based on the data in the range A$8$:F$32.$

$25$

Create a PivotTable based on the range A$7$:G$32.$ Place the PivotTable in cell I$17$ on the current worksheet. Place Payment Type in the Rows box and Amount in the Values box. Format the Amount with Accounting Number Format.

$26$

Insert a PivotChart using the Pie chart type based on the data. Place the upper$-$left corner of the chart inside cell I$22.$ Format the Legend of the chart to appear at the bottom of the chart area. Format the Data Labels to appear on the Outside end of the chart.

$27$

Insert a Slicer based on Date. Place the upper$-$left corner of the Slicer inside cell L$8.$

$28$

Use PowerQuery to connect to the Access database Exp$19\_$Excel$\_$AppCapstone$\_$Comprehensive$\_$Inventory.accdb. Load the Inventory table into a new worksheet named Inventory.

Note, Mac users, download and import the delimited Inventory.txt file into a new worksheet named Inventory starting in cell A$1.$

$29$

Create a footer with your name on the left, the sheet code in the center, and the file name on the right for each worksheet.

$30$

Save the file Exp$19\_$Excel$\_$AppCapstone$\_$ComprehensiveAssessment$-$ Manufacturing.xlsx$.$ Exit Excel. Submit the file as directed.