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