Each month, I compile a column chart based on data plucked from my company's
P&L (Income Stmt). The idea is to create a graphical representation of how
individual Inc. Stmt line items (Revenue, Benefits, Labor, Total SGA Costs,
etc.) positively or negatively impact EBITDA (bottom line) as related to the
actual vs. budget variance. Here is a sample data table that the chart uses:

YTD Base Plan/Act Change Increase
Decrease
Prior Yr. 2006 mth EBITDA If actual
# If actual #
Plan Budg. for mth better
than worse than
Revenue
budget = budget = var.
Labor & Ben
variance between the 2;
Equip Expense between the
2; Else 0
HR Fees Else 0
Total SGA ABS of I/D #
Cont. Srv.
Other Costs
Actual Act. EBITDA mth

Base starts in revenue row and is a running total of EBITDA after taking
into account the Absolute value of either the increase or decrease (variance
between budget and actual #s) of each particular row item (revenue, Labor &
Ben, etc.). Ultimately you end up with the Actual EBITDA at the bottom and
the chart graphically shows whether a particular row item positively or
negatively affected EBITDA. Prior Year and Plan are charted and the Rev.
column begins at the plan (Base) and either extends upward or downward by the
amount of the increase/decrease it caused EBITDA to have. Then the next line
item starts from where Revenue stopped and so on and so forth. Until at the
end, the last line item (All Other Costs) ending data point is even with the
Actual EBITDA. I realize this might be kind of confusing but I can email you
a picture of everything if you are willing to help.

The problem lies when Prior Year and Plan Base numbers are negative.
Instead of flowing through the chart from row item to row item and beginning
where the base of the previous one left off, all of the columns start at $0
(X axis) and extend up the amount they are suppossed to. Usually each new
row item begins at a different number or point on the graph. When the
initial numbers off which the original base point is based are negative, the
graph looks completely wack. I need to try to figure out a way to start the
next item where the previous one left off.
EX: Say plan is $300 and revenue actual vs. budget has a positive variance
and increases EBITDA by 50. Normally the column for revenue would start at
300 and stop at 350 where the next one would then start. But if plan is
$-300, instead of revenue starting at -$300 it starts at $0 and moves up $50.
Then the next row item's column starts over at $0 again.

Does anyone know how I can fix this problem? Any tips or help would be much
appreciated. I have been racking my brain and tried just about everything I
can think of. Sorry this was so long but I had to give alot of background
information. If you are willing to help shoot me an email to
with your return email address and I will send you
one of these charts and data tables to provide better information.
Thanks

On Wed, 16 Jan 2008, in microsoft.public.excel.charting,
sauza311 said:

The problem lies when Prior Year and Plan Base numbers are negative.
Instead of flowing through the chart from row item to row item and beginning
where the base of the previous one left off, all of the columns start at $0
(X axis) and extend up the amount they are suppossed to. Usually each new
row item begins at a different number or point on the graph. When the
initial numbers off which the original base point is based are negative, the
graph looks completely wack. I need to try to figure out a way to start the
next item where the previous one left off.

It sounds like you're saying "I have a waterfall chart that goes wrong
when it crosses the X axis". If so, have a look at this page on Jon
Peltier's site for ways to fix the problem:

http://peltiertech.com/Excel/Charts/...lcrossing.html

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

thanks for the response. I actually found that site a couple of hours ago.
Unfortunately for me, my numbers start negative and I have to use a different
series (Last Year's Number) as one of my endpoints. I tried to follow his
example but it didn't work for me. I have logically figured out what's going
wrong but still haven't found a solution. At first I tried putting ABS
values around all of the negatives, but it still didn't come out right. The
problem with the examples I found on the Internet is that they don't have
that extra BS line item (maybe if i took it out it would be easier? hmm...)
and everything goes crazy when it starts negative. Yes, the problem is that
each successive new column always starts on the Xaxis at $0 and doesn't begin
where it is suppossed to. If you think of a logical way to do these things
let me know.
Thanks
"Del Cotter" wrote:

On Wed, 16 Jan 2008, in microsoft.public.excel.charting,
sauza311 said:

The problem lies when Prior Year and Plan Base numbers are negative.
Instead of flowing through the chart from row item to row item and beginning
where the base of the previous one left off, all of the columns start at $0
(X axis) and extend up the amount they are suppossed to. Usually each new
row item begins at a different number or point on the graph. When the
initial numbers off which the original base point is based are negative, the
graph looks completely wack. I need to try to figure out a way to start the
next item where the previous one left off.

It sounds like you're saying "I have a waterfall chart that goes wrong
when it crosses the X axis". If so, have a look at this page on Jon
Peltier's site for ways to fix the problem:

http://peltiertech.com/Excel/Charts/...lcrossing.html

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.

I don't usually do this any more, but send me the data, and I'll chart it.

jon at peltier tech dot com

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


"sauza311" wrote in message
...
thanks for the response. I actually found that site a couple of hours
ago.
Unfortunately for me, my numbers start negative and I have to use a
different
series (Last Year's Number) as one of my endpoints. I tried to follow his
example but it didn't work for me. I have logically figured out what's
going
wrong but still haven't found a solution. At first I tried putting ABS
values around all of the negatives, but it still didn't come out right.
The
problem with the examples I found on the Internet is that they don't have
that extra BS line item (maybe if i took it out it would be easier?
hmm...)
and everything goes crazy when it starts negative. Yes, the problem is
that
each successive new column always starts on the Xaxis at $0 and doesn't
begin
where it is suppossed to. If you think of a logical way to do these
things
let me know.
Thanks
"Del Cotter" wrote:

On Wed, 16 Jan 2008, in microsoft.public.excel.charting,
sauza311 said:

The problem lies when Prior Year and Plan Base numbers are negative.
Instead of flowing through the chart from row item to row item and
beginning
where the base of the previous one left off, all of the columns start at
$0
(X axis) and extend up the amount they are suppossed to. Usually each
new
row item begins at a different number or point on the graph. When the
initial numbers off which the original base point is based are negative,
the
graph looks completely wack. I need to try to figure out a way to start
the
next item where the previous one left off.

It sounds like you're saying "I have a waterfall chart that goes wrong
when it crosses the X axis". If so, have a look at this page on Jon
Peltier's site for ways to fix the problem:

http://peltiertech.com/Excel/Charts/...lcrossing.html

--
Del Cotter
NB Personal replies to this post will send email to
,
which goes to a spam folder-- please send your email to del3 instead.