A Step Up Construction

A Step Up Construction has asked you with compiling a report that details storage location information, contracts, inventory, and location management. To complete the task, you will duplicate existing formatting, utilize various conditional logic functions, complete an amortization table, connect and transform several external data sources, and inspect the workbook for issues.

Format the Workbook

You need to open the existing workbook the company provided, save it with a new name, and make a few preliminary modifications.

1. Open the exam3_data workbook and save the workbook as exam3_LastFirst.

1. Group the Current and 2^nd Location worksheets in the workbook and Merge and Center the range A1:H1 from the Current worksheet across all worksheets including the formatting. Save the workbook.

Use Conditional Logic and Conditional Formatting

You are ready to calculate the total costs and use conditional logic and formatting.

1. Ensure the Current worksheet is active. Click cell G6 and enter a nested logical function that calculates employee Bonus eligibility. If the employee has a college degree YES and was hired before the cutoff date of 6/19/19 (cell H3), then he or she is eligible, and Y should be displayed; non-eligible employees should be indicated with an N.

1. Use the fill handle to copy the function down without formatting, completing the range G6:G25.

1. Apply conditional formatting to the range G6:G25 that highlights eligible employees with Light Red Fill with Dark Red Text color. Save the workbook.

Specialized Functions and Data Validation

You want to create a custom lookup function that enables you to locate employee information based on user-defined criteria and employee number. You also want to use conditional math to analyze the salaries of employees without a college degree (No). You will use data validation; a nested INDEX/MATCH function; and the conditional match functions MAXIFS, SUMIF, COUNTIF, and AVERAGEIF

1. Create a Data Validation list in cell J7 based on the employee IDs located in the range A6:A25. Add the input message Select Employee ID and use the Warning Style Error Alert.

1. Use the Data Validation list in cell J7 to select Employee_ID 90871 and select Last Name in cell K6 to test the function.

1. Enter a nested INDEX and MATCH function in cell K7 that examines the range B6:H25 and returns the corresponding employee information based on the match values in cell J7 and cell K6.

1. Enter a conditional math function in cell K14 that calculates the total number of NO employees.

Enter a conditional math function in cell K15 that calculates the total value of NO employee salaries.

Enter a conditional math function in cell K16 that calculates the average value of NO employee salaries.

Enter a conditional math function in cell K17 that calculates the highest NO employee salary.

1. Apply Currency Number Format to the range K15:K17. Save the workbook.

Perform Advanced Filtering and Database Functions

You want to determine additional statistical information about your YES employees, those with college degrees. You plan to use advanced filtering to create an isolated table of employees with degrees. You also will use Database functions to calculate the total number of employees with college degrees and additional summary statistics.

1. Click cell K11 and type YES. Click cell A28 and type Employees with Degree.

1. Use the Format Painter to apply the formatting from the cell A3 to the range A28:D28.

1. Use advanced filtering to restrict the data to display only Yes employees based on the criteria in the range K10:K11. Place the results in cell A29.

1. Enter a database function in cell K18 to determine the total number of YES employees.

Enter a database function in cell K19 to determine the total value of YES employee salaries.

Enter a database function in cell K20 to determine the average YES employee salary.

Enter a database function in cell K21 to determine the highest YES salary.

1. Format the range K19:K21 with Currency Number Format. Save the workbook.

What-If Analysis

You will evaluate the cost of adding a 2^nd Location. You will use what-if analysis tools to aid you in your assessments.

1. Ensure that the 2nd Location worksheet is active. Use Goal Seek to reduce the monthly payment in cell B6 to the optimal value of $7,500. Complete this task by changing the Loan amount in cell E6.

1. Create the following three scenarios using Scenario Manager. The scenarios should change the cells B7 and B8.

Good	Most Likely	Bad
B7 = 0.325	B7 = 0.396	B7 = 0.450
B8 = 5	B8 = 5	B8 = 4

1. Create a Scenario Summary Report based on the value in cell B6. Move Scenario Summary worksheet to the right of 2^nd Location worksheet. Save the workbook.

Complete an Amortization Schedule

You will complete an amortization table detailing payment, principal, interest, cumulative principal, and cumulative interest.

1. Ensure that the 2^nd Location worksheet is active Enter a reference in cell B12 to the beginning loan balance.

1. Enter a reference in cell C12 to the payment amount.

1. Enter a function in cell D12 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. All results should be formatted as positive numbers.

1. Enter a function in cell E12 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 and ensure the results are positive.

1. Enter a formula in cell F12 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.

1. Enter a function in cell G12 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 and ensure the results are positive.

1. Enter a function in cell H12 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. All results should be formatted as positive numbers.

1. Enter a reference to the remaining balance of payment 1 in cell B13.

1. Use the fill handle to copy the functions created in the prior steps down to complete the amortization table. Expand the width of columns C:H as needed. Save the workbook.

Use the Macro Recorder and Insert a Form Control

Youwill use the Macro Recorder to create a macro to sort the table by date after the workbook has been refreshed.

al. Enable the Developer tab if necessary. Use the Macro Recorder to record a macro named Sort. When activated, the macro should sort the Employee Id in ascending order on the current sheet.

am. Insert a Form Control button above the worksheet. Add the label Sort and assign the Sort macro. Save the workbook as a Macro Enabled workbook.

Check Workbook for Issues

You will inspect the document for accessibility issues.

an. Use the Accessibility checker to inspect for issues. Once located, take the recommended actions to alleviate the issues.