EX16_XL_COMP_GRADER_CAP_AS - Manufacturing 1.5

EX16_XL_COMP_GRADER_CAP_AS - Manufacturing 1.5
Whatasapp : +92306-7065210
Gmail : myitlabcourese@gmail.com
Grader - InstructionsExcel 2016 Project EX16_XL_COMP_GRADER_CAP_AS - Manufacturing 1.5 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: StepInstructions Points Possible 1Start Excel. OpeneApp_Cap2_Manufacturing.xlsxand save theworkbook asACC2343_Final_Manufacturing_LastFirst. 0 2Group all the worksheets in the workbook and fill the range A1:F1 fromthe Insurance worksheet across all worksheets maintaining the formatting. Ungroup the worksheets after the fill is complete and ensure the Insurance worksheet is active. Hint: -All worksheet grouped -Value from range A1:F1 filled across all worksheets -Same format maintained across all worksheets -Worksheets ungrouped 4 3Click cell I5 and enter a function that determines the number of full-time employees, (FT). Hint: -Function in cell I5 uses the right cells -Function used the right conditional Count function 2 4Enter a function in cell I6 that determines the average salary of all full-time employees. Format the results in Accounting Number Format. Hint: UseDAVERAGE(.......). -Function entered in I6 uses correct cells -Function calculates average salary of all full-time employees -Accounting number format applied to result 2 5Enter a lookup function in cell E5 that returns the tax deductionamount for the number of dependents listed in thecell C5. Use the table in range H13:I17 to complete the function. The maximum deduction is $500.00; therefore, employees with more than four dependents will receive no additional deductions. Hint: -Used suggested type of Lookup function (VLOOKUP) to return correct value. 2 Created On: 07/05/20191EX16_XL_COMP_GRADER_CAP_AS - Manufacturing 1.5
Grader - InstructionsExcel 2016 Project StepInstructions Points Possible -Absolute cell reference, and relative cell reference must be used in their places to get the complete marks here. 6Use Auto Fill to copy the function down, completing column E. Be sureto use the appropriate cell referencing. Format the data in column E with the Accounting Number Format. Hint: -Auto fill used to copy function down column -Appropriate cell referencing (absolute cell reference, and relative cell reference must be used in their places to get the complete marks here) -Data in column E formatted with Accounting Number format 4 7Enter a logical function in cell F5 that calculates employee FICAwithholding. 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. Hint: = IF (AND (logical 1, logical 2….)) -Logical function in cell F5 calculate FICA withholding-Full-time (FT) employee with at less 1 dependent pays 7% of the annual salary minus any deductions. -Other employees pay 5% of the annual salary minus any deductions -Cell reference I20 and I21 correctly -Function copied down column F -Data in column F formatted with Accounting Number format 3 8Apply conditional formatting to therange C5:C34 that highlights anydependents that are greater than3with Light Red Fill and DarkRed Text. Hint: In the Styles group, click Conditional Formatting. -Conditional formatting applied to range C5:C34 -Dependents greater than 3 highlighted -Light Red Fill -Dark Red Text 4 9Click cell H10, and enter an AVERAGEIFS function to determine the average salary of full-time employees with at least one dependent. Format the results inAccounting Number Format. 3 Created On: 07/05/20192EX16_XL_COMP_GRADER_CAP_AS - Manufacturing 1.5

Want to read all 8 pages?
Previewing 3 of 8 pages Upload your study docs or become a member.
View full document

Grader - InstructionsExcel 2016 Project StepInstructions Points Possible Hint: AVERAGEIFS (average-range, criteria-range1, criteria….) -AVERAGEIFS function used correctly -Returns average salary of full-time employees with at least one dependent -Result formatted with Accounting number format 10Use Advanced Filtering to restrict the data to only display full-time employees with at least one dependent. Place the results in cell A37. Use the criteria in the range H24:M25 to complete the function. Hint: On the Data tab, in the Sort & Filter group, click Advanced. -Advance filtering used correctly -

Видео EX16_XL_COMP_GRADER_CAP_AS - Manufacturing 1.5 канала PEARSON MYLAB