Formula Help

[exceldummy774]

Hi.

I need to write an excel formula to show the relative performance of five financial advisers.

The spreadsheet needs to have the following inputs for each adviser:

1. Number of clients
2. Total funds under management
3. Level of business brought in

The formula needs to calculate the relative performance of each adviser, based on the inputs.

My understanding is that I need to calculate the % for each individual input based on the total for each column (e.g. number of clients).

However, I'm not sure how to calculate the performance as a total %.... e.g. if there were two advisers:

• Adviser A has 100 clients, £10,000,000 in funds under management, and has brought in £100,000 in business.
• Adviser B has 200 clients, £20,000,000 in funds under management, and has brought in £200,000 in business.

How can I show the performance of adviser A against adviser B?

Do I need to do it on two different basis'? i.e. performance based on level of business against number of clients? and performance based on level of business against funds under management?

Or is there a better way to show this as a whole with just one total performance % for each adviser?

Thanks for your help in advance!

 

[RobP]

I think you need to discuss with your colleagues about what is the KPI that you would like to measure each against, or decide which columns are related ? (I mean for example does the no. of clients actually matter to you versus the amount of new business someone brings in ?, or does the measure of clients versus business managed actually mean anything to you as a business. Only you can decide that.

As for % of each topic, quick maths gives you three columns like such:

book2.xlsm
	A	B	C	D	E	F	G	H
1		Manager	No of Clients	Total Funds	Biz Level	Client %	Funds %	New Biz %
2	25/08/2024	Jim	100	10000000	100000	22.22222	21.73913	16.66667
3	15/07/2023	Bob	200	20000000	200000	44.44444	43.47826	33.33333
4	14/02/2022	Trev	150	16000000	300000	33.33333	34.78261	50
Sheet1

Cell Formulas
Range	Formula
F2:H4	F2	=C2/SUM(C$2:C$4)*100

 

[exceldummy774]

Thanks for your quick reply.

Before your reply, I created a sheet with the correct inputs (copied below).

I'm not sure if it's possible, but what I want to achieve ideally is one figure for each adviser that takes into account client, funds, and level of business written, to show their value to the practice.

 

[awoohaw]

what is your definition of value to the practice. Is it total commisions the practice received?
Does number of clients matter?
If both, how do you allocate the importance?
@RobP asked a very important question. What are the Key Performance Indicators that your firm has decided upon?

 

[RobP]

In the absence of further info, I guess one way could be to add up the % across your cols, (which I think implys an equal weighting on each % therefore), and just score on total as such :

book2.xlsm
	B	C	D	E	F	G	H	I	J	K	L
1	Manager	Clients	Client %	FUM	Funds %	FUM AV / clt	Credits	Credit %		Total :	Rank
2	A	534	38	136,140,000	49	254943.8202	127935	43		131	1
3	B	146	10	18690000	7	128013.6986	41007	14		31	5
4	C	163	12	37800000	14	231901.8405	35358	12		37	4
5	D	292	21	38100000	14	130479.4521	43558	15		49	3
6	E	271	19	46690000	17	172287.8229	46255	16		52	2
Sheet1

Cell Formulas
Range	Formula
F2:F6,I2:I6,D2:D6	F2	=E2/SUM(E$2:E$6)*100
G2:G6	G2	=+E2/C2
K2:K6	K2	=(D2+F2+I2)
L2:L6	L2	=RANK(K2,K$2:K$6)

 

[exceldummy774]

what is your definition of value to the practice. Is it total commisions the practice received?
Does number of clients matter?
If both, how do you allocate the importance?
@RobP asked a very important question. What are the Key Performance Indicators that your firm has decided upon?

Hi.

Thanks for your message.

The firm hasn't decided on KPIs - I just want to be able to demonstrate the value of each adviser based on those parameters, ideally taking all of them into account in one go.

Value to the practice needs to be the amount of business (credits) relative to the number of clients and funds under management (FUM), but I'm not sure how, or if, it's possible to build that into one formula.

I have adapted the sheet a bit more since my last post. It now has two columns showing credits relative to clients and credits relative to FUM, which is helpful, but I ideally I want to somehow combine those to provide an overall value for each adviser.

BTW, I'm adviser B - I have the least number of clients, lowest FUM, and lowest average FUM per client. But it looks like I have been the best performing adviser relatively. Adviser A has written 43% of the overall credits, but with a much bigger client bank and FUM. I'm just not sure how to make it more apparent in terms of overall performance.

 

[exceldummy774]

In the absence of further info, I guess one way could be to add up the % across your cols, (which I think implys an equal weighting on each % therefore), and just score on total as such :

book2.xlsm
	B	C	D	E	F	G	H	I	J	K	L
1	Manager	Clients	Client %	FUM	Funds %	FUM AV / clt	Credits	Credit %		Total :	Rank
2	A	534	38	136,140,000	49	254943.8202	127935	43		131	1
3	B	146	10	18690000	7	128013.6986	41007	14		31	5
4	C	163	12	37800000	14	231901.8405	35358	12		37	4
5	D	292	21	38100000	14	130479.4521	43558	15		49	3
6	E	271	19	46690000	17	172287.8229	46255	16		52	2
Sheet1

Cell Formulas
Range	Formula
F2:F6,I2:I6,D2:D6	F2	=E2/SUM(E$2:E$6)*100
G2:G6	G2	=+E2/C2
K2:K6	K2	=(D2+F2+I2)
L2:L6	L2	=RANK(K2,K$2:K$6)

Thank you.

If you see my previous reply it demonstrates that Adviser B is the best performing, but on yours it shows Adviser B ranked 5th out of 5.

This highlights the problem - although Adviser A's figures look good, once you actually dig a bit deeper and look at performance relatively, Adviser A hasn't actually done as well as it first appears. And this is really what I need to work out how to show more simply.

 

[RobP]

yes, you've hit the nail on the head - and if I was in your position, approaching my boss for a bigger share of profits, I'd be wanting to use your table too.

As we said, really its down to how you/the practice want to present your data. we can't really advise on that as its not really an Excel question.

Being in Finance, one hopes your maths is up to the job

Good luck

Rob

 

[exceldummy774]

yes, you've hit the nail on the head - and if I was in your position, approaching my boss for a bigger share of profits, I'd be wanting to use your table too.

As we said, really its down to how you/the practice want to present your data. we can't really advise on that as its not really an Excel question.

Being in Finance, one hopes your maths is up to the job

Good luck

Rob

me too!