Question

You were recently hired as an assistant controller at Just Tables (JT). JT sells one product, dining room tables made out of red cedar. You have been charged with preparing a cash budget for the second quarter (April to June) so that management can be aware of any financing requirements. You have collected the following information to assist you in preparation of the quarterly budget:

Recent and forecasted sales in units are as follows:

January (actual)                                                      140

February (actual)                                                   220

March (actual)                                                        200

April                                                                           220

May                                                                           400

June                                                                           360

July                                                                            320

August                                                                      390

September                                                               260

Tables are sold to retailers for $500 each. The company has a policy of having an ending inventory each month equal to 150% of the next month’s sales. The inventory balance at March 31^st was 330 tables. Each table requires 15 board feet of red cedar, which the company purchases for $4 per board foot. To protect against disruptions in production, management likes to keep enough cedar on hand at all times equal to 50% of the next month’s production needs. This requirement had been met on March 31^st in that the company had 3,675 board feet of red cedar in the warehouse. Purchases of lumber are paid for 50% at the time of purchase with the other 50% being paid the next month. All sales are on credit with 30% being collected in the month of sales, 50% being collected in the month following the month of sale and the remaining 20% being collected two months after the month of sale. JT has a tight credit policy and, as a result, does not have any bad debts. Tables are hand crafted taking, on average, 5 hours to assemble. Employees are paid $16 per hour and never work overtime. Manufacturing overhead includes all the costs of production other than the cedar and direct labour. The variable component is $25 per table manufactured and the fixed component is $32,000 per month. Fixed manufacturing overhead includes $12,000 of depreciation. Manufacturing overhead is applied to tables on the basis of tables manufactured.

JT’s monthly operating expenses are given below:

Variable: Sales commissions                                  $20 per table sold

Fixed: Wages and salaries                         $19,000

               Utilities                                                   1,700

               Insurance                                               1,500

               Depreciation                                         2,100

               Miscellaneous                                      2,200

All operating expenses are paid in the month for cash, with the exception of depreciation and insurance. Insurance is paid once a year in January ($18,000) then expensed over the entire year. The company plans to purchase some new manufacturing equipment in April for $26,000. JT declares a dividend of $5,000 on the last day of every quarter (i.e. March 31^st, June 30^th, September 30^th and December 31^st) and pays it one month later (i.e. the March dividend is paid April 30^th, the June dividend is paid July 31^st, etc.)

The balance sheet at March 31st is given below:

Assets

Cash                                                                                $ 25,000

Accounts receivable                                                       92,000

Inventory, red cedar lumber (raw materials)           14,700

Inventory, tables (finished goods)                              82,500

Prepaid insurance                                                           13,500

Fixed assets                                  270,000

Accumulated depreciation      (162,000)                  108,000

Total Assets                                                                    335,700

Liabilities and Shareholder’s Equity

Accounts payable, purchases                                       10,800

Dividends payable                                                             5,000

Capital stock                                                                     15,000

Retained earnings                                                         304,900

Total liabilities and shareholder’s equity                335,700

Management believes in keeping a minimum cash balance of $20,000 at the end of each month. The company can borrow from the bank at 12% annual interest. All borrowing must be done at the beginning of the month, and repayments must be done at the end of the month. Borrowings and repayments must also be in increments of $1,000. Interest is paid at the end of each quarter. Round all interest payments to the nearest whole dollar. The company wishes to use any excess cash to pay off the loan as rapidly as possible.

Required:

1. Prepare a Sales Budget, Production Budget, Direct Materials Budget, Direct Labour Budget, Manufacturing Overhead Budget and Sales and Administration Budget.
2. Prepare Schedules of Expected Cash Collections and Expected Cash Disbursements for Materials as well as a Cash Budget.
3. Prepare a Budgeted Income Statement for the Quarter Ended June 30^th.

4. Prepare a Budgeted Balance Sheet at June 30^th.

Answer 1

Cash budget for the second quarter (April to June):

Particulars	April	May	June	July	August
Units sales (S)	220	400	360	320	390
Sales @500 per unit (sales budget)	110000	88000	180000	160000	195000
Opening inventory @150% of the month sale (A)	330	600	540	480	585
Closing inventory @ 150% of next month sale (B)	600	540	480	585
Production Budget (B+S-A)	490	340	300	425

Required board @15 per unit	7350	5100	4500	6375
Opening stock	3675	2550	2250
Closing stock is 50% of next month required	2550	2250	3188
Material purchase budget in units	6225	4800	5438
Required production	490	340	300	425
Required labour @5 hr per unit	2450	1700	1500	2125

Cash Budget

Particulars	April	MAy	June
Sales recevied for the month	33000	60000	54000
Sales received for previous month	50000	55000	100000
Sales received for second previous month	22000	20000	22000
Total collection	105000	135000	176000
MAterial paid for the month	12450	9600	10876
Material paid for previous month	10800	12450	9600
Labour	39200	27200	24000
Fixed wages	19000	19000	19000
Utilities	1700	1700	1700
Miscellaneous	2200	2200	2200
Variable sales commission	4400	8000	7200
Dividend paid	5000
Machinery purcgased	26000
MAnufacturing overhead Variable	12250	8500	7500
Manufacturing overhaed fixed	20000	20000	20000
Total Payments	153000	108650	102076
Opening Cash	25000	(23000)	3350
Closing cash	(23000)	3350	77274