Microsoft Office Tutorials and References

In Depth Information

**Tutorial C: Building a Decision Support System in Excel**

You will use IF formulas to forecast Total Sales Dollars. Click cell C18, then bring up the IF function and

type the following in the text boxes:

Logical_test: $C$8=

(Note that you must use absolute cell referencing for B8 plus quotation marks for

Excel to recognize a text string.)

Value_if_true: B18*1.3 (the 2011 sales multiplied by 1.3 for 30% sales growth)

Value_if_false: B18*1.15 (the 2011 sales multiplied by 1.15 for 15% sales growth)

Compare your entries to Figure C-16.

“

R

”

FIGURE C-16

Using the IF function to enter the Total Sales Dollars forecast for 2012

When you click OK, cell C18 should display $455,000, because 30% of $350,000 is $105,000, and

$350,000 plus $105,000 equals $455,000. So, it appears that the formula returned a

“

true

”

value with an R

inserted in cell C8. Because you

“

anchored

”

cell C8 by entering $C$8, copy this formula over to cell D18 for

the year 2013.

Once you complete the Total Sales Dollars cells for the Expansion scenario, go down to the Calculations

(No Expansion) section and use IF statements to enter formulas for the Total Sales Dollars. Use 20% for

Recession and 5% for Boom. You can copy the formula from cell C18 into cell C24, but you then will have to

use the editing window to change the values in the true and false arguments from 1.3 and 1.15 to 1.2 and

1.05, respectively, to reflect the fact that you did not expand the business. See Figure C-17.