Last updated on July 8, 2024

Business Analyst Exam

Solve a business analyst exam using the following instruction. Included in this tutorial is (PART 1 OF 2) how to create a dropdown list and (PART 2 OF 2) how to populate a scorecard based on the dropdown list, and to create a clear button to delete populated data.

Exam Objective: Create an automated scorecard

On the scorecard tab:

• Cell D11 needs to be a dropdown list of staff.
• Cells D10 and D12 need to be populated.
• When I select/choose a staff on Cell D11, the Actual, Goal, % to Goal and Rating columns should be automatically populated (I’ve already populated the Weights and Goals columns)
• Compute for the Overall Score (cell H23) – Overall Score is the sum of each metric’s Rating
Overall Score cannot exceed 100%.
• Any “% to Goal” that is below the target should have a red font for failing using conditional formatting.
• Create a command button on the scorecard tab that would erase all actual data, % to goal, and rating columns.
• Feel free to insert columns/rows as necessary.

Scores can be found on the “Scores” sheet.

Hints:
CTS – Resolution – The higher the better
Call control – The lower the better
Reliability – The higher the better
Broadband Strike Rate – Add New The higher the better
Mobile Strike Rate – Add New The higher the better

Part 1 – Create Dropdown List

Instruction:
1. Prepare the data that needed to be in the dropdown list.

2.Select the cell where the dropdown list will be. Cell D11 in the “Scorecard” sheet.

3. Find Data Tab. Click Data Validation Button.

4. When Data Validation pop-up window occurs, go to Settings Tab. Choose “List” in Allow’s dropdown list.

5. In the Source area, choose the reference list in the “Score” sheet. Anything highlighted will be included in the dropdown list so do not include the word “Staff” in the Source.