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

Image

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….

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

//

Advertisements

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

 

Bar Chart Right to Left Side

Data

Image

Bar Chart

Image

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

Image

Goto

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

Image

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)

Image

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

CC

there you are…..