How to chart a profit & loss statement/Profitability analysis statement in Excel Charts

[Belinda]

Hello All

I want to chart a profit & loss statement / profitability analysis
statement in Excel Chart.

Essentially I have the numbers for the following in one of the Excel
worksheet:

=========================================
Gross Sales A
sales & Admin Expenses B
COGS (Cost of Goods Sold) C
=========================================
Gross Profit D = A-B-C
=========================================
Selling Expenses E
General & Admin Expenses F
=========================================
Total Expenses G = E + F
=========================================
EBIT (Earnings before H = D - G
interest & taxes)
=========================================
Interest Expenses & taxes I
=========================================
Net Income H - I
=========================================


I want to plot the above in a graphical chart form in Excel as
follows:



Gross +++++++++ |
Sales |
| Gross profit
Sales & +++ |-- ++++++++ |
Admin Exp | |
| |
COGS +++++++ | |
| EBIT
| +++++++++ |
| |
Selling Exp ++++++++ | | |
| Total Expen | |
Net Income
General & ++++ |-- +++++++ | | --
++++++++
Admin Exp | |
Interest |
Exp & taxes |
+++++++ |


I want to chart the numbers I have shown in Sheet1 above in Sheet2 as
a chart as I have featured above.

I am thinking of using column charts but further still not figuring
out how I can display these in the above form. Your ideas would be
highly appreciated how I can display these in the chart form indicated
above.

Also, apart from the column chart I am wondering I would like to use a
box to surround each of the above numbers appreciate if you can
provide some ideas how I could do this.

Thanks
Belinda

 

[Jon Peltier]

Belinda -

It's not exactly what you tried to draw, but you could use a Waterfall
chart to show all the costs removed from gross earnings. Here's one link:

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

Your gross income is like the Initial column in my example, and all the
other items in the table are yet another red bar dragging us closer to
zero. Whil I only had Initial and Final as full-height bars, you could
put in a full height bar wherever it makes sense.

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

 

[Belinda]

Belinda -

It's not exactly what you tried to draw, but you could use a Waterfall
chart to show all the costs removed from gross earnings. Here's one link:

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

Your gross income is like the Initial column in my example, and all the
other items in the table are yet another red bar dragging us closer to
zero. Whil I only had Initial and Final as full-height bars, you could
put in a full height bar wherever it makes sense.

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

Hi Jon

Many thanks again for your inputs apart from Waterfall I thought of
using the Pie of Pie and a Pie of Stacked bar chart to solve the above
problems briefly my ideas on the above was as follows:

To show the EBIT as a Pie of Pie and a Pie of Stacked bar chart what I
mean is to show the EBIT as a pie chart of Gross Profit and Total
expenses. And then show the Gross Profit as a stacked bar chart as
part of the Pie of stacked bar. Also if possible as part of the EBIT
to show the Total Expenses as a Pie of Pie explosion.

I do not know how to put together a Pie of Stacked bar or a Pie of
Pie. I battled trying to put together a Pie of Stacked bar but could
not get it to work appreciate if you can point me to some resource on
exmples of putting together a Pie of Pie and a Pie of Stacked Bar
chart.

Thanks
Belinda

 

[Jon Peltier]

Belinda -

I just answered your speedometer gauge question. Small world.

To do a Pie of Pie or Bar of Pie, set up your data so that the items
broken out in the second chart are at the bottom of the list. Make the
chart, and Excel puts an arbitrary number of points into the second
chart. Double click on one of the pies, and on the Options tab, where it
says Second Plot Contains the Last: enter the number of points you want
broken out. There are other interesting options there as well.

I'm not sure if you were asking this question, but you can only break
out one group of data in a single X-of-Pie chart. You can fake it,
though, by drawing multiple regular pie charts, and arranging the broken
out ones as satellites around the main one. Double click on the chart
area, then the plot area, and change each to have no border and no fill.
This way, they won't obscure each other when you move them close together.

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