Question

VROOM Inc. is a merchandiser that sells spa products to the hotel industry. (the company is not a manufacturer). Management is planning its cash needs for the second quarter April, May and June). The following information has been assembled to assist in preparing a cash budget for the second quarter: 1. Budgeted monthly income statements for April to July are as follows: Sale.000 300.00 April May June July Sales $470,000 $710,000 $410,000 $300,000 Cost of goods sold 336,000 504,000 280,000 220,000 Gross margin 134,000 206,000 130,000 80,000 Less: Operating expenses: Selling expense 53,200 96,000 49,600 40,800 Administrative expense 46,000 41,600 32,800 30,400 Total operating expenses 99,200 137,600 82,400 71,200 Net income $ 34,800 $68,400 $ 47,600 $ 8,800 2. Sales are 15% for cash and 85% on account. 3. Sales on account are collected over a three-month period in the following pattern: 20% collected in the month of sale, 70% collected in the month following the sale, and the remaining 10% is collected in the second month following the sale. February sales totalled $160,000, and March sales totalled $300,000. 4. Inventory purchases are paid 50% in the month of purchase. The remaining 50% is paid in the following month. Accounts payable at March 31 for inventory purchases during March total $150,000.- 5. At the end of each month, inventory must be on hand equal to 15% of the cost of the merchandise to be sold in the following month. The merchandise inventory at March 31 is $67,200. (HINT: the desired ending inventory for April is 15% x $504,000) 6. Dividends of $90,000 will be declared and paid in April.. 7. Land will be sold for $25,000 cash in May.

Answer 1

Vroom Inc. Sales Budget Cash Credit Total April May 70500 106500 399500603500 470000 710000 June Totals 61500238500 348500 1351500 410000 1590000 Sales Collections May June April 16000 210000 150400 February March April May June Totals 30000 279650 227200 39950 422450 131200 593600 Totals 16000 240000 470000 649650 131200 1506850 376400 536850 Purchase Budget Cost of goods sold Ending Inventory April 336000 75600 411600 -67200 344400 May 504000 42000 546000 -75600 470400 June 280000 33000 313000 - 42000 271000 Totals 1120000 33000 1153000 -67200 1085800 Beginning Inventory Purchases Purchase Payments April 150000 172200 March April May June Totals May June Totals 150000 172200 344400 235200235200470400 135500 135500 407400 370700 1100300 322200 Cash Budget For the Quarter ending June 30 April 130600 376400 May 20600 536850 25000 582450 June 62450 593600 Opening Balance Sales Collections Land Sold Cash Available Totals 130600 1506850 25000 1662450 507000 656050 Payments Purchase Payments Selling Expense Administrative Expense Dividends Total Payments Excess Cash over payments 407400 96000 16600 370700 49600 7800 322200 53200 21000 90000 486400 20600 1100300 198800 45400 90000 1434500 227950 520000 62450 428100 227950

1 Vroom Inc. 2 3 Sales Budget April =B7*0.15 =B7-B5 470000 May =C7*0.15 C7-05 710000 June =D7*0.15 =D7-05 410000 Totals =SUM(B5:05) =SUM(B6:06) =SUM(B7:07) May June 5 Cash 6 Credit 7 Total 8 9 Sales Collections 10 11 February 12 March 13 April 14 May 15 June 16 Totals 17 18 Purchase Budget 19 20 Cost of goods sold 21 Ending Inventory April =160000*0.1 =300000*0.7 =B5+(B6*0.2) =300000*0.1 =B6*0.7 =C5+(C6*0.2) =B6 0.1 =C6*0.7 =D5+D6*0.2) SUM(D11:D15) Totals =SUM(B11:011) =SUM(B12:012) =SUM(B13:013) =SUM(B14:014) =SUM(B15:D15) SUM(B16:016) =SUM(B11:315) SUM(C11C15) April 336000 =C20*0.15 =SUM(B20:321) -67200 =SUM(B22:B23) May 504000 =D20*0.15 =SUM(C20:021) --B21 SUM(C22:023) June 280000 =220000*0.15 =SUM(D20:D21) --C21 =SUM(D22:D23) Totals =SUM(B20:D20) =D21 SUM(E20:E21) =B23 =SUM(E22:E23) 22 23 Beginning Inventory 24 Purchases 25 26 Purchase Payments 27 May June April 150000 =B24/2 =B24/2 =C24/2 28 March 29 April 30 May 31 June 32 Totals 33 Totals =SUM(B28:D28) =SUM(B29:029) =SUM(B30:30) =SUM(B31:031) ESUM(B32:032) =C24/2 =D24/2 ESUMD28:D31) =SUM(B28:B31) ESUM(C28:031)

B 34 Cash Budget 35 For the Quarter ending June 30 36 May June April 130600 =B16 =D16 =C16 25000 SUM(C37:039) Totals =B37 =E16 =C39 ESUM(E37:E39) =SUM(B37:B39) SUMD37:039) 37 Opening Balance 38 Sales Collections 39 Land Sold 40 Cash Available 41 42 Payments 43 Purchase Payments 44 Selling Expense 45 Administrative Expense 46 Dividends 47 Total Payments 48 Excess Cash over payments 49 -C32 96000 =41600-25000 =D32 49600 =32800-25000 =B32 53200 =46000-25000 90000 =SUM(B43:346) =B40-847 =E32 =SUM(B44:044) =SUM(B45:045) SUM(E43:E46) SUM(C43:046) =C40-C47 SUM(D43:046) =D40-D47 -FAN-F47

Vroom Inc. Sales Budget Cash Credit Total April 70500 399500 470000 May 106500 603500 710000 June 61500 348500 410000 Totals 238500 1351500 1590000 Sales Collections May June April 16000 210000 150400 February March April May June Totals 30000 279650 227200 39950 422450 131200 593600 Totals 16000 240000 470000 649650 131200 1506850 376400 536850 Purchase Budget Cost of goods sold Ending Inventory April 336000 75600 411600 -67200 344400 May 504000 42000 546000 -75600 470400 June 280000 33000 313000 -42000 271000 Totals 1120000 33000 1153000 -67200 1085800 Beginning Inventory Purchases Purchase Payments June March April May June Totals April May 150000 172200172200 235200 Totals 150000 344400 470400 135500 1100300 235200 135500 370700 322200 407400 May June Cash Budget For the Quarter ending June 30 April Opening Balance 130600 Sales Collections 376400 Land Sold Cash Available 507000 593600 536850 25000 561850 Totals 130600 1506850 25000 1662450 593600 Payments Purchase Payments Selling Expense Administrative Expense Dividends Total Payments Excess Cash over payments 407400 96000 16600 370700 49600 7800 1100300 198800 45400 322200 53200 21000 90000 486400 20600 520000 41850 428100 165500 1344500 317950

1 Vroom Inc. 2 3 Sales Budget April =B7*0.15 =B7-B5 470000 May =C7*0.15 C7-05 710000 June =D7*0.15 =D7-05 410000 Totals =SUM(B5:05) =SUM(B6:06) =SUM(B7:07) May June 5 Cash 6 Credit 7 Total 8 9 Sales Collections 10 11 February 12 March 13 April 14 May 15 June 16 Totals 17 18 Purchase Budget 19 20 Cost of goods sold 21 Ending Inventory April =160000*0.1 =300000*0.7 =B5+(B6*0.2) =300000*0.1 =B6*0.7 =C5+(C6*0.2) =B6 0.1 =C6*0.7 =D5+D6*0.2) SUM(D11:D15) Totals =SUM(B11:011) =SUM(B12:012) =SUM(B13:013) =SUM(B14:014) =SUM(B15:D15) SUM(B16:016) =SUM(B11:315) SUM(C11C15) April 336000 =C20*0.15 =SUM(B20:321) -67200 =SUM(B22:B23) May 504000 =D20*0.15 =SUM(C20:021) --B21 SUM(C22:023) June 280000 =220000*0.15 =SUM(D20:D21) --C21 =SUM(D22:D23) Totals =SUM(B20:D20) =D21 SUM(E20:E21) =B23 =SUM(E22:E23) 22 23 Beginning Inventory 24 Purchases 25 26 Purchase Payments 27 May June April 150000 =B24/2 =B24/2 =C24/2 28 March 29 April 30 May 31 June 32 Totals 33 Totals =SUM(B28:D28) =SUM(B29:029) =SUM(B30:30) =SUM(B31:031) ESUM(B32:032) =C24/2 =D24/2 ESUMD28:D31) =SUM(B28:B31) ESUM(C28:031)

B 34 Cash Budget 35 For the Quarter ending June 30 36 May June April 130600 =B16 =D16 =C16 25000 SUM(C37:039) Totals =B37 =E16 =C39 ESUM(E37:E39) =SUM(B37:B39) SUMD37:039) 37 Opening Balance 38 Sales Collections 39 Land Sold 40 Cash Available 41 42 Payments 43 Purchase Payments 44 Selling Expense 45 Administrative Expense 46 Dividends 47 Total Payments 48 Excess Cash over payments 49 -C32 96000 =41600-25000 =D32 49600 =32800-25000 =B32 53200 =46000-25000 90000 =SUM(B43:346) =B40-847 =E32 =SUM(B44:044) =SUM(B45:045) SUM(E43:E46) SUM(C43:046) =C40-C47 SUM(D43:046) =D40-D47 -FAN-F47