how to i create a comparision chart of target vs achieved.

[Vikas Gupta]

I want to create a chart of target vs achieved. Target is annual target and
achievements in month.

 

[LeAnne]

I want to create a chart of target vs achieved. Target is annual target > and achievements in month.

Target vs. achieved *what*? Funds? Solicitations? Widgets? Pickled
herring?

What kind of chart did you have in mind? Column? Pie? Line graph?
Combination (like maybe a horizontal line for the target and bars for
each month's "achievement")?

How are your data arranged in the worksheet? How do you want them
displayed on your chart?

You have given nothing that anyone can go on if they want to try and
help you. More info is needed.

Good luck,

LeAnne

 

[Vikas Gupta]

I would like to have a column chart, with the annual target on y-axis. The
months would be stacked on one upon other and showing the % achieved YTD.

Lets say the target for 2004 is 2000.

Region1: Jan: 125, Feb: 175, Mar: 180, Apr: 200 .....
Region2: Jan: 128, Feb: 95, Mar: 200, Apr: 210 ....
Region3: Jan: 128, Feb: 95, Mar: 200, Apr: 210 ....
Region4: Jan: 128, Feb: 95, Mar: 200, Apr: 210 ....

The Regions must be on X-Axis, and months stacked on one upon other, with
Comparison of achieved vs target. If % achieved is shown that would be very
helpful.

 

[Jon Peltier]

Vikas -

This is different than the arrangement I assumed from your original
post. Do you want Target (2000) on the Y axis, or % (100%)? I am
guessing from context you want the numbers, although if the goal is the
same for all regions, it hardly matters. Also, I am guessing you want
some kind of % achieved to date.

Here's how I'd do it. Arrange the data thus:

Rgn1 Rgn2 Rgn3 Rgn4
Jan
Feb
Mar
....
Dec
Sum
Goal
%YTD

Populate the grid with the monthly numbers. The Sum row has formulas
which total the months above. The Goal row has your 2000 target. The
%YTD has the Sum divided by the Goal, as a percentage. The top left cell
is blank.

Select this entire range except for the %YTD row but including the RGNi
headers and the Month column. Make a stacked column chart with series in
rows. Select the Goal series, choose Chart Type from the Chart menu, and
choose Line, the subtype with lines but no markers. Do the same for the
Sum series, then double click on it and format it to have neither lines
nor markers: we'll use this for our percent labels. The easiest way to
get the labels in place is to use a third party add-in, like Rob Bovey's
Chart Labeler (http://appspro.com) or John Walkenbach's Chart Tools
(http://j-walk.com); both are free and easy to install and use. Use the
add-in to apply the labels from the %YTD to the Sum series, and choose
the label position above the points.

You're done but for specific formatting of the various chart elements.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______