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 preparation of the master budget for the first quarter:

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

	Debits			Credits
Cash	$	46,000
Accounts receivable		232,000
Inventory		57,000
Buildings and equipment (net)		375,000
Accounts payable				$	96,000
Capital shares					505,000
Retained earnings					109,000
	$	710,000		$	710,000

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

December (actual)	$	290,000
January		380,000
February		530,000
March		230,000
April		190,000

c.	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.
d.	The company’s gross margin is 40% of sales.
e.	Monthly expenses are budgeted as follows: salaries and wages, $29,000 per month; advertising, $70,000 per month; shipping, 5% of sales; depreciation, $14,000 per month; other expenses, 3% of sales.
f.	At the end of each month, inventory is to be on hand equal to 25% of the following month’s sales needs, stated at cost.
g.	One-half of a month’s inventory purchases are paid for in the month of purchase; the other half are paid for in the following month.
h.	During February, the company will purchase a new copy machine for $6,400 cash. During March, other equipment will be purchased for cash at a cost of $75,000.
i.	During January, the company will declare and pay $43,000 in cash dividends.
j.	The company must maintain a minimum cash balance of $32,000. An open line of credit is available at a local bank for any borrowing that may be needed during the quarter. All borrowing is done at the beginning of a month, and all repayments are made at the end of a month. Borrowings and repayments of principal must be in multiples of $1,000. Interest is paid only at the time of payment of principal. The annual interest rate is 12%. (Figure interest on whole months, e.g., 1/12, 2/12.)

Required: Using the preceding data, complete the following statements and schedules for the first quarter: 1. Schedule of expected cash collections. HILLYARD COMPANY Schedule of Expected Cash Collections January March February Quarter Cash sales Credit sales Total cash collections 2-a. Inventory purchases budget. HILLYARD COMPANY Inventory Purchases Budget January February March Quarter Total needs

Answer 1

Requirement1.

Hillyard Company
Schedule of Expected Cash Collections
	January	February	March	Quarter
Cash sales	$76,000	$106,000	$46,000	$228,000
Credit sales	$232,000	$304,000	$424,000	$960,000
Total cash collections	$308,000	$410,000	$470,000	$1,188,000

Working notes:

1. 20% of sales are collected as cash sales in the same month as sale. So, cash sales above for each month are calculated as 20% * respective month’s sale. So, cash sales for January = 20% *$380,000 = $76,000. Similarly, cash sales for February = 20% *$530,000 and cash sales for March = 20%*$230,000.

2. 80% of sales are collected in the month following the sales. So, credit sales collections above are basically credit sales collections,i.e., 80% of sales of previous month. So, credit sales collections in January = 80% * December sales,i.e., 80%*$290,000. Similarly, credit sales collections in February = 80%*January sales = 80%*$380,000. Same way for March.

3. Totals for each month and for the entire quarter are calculated by totaling up collections column-wise and row-wise

Requirement2A.

Hillyard Company
Inventory Purchase Budget
	January	February	March	Quarter
Budgeted cost of goods sold	$228,000	$318,000	$138,000	$684,000
Add: Desired ending inventory	$79,500	$34,500	$28,500	$142,500
Total needs	$307,500	$352,500	$166,500	$826,500
Less: Opening balance of inventory	$57,000	$79,500	$34,500	$171,000
Inventory to be purchased during the month	$250,500	$273,000	$132,000	$655,500

Working notes:

1. Since gross profit is 40% of sales, so cost of goods sold = 60% * sales. Budgeted cost of goods sold during the month = 60% *budgeted sales of that month. So, for January, budgeted cost of goods sold = 60%*$380,000 = $228,000.

2. Desired ending inventory = 25% of budgeted cost of goods sold of next month. For the month of March, desired ending inventory will be 25%* April month’s budgeted sales at cost,i.e., 25%* (60%*$190,000).

3. Total needs = Budgeted cost of sales during the month + Required closing balance of inventory

4. Opening balance of inventory for January is given as $57,000. For other months, it is closing balance of previous month’s inventory.

5. Inventory to be purchased = Total needs less opening balance of inventory available.

Requirement 2B.

Hillyard Company
Schedule of cash disbursements for Purchases
	January	February	March	Quarter
December Purchases	$93,750
January Purchases	$125,250	$125,250
February Purchases		$136,500	$136,500
March Purchases			$66,000
Total cash disbursements for Purchases	$219,000	$261,750	$202,500	$683,250

Working notes:

1. Inventory purchases are paid ½ in same month and ½ in following month. So, in January, ½ of December inventory purchased are paid and ½ of January Purchases are paid, and so on.

2. Calculation of December inventory purchases as per table in requirement 2A :

	December
Actual cost of goods sold (60%*sales)	$174,000
Add: Desired ending inventory	$57,000
Total needs	$231,000
Less: Opening inventory(25%*COGS for December)	$43,500
Inventory to be purchased during the month	$187,500

So, ½ of December Purchases (1/2*$187,500) to be paid in January = $93,750 and ½ of January Purchases(1/2*$250,500) to be paid in January, and so on.

Requirement 3.

Hillyard Company
Schedule of cash disbursements for expenses
	January	February	March	Quarter
Salaries & Wages	$29,000	$29,000	$29,000	$87,000
Advertising	$70,000	$70,000	$70,000	$210,000
Shipping (5%*Sales)	$19,000	$26,500	$11,500	$57,000
Other expenses (3% * Sales	$11,400	$15,900	$6,900	$34,200
Total cash disbursements for operating expenses	$129,400	$141,400	$117,400	$388,200

Working note:

Depreciation is not a cash expense. Hence, it is not included in the above schedule.

Requirement 4.

Hillyard Company
Cash Budget
	January	February	March	Quarter
Opening balance of cash	$46,000	$32,600	$32,030	$110,630
Add: Total cash collections during the month	$308,000	$410,000	$470,000	$1,188,000
Total cash available	$354,000	$442,600	$502,030	$1,298,630
Deduct: Disbursements:				$0
Cash disbursement for inventory purchases	$219,000	$261,750	$202,500	$683,250
Cash disbursements for operating expenses	$129,400	$141,400	$117,400	$388,200
Purchase of copy machine		$6,400		$6,400
Purchase of equipment			$75,000	$75,000
Payment of Cash dividends	$43,000			$43,000
Total disbursements	$391,400	$409,550	$394,900	$1,195,850
Excess(deficiency) of cash	-$37,400	$33,050	$107,130	$102,780
Financing:				$0
Borrowings	$70,000	$0	$0	$70,000
Interest on borrowings	$0	$20	$2,070	$2,090
Net borrowings	$70,000	$0	$0	$70,000
Repayments	$0	$1,000	$69,000	$70,000
Total financing	$70,000	$69,000	$0	$139,000
Cash balance at end of month	$32,600	$32,030	$36,060	$100,690

Working notes:

Since $32,000 cash balance must be maintained at the end of the month for next month opening balance, deficiency in January of $37,400 and $32,000 cash requirement = $69,400 must be met, but amount can be borrowed only in multiples of $1,000. So, $70,000 will be borrowed. Further, since interest is to be paid only when principal is repaid. So, no interest payment in January.

Now, cash balance at end of January = $ - 37,400 + $70,000 = $32,600. This will be opening balance in February.

Excess of cash in February = $33,050, while cash requirement at month end = $32,000. So, $1,000 can be repaid on loan. Interest on $1,000 for 2 months at 12% = $20. So, $1,020 repaid to bank. Balance loan left = $70,000 - $1,000 = $69,000. Cash balance at Feb. end = $33,050 - $1,020 = $32,030.

In March, entire $69,000 left is paid off with interest on it for 3 months at 12% per annum.