Grader $-$ Instructions Excel $2019$ Project

Created On: $11/18/20221$ Exp$19\_$Excel$\_$AppCapstone$\_$CompAssessment $-$ Manufacturing $1\mathrm{.}12$

Exp$19\_$Excel$\_$AppCapstone$\_$CompAssessment$\_$Manufacturing

Project Description:

You have recently become the CFO for Beta Manufacturing, a small cap company that produces auto parts. As you step into

your new position, you have decided to compile a report that details all aspects of the business, including: employee tax

withholding, facility management, sales data, and product inventory. To complete the task, you will duplicate existing

formatting, utilize various conditional logic functions, complete an amortization table with financial functions, visualize data with

PivotTables, and lastly import data from another source.

Steps to Perform:

Step Instructions Points

Possible

$1$ Start Excel. Download and open the file named

Exp$19\_$Excel$\_$AppCapstone$\_$ComprehensiveAssessment$-$ Manufacturing.xlsx$.$

Grader has automatically added your last name to the beginning of the filename.

$0$

$2$ Group all the worksheets in the workbook and fill the range A$1$:F$1$ from the Insurance

worksheet across all worksheets including the formatting. Ungroup the worksheets

after the fill is complete and ensure the Insurance worksheet is active.

$3$

$3$ Click cell I$5,$ and enter a function that determines the number of full$-$time employees,

$($FT$).$

$3$

$4$ Enter a database function in cell I$6$ that determines the average salary of all full$-$time

employees with at least one dependent. Format the results in Accounting Number

Format.

$2$

$5$ Enter a lookup function in cell E$5$ that returns the tax deduction amount for the

number of dependents listed in the cell C$5.$ Use the table in range H$13$:I$17$ to

complete the function. The maximum deduction is $$500\mathrm{.}00$; therefore, employees

with more than four dependents will receive no additional deductions.

$2$

$6$ Use Auto Fill to copy the function down, completing column E$.$ Be sure to use the

appropriate cell referencing. Format the data in column E with the Accounting

Number Format.

$4$

$7$ Enter a logical function in cell F$5$ that calculates employee FICA withholding. If the

employee is full$-$time and has at least one dependent, then he or she pays $7\%$ of the

annual salary minus any deductions. All other employees pay $5\%$ of the annual

salary minus any deductions. Copy the function down through column F$.$ Format the

data in column F with Accounting Number Format.

$3$

$8$ Apply conditional formatting to the range C$5$:C$34$ that highlights any dependents that

are greater than $3$ with Light Red Fill and Dark Red Text.

$4$

$9$ Click cell H$10,$ and enter an AVERAGEIFS function to determine the average salary

of full$-$time employees with at least one dependent. Format the results in Accounting

Number Format.

$3$

$10$ Use Advanced Filtering to restrict the data to only display full$-$time employees with at

least one dependent. Place the results in cell A$37.$ Use the criteria in the range

H$24$:M$25$ to complete the function.