Multiply ifs? Working on a commission workbook.

[Pavavion]

Good afternoon all I need some help with reworking a commission workbook. The goals in our office have change to the below structure and I am struggling to work out how best to input the formulas to get me to where i need.

We sell two products Radio & TV airtime.

Commission is based as below.

Write up to 85% of your budget on Radio earn 3.5% of everything you wrote on Radio.
OR
Write up to 102% of your budget on Radio earn 4.5% of everything you wrote on Radio.

PLUS
Write up to 85% of your budget on TV earn 2% of everything you wrote on Radio.
OR
Write up to 102% of your budget on Radio earn 3% of everything you wrote on Radio.

PLUS
Achieve your combined Radio+TV budget over 102% earn 20% on anything written above the combined 102%


I have where I am up to now after deleting many formulas and need some guidance how to get this done please?
Dropbox - Commision Worksheet.xlsx

I would like the sheet to work out the revenue amount applicable in the B columns. Once this is worked out then D columns are a simple multiple.
It is the conditions that i struggle with formulating.

EG: RADIO
If Radio budget achievement is <85% multiply actual by 3.5%
If Radio budget achievement is >85% but less then 102% multiply actual by 4.5%
If COMBINED Radio+TV budget achievement is >102% multiply actual (minus budget multiplied by 1.02%) by 20%

Unsure if this is explained well or not ..... hope the dropbox links helps??

 

[Pavavion]

Re: Multiply ifs? Need help working on a commission workbook.

Hi all still chasing some help if it is still available? Please let me know if I need to supply any further information.

 

[oldbrewer]

Re: Multiply ifs? Need help working on a commission workbook.

I lost interest in this thread because I could not see a firm definition of what you pay as commission, there was early on a mix up in radio and tv. I suggest you give us an unambiguous definition of commission to be paid, in the form of a table with sales and commission that covers every possible circumstance.

eg radio 75%.....tv 75%.......commission = 0%
....radio 95%......tv 95%.................................................etc etc

 

[Pavavion]

Re: Multiply ifs? Need help working on a commission workbook.

Radio	0.00% - 0.85% of Radio budget	3.5%
	85.01% - 1.02% of radio budget	4.5%
	ONE OR THE OTHER IS PAID NOT BOTH OF THE ABOVE
TV	0.00% - 0.85% of TV budget	2%
	85.01% - 1.02% of TV budget	3%
	ONE OR THE OTHER IS PAID NOT BOTH OF THE ABOVE
COMBINED	>1.02% of combined TV+RADIO	20%
	This is paid on the COMBINED Revenue that is above 102%...
	EG: (Radio budget+TV Budget Multiplied by 1.02%) -MINUS (Radio revenue+TV Revenue) x Multiplied by 20%

<tbody>
</tbody>

 

[oldbrewer]

Re: Multiply ifs? Need help working on a commission workbook.

thanks, what if radio budget is 105% - is it just 4.5% of 102%

and note you are using 0 to .85 followed by 85 to 1.02

it should be - I think - 0% to 85% and 85.01% to 102%

can you give us a worked example of combined - to qualify for this I assume you need to earn 102% or more for both radio and tv budgets - still don't understand the MINUS part - especially as you suddenly use the term revenue.... we will get there - the maths will be easy once the question is buttoned down....

 

[Pavavion]

Re: Multiply ifs? Need help working on a commission workbook.

thanks, what if radio budget is 105% - is it just 4.5% of 102%

and note you are using 0 to .85 followed by 85 to 1.02

it should be - I think - 0% to 85% and 85.01% to 102%

can you give us a worked example of combined - to qualify for this I assume you need to earn 102% or more for both radio and tv budgets - still don't understand the MINUS part - especially as you suddenly use the term revenue.... we will get there - the maths will be easy once the question is buttoned down....

Thanks for your help and feedback sorry im not the best explainer.

you are correct it should read: 0-85% and 85% to 102%

EG: just an example with easy figures:

Scenario 1
budget for radio $100 000
Budget for TV $100 000
Combined budget is $200 000

Revenue (this is what has been achieved)
Radio $65 000
TV $90 000

in this scenario:
the radio portion would be paid at 3.5% which equals $2 275 commission because LESS than 85% of $100 000 was achieved.

the TV portion would be paid at 3% which equals $2 700 commission because MORE than 85% of $100 000 was achieved.

In this scenario the combined achievement equals 77.5% which means there is NO combined bonus to be paid due to not achieving MORE then 102% of 200 000
**************************************************************************************************
Scenario 2
budget for radio $100 000
Budget for TV $100 000
Combined budget is $200 000

Revenue (this is what has been achieved)
Radio $110 000
TV $90 000

in this scenario:
the radio portion would be paid at 4.5% which equals $4 950 commission because MORE than 85% of $100 000 was achieved.

the TV portion would be paid at 3% which equals $2 700 commission because MORE than 85% of $100 000 was achieved.

In this scenario the combined achievement equals 100% which means there is NO combined bonus to be paid due to not achieving MORE then 102% of 200 000
**************************************************************************************************
Scenario 3
budget for radio $100 000
Budget for TV $100 000
Combined budget is $200 000

Revenue (this is what has been achieved)
Radio $140 000
TV $80 000

in this scenario:

The combined achievement equals 110% which means there is a combined bonus to be paid due to achieving MORE then 102% of 200 000.. THE combined revenue written over 102% is $16 000 (200 000*1.02)-(140 000+80 000). This amount is paid at 20% meaning the commission paid here on the OVER 102% combined is $3 200.

What now must be worked out is the commissions paid UP to the 102%.
TV is easy as under 85% was achieved...
the TV portion would be paid at 2% which equals $1 600 commission because LESS than 85% of $100 000 was achieved.

the radio portion would be paid at 4.5% UP TO the radio budget of 102% (100 000 x 1.02) which equals $4 590 commission because MORE than 85% of $100 000 was achieved.

Hope this makes sense?

 

[oldbrewer]

Re: Multiply ifs? Need help working on a commission workbook.

										checking arithmetic
			budget	revenue	%	threshold %				radio	tv	total
		radio	100000	65000	65	85				2275	1600	3875
		tv	100000	80000	80	85
		combined	200000
		commission
		3875
										checking arithmetic
			budget	revenue	%	threshold %				radio	tv	total
		radio	100000	65000	65	85				2275	2700	4975
		tv	100000	90000	90	85
		combined	200000
		commission
		4975
										checking arithmetic
			budget	revenue	%	threshold %				radio	tv	total
		radio	100000	86000	86	85				3870	2670	6540
		tv	100000	89000	89	85
		combined	200000
		commission
		6540
										checking arithmetic
			budget	revenue	%	threshold %				radio	tv	total	comb bonus	grand total
		radio	100000	125000	125	85				5625	4050	9675	1120	10795
		tv	100000	135000	135	85
		combined	200000
		commission
		10795
		now I have had a stab at this - you check the sums - particularly you said combined bonus was 20% - did you mean 2%
		once you agree figures I will tidy up and post the formulas

<colgroup><col span="2"><col><col span="3"><col><col span="6"><col><col span="3"></colgroup><tbody>
</tbody>

 

[Pavavion]

Re: Multiply ifs? Need help working on a commission workbook.

The first 3 scenarios are all correct except the very last one, it should be like the below:

	Radio	Television	Combined
102% Budget	102 000	102 000	204 000
$ Revenue>102% Budget	23 000	33 000	56 000
Comission Paid on 0-102%	102 000	102 000	0
$ paid	4590 (102 000* 4.5%)	3060(102 000 *3%)	11 200 (56 000 * 20%)
Total Commission			$18 850

<tbody>
</tbody>