Dependent Dropdown

Just learned a new trick called dependent drop-down

Two drop-downs where the values in second drop-down depends on the value in first drop-down

It uses data validation and INDIRECT function

In our case we use continents as first drop-down and different countries as second drop-down

Name ranges with their respective headings like continents, Asia, NorthAmerica ….

Quick tip: you need not to go to name manager to name a range it can be done directly by selecting the range and putting name in Name Box adjacent to Formula bar

make first drop-down in any cell e.g. C12 Go to

data -> data validation,  allow -> list , source =continents

make second drop-down where source =INDIRECT(\$C\$12)

INDIRECT function returns the reference specified by the text string

there you are….

//

EBIDTA bridge(waterfall) chart

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.

Chart 1

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…

Chart 2

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.

Bar Chart Right to Left Side

Data

Bar Chart

Add one more data column before the given data

with formula =MAX(\$C\$21:\$C\$29)+20-C21 (drag down), assuming data in C21:C29

20 is any relatively small number you like

Goto

Insert -> Chart -> bar -> 100% stacked bar chart

use color-> no fill to make blue bar invisible

select y-axis(1,2,3……)  press  ctrl+1

in axis option -> axis label select high

add data labels to red bars

format chart area as you like

( remove x-axis, legend)

there you are….

Concentric circles in MS excel/powerpoint/word

I personally like excel, so i will refer it

1) draw no. circles one bigger than other

(bonus tip : if you hold shift key before drawing circles, they will automatically be perfect )

2) put circles one over other, using format –> arrange —> bring to front/send to back

3) select all circles ( hold ctrl key to select all circles)

4) go to format –> arrange –> align

align center vertically and then align middle horizontally circles

there you are…..