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
As you fill in the arguments, Excel writes the formula for you in the formula editing window (circled in
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
=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