Microsoft Office Tutorials and References
In Depth Information
REVIEW OF EXCEL BASICS
Cash Flow Calculations: Borrowing and Repayments
The Scenario Manager cases that follow in this topic require accounting for money that the fictional company
will have to borrow or repay. This money is not like the long-term loan that the Collegetown Thrift Shop is
considering for its expansion. Instead, this money is short-term borrowing that companies use to pay current
obligations, such as purchasing inventory or raw materials. Such short-term borrowing is called a line of
credit, and is extended to businesses by banks, much like consumers have credit cards. Lines of credit usually
involve interest payments, but for simplicity
s sake, focus instead on how to do short-term borrowing and
repayment calculations.
To work through cash flow calculations, you must make two assumptions about a company
s borrowing
and repayment of short-term debt. First, you assume that the company has a desired minimum cash level at
the end of a fiscal year (which is also its cash level at the start of the next fiscal year), to ensure that the
company can cover short-term expenses and purchases. Second, assume the bank that serves the company
will provide short-term loans (a line of credit) to make up the shortfall if the end-of-year cash falls below the
desired minimum level.
NCP stands for Net Cash Position, which equals beginning-of-year cash plus net income after taxes for the
year. NCP represents the available cash at the end of the year, before any borrowing or repayment. For the
three examples shown in Figure C-45, set up a simple spreadsheet in Excel and determine how much the
company needs to borrow to reach its minimum year-end cash level. Use the IF function to enter 0 under
Amount to Borrow if the company does not need to borrow any money.
FIGURE C-45
Examples of borrowing
You can also assume that the company will use some of its cash on hand at the end of the year to pay off
as much of its outstanding debt as possible without going below its minimum cash on hand required. The
any cash above the minimum
is available to repay any debt. In the examples shown in Figure C-46, compute the excess cash and then
compute the amount to repay. In addition, compute the ending cash on hand after the debt repayment.
excess
cash is the company
s NCP less the minimum cash on hand required
FIGURE C-46
Examples of debt repayment
In the Scenario Manager cases of the following chapters, your spreadsheet will need two bank financing
sections beneath the Income and Cash Flow Statements sections. You will build the first section to calculate
any needed borrowing or repayment at year
s end to compute year-end cash on hand. The second section will
calculate the amount of debt owed at the end of the year after any borrowing or debt repayment.
Return to the Collegetown Thrift Shop tutorial and assume that it includes a line of credit at a local bank
for short-term cash management. The first new section extends the end-of-year cash calculation, which was
shown for the thrift shop in Figure C-19. Figure C-47 shows the structure of the new section highlighted in
boldface.

Search JabSto ::

Custom Search