Bonuses Based On Forecasting Accuracy

[nyycavo24]

I'm not going to bore you with the details, but in short, I'm modeling a comp plan for account managers. Part of that comp plan is accurately forecasting this year's revenue. As the account manager gets further away from the forecast (100%) the bonus diminishes. The greater the deviation from the middle (plus or minus), the less the bonus is. Complicating matters is that my intent is to penalize incorrect forecasts more if they come in at less than forecast, versus more. See the below:

Copy of AM Plan 2014 Addendum v2.jpg

Disregard the percentages, as those don't factor into the payout listed in the second set of numbers. The amounts in the Payout $'s section need to match the amount in the Payout Amount Box once the criteria are met. I've tried nested IF/AND/OR statements (because I believe I'll need all three), but can't seem to get it right. I have a feeling that if I would just settle on an equal deviation, that this might be much easier (although I'll have to figure that out too).

Any thoughts on the best way forward here?

Mike

[Olly]

Please post as an attachment, rather than a JPG.

Your answer is a lookup, but I don't have time to spend retyping all your data values into my workbook.