Excel dashboard project

Steps:

1- What is a Excel Dashboard and how to I get started? (15:23)

https://www.youtube.com/watch?v=zTQcPGYNh9w

Dashboard ProtoType (300 Points)

Every company no matter how big or small needs a dashboard to give them a source of direction, a gauge, that lets employees, customers, and even stockholders know where it’s headed.  Your dashboard is a visual interface that provides at-a-glance views into key measures relevant to a particular objective or business process. 

A dashboard consists of three key attributes:

 •    Displays data graphically (such as in charts). Provides visualizations that help focus attention on key trends comparisons, and exceptions.•     Displays only data that is relevant to the goal of the dashboard.•    Contains predefined conclusions relevant to the goal of the dashboard and relieves the reader from performing his own analysis. 

Data

Download the Excel file with the raw data. You need to understand the data to get started. The link to the Excel spreadsheet is available below the instructions. 

The Objective

You will complete a dashboard project. You will make a dashboard using the project data.  Each Dashboard must fit entirely on a single computer screen so it can be monitored at a glance.  It will have at least 4 charts.

Required Dashboard Elements when Creating the Dashboard:

• &νβσπ;&νβσπ;You will develop a dashboard layout mockup. It will represent the component and the type of data required to create the measures

Required Excel Elements when doing analysis and creating charts/graphs for Dashboard:

USE IN THE ANALYSIS TABS

Pivot Table

Conditional Formatting

IF Formula

Advanced Formula (Choose from your book)

Advanced Function (Choose from your book)

CLICK on each one and you have instructions on how to do it.  Your book has great examples.  

Sparkline You will use these in the analysis tabs.  

USE ON THE DASHBOARD TAB 

3 Pivot Table Charts 

Slicer

A macro

You pick:

1 chart that shows Trending (Smoothing or Linear Forecast)

This will equal 4 charts, a slicer and a Macro on the Dashboard.  

2 Challenging options:

1 chart with a Target (Thermometer or Bullet)

1 chart with an Interactive Control(Check Box or List Control) You cannot use a macro with the control.

1 chart that Groups Data (Pareto or Histogram)

Your Project should have the worksheets below included.

They form the three layers: data, analysis, and presentation. Here is what is should look like.

1. Worksheet 1 – Tab Labeled Mockup

  -- The Layout Mockup should be drawn using shapes and textboxes to look similar to Figure 1-3 (link is shown above).  I should see the questions that correspond to the components in the Dashboard.  Figure 1-3 has examples of questions. 

 2. Worksheet 2 – Tab Labeled Map (Click here to see an example

) -- Add a data model map tab to your data model. The map tab essentially summarizes the key ranges in the data model and allows you to document how each range interacts with the dashboard components in the final presentation layer. The idea is to give yourself a handy reference (using Hyperlinks) that guides you through the elements in your data model. The columns will be called TAB, CELL RANGE, PURPOSE, COMPONENT.

3. Worksheet 3 – Tab Labeled Dashboard

 4. Worksheet 4 - Tab Labeled DATA

5. Worksheets - Tabs Labeled Analysis 1

, Analysis 2 , Analysis 3  , etc. 

You can create subsets of the data as needed to create your components.

• Document the logic in your data model by using comments and labels liberally. To receive credit, I MUST be able to find everything.

Download the Excel file (named dashboardprotootype)that you will use for the Dashboard Prototype Project.  You need to understand the data to get started. You MUST use this file with the data included to create your Dashboard.  

• Goals to consider when creating the Dashboard:
  • Increase Sales each year by 4%
  • Increase Bikes Sales OrderQty by 9% each yea