Microsoft Office Tutorials and References

In Depth Information

**REVIEW OF EXCEL BASICS**

FIGURE C-47

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.

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:

=C43+C44-C45

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)