My favorite KPI to implement in a dashboard is an extension of the Return on Sales ratio. This KPI or set of ratios is formally called Common size income statement but is often seen in an income statement as a percentage of Sales. I first came across this in an international business class on evaluating company profitability through financial statement analysis.

Common size income statement lends itself particularly well to dashboards presented as a period-over-period line graph that allows you to see the shape and trend of the data, rather than just the numbers.

To create a common size income statement, start with the key subtotals of a standard income statement divided by total revenue. Here is an example of XYZ Company from an income statement template with a percentage of Sales column:

Figure 2-8: XYZ Company's income statement year-over-year with percentage of Sales

This is a very useful document, particularly if we have drilldown capabilities, but it definitely requires time to review the comparisons. If we look at the bottom line, we can see that Net Income has not only gone up, but has gone up as a percent of Sales. But this is not the information that we want a dashboard to convey quickly. This is what we would use with the drilldown to investigate the underlying details. Now, we want to bring those percentiles into a month/year-over-month/year line graph.

We begin by loading our year-over-year spreadsheet to a QlikView document.

Your script should look like this, except for the path of IS3yr.xls:

LOAD NewCompany, 
   Group, 
   Account, 
   [2007], 
   [2008], 
   [2009], 
   [2007 ROS], 
   [2008 ROS], 
   [2009 ROS]
FROM
[C:UsersUserDocumentsMy DocsPackt PublishingQlikView Finance BookQVFA Chapter2IS3yr.xls]
(ooxml, embedded labels, table is IS3yr);

Now, we will create our line graph for a common size income statement.

Create a new Sheet tab if desired:

Navigate around our new chart. Notice that you can get popup boxes with the information that gives the exact percentages for the area. You can select a year from the Legend and highlight the entire line by thickening it. If you click on a point, the graph will change to a percent range showing each of the 3 years as dots.

You can hover over the dots to get the information; the distance between the dots is increased so that they are easier to compare. Use the Clear button to return to Chart and show the original display.

In our new chart, we can make the following observations:

In this line graph, we can see that, overall, we have managed quite well in June 2009. Our net profit has increased in comparison to our costs and expenses, but we already knew that from our income statement.

We can see that this is, in part, because we have controlled our advertising expenses compared to 2007 without hurting our sales.

We can also see that we have kept our taxes in the same bracket with only slight increases.

Our total G&A expenses have gone up disproportionately to the preceding year, so we would want to examine that in more detail to see whether there are potential cost savings or if, perhaps, we have created new jobs giving us tax breaks, thereby increasing our net profit margin.

Because the lines are so close together (we are using the numbers from the sample Income Statement shown earlier in this chapter), we might want to change our image to a bar chart. To do this, right-click on Common Size Income Statement, which we have been examining, and on the General tab under the Chart Type section, choose the Bar Chart (#1 in Figure 1 of this chapter). Now, our chart looks similar to the next image:

Figure 2-10: Common size income statement as a bar chart

We also want to keep in mind the timing variances that might affect a particular month. Because of the potential for timing variances, we would want to be able to look at total year-over-total year at the year's end to forecast for the coming year. We will examine a year-over-year-by-month combination bar and a line chart in Chapter 5, QlikView Sales Analysis.

Other things that are readily visible are that we have kept our cost of goods sold in line, whilst increasing our gross profit margin, and kept the growth in salaries and wages to produce sales in parallel with the growth in sales. This might possibly indicate that increasing our sales force would increase our profits further.

We instantly get a day-to-day visual of how we are managing our sales and expenses compared to prior years, and, when the month closes, how we have done.

No KPI or dashboard widget will replace experience and business common sense, but good dashboard KPIs can lead an executive to ask the questions that will lead to business improvement.