Sprinkler Corporation produces plastic garden sprinklers. The company is preparing its budget for 2015. The first step is to plan for the first quarter of that coming year. Sprinkler has collected the following information from the managers.

1. Sales:

Sales for November 2014 112,500 units

Sales for December 2014 102,100 units

Expected sales for January 2015 113,000 units

Expected sales for February 2015 112,500 units

Expected sales for March 2015 116,000 units

Expected sales for April 2015 125,000 units

Expected sales for May 2015 137,500 units

Selling price per unit $12

Sprinkler likes to keep 10% of next months unit sales in ending inventory. All sales are on credit. 85% of the accounts receivable are collected in the month of sale and 15% of the accounts receivable are collected in the month after sale. Accounts receivable on December 31, 2014, totaled $183,780.

2. Direct Materials:

2 pounds of direct materials is needed to produce one unit. Sprinkler likes to keep 5% of the materials needed for the next months production in its ending inventory. Raw materials on hand on December 31, 2014, totaled 11,295 pounds.

Payment for materials is made within 15 days. 50% is paid in the month of purchase, and 50% is paid after the month of purchase. Accounts payable on December 31, 2014, totaled $120,595.

3. Direct Labor

Labor requires 12 minutes per unit for completion and is paid at a rate of $8 per hour.

4. Manufacturing Overhead

Indirect materials $0.30 per labor hour

Indirect labor $0.50 per labor hour

Utilities $0.45 per labor hour

Maintenance $0.25 per labor hour

Factory supervisors salary $42,000 per month

Factory Depreciation $16,800 per month

Property taxes $ 2,675 per month

Insurance $ 1,200 per month

Repairs $ 1,300 per month

5. Selling and Administrative expenses

Salaries $72,000 per month

Advertising $15,000 per month

Insurance $ 1,400 per month

Office Depreciation $ 2,500 per month

Other fixed costs $ 3,000 per month

Other Information

The cash balance on December 31, 2014, totaled $100,500, but management has decided it would like to maintain a cash balance of at least $800,000 beginning on January 31, 2015.

Dividends are paid each month @ $2.50 per share for 5,000 shares. The company has an open line of credit with national Bank. The terms of the agreement require borrowing to be in the increments of $1,000, and the interest rate is 8%. Sprinkler borrows on the first day of the month and repays on the last day of the month if possible.

A $500,000 equipment purchase is planned for February 2015.

Required:

Do the following for the first quarter (January, February & March) of 2015 by using EXCEL spreadsheet:

1. Prepare a Cash Budget. use the table below

March Indirect labor For the quarter ending March 31 January February Labor hours 13554 13542 Cost per labor hours 0.5 0.5 6777 6771 Totals 41124 14028 0.5 0.5 20562 7014 March Utilities For the quarter ending March 31 January February Labor hours 13554 13542 Cost per labor hours 0.45 0.45 6099 6094 Totals 41124 14028 0.45 6313 0.45 18506 March Maintenance For the quarter ending March 31 January February Labor hours 13554 13542 Cost per labor hours 0.25 0.25 3389 3386 14028 Totals 41124 0.25 10281 0.25 3507 March 1212839 Totals 100500 4072980 4173480 1385700 2598539 264983 775362 328992 112224 4208 12337 7014 20562 Cash Budget For the quarter ending March 31 January February Beginning Balance 100500 904060 Collection 1336380 1350900 Cash Available 1436880 2254960 Payments Purchases 250482 259897 Direct Labor 108432 108336 Indirect materials 4066 4063 Indirect labor 6777 6771 Utilities 6099 6094 Maintenance 3389 3386 Factory supervisor's salary 42000 42000 Property taxes 2675 2675 Insurance 1200 1200 Repairs 1300 1300 Salaries 72000 72000 Advertising 15000 15000 Insurance 1400 1400 Office Depreciation 2500 2500 Other fixed costs 3000 3000 Dividends 12500 12500 Equipment 500000 Total Payments 532820 1042121 Excess Cash 904060 1212839 6313 18506 3507 10281 42000 126000 2675 8025 1200 3600 1300 3900 72000 216000 15000 45000 1400 4200 2500 7500 3000 9000 12500 37500 500000 551824 2126765 2046715 2046715 Financing: Borrowing Interest Repayment Total Closing Balance 0 0 0 0 904060 1212839 2046715 2046715 Opening Amount (Beginning Cash balance) $ Collection from Customers $ Available Cash January 100,500 $ 1,336,380 $ 1,336,380 $ 1,436,880 $ February 904,060 $ 1,350,900 $ 1,350,900 $ 1,350,900 $ March Quarter Totals 1,212,839 $ 2,217,399 1,385,700 $ 4,072,980 1,385,700 $ 4,072,980 1,385,700 $ 4,173,480 Totals $ $ Less: Disbursements Direct Material Direct Labor Manufacturing Overhead Selling and Adminstrative Expenses Purchases $ $ $ Dividends $ Total Disbursements $ Add $ Borrowings Less: Repayments including Interest $ Closing Balance * Beginning Cash balance equal Ending cash balance of the previous month