# Tutorial: How to report on KPIs #2: Product mix and margins. By David Carter

Following his first article on reporting product mix as a KPI, David Carter goes beyond the sales figures to report on cost of sales, margin and margin percentage as well. Analysing sales is a pretty straightforward exercise, but it's when you get into margin analysis that the trouble starts.

KPI #1, Part 2: Product Mix - Margins and Margin percentage

1.

Continued...

Tags

### Formula explained

The formula has three parts:

round(value1,number of decimals)

where value1 is:

if(condition,calulate margin if true, show zero if not)

and condition is:

and(test1, test2)

meaning that both test1 and test 2 must be true for the 'if' condition to be true.

So if there's a non-zero value for both sales and gross profit, the percentage margin is calulated, if one side is zero then the result is 0. This avoids showing the #DIV01 throughout the pivot table.

The round formula was added outside when I noticed how many decimals the calculated result has, so it truncates to 4 decimals, and displays a percentage with 2 decimals.

Regards
Mike

### More on calculated pivot fields

Colin, thanks for the kind words. Much appreciated. I send all this stuff out into the ether and it's really useful to find out whether it actually helped people or not.

Mike, this looks interesting but I don't fully understand the formula. Could you take us through it? Thanks

### .

Thanks Mike. Makes it all worth while.

### Margin Percent - at last!

Just to note that after many years, I have finally added a margin percent (and an average selling price/ton)to a client's monthly sales/margin pivot table, on the strength of the suggestion re calculated fields included herein.

Now for the hard part - the explanations as to variations...

Many thanks
Mike

### hi david

as i perhaps did not make too clear, I do agree with your comments. happy to expand on my views on feed forward controls, although this is probably worth a topic in its own right.

perhaps my somewhat clumsy point relates to an earlier thread on this site about what users of management reports want, and actually read. The problem with this detailed sort of report is that it is often produced but not read (at least in any meaningful way), which is a shame given the effort that a) goes into producing it, and b) goes into ensuring the data to produce it is complete

### all in hand

Alastair, someone earlier (was it you?) did make the good point that the more money managers are paid, the less detail you should give them!

In fact, there'll be a 3rd instalment in this series showing how to summarise the report in a 2-liner.

### wrong approach?

Hi Alastair, all praise is welcome, backhanded or otherwise! Could you expand a bit on what you mean by feedforward controls? Perhaps give us some specific examples?

This particular exercise was simply to help someone find out their actuals - perhaps for the first time. You can't even begin to create budgets until you've established your actuals (I think).

Robin, your product looks interesting, but my article does stress the garbage in = garbage out principle here. Lots of people have bought analysis packages based on how wonderful they look with demo data. They then spend the next 6 months trying to get them to work with real data in their own organisation.

### wrong approach!

don't get me wrong - reporting like this is important, and the approach suggested is more like a proper reporting approach than the typical spreadsheet mash that you see in practice - so more power to this elbow!

but not much use if you don't already know what mix to expect, what margin that implies, and how mix changes affect the margin. feedback controls are important, but feedforward controls are more important.