Letting Scenario Manager Help with What-If Projections

The Scenario Manager is another useful OOo Calc tool. It is used to make in-depth calculations or to create formulas that include a what-if situation.

Let's look at our table. Let's say the Incentive formula needs to be changed because you want to give a 30% incentive for every salesperson who makes sales of $4,000 and more. You also want to note that those with sales of $3,999 or less are not eligible for the incentive.

First, I put the incentive multiplier0.3 percent, or 30% of a salesperson's productionunder the Total Sales figure.

The incentive multiplier.

Next, you change the formula in cell E4. Type =IF(D4>4000;D4*$D$17;0). The incentive is now automatically computed if Sales is greater than $4,000, and 0 if it's less than. Remember to use a ; (semicolon) as the formula separator. You should also put a $ (dollar sign) before and after the D in D17 in the formula to make it an absolute value. You can now copy the formula from cells E5 to E13 to automatically get the incentives for the other salespeople.

The what-if projection.

In other words, the formula says the following: If a certain salesperson achieves sales of $4,000 or greater, his or her incentive is the result of Sales multiplied by 0.3. If his or her sales are less than $4,000, no incentive is given.

Final output.

You also want to know who your outstanding salespeople are, so you will make another formula using the what-if projection. If E4 equals 0, it generates an "Amateur" remark in column F4, and "Outstanding" otherwise. So type =IF(E4=0;"Amateur";"Outstanding").