Target vs Actual Chart – Visualizing Performance Metrics

The target vs actual chart is frequently used to present various Key Performance Indicators (KPIs) over time. For instance, it allows us to display sales figures compared to the forecasted sales on a single chart. There are several different ways to present target achievement, and two of them are discussed below:
The chart on the left combines a column chart, where the target values serve as the background for the actual values column. The second chart is a combination of a column-line chart, where the target value is represented by a line, and the actual values column aims to intersect with it. Let’s take a closer look at how to create both of these charts.

Chart Construction

To perform the task, I used quarterly sales data from Adventure Works in 2013. The first step was to create a sorted set of employees and their sales results by quarters (1) and add additional columns indicating the target, which can be determined by any method. Then, I summarized the data, breaking it down into Target and Actuals by quarters and created a validation list, allowing me to choose between individual employees or the total, and added formulas to sum the results (2). Later, I proceeded to create the charts (3 and 4).
Let’s first examine chart number 3. It is a column chart containing two data series: Target and Actuals. I selected the target series, right-clicked to bring up the format data series, and, by choosing the Secondary Axis option, moved the data to the second axis, causing the columns to overlap. The next step was setting the gap width between the columns to 220%, ensuring that the Actuals column fits within the Target column. The remaining settings involve user preference formatting.
The second chart features a combination of series. Actuals values are represented again on a column chart, while the Target values are on a line chart. In this case, I disabled the fill color for the Target series in the data series formatting options and created markers shaped like horizontal lines using built-in markers, setting their size to 20. This ensures that the markers are roughly the width of the columns.

Summary

In this post, I answered the following questions:

  1. What is a target vs actual chart, and what indicators can be presented on it?
  2. How to create a target vs actual chart in Excel using two different methods?

As always, I encourage you to practice the solutions on sample data.

Interesting article? Share further!

Leave A Comment