2- Dashboards and Data Models

Dashboards and Data Models
Use your help video and the data in the Fire this Employee workbook Download Fire this Employee workbookto complete this project. Notice that the workbook has four worksheets: AllData, Charts, DataModel, and FireThisEmployee,

Save the file to your OneDrive as exactly the following:
MyLastName-DashboardsandModels. (Substitute your last name for MyLastName.)
Naming conventions are important to help users find your shared files; add no extra characters or spaces.

1 of 6. Pivot Tables

Type two business questions of your choice on your AllData worksheet positioned as demonstrated in your help video. (Try writing your questions yourself rather than using the same questions as members of your support group or a classmate.)
Insert two pivot tables on the AllData worksheet below your questions that will produce at least two rows of data, one column of calculated values, and the answers to your two business questions. (Don’t pivot the exact same way I did in the help video.)
Add the answers to your two business questions below each pivot table on the AllData worksheet in the position shown in the help video.
2 of 6. Charts and Slicers

Still on the AllData worksheet, create Charts associated with the pivot tables as shown in your help video.
Name your charts and pivot tables by finding options, found for pivot tables by putting your cursor in the pivot table and right-clicking. Select a chart to name it; the name text box is in the upper left corner.
Format the charts with sensible titles.
Copy and paste your charts onto the Charts worksheet. (Don’t cut and paste; copy and paste.) Size them so both charts are visible. If I must move or resize the charts substantially to see them change simultaneously, points will be deducted.
Add two slicers to each chart on the Charts worksheet and connect every slicer to every other slicer. Be sure your charts and slicers are legible and dynamic when charts are sliced.
Slice your charts any way you want, but make sure you have at least two for each chart. Check to be sure the charts adjust simultaneously with each slice you make. Size your charts and slicers so that everything is visible to minimize scrolling left-right and up-down. If I must resize the charts to see them change simultaneously, points will be deducted.
On the same worksheet (Charts), type information you derive from your sliced charts and your plan to apply the information. Make sure your charts illustrate the information you derive. (NOTE: your information must be reflected in your charts.)
Example:

Info derived: Schmoe was our best customer in our second year of business, but he stopped purchasing in year three. Plan: I will analyze my data to see what he was purchasing in year two; I’ll then analyze our competitor’s prices to see how we stack up; I’ll make price adjustments, or offer a promotion, or offer Schmoe a special discount. In any case, I’ll pay Schmoe a visit.

Position the text of your information and plan so that I can find it easily.

Before you submit, be sure you return your slicers to the original data from the AllData sheet. (You may find that only one of your answers is correctly reflected. That’s ok.)

3 of 6. Add PowerPivot to your Excel application if you don’t already have it installed. Follow your help video

4 of 6. Create a Data Model

From the DataModel sheet, add each of the four tables to a data model, as demonstrated in your help video. (You’ll see four tables already created for you on your DataModel worksheet.)
Relate the tables in the diagram view. If data isn’t what you expect, try re-relating from your diagram view. It matters whether you drag from satellite tables to main table or from main table to satellite table.
Insert two pivot tables (your help video only shows only one pivot table) from your data model onto the DataModel worksheet. Make sure your pivot tables are clearly visible so I (Dr. Cahill) don’t have to manipulate them to see them.
Based on your pivot table data on the DataModel worksheet, you will derive information that helps you come to a conclusion that you must fire one employee.
5 of 6. Fire an Employee

On the FireThisEmployee worksheet, cell A1, type the last name of the employee you will fire and, in a few words, the data that support your decision. Make certain your pivot tables’ data reflect your decision.
6 of 6. Submit Your Assignment