Adding an Arbitrary Target

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.

In yesterday’s post, Add a Target Line, I showed how to add a simple horizontal line target indicator to a chart. What if your target isn’t constant. I’m not talking about your boss’ moving target, but a case illustrated by the following dummy data. Our company makes widgets, which are more popular in spring and summer, as well as at the year end holidays. Most of the year our target is 20 units, but from April through August the target is 25, and in December we aim for 30 units.

We could simply create a line chart to show both the target and actual values. However, the target looks too much like the actuals, and we’d like them to look more different, so they can be distinguished at a glance.

The two lines are even more difficult to distinguish when the chart is shrunk to typical dashboard chart dimensions.

One way to distinguish the two series is to right click on the target series, choose Chart Type, and select an area type. Note that this moves the January and December data points to the edges of the chart.

A lighter target series fill color helps emphasize the actual values.

We can even dispense with the colors and switch to grays for improved photocopying results, or actually choose any color scheme that isn’t overpowering.

Another approach is to convert the target series to a column chart.

We can reduce the gap width to zero, and choose a lighter color, to de-emphasize the target.

The gray scheme from above works well for the column chart variant too.

The chart can be improved further by removing axis lines, applying a gap width of 5 to the columns, and pulling the month labels inside the bottoms of the bars to expand the vertical scale. (Excel doesn’t allow axis tick marks without the axis line, so I added an XY series with no line or markers but with short error bars.)

We can shrink the charts even further (the following are narrower but slightly taller). The dual line chart is no better than in the larger sizes.

The area and both column versions of the target series work well.

- - - -

Possibly Related Posts:

Bookmark and share this entry:

Posted: Wednesday, July 30th, 2008 under Combination Charts.
Comments: 14

Comments

I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.

Read the PTS Blog Comment Policy.

Comment from Alvaro Ledesma
Time: Wednesday, July 30, 2008, 9:53 am

Jon, What I think is missing is your example from previous post: targets as columns with border / no fill. Either version of that formating (target wider than actual or the actual filling-up targets) deserve to be included in this post.

Regards

Alvaro

Comment from Jon Peltier
Time: Wednesday, July 30, 2008, 9:59 am

Alvaro - For time series data I’d rather keep my series of interest (in this case, actual values) in a line/XY chart format.

However, if in the previous example you set different goals for the categories in the chart, you can readily show them using the column/column formats.

Comment from PragmaticCynic
Time: Wednesday, July 30, 2008, 11:47 am

Jon,

Generally I do the area/line approach but make the actual data into the area or column and the target as a line. People tend to think of targets as goals and goals as lines.

Comment from Jon Peltier
Time: Wednesday, July 30, 2008, 11:54 am

With the line always appearing in front of the area or column, it makes sense to me that the line is the important data (the actuals), so I use the background data for the target, and use lighter colors to de-emphasize it.

As long as those reading your chart understand your style, I guess it’s more important to follow your style systematically.

Comment from AlexJ
Time: Wednesday, July 30, 2008, 9:39 pm

Agreeing with PragmaticCynic and disagreeing with Jon (sorry, Jon), my background in the process control industry leads me to follow their standard for operator interfaces: Target (setpoint) is a line, actual (process variable) is a bar. This provides a standard method for the operator to assess operational state for many control loops. (Come to think of it, the display is much like a vertical bullet graph!)

Translating that to financial forcasting approach by month over a full year, I organized my graph to provide:
1 - a moving monthly target for each month of the year
2 - a monthly bar represents actuals in past months of the year, but in future months from now to the end of the year, a stacked bar represents the actuals expected from backlog (orders in house) plus revenue from forecasted orders.

This approach provides a one-glance forecast of performance against plan to the end of the fiscal year.

~~(Let me know how I could upload an example to you. I’d even be willing to be the subject (victim?) of an Improve This Graph exercise.)~~

Alex provided me with the following charts, which I have just now gotten around to adding.

Comment from Jay
Time: Friday, August 1, 2008, 4:03 am

Jon,

While reading your valuable insight into the target-value graphing, I wish to know what is the best way to “animate” these graphs? How do I show this graph in the presentation (powerpoint) so that first I show the values and then plot the target, to reinforce my point and to focus on the discussions. Am I forced to use two different charts or is there a better way?

Thanks

Jay

Comment from Jon Peltier
Time: Friday, August 1, 2008, 11:03 am

Jay - I rarely animate objects on slides in PowerPoint. I use PowerePoint as much as a static snapshot, or archive, as for official presentations.

I do know that PowerPoint has some capability to animate charts, by allowing you to select different timings for thee appearance of series in the chart. I did a quick Google search on the keywords “powerpoint animate chart” and several promising links came up.

Comment from derek
Time: Friday, August 1, 2008, 4:02 pm

I just tried it out on ancient Powerpoint 95, and Animation Settings lets you start a slide with one frame, “build” a first image over that, then another over that. If you align several charts on top of one another, I see no reason you couldn’t have quite a show, clicking to “advance” the action as you speak, bringing new features of the chart in.

Of course, even without animation settings, you can do this simply by having near-identical looking slides in sequence. Animation probably just makes developing a slide easier, not having to copy each ad hoc change in ,e.g., title font across the whole sequence.

Comment from Jon Peltier
Time: Friday, August 1, 2008, 11:50 pm

Derek -

Those “poor man’s” animations are certainly possible. I do know that I’ve seen examples in which Excel charts or perhaps MS Graph charts are ungrouped by PowerPoint in order that certain chart elements can be displayed in a prescribed sequence. I should figure out how and post it some day.

Pingback from Excel links of the week : ‘what can excite you most’ [Aug 4] | Pointy Haired Dilbert - Chandoo.org
Time: Monday, August 4, 2008, 12:11 pm

[...] More on adding target lines your bar charts, Jon at PTS blog provides more alternatives for adding target lines to bar charts to show target vs. actual performance. This can be useful if you build dash boards and need to show more details. [...]

Comment from John Gardner
Time: Friday, September 19, 2008, 1:59 am

AlexJ: The entry from the previous day was done as you suggest. I wonder why Jon switched from line=target to line=performance?

Comment from Jon Peltier
Time: Friday, September 19, 2008, 7:59 am

John - The two cases are different. In the earlier post the chart was comparing several discrete categories against a single target, say, words per minute vs. job applicants, or monthly sales objective vs. sales staff.

In this post I was looking at a more continuous independent variable, specifically monthly sales target vs. month of the year. I used a lighter fill for the target, and a darker line for actual values, intending the actual values would thus have greater emphasis.

Comment from JoLeeAnn
Time: Wednesday, October 22, 2008, 2:38 pm

I am using a bar/line graph:

Line being target
2+ value points equal my bars

I am trying to get the target line to go from the left Y axis to the far right side of graph.

Current results are that the line goes from mid-point to mid-point of the X axis points.

Current fix is to draw a line from one end to the other. (I don’t want to draw a line).

Please help

Main menu:

Subscribe

Site search

Recent Posts

Recently Commented

Popular Posts

Archive

Categories