Please use the data given in the word doc to fill out the excel sheet. Also, provide the numbers used in the spots that say FORMULA on the excel sheet so that I know how you calculated the answer. I will upvote, thanks
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: c. d. December (actual) $ 280,000 January 400,000 February 600,000 March 300,000 April 200,000 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. 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. 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(i.e. cost of goods sold). 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. 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 f. g h. i. 1. 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: Prepare a master budget. .................... ...................SEERRRRRRRRR Chapter 9 Template NOTE ............................................ Managerial Accounting Formula = You must enter a formula Professor Bradley #VALUE! A formula already exists and will autofill as you put in the rest of the information Student Name 1 Sales Budget December January February March Quarter April Sales $ 280,000$ 400,000 $ 600,000 $ 300,000 $ 1,300,000 $ 200,000 Schedule of expected cash collections: January February March Quarter April Cash sales $ 80.000 $ 120,000 $ 60,000 $ 260,000 $ 40,000 Credit sales Formula Formula Formula Formula Formula Total cash collections #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! 3 Schedule of purchases: December January February March Quarter April Budgeted cost of goods sold $ 168,000 Formula Formula Formula #VALUE! $ 120,000 Add desired ending inventory. #VALUE! Formula Formula Formula Formula $ Total needs #VALUE! #VALUE! #VALUE! Formula Formula $ 120,000 Less beginning inventory $ 42,000 $ 60,000 Formula Formula Formula Required purchases #VALUE! #VALUE! #VALUE! Formula Formula b. Disbursements for purchases January February March Quarter Amount Amount December purchases Formula Formula January purchases Formula Formula Formula February purchases Formula Formula Formula March purchases Formula Formula Total cash disbursements for purchases #VALUE! #VALUE! #VALUE! #VALUE! 4 Schedule of cash disbursements for operating expenses: January February March Quarter Salaries and wages 27,000 $ 27,000 $ 27,000 $ 81.000 Advertising 70,000 $ 70,000 $ 70,000 $ 210,000 SERER 4 .......... ******** Salaries and wages Advertising Shipping Other expenses Total cash disbursements for operating expenses 5 Cash budget: Schedule of cash disbursements for operating expenses: January February March Quarter $ 27,000 $ 27,000 $ 27.000 $ 81,000 $ 70.000 70,000 $ 70,000 210,000 $ 20,000 $ 30,000 $ 15,000 $ 65,000 $ 12,000 $ 18,000 $ 9,000 $ 39,000 $ 129,000 $ 145,000 $ 121,000 $ 395,000 Lasn balance, beginning January Amount Formula #VALUE! February Formula #VALUE! #VALUE! March Formula Formula Formula Quarter Formula Formula Formula Formula #VALUE! Formula Formula $ 129,000 $ 145,000 $ 121,000 $ 395,000 Cash Add cash collections Total cash available Less disbursements: Purchases of inventory Operating expenses Purchases of Equipment* Cash dividends* Total disbursements Excess (deficiency) of cash Financing: Borrowing* Repayments* Interest* Total effect of financing. Cash balance, ending. |*Input appropriate formulas or given amounts Formula #VALUE! Formula #VALUE! $ 121,000 1 $ 395,000 Formula Formula Formula Formula Formula Formula Formula Formula Formula Formula 6 Income statement: HILLYARD COMPANY Income Statement For the Quarter Ended March 31 HTCOne lalelllcll ***********HERESE.***********!!!!! Formula ***** Formula Formula #VALUE! Formula ** .............. #VALUE! Formula For the Quarter Ended March 31 Sales Less cost of goods sol : Beginning inventory (given). Add purchases (Part 3) Goods available for sale Ending inventory (Part 3) Gross margin Less operating expenses: Salaries and wages (Part 4). Advertising (Part 4) Shipping (Part 4) Depreciation Other expenses (Part 4). Net operating income Less interest expense (Part 5). Net income $ 81,000 $ 210,000 $ 65,000 Fomula $ 39,000 Formula Formula Formula Formula ...................1111111....... Balance sheet: HILLYARD COMPANY Balance Sheet 31-Mar Assets Current assets: Cash Accounts receivable Inventory.. Total current assets Buildings and equipment, net Total assets Liabilities and Equity Formula Formula Formula #VALUE! Formula #VALUE! current liabilities. O.UUU Jaramics anu ................wagen Advertising (Part 4). Shipping (Part 4). Depreciation Other expenses (Part 4). Net operating income Less interest expense (Part 5). Net income $ 210,000 65,000 Fomula $ 39,000 Formula Formula Formula Formula Balance sheet: Formula Formula Formula #VALUE! ormula #VALUE! HILLYARD COMPANY Balance Sheet 31-Mar Assets Current assets: Cash Accounts receivable Inventory.. Total current assets Buildings and equipment, net Total assets Liabilities and Equity Current liabilities: Accounts payable Stockholders' equity: Capital stock Retained earnings* Total liabilities and equity Retained earnings, beginning Add net income Total Deduct cash dividends Retained earnings, ending Formula $ 500,000 Formula Formula Formula $ 109,000 Formula #VALUE! #REF! Formula
