Microsoft Office Tutorials and References
In Depth Information
REVIEW OF EXCEL BASICS
Calculation section for End-of-Year Cash on Hand with borrowing and repayments added
The heading in cell A36 was originally End-of-year Cash on Hand in Figure C-19, but you will add
lineof-credit borrowing and repayment to the end-of-year totals. You must add the line-of-credit borrowing from
the bank to the NCP and subtract the line-of-credit repayments to the bank from the NCP to obtain the
Endof-Year Cash on Hand.
The second new section you add will compute the End-of year debt owed. This section is called Debt
Owed, as shown in Figure C-48.
Debt Owed section
As you can see, the thrift shop currently owes $47,000 on its line of credit at the end of 2011. The
Endof-year debt owed equals the Beginning-of-year debt owed plus any new borrowing from the bank
s line of
credit, minus any repayment to the bank
s line of credit. Therefore, the formula in cell C46 would be:
Assume that the amounts for borrowing and repayment (cells C44 and C45) were calculated in the first
new section (for the year 2012, the amounts would be in cells C38 and C39), and then copied into the second
section. The formula for cell C44 would be =C38, and for cell C45 would be =C39. The formula for cell C43,
Beginning-of-year debt owed in 2012, would simply be the End-of-year debt owed in 2011, or =B46.
Now that you have added the spreadsheet entries for borrowing and repayment, consider the logic for the
borrowing and repayment formulas.
Calculation of Borrowing from the Bank Line of Credit
When using logical statements, it is sometimes easier to state the logic in plain language and then turn it into
an Excel formula. For borrowing, the logic in plain language is:
If (cash on hand before financing transactions is greater than the minimum cash required,
then borrowing is not needed; else,
borrow enough to get to the minimum)
You can restate this logic as the following:
If (NCP is greater than minimum cash required,
then borrowing from bank=0;
else, borrow enough to get to the minimum)