StudyHub

Write My Assignment FREE

Dashboard Creation Assignment

Your manager wants you to build an interactive Excel Dashboard to show to his/her General Managers about this small part of the business. The GMs are trying to understand if there are any connections between age, gender, and length of service in each of the departments. (see example picture at end of this file.)

You will create multiple pivot tables and some subsequent pivot charts all based on the same data file to give them a view of these relationships per department. You will also need to create one slicer that will “slice” the data by Department. This one slicer will interactively affect several of the pivot tables and charts on the dashboard (in real time) when you/they select one or more department buttons on that slicer.

Pay attention to aesthetics of the dashboard, table, and charts. Choose colors and fonts that are interesting and pleasing to you. Labels and titles do not have to match the example exactly, but they must convey the information represented in the tables/charts/sheets. (Sheet 1, Sheet 2, etc. are not acceptable.)

Older PC and MAC NOTES: if your version of Excel on your Mac or PC does not support Pivot tables, Pivot Charts, and Slicers across them, then you will need to do this assignment in the lab/library, borrow a computer or do it on the RDC/eLab.

NOTE ON EXAMPLE PICTURE: Your numbers may be different from the picture posted.

If you have problems with some of the steps use a search engine to find other videos to help you.

STEPS:

1. Download and save from this week’s Canvas Module: HR-Dashboard-2-Start. Rename it to:

<Last Name>_<First Initial> HR Dashboard

2. Open spreadsheet and make sure there is a data sheet called Master. It is currently a data table with headers called HR_Data.

Create Dashboard sheet

3. If needed, create a new work sheet called Dashboard. I always move it to the far left for ease of use.
4. Merge and center A1:P3 and title it Human Resources Dashboard.

Check Key Metrics Pivot table

5. Look at the unfinished pivot table in the Key Metrics You can see that the pivot table is also called Key Metrics.
6. Modify this pivot table so it has all the key metrics:
7. Total number of employees
8. Average age of employees (make the number easily readable – i.e. one decimal place)

• Average length of service (make the number easily readable – as above.)

MOVE this pivot table to Dashboard at approximately B10. (DO NOT COPY TABLES OR CHARTS. They generally will not work properly.)

 

 

 

Check Gender Numbers Pivot Table on Key Metrics sheet

 

6. In Key Metrics sheet there should be another pivot table labeled Gender Count.

 

7. Remove Grand Total row from this table.

 

8. Name this pivot table Gender Numbers

 

9. This table will specify the total number of males and females for all

 

10. Move it to the Dashboard under Key Metrics pivot table.

 

Create Age by Gender Pivot Table in Age by Gender sheet

 

1. From Master, create another pivot table in a sheet called Age by Gender. This pivot table will show:

 

1. The number of female and male employees and total population by age groups:

 

1. 20-29, 30-39, 40-49, 50-60, >60

 

1. Table label is: Age Analysis by Gender

 

1. Pivot table and work sheet it is on, is called: Age by Gender

 

1. Move it to the Dashboard a couple of cells to the right of the Key Metrics pivot table.

 

Create LOS (Length of Service) Pivot Table on LOS sheet

 

1. From Master, create another pivot table in a sheet called LOS. This table will show:

 

1. Analysis by Length of Service

 

1. Show how many employees are in each of these LOS (Length of Service) groups:

 

1. 3-5, 6-8, 9-11, 12-15, >15

 

• Include the Total

 

1. Label the table: Length of Service

 

1. Name the table: LOS

 

1. Did you get the right total of employees? (Sanity check.)

 

1. Move it to the Dashboard a couple of cells to the right of the Age by Gender pivot table.

 

 

Create Number of Employees by Dept Pivot Table and Chart

 

2. From Master, another pivot table was created in a sheet labelled By Dept.

 

1. This table should show:

 

1. Number of Employees by Department

 

1. Grand Total of employees

 

1. Name the pivot table Num Emp by Dept

 

1. From this pivot table, create a pivot chart on the same sheet

 

1. Make it a Column Chart – any style

 

1. Label each column with the number of employees in the department.

 

1. Move the pivot chart to the Dashboard a few rows under Key Metrics pivot tables

 

Create Num Emp By Gender Pivot Table and a Pivot Chart

 

3. From Master, create another pivot table in the sheet By Gender.

 

1. This Pivot table will show:

 

1. Number of Employees by Gender

 

1. Grand Total of employees

 

1. Name the table Num Emp by Gender

 

1. Create a pivot chart from this pivot table

 

1. Make a pie chart – any style

 

1. Data labels need to be percentages of the total.

 

Create Dashboard

 

4. If you haven’t done so already, MOVE each of the requested pivot tables from their respective sheets to the Dashboard See illustration at the end of this instruction set to see which tables and charts need to be moved. (DO NOT COPY TABLES OR CHARTS. They generally will not work properly.)

 

5. On the Dashboard sheet create a slicer that will slice data by Departments.

 

1. Slicer must be connected to pivot tables/charts:

 

1. Age Analysis by Gender, Length of Service, Percentage Employees by Gender.

 

1. It must not connect to any other tables or charts.

 

1. That means, when I click on the slicer buttons for Logistics and Sales simultaneously, only the data/charts listed in above will change.

 

1. Place slicer horizontally across the top of the charts below the title. Make it a style that matches your color scheme.

6. If all works correctly, save and submit.

 

Dashboard Example

The post Dashboard Creation Assignment Your manager wants you to build an interactive Excel Dashboard to show to his/her General Managers about this small part of the business. The GMs are trying to understand if there are any connections between age, gender, and length of service in each of the departments. (see example picture at end of this file.) appeared first on My Blog.

Expert Help With This Assignment — On Your Terms