A chart that I have to show each month combines a trend of current results with a forecast. As an example, for the month of May 2008 the chart would look similar to this:

Fast-forwarding a few months, in August 2008 it would look similar to this:

By comparing the two examples, you can see that the "Actual" line (red line) expands each month reflecting current results. Conversely, the "Plan" line (blue line) contracts as the year moves forward. A marker and label indicating the Actual and Plan net operating income is always shown for the current month.

Although the chart appears to be Line chart, it really is a combination Line-XY (Scatter) chart. The chart can be automated so that it automatically updates as the most current data is entered. To do so, the screenshot below shows the setup:

The Actual and Plan data are entered into the range C4:D15. The Chart Source and Data Labels Source ranges contain formulas that allow the chart to automatically update. At the start of the year enter your Plan data into the range D4:D15. At the end of each month the results are entered into the range C4:C15.

There are six different formulas that drive the chart. The first two formulas drive the Actual and Plan lines.

The first formula, which drives the Actual series, is entered into cell F4 and then copied through the range F4:F15. The formula is:

=IF(C4,C4,NA())

The second formula, which drives the Plan series, is entered into cell G4 and then copied through the range G4:G15. The formula is:

=IF(COUNT($C$4:C4)=COUNT($C$4:$C$15),D4,IF(C4,NA(),D4))

The remaining four formulas drive the points for the current month and use an XY (Scatter) chart as their source. The following formulas appear in the following cells:

Actual Label Sources

Cell J5: =COUNT($C$4:$C$15)

Cell J6: =OFFSET($C$3,COUNT($C$4:$C$15),0)

Plan Label Sources

Cell J10: =COUNT($C$4:$C$15)

Cell J11: =OFFSET($D$3,COUNT($C$4:$C$15),0)

Finally, the chart source looks like this:

Line Chart Sources

Actual Line Source

Plan Line Source

XY Chart Sources

Actual Marker and Label

Plan Marker and Label

John -

Good post, a nice chart type.

Your formula in G4 can be simplified:
=IF(COUNT($C$4:C4)<COUNT($C$4:$C$15),NA(),D4)

Also, the labels on the points for the latest month may need to be manually adjusted to prevent overlapping. But you can use two more cells to hold labels, say J7 and J12, with the following formulas:

J7: =IF(J6>J11,TEXT(J6,”$0.0”)&CHAR;(10)&CHAR;(10),CHAR(10)&CHAR;(10)&TEXT;(J6,”$0.0”))

J12: =IF(J11>=J6,TEXT(J11,”$0.0”)&CHAR;(10)&CHAR;(10),CHAR(10)&CHAR;(10)&TEXT;(J11,”$0.0”))

This puts two blank lines below the label for the higher point, and two blank lines above the label for the lower point. When the labels are centered on the points, they correctly display above the higher point or below the lower point.

Is there no value in looking at how actual and plan compare in past months? Perhaps to help improve future planning?

Jon,

Thanks for the simplified formulas and the label formulas.  I’ve seen the use of the “CHAR” several times before in the Charting forum but, for whatever reason, it never occured to me to use it like you’ve illustrated.

Actually, a comparison of actual vs. plan is what most of my presentation consists of.  The chart in the post is only one of many that we look at one a monthly basis and it is a very simplified version of the actual chart.  The actual chart tracks a 12-month moving average of actual vs. plan NOI and operating margin starting in year 2000 to present.  I’ll try to post that sometime in the future - using dummy data of course so I still have a job.

Thanks again.

John Mansfield