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:

1. As of December 31 (the end of the prior quarter), the companys general ledger showed the following account balances:

		Debits	Credits
	Cash	$ 48,000
	Accounts Receivable	224,000
	Inventory	60,000
	Buildings and Equipment (net)	370,000
	Accounts Payable		$ 93,000
	Capital Stock		500,000
	Retained Earnings		109,000
		$ 702,000	$ 702,000

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

December (actual)	$ 280,000
January	400,000
February	600,000
March	300,000
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 companys gross profit rate is 40% 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; depreciation, $14,000 per month; other expense, 3% of sales.
6. At the end of each month, inventory is to be on hand equal to 25% of the following months sales needs, stated at cost.
7. One-half of a months inventory purchases is paid for in the month of purchase; the other half is paid for in the following month.
8. During February, the company will purchase a new copy machine for $1,700 cas 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. The company must maintain a minimum cash balance of $30,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 data above, complete the following statements and schedules for the first quarter:

1. Schedule of expected cash collections:

2. a. Inventory purchases budget:

b. Schedule of cash disbursements for purchases:

3. Schedule of cash disbursements for expenses:
4. Cash budget:
5. Prepare an income statement for the quarter ending March 31.
6. Prepare a balance sheet as of March 31.

Note: Always look at the description of the cell below the tool bar before proceeding. I have put some formulas in already so that when you enter other numbers or formulas where another cell has #Value!, a dash, or formula you will see a number magically appear because I have already input information.

1 Sales Budget December January 280,000 400,000 February 600,000 March 300,000 Quarter 1,300,000 - Sales April 200,000 2 Schedule of expected cash collections: January February March Quarter 3 Cash sales 80,000 120,000 60,000 260,000 4 Credit sales Formula Formula Formula Formula 5 Total cash collections #VALUE! #VALUE! #VALUE! #VALUE! 0 3 Schedule of purchases: 0 January Formula February Formula March Formula Quarter #VALUE! Budgeted cost of goods 1 sold 2 Add desired ending 3 inventory 4 Total needs 5 Less beginning inventory 6 Required purchases Formula #VALUE! 60,000 #VALUE! Formula Formula #VALUE! Formula Formula Formula #VALUE! Formula Formula Formula Formula Formula b. Disbursements for purchases 7 o February March January Amount Formula Formula Formula 1 December purchases 2 January purchases 3 February purchases 4 March purchases 5 Total cash 6 disbursements for 7 purchases Quarter Formula #VALUE! #VALUE! Formula Formula Formula #VALUE! #VALUE! #VALUE! #VALUE! 4 for operating expenses: 2 Salaries and wages 3 Advertising 4 Shipping 5 Other expenses 6 Total cash disbursements 7 for operating expenses January 27.000 70,000 20,000 12.000 129,000 February 27,000 70,000 30,000 18,000 145,000 March 27,000 70,000 15,000 9,000 121,000 Quarter 81,000 210,000 65,000 39,000 395,000 5 Cash budget: January Amount #VALUE! #VALUE! February March Formula Formula #VALUE! Formula #VALUE! Formula Quarter Formula Formula Formula #VALUE! 129,000 #VALUE! Formula 145,000 121,000 Formula 395,000 2 Cash balance, beginning 3 Add cash collections 4 Total cash available 5 Less disbursements: 6 Purchases of inventory 7 Operating expenses 3 Purchases of 9 equipment 0 Cash dividends* 1 Total disbursements 2 Excess (deficiency) of 3 cash 4 Financing 5 Borrowing* Sheet1 Sheet2 ? #VALUE! #VALUE! 121,000 395,000 #VALUE! #VALUE! Formula Formula ? ? ? Sheet3 Formula 62 Excess (deficiency) of 63 cash #VALUE! #VALUE! Formula 64 Financing 65 Borrowing* 66 Repayments* 67 Interest 68 Total effect of financing Formula ? Formula 69 Cash balance, ending Formula Formula Formula 70 *Input appropriate formulas or given amounts Formula Formula 11 6 Income statement: 74 HILLYARD COMPANY 75 Income Statement 76 For the Quarter Ended March 31 77 Formula Formula Formula #VALUE! Formula #VALUE! Formula 78 Sales 79 Less cost of goods sold: 80 Beginning inventory (given) 81 Add purchases (Part 3) 82 Goods available for sale 83 Ending inventory (Part 3) 84 Gross margin 85 Less operating expenses 86 Salaries and wages (Part 4) 87 Advertising (Part 4) 88 Shipping (Part 4) 89 Depreciation 90 Other expenses (Part 4) 91 Net operating income 92 Less interest expense (Part 5) 93 Net income 81,000 210,000 65,000 Fomula 39,000 Formula Formula Formula Formula 94 7 Balance sheet: 99 100 HILLYARD COMPANY 101 Balance Sheet 102 31-Mar 103 104 Assets 105 Current assets: 106 Cash 107 Accounts receivable 108 Inventory 109 Total current assets 110 Buildings and equipment, 111 net Formula Formula Formula #VALUE! Formula 112 #VALUE! 113 Total assets 114 115 Liabilities and Equity 116 Current liabilities: 117 Accounts payable 118 Stockholders' equity: 119 Capital stock Sheet1 Sheet2 Formula 500,000 Sheet3 Formula Formula #VALUE! Formula #VALUE! Formula 2A B C 79 Less cost of goods sold: 80 Beginning inventory (given) 81 Add purchases (Part 3) 82 Goods available for sale 83 Ending inventory (Part 3) 84 Gross margin 85 Less operating expenses 86 Salaries and wages (Part 4) 87 Advertising (Part 4) 88 Shipping (Part 4) 89 Depreciation 90 Other expenses (Part 4) 91 Net operating income 92 Less interest expense (Part 5) 93 Net income 81,000 210,000 65,000 Fomula 39,000 Formula Formula Formula Formula 7 Balance sheet: 100 HILLYARD COMPANY 101 Balance Sheet 102 31-Mar 103 104 Assets 105 Current assets: 106 Cash 107 Accounts receivable 108 Inventory 109 Total current assets 110 Buildings and equipment, 111 net 112 113 Total assets Formula Formula Formula #VALUE! Formula #VALUE! 114 Formula 115 Liabilities and Equity 116 Current liabilities: 117 Accounts payable 118 Stockholders' equity: 119 Capital stock 500,000 120 Retained earnings* Formula 121 Total liabilities and equity 122 Retained earnings, beginning Add net income Total 126 Deduct cash dividends 127 Retained earnings, ending Formula Formula 123 124 109,000 Formula #VALUE! Formula Formula 128 130 131 133 134 136 137 138 139 Sheet1 Sheet2 Sheet3