Seeking example of buget vs. actual for multiple team members

[Guest]

I am looking for an example to help me to be able to accomplish a task.

I have an excel spreadsheet that contains the following information:

column A - team member names
col B - January budget amount
col C - January actual amount
col D - Feb budget
col E - Feb actual
the rest of the columns contain budget and actual for the rest of the year.


I am having trouble using the chart wizard to create what I want.

The purpose of my chart is to show easily how each team member is exceeding
budget numbers across the year. The chart wizard gives me a legend with lots
of colors. I don’t want the legend or the numerous colors.

I would like the team member names going down the vertical axis and would
like budget in one color and actual in another color --- or--- budget shown
with a bar and the actual shown with a line. It is separating the budget and
actual bars. I would like the budget and actual for a person right next to
each other. I would like to see the months highlighted or labeled somehow.

Maybe there is a better way to show this. I am new to charting in excel so
any advice on a better way to show this would be appreciated.

I would love to have examples or instructions on this.
Thank you so much for your time.

 

[Guest]

NextTry this Leila,
Column A Column B Column C
Name Budget Actual
Someone $100 $100
Someone $100 $95
Someone $100 $110

Select "Chart Wizard".
Column, and the default choice in the upper left corner.
Select"Next>"
Data Range: Select the first name and drag to the last entry in the actual
column.
In my example, A2 thru C4. it will look like this in the Data
Range:"=Sheet1!$A$2:$C$4"
Select the "Series" tab.
Select "Series1", in Name: type ="Budget"
Select"Series2", in Name: type ="Actual"
Select "Next>"
Fill in info as you see fit.
Select"Next>", choose sheet or insert.
You are all done.

Good Luck, hth

Dennis

 

[Del Cotter]

I am looking for an example to help me to be able to accomplish a task.

I have an excel spreadsheet that contains the following information:

column A - team member names
col B - January budget amount
col C - January actual amount
col D - Feb budget
col E - Feb actual
the rest of the columns contain budget and actual for the rest of the year.

I am having trouble using the chart wizard to create what I want.

You have quite a complex data set there, with four dimensions (team
member, month, budget/actual, and amount). I would recommend what's
called a "small multiple", where instead of putting everything on the
same graph, multiple graphs are presented, each one small and exactly
the same format as its neighbours.

One way to have a small multiple chart in Excel is to make what's
sometimes called a *panel chart*, because the different graphs are like
panels separated by borders. Two examples of web pages showing how to
make a panel chart are by Kelly O'Day:

http://processtrends.com/toc_panel_charts.htm

and Jon Peltier:

http://peltiertech.com/Excel/ChartsHowTo/PanelChart1.html

In the Peltier example, the months are represented by tiny letters
JFMAMJJASOND, and your budget and actual could be two colored lines.
Each team member name would be a separate panel.

Alternatively, you could combine the actual and budget into a single
dimension, a percentage (actual/budget) then you would have only three
dimensions, and could try creating a 3D bar chart using the Excel
wizard.

It really depends on how many team members you have in total. if it's
much less than twelve, let's say only six people, you may even be able
to get away with a line graph with twelve lines, if you choose your
colors *very* carefully. Make the actual lines in six good colors (this
is where it gets very hard-- you have to be good at choosing colors),
then make the budget lines either in darker versions of the same six
colors, or in the exact same six colors, and a different line style.

This depends on you being lucky enough that none of the lines interfere
with each other and cause confusion. On the whole I would suggest the
first option, the panel chart, which avoids all the dangers by
separating the lines out into separate areas. Then there's no need to
worry about color choice, no matter ho many team members you have, just
red for budget and blue for actual, or whatever you prefer.

If you find the panel chart tough to design, you could just print out as
many separate charts as you have team members, and pin them up next to
each other. That's a multiple too!