Microsoft Office Tutorials and References

In Depth Information

**REVIEW OF EXCEL BASICS**

You have not added minimum cash at the end of the year as a requirement, but you could add it to the

Constants section at the top of the spreadsheet (in this case the new entry would be cell C6). Assume that

you want $50,000 as the minimum cash on hand at the end of both 2012 and 2013. Assuming that the NCP is

shown in cell C37, you could restate the formula for borrowing (cell C38) as the following:

IF(NCP>Minimum Cash, 0; otherwise, borrow enough to get to the minimum cash)

You have cell addresses for NCP (cell C37) and for Minimum Cash (cell $C$6). To develop the formula for

cell C38, substitute the cell address for the test argument; the true argument is simply zero (0), and the false

argument is the minimum cash minus the current NCP. The formula stated in Excel for cell C38 would be:

=IF(C37>=$C$6, 0, $C$6-C37)

Calculation of Repayment to the Bank Line of Credit

Simplify the statements first in plain language:

IF(beginning of year debt=0, repay 0 because nothing is owed), but

IF(NCP is less than the minimum, repay 0, because you must borrow), but

IF(extra cash equals or exceeds the debt, repay the whole debt),

ELSE (to stay above the minimum cash, repay the extra cash above the minimum)

Look at the following formula. If you assume that the repayment amount will be in cell C39, the

beginning-of-year debt is in cell C43, and the minimum cash target is still in cell $C$6, the repayment

formula for cell C39 with the nested IFs should look like the following:

=IF(C43=0,0,IF(C37<=$C$6,0,IF(C37-$C$6>=C43,C43,C37-$C$6)))

The new sections of the thrift shop spreadsheet would look like those in Figure C-49.

FIGURE C-49

Thrift shop spreadsheet with line-of-credit borrowing, repayments, and Debt Owed added

Notice that all the owed line-of-credit debt will be paid off by the end of 2013.