Sample Budget and Actual Data
I will not go in the detail of making bridge/waterfall chart as excel gurus like Chandoo/Peltier have written a lot about it.
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
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…
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