EBIDTA bridge(waterfall) chart

Sample Budget and Actual Data

Image

I will not go in the detail of making bridge/waterfall chart as excel gurus like Chandoo/Peltier have written a lot about it.

Chart 1

Image

The Chart 1 gives absolute variances between budget and actual, relative variance chart is discussed below

We need to move from budget to actual P&L by changing numbers one at time like quantity, price, variable cost, fixed cost and then make P&L as per budget to arrive at relative profit as shown below

Image

The blue column is budget

In quantity column-> sales = Act Qty x Budget Price, all other things as % of budget

In price column -> sales= Act qty x Act price, all other things as % of budget

In VC column -> VC= Act variable cost, FC as % of budget

In FC column -> FC = Act fixed cost

Remember in variable cost column I have not changed sales form price column, similarly in fixed cost

What I m essentially doing is moving from budget to actual P&L step by step. The first column and last column are budget and actual P&L.

It gives relative increase/decrease in profit vis-a-vis budget , which tells me that

Because of high qty sales and everything else as per plan, I would have got 1200 profit

Because of lower price and everything else as per plan, I would have got 1120 profit

and so on…

Chart 2

Image

and also if you compare sales figure of chart 1 & 2, 540 of increase in sales translated to only 120 increase in profit

similarly 200 of savings in fixed cost led to 320 of increase in profit.

file link: https://docs.google.com/file/d/0B2FLZ9YjqpJTM3hHX3J3UGlVLVk/edit?usp=sharing

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s