Built-Tight Cash Budget For the months of July, August, and September Beginning cash balance Add: Budgeted cash collections From current month cash sales July 15000 August =C23 September Quarter =D23 =C5 =64000*20% =80000*20% |=48000*20% =SUM(C7:E7) From previous month credit sales 45000 =64000*80% =80000*80% =SUM(C8:E8) Total cash collections =SUM(C7:C8) |=SUM(D7:D8) =SUM(E7:E8) =SUM(F7:F8) Total cash available before disbursements =C5+C9 =D5+D9 =E5+E9 =F5+F9 Less: Budgeted cash payments for Direct materials 16160 13440 Direct labor 4040 3360 Factory overhead For sales commissions For office salaries For rent For interest on loan Total budgeted cash payments Cash available before financing Add: Cash borrowed Less: Cash repaid 20200 16800 |=64000*10% =80000*10% 4000 6500 =5000*1% 4000 6500 Ending cash balance 13760 =SUM(C12:E12) 3440 =SUM(C13:E13) 17200 =SUM(C14:E14) |=48000*10% =SUM(C15:E15) 4000 =SUM(C16:E16) 6500 =SUM(C17:E17) |=ROUND((5000-450)*1%,0) 0 =SUM(C18:E18) =SUM(C12:C18) =SUM(D12:D18) |=SUM(E12:E18) |=SUM(F12:F18) =C10-C19 =D10-D19 =E10-E19 =F10-F19 0 0 0 =SUM(C21:E21) |=C20-15000 =5000-C22 0 |=SUM(C22:E22) |=C20+C21-C22 =D20+D21-D22 |=E20+E21-E22 =F20+F21-F22 A1 fx Solution - A B C D E F G 1 Solution 2 3 4 5 Beginning cash balance Built-Tight Cash Budget For the months of July, August, and September July $15,000 August $15,000 September Quarter $25,504 $15,000 6 Add: Budgeted cash collections 7 From current month cash sales $12,800 $16,000 $9,600 $38,400 8 From previous month credit sales $45,000 $51,200 $64,000 $160,200 9 Total cash collections $57,800 $67,200 $73,600 $198,600 10 Total cash available before disbursements $72,800 $82,200 $99,104 $213,600 11 Less: Budgeted cash payments for 12 Direct materials 13 15 17 14 16 Direct labor Factory overhead For sales commissions For office salaries For rent $16,160 $4,040 $20,200 $13,440 $3,360 $16,800 $17,200 $13,760 $43,360 $3,440 $10,840 $54,200 $6,400 $8,000 $4,800 $19,200 $4,000 $4,000 $4,000 $12,000 $6,500 $6,500 $6,500 $19,500 18 For interest on loan $50 $46 $0 $96 19 Total budgeted cash payments $57,350 $52,146 $49,700 $159,196 20 Cash available before financing $15,450 $30,054 $49,404 $54,404 21 Add: Cash borrowed $0 $0 $0 $0 22 Less: Cash repaid $450 $4,550 $0 $5,000 23 Ending cash balance $15,000 $25,504 $49,404 $49,404 24 Built-Tight is preparing its master budget for the quarter ended September 30. Budgeted sales and cash payments for product costs for the quarter as shown in the picture. A B C D 1 July August September 2 Budgeted sales $64,000 $80,000 $48,000 3 Budgeted cash payments for 4 Direct materials 16,160 13,440 13,760 5 Direct labor 4,040 3,360 3,440 6 Factory overhead 20,200 16,800 17,200 Sales are 20% cash and 80% on credit. All credit sales are collected in the month following the sale. The June 30 balance sheet includes balances of $15,000 in cash; $45,000 in accounts receivable; $4,500 in accounts payable; and a $5,000 balance in loans payable. A minimum cash balance of $15,000 is required. Loans are obtained at the end of any month when a cash shortage occurs. Interest is 1% per month based on the beginning-of-the-month loan balance and is paid at each month-end. If an excess balance of cash exists, loans are repaid at the end of the month. Operating expenses are paid in the month incurred and consist of sales commissions (10% of sales), office salaries ($4,000 per month), and rent ($6,500 per month). Prepare a cash budget with explanations for each of the months of July, August, and September. (Round amounts to the dollar.)