A B C E F G H 1 Sales Budget December January February March Quarter April Sales 280,000 400,000 600,000 300,000 1,300,000 200,000 10 2 Schedule of expected cash collections: 12 January February March Quarter 13 Cash sales 80,000 120,000 60,000 260,000 14 Credit sales Formula Formula Formula Formula 15 Total cash collections #VALUE! #VALUE! #VALUE #VALUE! 16 17 3 Schedule of purchases: 18 19 January February March Quarter 20 Budgeted cost of goods Formula Formula Formula #VALUE! 21 sold Add desired ending 23 inventory Formula Formula Formula Formula 24 Total needs #VALUE! #VALUE! Formula Formula 25 Less beginning inventory 60,000 Formula Formula Formula 26 Required purchases #VALUE #VALUE! Formula Formula 27 28 b. Disbursements for purchases 29 30 January February March Quarter 31 December purchases Amount Formula 32 January purchases Formula Formula #VALUE! 33 February purchases Formula Formula #VALUE! 34 March purchases Formula Formula 35 Total cash 36 disbursements for 37 purchases #VALUE! #VALUE! #VALUE! #VALUE! 38 4 for operating expenses: 40 41 January February March Quarter 42 Salaries and wages 27,000 27,000 27,000 81,000 13 Advertising 70,000 70,000 70,000 210,000 44 Shipping 20,000 30,000 15,000 65,000 45 Other expenses 12,000 18,000 9,000 39,000 46 Total cash disbursements 129,000 145,000 121,000 395,000 47 for operating expensesA B C E F G 48 49 50 5 Cash budget: 51 January February March Quarter 2 Cash balance, beginning Amount Formula Formula Formula 53 Add cash collections #VALUE! #VALUE! Formula Formula 54 Total cash available #VALUE! #VALUE! Formula Formula 55 Less disbursements: 56 Purchases of inventory #VALUE! #VALUE! Formula Formula 57 Operating expenses 129,000 145,000 121,000 395,000 58 Purchases of 59 equipment* ? ? ? 60 Cash dividends* 2 ? 61 Total disbursements #VALUE! #VALUE! 121,000 395,000 62 Excess (deficiency) of 63 cash #VALUE! #VALUE! Formula Formula 54 Financing: 65 Borrowing* 66 Repayments* 67 Interest* Total effect of financing Formula Formula Formula 69 Cash balance, ending Formula Formula Formula Formula 70 *Input appropriate formulas or given amounts 71 72 6 Income statement: 73 74 HILLYARD COMPANY 75 Income Statement 76 For the Quarter Ended March 31 77 78 Sales Formula 79 Less cost of goods sold: 80 Beginning inventory (given) Formula 81 Add purchases (Part 3) Formula 82 Goods available for sale #VALUE! 83 Ending inventory (Part 3) Formula #VALUE! 84 Gross margin Formula 85 Less operating expenses: 86 Salaries and wages (Part 4) 81,000 87 Advertising (Part 4) 210,000 88 Shipping (Part 4) 65,000 89 Depreciation Fomula Other expenses (Part 4) 39,000 Formula 31 Net operating income Formula 92 Less interest expense (Part 5) Formula 93 Net income Formula 9496 97 98 7 Balance sheet: 99 100 HILLYARD COMPANY 101 Balance Sheet 102 31-Mar 103 104 Assets 105 Current assets: 106 Cash Formula 107 Accounts receivable Formula 108 Inventory Formula 109 Total current assets #VALUE! 110 Buildings and equipment, 171 net Formula 112 113 Total assets #VALUE! 114 115 Liabilities and Equity 116 Current liabilities: 117 Accounts payable Formula 118 Stockholders' equity: 119 Capital stock 500,000 120 Retained earnings* Formula Formula 121 Total liabilities and equity Formula 122 123 * Retained earnings, beginning 109,000 124 Add net income Formula 125 Total #VALUE! 126 Deduct cash dividends Formula 127 Retained earnings, ending Formula 128Managerial Accounting Master Budget Preparation Chapter 9 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 $ 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 b. 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 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. The company's gross profit rate is 40% of sales. e . 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. 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 is paid for in the month of purchase; the other half is paid for in the following month. h. 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. During January, the company will declare and pay $45,000 in cash dividends. 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 inmultiples 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: 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