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??
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??
