Bar graph showing cost components from EBITDA -> Net Income

ssiao

New Member
Joined
Apr 26, 2013
Messages
8
Hello!

I am new to this board.
Today I was asked to prepare a bar graph from 2005 to 2012 showing the EBITDA and the other cost components below EBITDA to reach final Net Income. At first I thought it would be easy to prepare, but I now realize that to reach the Net Income, the other cost components must be taken out of EBITDA. So how can I make a bar graph that goes from EBITDA + Other Cost to Net Income?

I have been thinking about this graph most of the day today but could not come up with good solution.
If anyone can help, I would really apprecaite it.

Thank you!
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,797
Office Version
365
Platform
Windows
Welcome to the board.

I think if I were to show the key components between EBITDA and Net Profit I would probably go for a waterfall chart. Are you just looking at a single period (month/qtr/year)?

See waterfall chart tutorial here.

The "initial" would be EBITDA and the "final" net profit. Not knowing your P&L I would only be able to speculate what items fall below the line, but I presume you have a bunch of GL account codes that apply; and perhaps that you can further categorise.
 

ssiao

New Member
Joined
Apr 26, 2013
Messages
8
Thank you for the quick response! I thought about waterfall graph also, but the request was bar graph to show trend across the years (from 2005 to 2012). I am a bit stumped on this request.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,797
Office Version
365
Platform
Windows
Is your net profit always positive, or do you have some losses an one or more periods?
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,797
Office Version
365
Platform
Windows
Typically your EBITDA will always be greater than your Net Profit, unless you've got some strange happenings on the "below-the-line" GL's. So I would use a stacked bar chart where the 1st stack of a bar shows Net Profit. Then each stack on top of that will show the individual "below-the-line" items, finally ending at your EBITDA position. Each bar would represent a different period.
 

ssiao

New Member
Joined
Apr 26, 2013
Messages
8
The net profit is always positive; however, some cost components below EBITDA can be positive or negative depending on the year.
 

ssiao

New Member
Joined
Apr 26, 2013
Messages
8
Thank you for the suggestions. Just now I tried the stack bar graph; however, it is adding up the EBITDA and "below-the-line" items. The final net income position on the graph is the total of EBITDA + below the line. This graph request may not be possible, so I will see what alternative graphs I can prepare instead. Thank you!
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,797
Office Version
365
Platform
Windows
You need to start with Net Profit, not EBITDA, and add on from that.
 

ssiao

New Member
Joined
Apr 26, 2013
Messages
8
Thanks a lot! I think I finally got it! I was including the EBITDA in the bar graph itself.
When I remove the EBITDA amount, the amounts of Net Profit + Below the Line items = EBITDA.

This will work! Thank you so much!
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,797
Office Version
365
Platform
Windows
Thanks a lot! I think I finally got it! I was including the EBITDA in the bar graph itself.
When I remove the EBITDA amount, the amounts of Net Profit + Below the Line items = EBITDA.

This will work! Thank you so much!
Bingo ;) Good luck. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,095,544
Messages
5,445,099
Members
405,316
Latest member
joaoamaro

This Week's Hot Topics

Top