Question

Hillyard Company, an office supplies specialty store, prepares its master budget on a quarterly basis. The following data have been assembled to assist in preparing the master budget for the first quarter:

As of December 31 (the end of the prior quarter), the company’s general ledger showed the following account balances:

Cash	$	60,000
Accounts receivable		216,000
Inventory		60,750
Buildings and equipment (net)		370,000
Accounts payable			$	91,125
Common stock				500,000
Retained earnings				115,625
	$	706,750	$	706,750

Actual sales for December and budgeted sales for the next four months are as follows:

December(actual)	$	270,000
January	$	405,000
February	$	602,000
March	$	317,000
April	$	213,000

Sales are 20% for cash and 80% on credit. All payments on credit sales are collected in the month following sale. The accounts receivable at December 31 are a result of December credit sales.

The company’s gross margin is 40% of sales. (In other words, cost of goods sold is 60% of sales.)

Monthly expenses are budgeted as follows: salaries and wages, $35,000 per month: advertising, $61,000 per month; shipping, 5% of sales; other expenses, 3% of sales. Depreciation, including depreciation on new assets acquired during the quarter, will be $45,300 for the quarter.

Each month’s ending inventory should equal 25% of the following month’s cost of goods sold.

One-half of a month’s inventory purchases is paid for in the month of purchase; the other half is paid in the following month.

During February, the company will purchase a new copy machine for $3,000 cash. During March, other equipment will be purchased for cash at a cost of $80,000.

During January, the company will declare and pay $45,000 in cash dividends.

Management wants to maintain a minimum cash balance of $30,000. The company has an agreement with a local bank that allows the company to borrow in increments of $1,000 at the beginning of each month. The interest rate on these loans is 1% per month and for simplicity we will assume that interest is not compounded. The company would, as far as it is able, repay the loan plus accumulated interest at the end of the quarter.

Required:

Using the data above, complete the following statements and schedules for the first quarter:

1. Schedule of expected cash collections:

2-a. Merchandise purchases budget:

2-b. Schedule of expected cash disbursements for merchandise purchases:

3. Cash budget:

4. Prepare an absorption costing income statement for the quarter ending March 31.

5. Prepare a balance sheet as of March 31.

Answer 1

1

Following assumptions are taken for calculating schedule of cash collections

• The collection schedule is for example the budgeted sales amount of January is $405,000 , then 20% of sales is cash sales and collected in month of sale that is cash collection for jan will be $81,000 and balance 80% is credit sales will be collected next month that is collection will be in feb for $324,000

Basis on this the schedule of expected cash collections is given below

Schedule of Expected Cash Collections
	December	January	February	March	Quarter
budgeted sales	270000	405000	602000	317000
Cash sales(20% of current month	54000	81000	120400	63400	264800
Credit sales(80% of previous month)		$216,000	$324,000	$481,600	1021600
Total Collections		297000	444400	545000	1286400

2 )

Following assumptions are taken for calculating schedule of Merchandise purchases budget

• The cost of goods sold is 60% of budgeted sales for the month for calculation of Jan cost of goods sold is given below

Cost of goods sold for Jan = budgeted sales of jan x 60%

Cost of goods sold for Jan = $405000x 60%

Cost of goods sold for Jan = $243000

• The calculation of ending/closing inventory for the month is 25% of next month cost of goods sold amount

Closing inventory of jan = cost of goods sold of feb x 25%

Closing inventory of jan = $361200x 25%

Closing inventory of jan = $90,300

The formula to calculate merchandise purchase is

Purchase for the month= cost of goods sold for the month   + closing inventory for the month – opening inventory for the month or closing inventory of previous month

The schedule of merchandise purchase budget is calculated below

Merchandise Purchases Budget
	January	February	March	april	Quarter
budgeted sales	405000	602000	317000	213000
Budgeted Cost of Goods Sold(60% of current month sale)	243000	361200	190200	127800	794400
Add desired ending inventory	90300	47550	31950		169800
Total needs	$333,300	$408,750	$222,150		964200
Less beginning inventory	$60,750	90300	47550		198600
Required purchases	$272,550	$318,450	$174,600		765600

Following assumptions are taken for calculating Schedule of expected cash disbursements for merchandise purchases:

• The December accounts payable as shown in balance sheet is cash amount to be paid in January for merchandise purchases
• The cash disbursement schedule is the total purchases made in jan will be as follows

50% cash will paid for purchases in jan

Balance 50% cash will be paid in feb forpurchases of jan

The Schedule of expected cash disbursements for merchandise purchases is given below

Schedule of Expected Cash Disbursements-Merchandise Purchases
	January	February	March	Quarter
December purchases( accounts payable closing balance of december balance sheet)	$91,125			$91,125
January purchases	$136,275	$136,275		$272,550
February purchases		$159,225	$159,225	$318,450
March purchases			$87,300	$87,300
Total disbursements	$227,400	$295,500	$246,525	$769,425

The cash budget is amount of actual cash inflow and outflow is shown depreciation being a notional entry where actually cash is not paid is not taken in cash budget

The following assumptions are taken

• The minimum cash closing balance of $30,000 has to be maintained
• For financing needs for any shortfall of cash the caompnay can take loan from bank in multiples of $1000 at the beginning of the months
• For any loan taken interest @1% will be paid per month

First we make schedule of cash disbursement – selling and administrative expenses

Schedule of Expected Cash Disbursements-Selling and Administrative Expenses
	January	February	March	Quarter
budgeted sales	405000	602000	317000
Salaries and wages	$35,000	$35,000	$35,000	$105,000
Advertising	$61,000	$61,000	$61,000	$183,000
Shipping(5% of budgeted sales)	$20,250	$30,100	$15,850	$66,200
Other expenses(3% of budgeted sales)	$12,150	$18,060	$9,510	$39,720
Total disbursements	$128,400	$144,160	$121,360	$393,920

The cash budget is calculated below

Cash Budget
	January	February	March	Quarter
Cash balance, beginning	$60,000	$30,200	$31,940	$60,000
Add cash collections	$297,000	$444,400	$545,000	$1,286,400
Total cash available	$357,000	$474,600	$576,940	$1,346,400
Less cash disbursements
For inventory purchase	$227,400	$295,500	$246,525	$769,425
For selling and admin expenses	$128,400	$144,160	$121,360	$393,920
For purchase of equipment		3000	80000	$83,000
For cash dividends	$45,000			$45,000
Total cash disbursements	$400,800	$442,660	$447,885	$1,291,345
Excess (deficiency) of cash	($43,800)	$31,940	$129,055	$55,055
Financing needed
Borrowing	74000			$74,000
repayment			-74000	($74,000)
Interest(74000*12%*3/12)			-2220	($2,220)
Net cash from financing	74000	0	-76220	($2,220)
Cash balance, ending	$30,200	$31,940	$52,835	$52,835

Prepare an absorption costing income statement for the quarter ending March 31. Given below

Hillyard Company
Budgeted Income Statement
For the Quarter ended March 30,20XX
Sales					1324000
Less:	Cost of Goods Sold				794400
Gross Margin					529600
Less:	Operating Expenses(selling and distribution expense)				$393,920
	Depreciation				45300
Operating Income					90380
Less:	Interest Expense				2220
Net Income					88160

Prepare a balance sheet as of March 31. Is given below

Hillyard Company
Budgeted Balance Sheet
For the Quarter ended March 30,20XX
Current Assets
	Cash					$52,835
	Account Receivable(80% of 317000)					253600
	Inventory					31950
Total Current Assets						338385
Building and Equipment						$407,700
(Beginning 370000+New purchase 83000-Depreciation 45300)
Total Assets						746085
Liabilities and Equity
	Account Payable(50% of 174600)					$87,300
Equity:
	Common Stock					500000
	Retained Earnings:
		Beginning		115625
	add:	Net Income		88160
		Total		203785
	Less:	Cash Dividend		$45,000		158785
Total Liability and Equity						746085