Spreadsheeting Home Page

Lorenz Curves: what they are

Lorenz Curves: using Excel to draw them

Felix wrote

Hi Duncan:

I found the concept of the LC curve quite interesting, but I am curious as to how to design this other chart that I have in mind. Specifically, this chart will compare the percentage of bonus pay out, based on the percentage of income, and the percentage of ROI attained. For example, your bonus is 20% if you attain 75% of your income budget and 80% of your budgeted ROI; alternatively, it would be say 30% if you attain 80% income target and 90% of ROI target. In other words, your bonus depends on what percentage of your target for income and ROI you attain simultaneously. I am thinking that you could plot the income and ROI targets on the X,Y axes separately, and the bonus on the equality line; is that correct? I will appreciate your response.

Thanks
Felix

I set up spreadsheet file for Felix in which I demonstrated both a Lorenz Curve type approach and an approach using regression analysis.

Hi Felix,

I have attached a small Excel file (email me for your very own copy!) that helps to answer your interesting question. Please note that you need to be careful with your model because of the way you might want Income Budget and Budget ROI to relate to your bonus.

My first graph has little to do with the Lorenz curve since it gives you a different approach. For my example I have assumed the figures that you see in the spreadsheet but be aware that this does not cover every single possible combination of the two.

So, you might need to revise the model with other values for your two X variables.

The table you see when you first open this file is the table I used to derive the multiple regression formula that I then use in column D (D6:D15) to evaluate the bonus level. If change any Income budget or ROI budget value from now on you will get a revised Bonus figure.

I then drew the graph very easily as you can see with the bonus levels as the X values and the bonus itself as the Y values.

The second graph is only partly finished but it does follow the Lorenz Curve format. What you see is an equality line based on the Budget Income and ROI you see in the table and then I have taken the Bonus values and placed them on a secondary Y axis. The Bonus line is hidden since we don't need it directly but all you need to do is read across from anywhere on the equality line to the right hand Y axis and you have your Bonus value.

The basic table of data and two graphs from the file follow.

Plotting Bonus Values Using a Multi Variate Scheme

	>=	<
Level	Income Budget	ROI Budget	Bonus Calculation
1	0.7	0.7	0.10
2	0.75	0.8	0.20
3	0.8	0.9	0.30
4	0.85	1	0.4
5	0.9	1.1	0.5
6	0.95	1.2	0.6
7	1	1.3	0.7
8	1.05	1.4	0.80
9	1.1	1.5	0.90
10	1.15	1.6	1.00

I hope this all makes sense!

Best wishes

© Duncan Williamson
5 December 2002