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 $ 49,000 224,000 57,000 356,000 Credits Cash Accounts receivable Inventory Buildings and equipment (net) Accounts payable Capital shares Retained earnings $ 93,000 485,000 108,000 $686,000 $686,000 b. Actual sales for December and budgeted sales for the next four months are as follows December (actual) January February March $280,000 380,000 540,000 250,000 180,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, $25,000 per month; advertising $71,000 per month, shipping, 5% of sales, depreciation, $13,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, g. One-half of a month's inventory purchases are paid for in the month of purchase; the other half are paid h. During February, the company will purchase a new copy machine for $6,200 cash. During March, other i. During January, the company will declare and pay $47,000 in cash dividends stated at cost. for in the following month equipment will be purchased for cash at a cost of $78,000 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.) j.
someone explain to me how to find cash balance, beginning from February to march and other steps

Answer 1

Hillyard Company

Cash Budget

Hillary Company
Cash Budget
	January	February	March	Quarter
Beginning Cash Balance	$49,000	$32,600	$32,950	$49,000
Expected Cash Collection	$300,000	$412,000	$482,000	$1,194,000
Available Cash	$349,000	$444,600	$514,950	$1,243,000
Cash Disbursements:
for Purchases	$219,000	$266,250	$210,000	$695,250
for salaries and wages	$25,000	$25,000	$25,000	$75,000
Advertising	$71,000	$71,000	$71,000	$213,000
Shipping (5% of sales)	$19,000	$27,000	$12,500	$58,500
Other expenses (3% of sales)	$11,400	$16,200	$7,500	$35,100
Purchase of Copy Machine	$0	$6,200	$0	$6,200
Purchase of Equipment	$0	$0	$78,000	$78,000
Cash Dividends	$47,000	$0	$0	$47,000
Total Disbursements	$392,400	$411,650	$404,000	$1,208,050
Excess/ (Deficiency)	($43,400)	$32,950	$110,950	$34,950
Financing:
Borrowings	$76,000	$0	$0	$76,000
Repayments	$0	$0	($76,000)	($76,000)
Interest	$0	$0	($1,520)	($1,520)
Total Financing	$76,000	$0	($77,520)	($1,520)
Ending Cash Balance	$32,600	$32,950	$33,430	$33,430

1. Borrowings are made in multiples of $1,000. Since, the January cash budget shows a deficiency of $43,400 and the required minimum balance is $32,000, the company would borrow $76,000 as shown as follows,

= 43,400 + 32,000 = 75,400 rounded to nearest 1,000 would be $76,000

1. Interest Payment for two months, January and February = 76,000 x 12% x 2/12 = $1,520

1. Determination of expected cash collections for the quarter ended March 31:

Expected Cash Collection:
	January	February	March	Quarter
Accounts Receivable as on Dec 31	$224,000			$224,000
January Sales -
Cash (20% x 380,000)	$76,000			$76,000
Credit (80% x 380,000)		$304,000		$304,000
February Sales -
Cash (20% x 540,000)		$108,000		$108,000
Credit (80% x 540,000)			$432,000	$432,000
March Sales -
Cash (20% x 250,000)			$50,000	$50,000
Total Cash Collection	$300,000	$412,000	$482,000	$1,194,000

Note: Cash collections for each month are a sum of cash sales + collections from credit sales of previous month.

For instance, cash collections for January = cash sales in January (20% of budgeted sales) + collection of credit sales of previous month, which equals to the accounts receivable balance at the end of December.

1. Purchases Budget:

Purchases = budgeted cost of goods sold + desired ending inventory – beginning inventory

Budgeted cost of goods sold = budgeted sales (actual sales) – gross margin

Gross margin = 40%

Hence, budgeted cost of goods sold = budgeted sales (actual sales) – 40% of budgeted sales

Budgeted cost of goods sold = 60% of budgeted sales

For instance, budgeted cost of goods sold for January = 60% x 380,000 = $228,000

Desired ending inventory = 25% of cost of goods sold of following month

Desired ending inventory for January = 25% of February’s COGS

= 25% x (60% x 540,000) = $81,000

Beginning inventory for any month = ending inventory of previous month

Beginning inventory for January = ending inventory of December

= 25% x (60% x 380,000) = $57,000

Desired ending inventory for March = 25% of COGS of April

= 25% x (60% x 180,000) = $27,000

Inventory Purchases Budget –

Inventory Purchases Budget
	January	February	March	Quarter
Budgeted Cost of Goods Sold (60% of sales)	$228,000	$324,000	$150,000	$702,000
Add: Desired ending inventory (25% of Next month's COGS)	$81,000	$37,500	$27,000	$145,500
Total	$309,000	$361,500	$177,000	$847,500
Less: Beginning inventory	($57,000)	($81,000)	($37,500)	($175,500)
Inventory Purchases	$252,000	$280,500	139,500	$672,000

1. Payment for Purchases Budget –

Payment for Purchases:
	January	February	March	Quarter
Accounts Payable for December	$93,000			$93,000
payment for purchases(50% of inventory purchases)
Payment for January purchases	$126,000	$126,000		$252,000
Payment for February purchases		$140,250	$140,250	$280,500
Payment for March Purchases			$69,750	$69,750
Total payment for purchases	$219,000	$266,250	$210,000	$695,250

Note: payment for inventory purchases = 50% in current month and 50% in following month

Payment for January inventory purchases = 50% x 252,000 = $126,000 in January

The remaining 50% is paid in February as follows, 252,000 x 50% = $126,000

Likewise, payment for other months is determined.