Actual vs. Plan Crawl Chart
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?