Filetype Excel XLS | Posted on 09 Aug 2022 | 3 years ago
Authentication
digital resources
194x Filetype XLS File size 0.06 MB Source: www.swlearning.com
A B C D E F G H I J K
1 7/5/2003
2
3 Chapter 16. Model for managing and financing current assets
4
5 Chapter 16 deals with working capital management. Two useful tools for working capital management are
6 (1) the cash conversion cycle and (2) the cash budget. This spreadsheet model shows how these tools are
7 used to help manage current assets.
8
9 THE CASH CONVERSION CYCLE
10
11 The cash conversion cycle model focuses on the length of time between when the company must make
12 payments and when it receives cash inflows. The cash conversion cycle is determined by three factors: (1)
13 The inventory conversion period, which is the average time required to convert materials into finished goods
14 and then to sell those goods. The inventory conversion period is measured by dividing inventory by the
15 average daily sales. (2) The receivables collection period, which is the length of time required to convert the
16 firm's receivables into cash, or how long it takes to collect cash from a sale. The receivables collection
17 period is measured by the days sales outstanding ratio (DSO), which is accounts receivable divided by
18 average daily sales. (3) The payables deferral period, which is the average length of time between the
19 purchase of materials and labor and payment for them. The payable deferral period is calculated by dividing
20 average accounts payable by purchases per day (cost of goods sold divided by 360 or 365 days). The cash
21 conversion cycle is determined by the following formula:
22
23 Inventory conversion Receivables collection Payables deferral
24 Cash conversion cycle = period + period - period
25
26 Problem
27 Calculate the cash conversion cycle for the Real Time Computer Company. Annual sales are $10 million,
28 and the annual cost of goods sold is $8 million. The average levels of inventory, receivables, and accounts
29 payable are $2,000,000, $666,667, and $666,667, respectively. RTCC uses a 365-day accounting year.
30
31 Sales $10,000,000
32 COGS $8,000,000
33 Inventories $2,000,000
34 AR $657,534
35 AP $657,534
36 Days/year 365
37
38 Cash conversion cycle (CCC) Inventory conversion Receivables collection Payables deferral
39 = period + period - period
40
41 = Inventory/Sales per day + AR/Sales per day - AP/COGS per day
42 = 73.00 + 24.00 - 30.00
43 = 67.00
1 of 7
A B C D E F G H I J K
44
45 It takes 73 days to make and then sell a computer, and another 24 days to collect cash after the sale, or a
46 total of 97 days between spending money and collecting cash. However, the company can delay payment for
47 parts and labor for 30 days. Therefore, the net days the firm must finance its labor and purchases is 97 - 30
48 = 67 days, which is the cash conversion cycle. Companies like to shorten their cash conversion cycles as
49 much as possible without adversely impacting operations. As noted in the chapter, Amazon.com and Dell
50 have been able to produce goods on demand, hence to reduce the inventory conversion period to close to zero.
51 In addition, since payments are made by credit card, the receivables collection period is also close to zero.
52 Then, if they pay suppliers after a 20 payables deferral period, they can end up with a NEGATIVE cash
53 conversion cycle. In that case, the faster the firms grow, the more cash they generate.
54
55 Disregarding profits, how much capital does RTCC have tied up in working capital?
56
57 Answer: (C of GS / day) * (CCC) = $ 21,918 * 67.00
58 = $ 1,468,493
59
60 If the cost of capital is 10%, then it costs RTCC $146,667 per year to carry working capital.
61
62 Question: If RTCC began selling on a credit card only basis, how would this affect its CCC, and what effect
63 would it have on the cost of carrying working capital?
64
65 Answer: Receivables would go to zero, the CCC would fall to 43 days, and carrying costs would decline by
66
67 24.00 * $ 21,918 * 10% = 52,603
68
69 We could do sensitivity analysis to see how other changes would affect profitability.
70
71 THE CASH BUDGET
72
73 The cash budget is a statement that shows cash flows over a specified period of time. Generally, firms use a
74 monthly cash budget for the coming year, plus a more detailed daily or weekly cash budget for the coming
75 month. Monthly cash budgets are used for long-range planning, and daily or weekly budgets for actual cash
76 control. The following monthly cash budget examines MicroDrive Inc. for the last 6 months of 2003.
77
78 Input Data
79 Collections during month of sale 20% Assumed constant. Don't change.
80 Collections during 1st month after sale 70% Formula. Don't change.
81 Collections during 2nd month after sale 10% Allow this value to change to reflect slower collections.
82 Discount on first month collections 2%
83 Purchases as a % of next month's sales 70%
84 Lease payments $15
85 Construction cost for new plant (Oct) 100
86 Target cash balance $10
87 Sales adjustment factor 0.00
88
2 of 7
A B C D E F G H I J K
89 THE CASH BUDGET
90 May June July August September October November December
91 Collections and purchases worksheet
92 Sales (gross) $200 $250 $300 $400 $500 $350 $250 $200
93 Collections
94 During month of sale 59 78 98 69 49 39
95 During first month after sale 175 210 280 350 245 175
96 During second month after sale 20 25 30 40 50 35
97 Total collections $254 $313 $408 $459 $344 $249
98
99 Purchases
100 70% of next months sales $210 $280 $350 $245 $175 $140
101 Payments on last month's purchases $210 $280 $350 $245 $175 $140
102
103 Cash gain or loss for month
104 Collections $254 $313 $408 $459 $344 $249
105 Payments for purchases 210 280 350 245 175 140
106 Wages and salaries 30 40 50 40 30 30
107 Lease payments 15 15 15 15 15 15
108 Other expenses 10 15 20 15 10 10
109 Taxes 30 20
110 Payment for plant construction 100
111 Total payments $265 $350 $465 $415 $230 $215
112 Net cash gain (loss) during month ($11) ($37) ($57) $44 $114 $34
113
114 Loan requirement or cash surplus
115 Cash at start of month if no borrowing $ 15 $4 ($33) ($90) ($46) $68
116 Cumulative cash $4 ($33) ($90) ($46) $68 $102
117 Target cash balance $10 $10 $10 $10 $10 $10
118 Cumulative surplus cash or loans
119 outstanding to maintain $10 target cash balance ($6) ($43) ($100) ($56) $58 $92
120
121 Max loan: $100
122
123 Question: If the percent of customers who pay in the 2nd month after the sale increased due to poor credit
124 management, how would this affect the maximum required loan?
125
126 Answer: Do a sensitivity analysis.
127 Effect of Late Payment % on Loan Requirements
128 % paying Max Req'd Loan
129 late $ 100 $ 300
130 0% $ 80 t $ 250
n
131 10% $ 100 e
m $ 200
132 20% $ 120 e
r
i $ 150
30% $ 140 u
133 q
e $ 100
40% $ 160 R
134
n $ 50
135 50% $ 180 a
o
L $ 0
136 60% $ 206
137 70% $ 236 0% 10% 20% 30% 40% 50% 60% 70% 80%
% Paying Late
3 of 7
Effect of Late Payment % on Loan Requirements
$ 300
t$ 250
n
e
m$ 200
e
r
i$ 150
u
q
e$ 100
R
n $ 50
a
o
L $ 0
0% 10% 20% 30% 40% 50% 60% 70% 80%
A B C D E F G H I J K
138 80% $ 266 % Paying Late
139
4 of 7
no reviews yet
Please Login to review.
