FORECAST and Interpolate

[exceldotcom]

Hi everyone!

Have a question which I've been struggling with for a while today. I'll try and keep this short. Not asking anyone to write the formula for me, just point me in the right direction if possible.

(A) I have a target bonus payment totaling $50,000. (B) I have a sales target of $1,000,000. (C) I have a grid which shows me payout based on performance.

0% of sales target (ST) = 0% bonus payout (P/O).
20% of ST = 20% P/O ($10,000)
100% of ST = 100% P/O ($50,000...)
150% of ST = 175% P/O
200%+ = 1.5% of ST (i.e. $750 per 1.5% over 200%)

I'm basically trying to create a dynamic calculator (once I get over 150% the leverage kicks in and becomes tricky) for myself that interpolates between these breakpoints. I.e. if I hit 171% of ST I get X P/O or 205% of ST I get X% P/O.

My research is leading me to believe some kind of FORECAST formula would do the trick but unfortunately my Excel skills do not stem there... if anyone has any suggestions I will appreciate it!

Thanks!

 

[Misca]

I'd try to solve this using a lookup table and a couple of VLOOKUPs (or if you used INDEX / MATCH you'd get away without extra columns in your lookup table):

First you need to find out not only the actual threshold but the next threshold as well so you can calculate, what percentage of the actual threshold the sales have surpassed. The easiest way to calculate this would be to have in your lookup table not only the threshold level but the next threshold as well on the same row (=VLOOKUP solution only: The MATCH in INDEX / MATCH can be easily used to get values from the next row as well): If the sales are 125 % of the sales target this part should return 50 % ( = ( 125 % - 100 % ) / ( 150 % - 100 % ) )

Also, I'd put the % of the ST column in the same lookup table for every level as well (just put 0 % where the ST doesn't apply). This way I could use the same formula without additional IFs.