Hillyard Company, an office supplies specialty store, prepares its master budget

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: 1.As of December 31, (the end of the prior quarter), the company’s general ledger showed the following account balances: Cash $48,000 (debit) Accounts receivable $224,000 (debit) Inventory $60,000 (debit) Buildings and equipment, net $370,000 (debit) Accounts payable $93,000 (credit) Capital stock $500,000 (credit) Retained earnings $109,000 (credit) 2.Actual sales for December and budgeted sales for the next four months are as follows: December $280,000, January $400,000, February $600,000, March $300,000 and April $200,000. 3.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. 4.The company’s gross margin is 40% of sales. (In other words, cost of goods sold is 60% of sales.) 5.Monthly expenses are budgeted as follows: salaries and wages, $27,000 per month; advertising, $70,000 per month; shipping, 5% of sales; other expenses, 3% of sales. Depreciation, including depreciation on new assets acquired during the quarter, will be $42,000 per quarter. 6.Each month’s ending inventory should equal 25% of the following month’s cost of goods sold. 7.One-half of the month’s inventory purchases is paid for in the month of purchase; the other half is paid in the following month. 8.During February, the company will purchase a new copy machine for $1,700 cash. During March, other equipment will be purchased for cash at a cost of $84,500. 9.During January, the company will declare and pay $45,000 in cash dividends. 10.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: Schedule of Expected Cash Collections January February March Quarter Cash sales $80,000 Credit sales $224,000 Total Collections $304,000 Using the data above, complete the following statements and schedules for the first quarter. Submit your responses in an Excel spreadsheet: 1.Schedule of expected cash collections 2.Merchandise purchases budget Merchandise Purchases Budget January February March Quarter Budgeted Cost of Goods Sold $240,000* $360,000 Add desired ending inventory $90,000** Total needs $330,000 Less beginning inventory $60,000 Required purchases $270,000 *$400,000 sales x 60% cost ratio = $240,000 ** $360,000 x 25% = $90,000 3.Schedule of expected cash disbursements-merchandise purchases Schedule of Expected Cash Disbursements-Merchandise Purchases January February March Quarter December purchases $93,000 $93,000 January purchases $135,000 $135,000 $270,000 February purchases March purchases Total disbursements $228,000 4.Schedule of expected cash disbursements-selling and administrative expenses Schedule of Expected Cash Disbursements-Selling and Administrative Expenses January February March Quarter Salaries and wages $27,000 Advertising $70,000 Shipping $20,000 Other expenses $12,000 Total disbursements $129,000 5.Cash budget: Cash Budget January February March Quarter Cash balance, beginning $48,000 Add cash collections $304,000 Total cash available $352,000 Less cash disbursements For inventory $228,000 For selling and admin expenses $129,000 For purchase of equipment ------ For cash dividends $45,000 Total cash disbursements $402,000 Excess (deficiency) of cash ($50,000) Financing needed Cash balance, ending

Explanation / Answer

Hillyard Company Sales Budget For the Quarter ended March 30,2011 January February March Quarter Budgeted Sales in Dollars 400000 600000 300000 1300000 Total Budgeted Sales 400000 600000 300000 1300000 Hillyard Company Expected Cash Collection For the Quarter ended March 30,2011 January February March Quarter Account Receivable 12/31(80% of 280000) 224000 224000 January-11 Sales 20% of 400000 80000 80000 80% of 400000 320000 320000 February-2011 Sales 20% of 600000 120000 120000 80% of 600000 480000 480000 March -2011 Sales 20% of 300000 60000 60000 Total Cash Collection 304000 440000 540000 1284000 Hillyard Company Inventory Purchase Budget For the Quarter ended March 30,2011 January February March Quarter Budgeted Cost of Goods Sold(60% of Sales) 240000 360000 180000 780000 Add: Desired Ending Inventory(25% of Next Month's COGS) 90000 45000 30000 30000 Total Needs 330000 405000 210000 810000 Less: Beginning Inventory 60000 90000 45000 60000 Required Inventory Purchase 270000 315000 165000 750000 Hillyard Company Cash Disbursement for Purchase For the Quarter ended March 30,2011 January February March Quarter December Purchase 93000 93000 January Purchase(50% 0f 270000) 135000 135000 270000 February Purchase(50% 315000) 157500 157500 315000 March Purchase(50% of 165000) 82500 82500 Total cash Disbursement for Purchase 228000 292500 240000 760500 *Account Payable of December -2010 for Purchase Tk93000 will be paid in January-2011 Hillyard Company Cash Disbursement for operating Expenses For the Quarter ended March 30,2011 January February March Quarter Salaries and Wages 27000 27000 27000 81000 Advertising 70000 70000 70000 210000 Shipping (5% of Sales) 20000 30000 15000 65000 Other Expenses(3% of Sales) 12000 18000 9000 39000 Total Cash Disbursement 129000 145000 121000 395000 Hillyard Company Cash Budget For the Quarter ended March 30,2011 January February March Quarter Beginning Cash Balance 48000 30000 30800 48000 Add: Cash Collection 304000 440000 540000 1284000 Total Cash Available 352000 470000 570800 1332000 Less: Cash Disbursement Inventory Purchase 228000 292500 240000 760500 Operating Expense 129000 145000 121000 395000 Equipment Purchase 0 1700 84500 86200 Cash Dividend 45000 0 0 45000 Total Cash Disbursement 402000 439200 445500 1286700 Excess/Deficiency -50000 30800 125300 45300 Financing: Borrowing 80000 80000 Repayments 0 -80000 -80000 Interest(80000*12%*3/12) 0 -2400 -2400 Total Financing 80000 0 -82400 -2400 Ending Cash Balance 30000 30800 42900 42900 Hillyard Company Budgeted Income Statement For the Quarter ended March 30,2011 Sales 1300000 Less: Cost of Goods Sold 780000 Gross Margin 520000 Less: Operating Expenses 395000 Depreciation 42000 Operating Income 83000 Less: Interest Expense 2400 Net Income 80600 Hillyard Company Budgeted Balance Sheet For the Quarter ended March 30,2011 Current Assets Cash 42900 Account Receivable(80% of 300000) 240000 Inventory 30000 Total Current Assets 312900 Building and Equipment 414200 (Beginning 370000+New purchase 86200-Depreciation 42000) Total Assets 727100 Liabilities and Equity Account Payable(50% of 165000) 82500 Equity: Common Stock 500000 Retained Earnings: Beginning 109000 add: Net Income 80600 Total 189600 Less: Cash Dividend 45000 144600 Total Liability and Equity 727100

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.