Simple Interest Worksheets - Excel

Document Sample

```					15 Chapter model                                             11/14/2010 12:39                                 2/16/2006

Chapter 15. Working Capital Management

This chapter deals with working capital management. Two useful tools for working capital management are (1)
the cash conversion cycle and (2) the cash budget. This spreadsheet model shows how these tools are used
to help manage current assets and presents them on separate worksheets.

THE CASH CONVERSION CYCLE (Section 15.2)
The cash conversion cycle focuses on the length of time between when the company must make payments
and when it receives cash inflows. The cash conversion cycle is determined by three factors: (1) The inventory
conversion period, which is the average time required to convert materials into finished goods and then to sell
those goods. (2) The receivables collection period, which is the length of time required to convert the firm's
receivables into cash, or how long it takes to collect cash from a sale. (3) The payables deferral period, which
is the average length of time between the purchase of materials and labor and payment for them. The cash
conversion cycle is determined by the following formula:

Cash                            Inventory                      Receivables                   Payables
conversion           =           conversion          +            collection        −          deferral
cycle                            period                          period                      period

EXAMPLE
Calculate the cash conversion cycle for Great Fashions Inc. Sales are \$1,216,666 and costs of goods sold are
\$1,013,889, while inventories are \$250,000, accounts receivable are \$300,000, and payables are \$150,000.
Based on a 365-day year, calculate the CCC.

Sales            \$1,216,666
COGS             \$1,013,889
Inventories        \$250,000
AR                 \$300,000
AP                 \$150,000
Days/year               365

CCC              =              ICP                  +         RCP                −           PDP
=         Inv /(COGS/365)           +    AR/(Sales/365)          −        AP/(COGS/365)
=              90                   +          90                −            54
=            126.00

It takes 90 days to make and sell dresses and another 90 days to collect cash after the sale, or a total of 180
days between spending money and collecting cash. However, the company delays its own payments for 54
days. Therefore, the net days the firm must finance its labor and purchases is 90 + 90 − 54 = 126 days, which
is the cash conversion cycle.

Disregarding profits, how much capital does GFI have tied up in working capital?

Answer:       (C of GS / day) * (CCC)           =   =         \$   2,778              ×                 126
=         \$ 350,000

If the cost of capital is 10%, then it costs GFI \$35,000 per year to carry working capital.
Question: If GFI began selling on a credit card only basis, how would this affect its CCC, and what effect
would it have on the cost of carrying working capital?

Answer: Receivables would go to zero, the CCC would fall to zero days, and carrying costs would decline by:

30        ×       \$      2,778       ×          10%         =            \$8,333

We could do sensitivity analysis to see how other changes would affect profitability.
al management are (1)
these tools are used

st make payments
tors: (1) The inventory
goods and then to sell
o convert the firm's
deferral period, which
or them. The cash

ts of goods sold are
es are \$150,000.

le, or a total of 180
wn payments for 54
54 = 126 days, which
and what effect

sts would decline by:
A          B        C        D         E        F     G        H        I        J       K      L      M      N
5 THE CASH BUDGET
6 Selected Input Data
7 Collections during month of sale                          20%     Assumed constant. Don't change.
8 Collections during 1st month after sale                   70%     Assumed constant. Don't change.
9 Collections during 2nd month after sale                   10%     Fixed at 10% less bad debt %
10 Percent bad debts                                          0%     Can change to see effects
11 Discount on first month collections                        2%     Can change to see effects
12 Purchases as a % of next month's sales                    70%     Can change to see effects
13 Lease payments per month                                   \$15    Can change to see effects
14 Construction cost for new plant (Oct)                     \$100    Can change to see effects
15 Target cash balance                                        \$10   Will borrow this amount at start
16 Sales adjustment factor                                    0%    (% increase or decrease from base)
17
18                                                           May     June    July     August   Sept   Oct     Nov    Dec
19 Sales (gross)                                             \$200     \$250    \$300      \$400   \$500   \$350    \$250       \$200
20   Collections
21   During month of sale: 0.2(Sales)(0.98)                                    \$59       \$78    \$98    \$69     \$49        \$39
22   During 1st month after sale: 0.7(prior month's sales)                     175       210    280    350     245        175
23   During 2nd month after sale: 0.1(sales 2 months ago)                       20        25     30     40      50         35
24   Total collections                                                        \$254      \$313   \$408   \$459    \$344       \$249

25 Payments
26 Purchases: 70% of next month's sales                               \$210    \$280      \$350   \$245   \$175    \$140

27   Payment for materials: Last month's purchases                            \$210      \$280   \$350   \$245    \$175       \$140
28   Wages and salaries                                                         30        40     50     40      30         30
29   Lease payments                                                             15        15     15     15      15         15
30   Other expenses                                                             10        15     20     15      10         10
31   Taxes                                                                                       30                        20
32   Payment for plant construction                                                                   \$100
33   Total payments                                                           \$265      \$350   \$465   \$415    \$230       \$215
34 Net cash flows
35 Net cash inflow (NCF) for month: Row 24 minus Row 33                       (\$11)    (\$37)  (\$57)    \$44    \$114       \$34
36 Cumulative NCF: Month's NCF plus prior month's cumulative NCF              (\$11)    (\$48) (\$105)   (\$61)    \$53       \$87
37 Cash surplus (or loan requirement)
38 Target cash balance                                                         \$10      \$10    \$10     \$10     \$10       \$10
39 Surplus cash (or loan needed): Row 36 - Row 38                             (\$21)    (\$58) (\$115)   (\$71)    \$43       \$77
40    Maximum required loan (shown as a negative)                            (\$115)
41   Maximum available for investment                                          \$77
Question: If the percent of customers who end up as bad debts increases, how would this
42 affect the maximum required loan?
43
44 Answer: Do a sensitivity
Effect of Bad Debts on Loan Requirements
45 analysis.                           Loan
Requirement
46   % bad Max Req'd Loan
47   debts      \$ 115
\$ 200
48   0.00%      \$ 115
49   1.00%      \$ 122                  \$ 175
50   2.00%      \$ 130
51   3.00%      \$ 137                  \$ 150
52   4.00%      \$ 145
\$ 125
53   5.00%      \$ 152
54   6.00%      \$ 160                  \$ 100
55   7.00%      \$ 167                       0.00%               5.00%           10.00%
56   8.00%      \$ 175                                        % Bad Debts
57   9.00%      \$ 182
58 10.00%       \$ 190
59
60
You could do all sorts of "What if" analyses. For example, what if sales declined by 10%. How
61 would that affect the max loan requirement?
SECTION 15.10
SOLUTIONS TO SELF-TEST QUESTIONS

4a If a firm borrowed \$500,000 at a rate of 10%, simple interest, with monthly interest
payments and a 365-day year, what would the required interest payment be for a 30-
day month?

Loan amount                          \$500,000
Nominal rate                           10.00%
Days in year                               365
Days in month                               30
Simple interest rate per day         0.000274
Interest charge for month           \$4,109.59

4b If a firm borrowed \$500,000 at a rate of 10%, simple interest, with monthly interest
payments and a 365-day year, what would the effective annual rate be?

Nominal rate                          10.00%
Number of months/year                      12
Effective annual rate (EAR)           10.47%

5a If this \$500,000 loan had been on a 10% add-on basis, payable in 12 end-of-month
installments, what would the monthly payments be?

Loan amount                          \$500,000
Nominal add-on rate                   10.00%
Number of months/year                      12
Monthly payment                    \$45,833.33

5b If this \$500,000 loan had been on a 10% add-on basis, payable in 12 end-of-month
installments, what would the APR be?

N                                          12
PV                                   \$500,000
PMT                                \$45,833.33
FV                                          0
I/YR                                 1.4977%
APR                                   17.97%

5c If this \$500,000 loan had been on a 10% add-on basis, payable in 12 end-of-month
installments, what would the effective rate be?