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:
Cash $48000 (debit)
Accounts receivable $224000 (debit)
Inventory $60000 (debit)
Buildings and equipment net $370000 (debit)
Accounts payable $93000 (credit)
Capital stock $500000 (credit)
Retained earnings $109000 (credit)
Required:
Schedule of Expected Cash Collections
January
February
March
Quarter
Cash sales
$80000
Credit sales
$224000
Total Collections
$304000
Using the data above complete the following statements and schedules for the first quarter. Submit your responses in an Excel spreadsheet:
Merchandise Purchases Budget
January
February
March
Quarter
Budgeted Cost of Goods Sold
$240000*
$360000
Add desired ending inventory
$90000**
Total needs
$330000
Less beginning inventory
$60000
Required purchases
$270000
*$400000 sales x 60% cost ratio = $240000
** $360000 x 25% = $90000
Schedule of Expected Cash Disbursements-Merchandise Purchases
January
February
March
Quarter
December purchases
$93000
$93000
January purchases
$135000
$135000
$270000
February purchases
March purchases
Total disbursements
$228000
Schedule of Expected Cash Disbursements-Selling and Administrative Expenses
January
February
March
Quarter
Salaries and wages
$27000
Advertising
$70000
Shipping
$20000
Other expenses
$12000
Total disbursements
$129000
Cash Budget
January
February
March
Quarter
Cash balance beginning
$48000
Add cash collections
$304000
Total cash available
$352000
Less cash disbursements
For inventory
$228000
For selling and admin expenses
$129000
For purchase of equipment
——
For cash dividends
$45000
Total cash disbursements
$402000
Excess (deficiency) of cash
($50000)
Financing needed
Cash balance ending
Provide your answers in a clearly organized Excel spreadsheet. Provide your answers in a clearly organized Excel spreadsheet