Crane Products, a rapidly growing distributor of home gardening equipment, is fo
ID: 2561224 • Letter: C
Question
Crane Products, a rapidly growing distributor of home gardening equipment, is formulating its plans for the coming year. Carol Jones, the firm’s marketing director, has completed the following sales forecast.
Phillip Smith, an accountant in the Planning and Budgeting Department, is responsible for preparing the cash flow projection. He has gathered the following information.
Crane maintains a minimum cash balance of $50,000. If the cash balance is less than $50,000 at the end of the month, the company borrows against its 12% line of credit in order to maintain the balance. All borrowings are made at the beginning of the month, and all repayments are made at the end of the month (in increments of $1,000). Accrued interest is paid in full with each principal repayment. The projected cash balance on April 1 is $50,300.
1: Prepare the cash receipts budget for the second quarter.
2: Prepare the purchases budget for the second quarter.
3. Prepare the cash payments budget for the second quarter.
4: Prepare the cash budget for the second quarter.
Month Sales Month Sales January $903,800 July $1,501,000 February $1,001,200 August $1,501,000 March $903,800 September $1,604,500 April $1,150,300 October $1,604,500 May $1,252,100 November $1,501,000 June $1,407,500 December $1,700,900Explanation / Answer
1 a expected collections Mar Apr May June Total Month after sale 542280 =903800*60% 690180 =1150300*60% 751260 =1252100*60% 1983720 Two month after sale 400480 =1001200*40% 361520 =903800*40% 460120 =1150300*40% 1222120 Total collections 942760 1051700 1211380 3205840 2) Merchandise purchase Budget Mar Apr May June Total July Budgeted cost of goods sold 361520 460120 =1150300*40% 500840 =1252100*40% 563000 =1407500*40% 1523960 600400 Plus Desired Inventory 322084 (460120*70%) 350588 (500840*70%) 394100 (563000*70%) 420280 (600400*70%) 1164968 Inventory Needed 683604 810708 894940 983280 2688928 Less: Beginning Inventory 253064 (361520*70%) 322084 (460120*70%) 350588 394100 1066772 required purchase 430540 488624 544352 589180 1622156 3 Apr May June Total March pur 107635 (430540*25%) 107635 Apr pur 366468 (488624*75%) 122156 (488624*25%) 488624 May pur 408264 (544352*75%) 136088 (544352*25%) 544352 June pur 441885 (589180*75%) 441885 Total cash disbursement for purchase 474103 530420 577973 1582496 4 cash budget Apr May June Total Beginning cash bal 50300 -44631 50810 41000 Add cash collection 942760 1051700 1211380 3205840 Total cash available 993060 1007069 1262190 3246840 Less: cash disbursement Purchase inventory 474103 530420 577973 1582496 General and admin exp excluding property tax and depreciation 89258 =(1565600-349400-145100)/12 89258 89258 267775 Property Tax 36275 =145100/4 36275 Hourly wages & Fringe benefit 345090 =1150300*30% 375630 =1252100*30% 422250 =1407500*30% 1142970 Tax paid 129240 =323100*40% Total cash disbursements 1037691 995308 1125756 3029516 Excess (deficiency) of cash -44631 11760 136434 217324 Financing: Borrowings 95000 0 95000 Repayments 55000 40000 Interest 950 =95000*12%/12 400 =40000*12%/12 Total Financing 0 39050 -40400 -1350 Ending cash balance -44631 50810 96034 215974