The bonus, B, paid to a company’s franchise managers is based on the following formulae:
B = b1Y + b2(Y – Y), for Y ≥ Y
B = b1Y – b3 (Y - Y), for Y > Y
Where:
b1 = 5%...or a percentage of your choosing
b2 = 2%...or a percentage of your choosing
b3 = 6%...or a percentage of your choosing
Y = forecasted sales revenue
Y = actual sales revenue
Can you model the formulae above in Excel…so that your “employees” can enter their Forecasted Sales and their Actual Sales and have their bonuses calculated (in one cell!)?
Let Y and Y each have different values such that you have at least 5 iterations… and include results in another table within the Excel spreadsheet.
Someone please show me how to do this in excel. Please show all work.