Charting Forecast against actual

[jdawg_1989]

I'm after some advice...

I have a glide path which shows a forcasted cumulative and actual cumulative for the 12 months of the year.

The problem is this....We are now nearly Mid July and the Forecasted figure for the end of July is 85, but to date we have done 78.

This throws the chart out as it think we are below forecast, but infact we are on track to meet the 85 by the end of the month.

What would be the best way for me to fix this? Attatched is some sample data. If you produce a chart from the following you should see what I mean.

Thanks in advance.
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: center;;">Forecast (Cumulative)</td><td style="text-align: center;;">Actual (Cumulative)</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Jan</td><td style="text-align: center;;">20</td><td style="text-align: center;;">30</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Feb</td><td style="text-align: center;;">30</td><td style="text-align: center;;">35</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Mar</td><td style="text-align: center;;">40</td><td style="text-align: center;;">44</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Apr</td><td style="text-align: center;;">50</td><td style="text-align: center;;">57</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">May</td><td style="text-align: center;;">60</td><td style="text-align: center;;">71</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Jun</td><td style="text-align: center;;">70</td><td style="text-align: center;;">77</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Jul</td><td style="text-align: center;;">85</td><td style="text-align: center;;">78</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Aug</td><td style="text-align: center;;">95</td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Sep</td><td style="text-align: center;;">105</td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Oct</td><td style="text-align: center;;">115</td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Nov</td><td style="text-align: center;;">125</td><td style="text-align: center;;">#N/A</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Dec</td><td style="text-align: center;;">135</td><td style="text-align: center;;">#N/A</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

 

[Jon Peltier]

You could (a) not put the sum into the July cell until July is over, which means you plot only the total through June, or (b) plot by actual date, using 1/31/2011 for Jan, 2/28/2011 for Feb, etc., and today's date to plot the partial month of July.

 

[jdawg_1989]

You could (a) not put the sum into the July cell until July is over, which means you plot only the total through June, or (b) plot by actual date, using 1/31/2011 for Jan, 2/28/2011 for Feb, etc., and today's date to plot the partial month of July.

Hi, Thanks for the reply

The problem with (a) is that the client wants to see our current position on a weekly basis, and cannot wait until the end of each month.

I've thought about (b) but couldn't think of a way to display it. Sorry to be really dense but could you give me an example of how you would do it?

Thanks.

 

[Jon Peltier]

Set up your data like this. Don't worry that the value for today's data is at the very end. When you make a line chart with the data, Excel will internally sort it properly.

             Fcst   Actl
01/31/2011    20     30
02/28/2011    30     35
03/31/2011    40     44
04/30/2011    50     57
05/31/2011    60     71
06/30/2011    70     77
07/31/2011    85    #n/a
08/31/2011    95    #n/a
09/30/2011   105    #n/a
10/31/2011   115    #n/a
11/30/2011   125    #n/a
12/31/2011   135    #n/a
07/11/2011   #n/a    78

Select this range and make a line chart. Format the scale of the chart to make sure the base unit is days, the major unit is 1 month, and the min and max are 1/1/2011 and 12/31/2011. Use a number format like mmm yy so the labels look like Jan 11, Feb 11, etc.

I would format the Actual chart series as a thin line with markers and the Forecast series as a thicker line without markers.

 

[jdawg_1989]

Set up your data like this. Don't worry that the value for today's data is at the very end. When you make a line chart with the data, Excel will internally sort it properly.

             Fcst   Actl
01/31/2011    20     30
02/28/2011    30     35
03/31/2011    40     44
04/30/2011    50     57
05/31/2011    60     71
06/30/2011    70     77
07/31/2011    85    #n/a
08/31/2011    95    #n/a
09/30/2011   105    #n/a
10/31/2011   115    #n/a
11/30/2011   125    #n/a
12/31/2011   135    #n/a
07/11/2011   #n/a    78

Select this range and make a line chart. Format the scale of the chart to make sure the base unit is days, the major unit is 1 month, and the min and max are 1/1/2011 and 12/31/2011. Use a number format like mmm yy so the labels look like Jan 11, Feb 11, etc.

I would format the Actual chart series as a thin line with markers and the Forecast series as a thicker line without markers.

Implemented this first thing this morning.

It solved my problem, Thanks for your help