Name MarksRobby 90Miriam 95Sandy 80Paul 75Kevin 50

MS Excel Preparing Basic Dashboards

Name MarksRobby 90Miriam 95Sandy 80Paul 75Kevin 50

Name MarksRobby 90Miriam 95Sandy 80Paul 75Kevin 50

Table of Contents

1. Introduction to Dashboards......................................................................................................1

2. Dashboard Creation Process....................................................................................................1

3. Sample Project Management Dashboard-High Level Steps....................................................2

4. Project Management Dashboard Creation-Details..................................................................5

4.1 Working with data and performing calculations...............................................................5

4.1.1. Metric 1: Project Plan....................................................................................................5

4.1.2 Metric 2: Cost Management.........................................................................................12

4.1.3 Metric 2: Issue Management........................................................................................13

4.1.4 Metric 3: Resource Management..................................................................................15

4.2 Preparing Charts...................................................................................................................17

4.2.1 Cost Management.........................................................................................................17

4.2.2 Issue Management........................................................................................................23

4.2.3 Resource Management..................................................................................................25

5. Final Formatting and Presentation.............................................................................................32

References......................................................................................................................................37

1. Introduction to DashboardsInformation is the lifeline of any organization today. Imagine a situation where it becomes cumbersome to analyze this information and make conclusions. Think of a situation where your manager and other decision makers have to go through pages of reports to derive insights. With this level of competition one cannot afford to waste a lot of time and effort in information analysis. There has to be a quick mechanism which allows organizations to quickly analyze information accurately and then derive useful insights for their business. This is where dashboards can help. A dashboard is a simple view of all the critical information which is needed for decision making and analysis at one place. Dashboards help organizations in understanding trends and making intelligent decisions without going through pages of reports. A good dashboard captures all the critical and relevant information at one place.

MS Excel being a strong data analysis tool can help us in preparing very good dashboards. Although there are many dashboard creation tools available in the market, MS Excel stands out and is popular because it combines strong data analysis with dashboard preparation. Apart from this it is affordable too and allows users to create custom dashboards.

2. Dashboard Creation ProcessThe essence of dashboard creation like any other type of preparation lies in understanding what needs to be prepared. There are no hard and fast rules for creating good dashboards but there are some basic practices which should be followed to get good results. The practices are as follows:

Understanding the purpose: The very first step is to understand why we are preparing a dashboard? Who are the audience? What are their goals and what information do they seek? For e.g. a sales dashboard will primarily help an organization understand the sales trends, revenues and strategize accordingly.

Understanding the information: The second step is to understand the information which needs to be captured. A dashboard can have a wide variety of information but the key lies in understanding and capturing the information which is critical for our goal. Building a dashboard around the wrong or rudimentary information will not serve the actual purpose.

Building metrics: The third step lies in building appropriate metrics to capture the key information. Metrics are measurable parameters which help organizations in understanding their goals better. The dashboard has to be built around certain

important metrics. For e.g. for a sales dashboard the key metrics could be sales amount of each sales person and the breakup month wise, sales in major regions/zones, sales for key customers and sales for key products etc. The important metrics have to be filtered out.

Deciding the layout/structure: The fourth step lies in deciding how the metrics or dashboard items will be laid out to cover the space effectively.

Working with data and performing calculations: The next step is to organize the data properly. The data has to be clearly and concisely organized in excel spreadsheets. If the dataset is small the calculation worksheet can be same as the data worksheet. For large datasets data should be maintained in separate worksheets to calculation worksheets. One may not require the entire data for calculations and analysis. Therefore it is important to segregate the data which is required for calculation and analysis.

Preparing Charts: After the calculations have been done one needs to prepare the right type of charts for the dashboard. A dashboard looks attractive if information is concisely depicted in the form of charts. It is very important to choose the correct chart to say the message loud and clear.

Final formatting and presentation: The next step is to do formatting and make the dashboard presentable. One can play with a wide range of colors and fonts but it is better to stick to fewer colors, fonts and use subtle colors to avoid confusion.

3. Sample Project Management Dashboard-High Level StepsTo understand the creation process and various tricks which go into it let us analyze this dashboard created by us. The high level steps involved in creation of this dashboard are:

Understanding the purpose: The purpose of this dashboard was to capture useful information about various important aspects of project management like project plan, issues, costs and resources. This dashboard is intended for project managers and other project stakeholders.

Understanding the information and building metrics: We needed to capture information around resources, cost, project plans and project issues. Therefore the key metrics which helped us in quantifying this information from project management perspective are:-Resource utilization- Estimated and actual costs-Project activities start and end dates- Issue open and close rates

Bigger Chart Smaller Chart

Bigger ChartSmaller Chart

Longer Chart which shows trends

Information Summary Picture/Image

Heading Picture/Image

Deciding the layout/structure: We decided that the items in the dashboard should be laid out as shown below. We also decided the boxes which should have charts and other elements.

You can decide your own layout style.

Working with data: Now with the metrics and dashboard structure we had a clear picture of what was required. Therefore we gathered the data and segregated the part needed for analysis. We then organized the data in four different worksheets one for each metric. Calculations were performed.

Preparing Charts: After organizing the data we prepared charts to represent information correctly. We chose the charts which could best represent the associated metric/information. For e.g. for representing Issue open and close rates over a period of

12 months we chose the column chart as it effectively depicts “trend over time:. Final formatting and presentation: We formatted the charts with appropriate colors and

then pasted them on our dashboards as per the layout shown earlier. After the final touchups we came up with this dashboard:

4. Project Management Dashboard Creation-DetailsLet us try to understand how this dashboard was created in greater details. We will start from the fourth step “Working with data” as that is the point from which excel work starts. The steps are:

4.1Working with data and performing calculations

4.1.1. Metric 1: Project PlanLet us try to understand this step metric by metric. The first metric is “Project Planning”. We have

created a Gantt chart for this metric as project plans can best be represented with Gantt charts. The

dataset is small, therefore we have used the same sheet for calculation.

We have listed out the critical activities and the start/end date of each activity. We have calculated

the duration using the formula:

=F7-E7

Dragging and copying this formula across cells G8 to G26 gives the duration of each activity. The % completion has been entered directly based on the tasks completed.

Note that the percentage completion field uses conditional formatting.

This conditional formatting was applied like this:

-Select the range of cells H7 to H26 and go to the conditional formatting menu. Select the option “New Rule”.

-In the “Edit Formatting Rule” dialog box select the option “Format all cells based on their values”.

-Choose the “Format Style” as Icon sets in the drop down menu.

-For the icon sets choose these values:

For the green icon: Choose >= and enter 50 in the value box. Remember to choose the value type as “Percent”

For the yellow icon: Choose > and enter 20 in the value box. Remember to choose the value type as “Percent”

The red icon will automatically adjust itself.

On clicking “OK” you will get the desired results.

This conditional formatting rule tells excel to insert a green icon when the values across cells H7 to H26 are greater than 50%. If the values are less than 50% but greater than 20% it should insert a yellow icon. If the values are lesser than 20 then a red icon should be inserted indicating that these activities have very less completion rates and require attention. The aim of this conditional formatting is to draw the managers attention to activities with little or no completion rates. The colored icons act as alert icons.

Now let us understand how the Gantt Chart was created. Although creation of charts is a separate step altogether, in this case the Gantt chart was created in the data creation and calculation step only using conditional formatting.

-The Gantt chart was created using simple conditional formatting. The formula used for the creation of Gantt chart:

=IF(AND(I$6>=$E7,I$6<=$F7),"1"," ")

This formula was entered in cell I7. This formula tells excel to check if the value in cell I6 is greater than equal to the value in cell E7. Simultaneously it also tells excel to check if the value in cell F6 is less than equal to the value in cell F7. If both the conditions are true excel should print “1” otherwise leave a blank space. For the cell I6 we have used mixed reference. This is because this formula will be copied vertically as well as horizontally to cover the chart area. Therefore the formula should update itself both ways.

The formula will compare the values corresponding to each month with the start and end dates. If the value is between the start and end dates it will print 1 in the corresponding cells. If these conditions are not true it will leave the cell blank.

-After entering the formula copy and paste it across all the cells ranging from I7 to T26. You will get the following results:

-Thus all the cells which mark the beginning till the end of the activities are filled with 1. Our next step is to fill the cells containing “1” with a color of our choice using conditional formatting.

-To apply conditional formatting go to the conditional formatting button and choose “New Rule”. Now select the option “Format only cells that contain”. In the “format only cells with option” choose the options “Cell value” and “equal to” in the drop downs. Enter the value as “1” with double quotation marks.

-Click the “Format” button and in the “Format Cells” window go to the “Fill” option. Choose the fill color as “blue” or any other color of your choice. Press “OK”.

-We will get our Gantt Chart. But you must have noticed that the color filled cells still clearly show up

numbers. To conceal the numbers we can select the cells and go to formats. Select the option “More Number Formats”. In the “Format Cells” window go to “Custom”. In the text box “Type” enter “;;;”.This signifies that the cells have to be blank.

Press “OK” and you will have a perfect Gantt chart! You can format it, add borders and make it look better!

4.1.2 Metric 2: Cost ManagementThe most important information for project managers w.r.t cost is the variance between estimated and actual costs. We have created a table for estimated and actual costs. The variance has been calculated by this formula:

=E5-F5

This formula calculates the difference (variance) between estimated and actual costs. When this formula is dragged and copied across cells ranging from G5 to G16 we get the variance for each month.

Now in order to find out the months with zero variance or our best months we have used this formula in cell H5:

=IF(G5=0,"L"," ")

This formula tells excel to check if the value in cell G5 is equal to zero and print “L” if this condition is true. If the condition is not true it should leave the cell blank. This formula has to be copied and pasted across cells G5 to G16 to get the desired results.

We will address the need for doing this calculation later in the tutorial.

4.1.3 Metric 2: Issue ManagementIn our issue management datasheet we have prepared the data for issues raised, issues opened and issues closed for each month.

This data has helped us in calculating the issue open rates, close rates and backlog rates using these formulas:

Issue open rate (Formatted as percentage)

=E4/D4

Issue Close rate (Formatted as percentage)

=G4/D4

Issue Backlog Rate (Formatted as percentage)

=F4-H4

When these formulas are dragged and copied across the other cells below them we will get the respective rates for each month.

Note: All the values calculated have to be formatted as percentage to get the convert the results to a percentage form.

Having calculated these we can easily find out the maximum Issue open and close rates using these formulas:

Maximum Issue open rate

=IF(F4=MAX(F4:F15),C4," ")

Maximum Issue open rate

=IF(H4=MAX(H4:H15),C4," ")

These formulas tell excel to check if the values in cells F4 & H4 are greater than the values across the ranges (F4:F14 and H4:H14) respectively. If the value in cells F4 & H4 is more than the maximum value than excel should return the name of the corresponding month. When these formulas are dragged and copied in the remaining cells we get the month names with maximum issue open and close rates respectively.

Similarly for calculating the best months we have to compare the maximum issue open & close rates using the formula:

=IF(J4=K4,K4," ")

This formula when copied will give us our best months where issue open rate and close rates are equal.

4.1.4 Metric 3: Resource ManagementIn the data for this metric we have gathered the names of our resources, their billed hours(which can be obtained from the time sheets) and the organization’s actual hours. Actual hours can vary from organization to organization. We have calculated the actual hours like this:

Actual hours per month=Working hours per day*number of working days a month (this can vary, we have considered this 22)

=10*22=220

Through billed hours and actual hours we can calculate the employee utilization for each employee from January to December using this formula:

Employee Utilization%=Billed hours/Actual hours (Formatted as percentage)

Check the worksheet and you will get this!

Similarly we can calculate the “overutilized”,”Underutilized” and “Correctutilized” resources using a simple IF formula:

Overutilized resources for January

=IF($E6>100%,E6," ")

Underutilized resources for January

=IF($E6<90%,E6," ")

Similarly correct utilized resources for January

=IF(AND($E6>=90%,$E6<=100%),E6," ")

These formulas check whether the value in cell E6 against a fixed percentage value. If the condition is true it should print the value in cell E6 else should leave the cell blank. When this formula is dragged and copied across the required cells we will get our utilizations. This formula can be used for data of all the months to get the desired numbers.

We have also calculated the number of resources which have been overutilized and underutilized for each month using this formula:

No. of overutilized resources

=COUNT(F6:F55)

No. of underutilized resources

=COUNT(H6:H55)

We already know that we have retrieved the utilization percentages of the resources which have been overutilized and underutilized previously. Therefore the COUNT formula will count all the cells in the range which contains numbers. The cells which are filled with percentage values will be counted and we will get the number of resources which have been overutilized and underutilized.

The same calculation has been done for all the months.

4.2 Preparing ChartsLet us revisit our metrics again and understand how we prepared appropriate charts for each metric.

4.2.1 Cost ManagementWe have prepared a column chart with markers for representing our estimated and actual costs. Let us understand how this custom chart has been created.

Step 1: Select your data and insert a 2D column chart.

Step 2: Select the bars which represent the “estimated cost” series.

Step 3: Go to “Change Chart Type” option in the main Chart tab.

Step 4: Go to “Line” chart option and select the line chart fourth from left.

Step 5: Clicking “OK” will generate this chart.

Step 6: Select the line chart and press the right click button. Go to the option “Format data series”.

Step 7: Go to “Marker Options” and select the “Built-in” radio button. Select the “Type” as indicated in the snapshot. You should also reduce the marker size to “5”.

Step 8: Go to “Line Color” option in the same window and select “No Line”.

Step 9: We are done!

Creating Messages

Now you must be wondering how we have created these summary messages in Red.

It’s very simple.

Step 1: Just enter these messages in cells I7 and I8.

Step 2: Go to cell J8 and enter this formula:

=IF(H5="L",D5,"")

This formula tells excel to check if the value in cell H5 is Equal to “L”. If this condition is true then it should return the name of the month corresponding to the cell else leave a blank space.

Step 3: Drag and paste the formula across 11 cells and you will get the names of months with zero cost variance (our best months).

Maximum variance between estimated and actual costs can be obtained through this formula:

=INDEX(D5:D16,MATCH(MAX(G5:G16),G5:G16,0))

This formula tells excel to first find the maximum value across cells G5 to G16. The MATCH function tells excel to find the position of the position of the cell with the maximum value. The INDEX function finally tells excel to return the name of the month corresponding to the cell position returned by the MATCH function.

The summary messages have been created by simply concatenating the text in cells.

4.2.2 Issue ManagementThe chart for issue management is very simple. Let us understand how this chart was created:

Step 1: Select the columns for issues raised, issue open rate, issue close rate and issue backlog rates. You can select these columns by selecting any one column like issues raised and then selecting other columns while holding the CTRL key.

Step 2: Create a column chart and convert the “issues raised” series to a line chart.

Step 3: Now select your line chart for “issues raised” and press right click. Go to the option “Format Data Series”.

Step 4: Go to “Series Options” and select “Secondary Axis”.

Step 5: See the results for yourself!

The summary messages have been created through simple concatenation. Go through the

workbook and you will get it!

4.2.3 Resource ManagementYou might have noticed that we have created two charts for this metric. The summary chart is a simple column chart whereas the individual resource utilization chart is a dynamic chart.

Let us first understand how the resource utilization summary chart was created.

Step 1: Organize your data in a separately to create the chart. We have already calculated the no. of resources overutilized and underutilized for each month. In this step we just need to organize that information so that we can prepare a summary chart. We have used simple cell references to get that data into our table.

Step 2: Select the data and create a column chart. You have got it!

January

February

March

AprilMay June

July

August

Septem

ber

October

November

December

0

5

10

15

20

25

30

35

Resource Utilization Summary-2012

No. of Overutilized ResourcesNo. of Underutilized Resources

This was easy. Now let us understand the second chart which shows individual resource utilization trend and gives the user an option to select the employee name.

Step 1: First we need to create a table which contains the employee name and his/her utilization% for each month. To create such a table use cell references to retrieve the employee names. The formula in cell K66 is:

=INDEX(E6:E55,1,1)

This formula tells excel to find the item corresponding to the first row and first column. The value returned will be John’s utilization%. Remember, INDEX function returns the value at the intersection of a row and column. When this formula is copied across cells K67 to K115 we will get the utilization% for the month of January for all our employees. Similarly keep on changing the cell range in the function to get the utilization percentages for all the months. Finally we will get this table(Only a part of the table is shown):

Step 2: Now create the fields’ employee name and all the months(till December) in your calculation sheet.

Step 3: You might have noticed that there is a list box in the dashboard which allows you to select the employee name. So let us first create that list box. Go to the developers tab in the main menu.

Step 2: Now go to “Insert” and insert a list box at the place where you want the control to appear. You should directly insert it in the dashboard. For explaining purpose we have inserted it in the same worksheet.

Step 4: After inserting the list box press right click and got to the option “Format Control”.

Step 4: In the “Input Range” text box enter the employee names list from the table you just created.

Step 5: In the “Cell link” text box enter the cell H97. The cell link can be any other empty cell too.

Step 6: Choose the “Selection type” as Single and press OK. You will get the following:

Step 7: Now select any one employee for e.g. “John” in the scroll bar. Note that the value in cell H97 changes to 1. Enter this formula in cell J119:

=OFFSET(J65,$H$97,0)

This formula tells excel to go to the row employee name and return the item which cell H97 refers to. Cell H97 has the value 1 and it refers to “John”. So we will get “John” in cell J119.

Step 8: Now enter this VLOOKUP formula in cell K119.

=VLOOKUP($J$119,$J$65:$V$115,2,FALSE)

This formula tells excel to search for the item in the column next to the item in cell J119(“John”) in the range for utilization percentages.

Step 9: Drag and copy the formula for the rest of the months changing the column numbers to 3,4,5,6 and so on. You will get the following results.

Step 10: Now select a different employee from the scroll bar and see the results for yourself!

Step 11: Now the next table which calculates breaks up the utilization trend into three parts(Underutilized, overutilized and correctutilized) has been created using simple cell references.

Step 12: The utilization trend for each month has been calculated using simple “IF” formula. The whole idea is to show the three utilization sections(Underutilized, overutilized and correctutilized in different colors. Try to recall our conditional formatting lesson: Conditional formatting of charts).

Step 13: Select this data and create a column chart.

Step 14: Now select a different employee in the scroll bar and your chart changes automatically. This is a dynamic and an interactive chart!

Bigger Chart Smaller Chart

Bigger ChartSmaller Chart

Longer Chart which shows trends

Information Summary Picture/Image

Heading Picture/Image

The summary messages for this metric have also been created using simple MAX function and concatenating the cells. Check out the worksheet and you will get it!

5. Final Formatting and PresentationThis is our final step. This step decides the look and feel of our dashboard. Try to recall the dashboard layout which we thought of initially. Our job now is to arrange our charts and messages in this layout.

Before jumping into the layout concepts let us try to understand an important tool which we have used here: The camera tool.

A camera tool is a hidden tool in excel. Insert it like this:

-Click the office button the top of the excel workspace.

-Go to the “Excel options” in the window which appears.

-Click “Customize” and from the list choose “Camera tool”.

The camera tool allows users to create live preview of images. Whenever you make changes to your data the camera tool output changes automatically. This means that the images captured with camera tool are dynamic and adjust themselves automatically with change in data.

We have used camera tool outputs for our Gantt charts. The Gantt chart will change automatically in the dashboard when the activities start and end dates change.

To capture an image using the camera tool first select the chart or the area which you wish to capture and press the camera tool button.

Now press the cursor in any part of the worksheet (preferably a different worksheet). Your live snapshot is ready!

We have used the camera tool to take live snapshots of the Gantt Chart and the Summary Messages. This makes the Gantt Chart as well our summary messages dynamic. If the values and the associated data change the Messages and the Gantt Chart will automatically change in the dashboard. You may try changing the associated values yourself!

We have put everything as decided in the layout to get our final dashboard. Playing with colors and formatting depends upon your creativity. We have used simple background colors and text boxes to make it look attractive!

Let us explore out layout once more to see how everything fits in well.

Final Dashboard

References

1. http://en.wikipedia.org/wiki/Dashboard_%28business%29 2. Chandoo.org