- Creating "Natural-Language" Formulas
- Using the "Intelligent Sum" Button
- Working with Stylist and Autoformat in Calc
- Letting Scenario Manager Help with What-If Projections
- Using and Creating Spreadsheet Themes
- Using DataPilot to Import and Manipulate Information from Databases Created with Other Software
- Mail Merging with OOo Writer Documents and Calc Data
- Summing Up
This chapter is from the book
This chapter is from the book
This chapter is from the book
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 multiplier—0.3 percent, or 30% of a salesperson’s production—under the Total Sales figure.
)
Figure 9.13 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.
)
Figure 9.14 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.
)
Figure 9.15 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").