Microsoft Office Tutorials and References

In Depth Information

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

Type the following in the Function Arguments dialog box:

Next to Logical_test, type C33<=0.

Next to Value_if_true, type 0.

Next to Value_if_false, type C33*C4 (the Income before Taxes multiplied by the Tax Rate for

2012).

As you fill in the arguments, Excel writes the formula for you in the formula editing window (circled in

Figure C-13).

FIGURE C-13

The Function Arguments dialog box with the arguments filled in

Once you have typed in the arguments, click OK; Excel enters the formula into the cell. Because you had

negative income, the cell should display a zero for now. Because the same formula will be used in 2013 (but

with the 2013 tax rate), you can simply copy and paste the formula from cell C34 to cell D34. You also have

to calculate the income tax for the Income and Cash Flow Statements (No Expansion). In cell C43, use the

same IF function, but in the Logical_test, Value_if_true, and Value_if_false arguments, you must type

C42

=0, 0, and C42*C4, respectively. Again, the cell will display $0 for an answer. Copy cell C43 to cell D43

to complete the Income Tax Expense line.

Net Income after Taxes is simply the Income before Taxes minus the Income Tax Expense. Enter the

formula into cell C35, then copy cell C35 over to cells D35, C44, and D44. If you did this correctly, cells C35

and D35 will display a negative $10,000, and cells C44 and D44 will display $0.

End-of-year Cash on Hand, the last line in both Income and Cash Flow Statements sections, is not

difficult either. Conceptually, the cash you have at the end of the year is equal to your Beginning-of-year Cash on

Hand plus your Net Income after Taxes. Enter the formula into cell C36, then copy cell C36 over to cell D36.

Note that because the Income and Cash Flow Statements (No Expansion) do not have a line item for Business

Loan Payment, you cannot copy the same command down to it. You have to enter the formula manually for

<