XYZ Corporation is a furniture company that just closed its balance sheet in Dec
ID: 2721184 • Letter: X
Question
XYZ Corporation is a furniture company that just closed its balance sheet in December with a cash balance $60 million. Its December 2014 sales were $200 million and material purchases were 120 million. The sales of next twelve months in 2015 (in millions) are forecasted below:
Jan.
Feb.
Mar.
Apr.
May
June
July
Aug.
Sept.
Oct.
Nov.
Dec.
204
210
200
208
216
220
226
230
220
232
240
260
The terms of sales are as follows: 50% cash sales and 50% credit sales with a 30 days collection period.
According to its past experience, material purchases from vendors accounted for 60% of the sales in the corresponding month. XYZ usually pays its vendors cash for 20% of the material purchases and pay the rest one month later.
Other cash incomes include $10 million each month from January to June and $12 million per month from July to December.
Cash disbursements include payroll that is 25% of each month’s sales and interest expenses, which is $5 million per month from January to June and $6 million from July to December. XYZ will spend $90 million per month from February to May on capital expenditures and pay quarterly dividends of $50 million in March, June, September and December, respectively. In addition, XYZ Corporation uses straight line method for depreciation, which is $60 million per month.
To maintain its working capital needs, XYZ normally needs $20 million cash on hand and will invest excess cash (cash holdings above $20 million) in short-term investments. To meet its seasonal needs, XUZ plans to establish a line of credit with a local bank.
You are asked to prepare monthly net cash changes and cash budget for XYZ Corporation.
Question: When setting up a line of credit with a local bank, (1) how much total loan should XYZ Corporation borrow; and (2) what is XYZ Corporation’s debt repayment plan (including how much and in which month)?
Jan.
Feb.
Mar.
Apr.
May
June
July
Aug.
Sept.
Oct.
Nov.
Dec.
204
210
200
208
216
220
226
230
220
232
240
260
Explanation / Answer
DEC JAN FEB MAR APR MAY JUNE JULY AUG SEP OCT NOV DEC TOTAL Basic Data: Sales Forecast 200 204 210 200 208 216 220 226 230 220 232 240 260 Planned Purchases 120 122 126 120 125 130 132 136 138 132 139 144 156 CASH RECEIPTS Cash Sales (50% of sales) 102 105 100 104 108 110 113 115 110 116 120 130 Collection of Accounts Receivable 100 102 105 100 104 108 110 113 115 110 116 120 Miscellaneous Income 10 10 10 10 10 10 12 12 12 12 12 12 Total Receipts 212 217 215 214 222 228 235 240 237 238 248 262 Cash Disbursements: Cash Payment 24 25 24 25 26 26 27 28 26 28 29 31 80% of preceding month's purchases 96 98 101 96 100 104 106 109 110 106 111 115 Depreciation Expense 60 60 60 60 60 60 60 60 60 60 60 60 Payroll (labor) 51 53 50 52 54 55 57 58 55 58 60 65 Interestexpense 5 5 5 5 5 5 6 6 6 6 6 6 Equipment Purchases 90 90 90 90 Quarterly Dividends 50 50 50 50 Total Disbursements 236 331 380 328 335 300 256 261 307 258 266 327 Net Cash Change (24) (114) (165) (114) (113) (72) (21) (21) (70) (20) (18) (65) JAN FEB MAR APR MAY JUNE JULY AUG SEP OCT NOV DEC TOTAL CASH at Begg 60 36 20 20 20 20 20 20 20 20 20 20 Net cash change (24) (114) (165) (114) (113) (72) (21) (21) (70) (20) (18) (65) (3) Ending Cash Balance 36 (78) (145) (94) (93) (52) (1) (1) (50) 0 2 45 (4) Desired Minumum Cash Balance 20 20 20 20 20 20 20 20 20 20 20 20 (5) Cash Surplus Available for Investment or 16 --- --- ---- --- --- --- --- --- -- -- -- Debt Repayment, or (Borrowing Required) ---- (98) (165) (114) (113) (72) (21) (21) (70) (20) (18) (45) (6) Total Invested in Marketable Securities 16 -- --- --- --- -- -- -- --- -- --- -- (7) Ending Cash Balance 36 20 20 20 20 20 20 20 20 20 20 20
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.