80.2K

Verified Solution

Link Copied!

Exp19_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.

7 3 Enter a logical function in cell F5 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. 8 4 Apply conditional formatting to the range C5:C34 that highlights any dependents that are greater than 3 with Light Red Fill and Dark Red Text. 9 3 Click cell H10, 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. 10 2 Use 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. 11 5 Ensure that the Facilities worksheet is active. Use Goal Seek to reduce the monthly payment in cell B6 to the optimal value of $6000. Complete this task by changing the Loan amount in cell E6. 7 3 Enter a logical function in cell F5 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. 8 4 Apply conditional formatting to the range C5:C34 that highlights any dependents that are greater than 3 with Light Red Fill and Dark Red Text. 9 3 Click cell H10, 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. 10 2 Use 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. 11 5 Ensure that the Facilities worksheet is active. Use Goal Seek to reduce the monthly payment in cell B6 to the optimal value of $6000. Complete this task by changing the Loan amount in cell E6

Answer & Explanation Solved by verified expert

See Answer