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:
(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.
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
value with an R
inserted in cell C8. Because you
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.