Please help with these questions. It is due today.

Please submit back as an excel document.

NAME: PROBLEM Set 7 Chapter 15 -- Revenue Cycle and Current Accounts Management Assigned Problem 1 James Buchanan Orthotics and Prosthetics is planning to request a line of credit from its bank. The company has produced sales estimates, and these appear in the worksheet below. Collection estimates are as follows: 10 percent within the month of sale, 75 percent in the month following the sale, and 15 percent in the second month following the sale. Labor and supplies estimates also appear in the worksheet below. Payments for labor and supplies are typically made during the month following the one in which these costs have been incurred. General and administrative salaries will amount to approximately $27,000 a month; lease payments under long-term lease contracts will be $9,000 a month; depreciation charges will be $36,000 a month; miscellaneous expenses will be $2,700 a month; income tax payments of $63,000 will be due in both September and December; and a progress payment of $180,000 on a new building must be paid in October. Cash on hand on July 1 will amount to $132,000, and a minimum cash balance of $90,000 will be maintained throughout the cash budget period. What loan will be the company require in October? ANSWER May Collections worksheet: 1. Billed charges 2. Collections Within 30 days 30-60 days 60-90 days 3. Total collections Supplies worksheet: 4. Amount of labor and supplies 5. Payments made for labor and supplies Net cash gain (loss): 6. Total collections (line 3) 7. Total purchases (line 5) 8. General and administrative salaries 9. Lease payments 10. Miscellaneous expenses 11. Taxes 12. Progress payment 13. Total payments 14. Net cash gain(or loss) Borrowing/surplus summary: 15. Cash at beginning with no borrowing 16. Cash at end with no borrowing (line 14 + 15) 17. Target cash balance (given) 18. Cumulative surplus cash(or loan balance) June July $180,000 $180,000 $0 $0 $0 $90,000 $0 $0 -$27,000 -$9,000 -$2,700 August $360,000 $540,000 $90,000 $126,000 $882,000 -$27,000 -$9,000 -$2,700 -$27,000 -$9,000 -$2,700 -$27,000 -$9,000 -$2,700 $132,000 $90,000 $90,000 September October November December January $720,000 $360,000 $360,000 $90,000 $180,000 $306,000 $234,000 $162,000 $90,000 -$27,000 -$9,000 -$2,700 -$63,000 -$27,000 -$9,000 -$2,700 -$27,000 -$9,000 -$2,700 -$27,000 -$9,000 -$2,700 -$63,000 -$27,000 -$9,000 -$2,700 $90,000 $90,000 $90,000 -$180,000 $90,000 $90,000 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 A B NAME: PROB LEM SET 7 C D E F G H Chapter 15 -- Revenue Cycle and Current Accounts Management Assigned Problem 2 Suppose one of the suppliers to Seattle Health System offers terms of 3/20, net 60. a. When does the system have to pay its bills from this supplier? b. What is the approximate percentage cost of the costly trade credit offered by this supplier? (Assume 360 days per year.) ANSWER a. b. Discount percent Days credit received Days of free trade credit Periodic cost of trade credit Number of discount periods per year Approximate % cost I 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 A B NAME: PROBLEM SET 7 C D E F G H Chapter 15 -- Revenue Cycle and Current Accounts Management Assigned Problem 3 Fargo Memorial Hospital has annual net patient service revenues of $14.4 million. The hospital's patient accounts manager estimates that 10 percent of third party payers pay on Day 30, 60 percent pay on Day 60, and 30 percent pay on Day 90. Assume 360 days per year throughout this problem. a. What is Fargo's average collection period? b. What is the firm's current receivables balance? c. What would be the firm's new receivables balance if a newly proposed electronic claims system resulted in collecting from third-party payers in 45 and 75 days, instead of in 60 and 90 days? d. Suppose the firm's annual cost of carrying receivables was 10 percent. If the electronic claims system costs $30,000 a year to lease and operate, should it be adopted? (Assume that the entire receivables balance has to be financed.) ANSWER a. Day Percent who pay Average collection period = 30 60 90 30 45 75 b. Gross sales per year Days per year Gross sales per day Average collection period Receivables balance c. Day Percent who pay Average collection period = Gross sales per year Days per year Gross sales per day Average collection period New receivables balance d. Old receivables balance New receivables balance Difference Carrying cost Carrying cost savings Cost of claims system Net benefit of claims system