Masher Budget Excel Assigmnml; Hillard Company, an off-ice supplies specialty store, prepares its master budget on basis. The following dais havebeen assembled to assist in preparation ofthemaster budget for the firstquaxter: a. As of December 31 (the end of the prior quarter), the company's General Ledger shows: Debits Cash $ 50,000 Accounts Receivable 259,000 Inventory 60,000 Buildings & Equipment (Net of Depreciation) 300,000 Accounts Payable $ 122,000 Capital Stock 430,000 Retained Earnings 117,000 $ 669,000 $ 669,000 Actual Sales for December and budgeted sales for the next four months are as follows: b. December (Actual) $ 370,000 January 600,000 February 570,000 March 295,000 April 290,000 c. Sales are 30% for cash and 70% on credit. All payments on credit sales are collected in the month sale. The accounts receivable at December 31 are a result of December credit sal ES. d. The company's gross prot rate is 35% of sales. F3 quarterly : following the Monthly expenses are budgeted as follows: salaries and wages, $75,000 per month; advertising, 3; month: shipping, 5.5% of sales; depreciation, $15,000 per month; other expenses, 2.5% of sales. . At the end of each month, inventory is to be on hand equal to 20% of the following month's salesl at cost. . One-half of a month's inventory purchases is paid for in the month of purchase; the other half is p.' following month. During February, the company will purchase a new copy machine for $2,000 cash. During March, 0 equipment will be purchased for cash at a cost of $50,000. Depreciation expense on assets purcha rst six months of the year half will begin being depreciated at the second half of the year. . During January, the company will declare and pay $50,000 in cash dividends. '. The company must maintain a minimum cash balance of $25,000. An open line of credit is availabl bank for any borrowing that may be needed during the quarter. All borrowing is done at the beginr month, and all repayments are made at the end of a month. Borrowings and repayments of princip multiples of $1,000. Interest is paid only at the time of oomplete repayment of total principal. Inte computed on funds utilized for each month. The annual interest rate is 12%. (Figure interest on wl e.g., 1x12, 2/12) If required, make necessary assumptions and state them in the answer document. 55,000 per needs, stated aid for in the nther sed during the e at a local iing of a :al must be in rest is nole months, Schedule of expected cash oollections: Quarter Quarter Quarter January February March Cash Sales $ 180,000 Credit Sales 259,000 Total Cash Collections $ 439,000 a. Inventory Purchases budget: January February March Budgeted Cost of Goods Sold $ 390,000 Add desired ending inventory 74,100 Total Needs 464,100 Less Beginning Inventory 60,000 Requlred Purchases $ 404,100 | For January sales: $500,000 sales X 65% cost ratio = $325,000 $409,500 X 20% =|$81,900 . Schedule of cash disbursemenls for purchase: | January February March December Purchases $ 122,000 January Purchases ($270,000) 202,050 202,050 a; 122,000 February Purchases 404,100 Total Cash Disbursements for purchase a; 324,050 March Purchases Schedule of cash disbursemenls for expenses: January February March Salaries and Wages $ 75,000 Advertising 55,000 Shipping 39,000 Other Expenses 15,000 _ Total Cash disbursements for operating expenses $ 184,000 Cash Budget: January February March Cash Balance, Beginning $ 50,000 Add Cash collections 439,000 Total Cash Available 489,000 Less Disbursemenls: Purchases of inventory 324,050 Operating Expenses 184,000 Purchases of equipment - Cash Dividends 50,000 Total Disbursements 558,050 Excess (deciency) of cash (69,050) available over disbursements Financing: Borrowing $95,000 Repayments Interest Total Financing $95,000 Ending Cash Balance $25,950 Quarter Quarter 5 Prepare an income statement for the quarter ending March 31 as shown in this chapter. {Do an Inc :ome statement for Jan., Feb., March, and full quarter) 6 Prepare a balance sheet as of March 31