ABC Company, an office supplies specialty store, prepares its master budget on a
ID: 2583530 • Letter: A
Question
ABC 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. 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 195,000 Inventory 45,600 Buildings and equipment (net) 350,000 Acccounts payable $79,800 Common stock 450,000 Retained earnings 108,800 $638,600 $638,600 b. Actual sales for December and budgeted sales for the next four months are as follows: December (actual) January February March April $260,000 $380,000 $410,000 $280,000 $210,000 c. Sales are collected as follows: 25% collected in cash at the time of the sale 75% on credit and collected in the month following sale The accounts receivable at December 31 are a result of December credit sales. d. The company's gross margin as a percent of sales is 40% In other words, cost of goods sold is 60% of sales. e. Monthly expenses are budgeted as follows: Salaries and wages $35,000 per month Advertising $50,000 per month Shipping 4% of sales Other expenses 3% of sales Depreciation, including depreciation on new assets acquired during the quarter, will be $42,000 for the quarter. All selling and administrative expenses, except depreciation, are paid in cash in the month they are incurred. f. Each month's ending inventory should equal 20% of the following month's cost of goods sold g. Inventory purchases are paid for as follows: 50% in the month of the purchase with the remaining balance paid in the following month. h. During February, the company will purchase a new copy machine for $2,100 cash. During March, the company will purchase other equipment for $76,000 cash. i. Cash dividends paid in January will be $35,000 j. Management wants to maintain a current cash balance of $20,000 The company has an agreement with the local bank that allows the company to borrow in increments of $1,000 at the beginning of the month. The monthly interest rate on the loan is 1% For simplicity, assume that the interest is not compounded. The company would, as far as it is able, repay the loan plus any accumulated interest at the end of the quarter. Required: Using the data above, complete the following statements and schedules for the first quarter using the formats given below. To receive full credit, all amounts below must be entered as formulas or cell references, except for the financing section of the cash budget. Submissions using formulas or cell references for the financing section may receive up to 3 points extra credit, dependent upon the degree to which the formulas can be used for any and all possible scenarios. (Hint: IF statements.) 1. Schedule of expected cash collections January February March Quarter Cash sales Credit sales check figure: Total cash collections Total cash collections = $1,055,000 2a. Merchandise purchases budget January February March Quarter Budgeted cost of goods sold Desired ending inventory Total needs Beginning inventory Required purchases 2b. Schedule of expected cash disbursements for merchandise purchases January February March Quarter December purchases January purchases check figure: February purchases Total cash disbursements March purchases for purchases = Total cash disbursements for purchases $621,600 3. Cash budget January February March Quarter Beginning cash balance Cash collections Total cash available Cash disbursements: Inventory purchases Selling & admin. expenses Equipment purchases Cash dividends Total cash disbursements Excess (deficiency) of cash Financing: Borrowing Repayment of principal Interest Total financing check figure: Ending cash balance Mar. 31 cash balance = $37,650 4. Prepare an absorption costing income statement for the quarter ended March 31 in the space below. check figure: Net income = $55,350 5. Prepare a balance sheet as of March 31 in the space below. check figure: A = L + SE = $658,950 ABC 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. 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 195,000 Inventory 45,600 Buildings and equipment (net) 350,000 Acccounts payable $79,800 Common stock 450,000 Retained earnings 108,800 $638,600 $638,600 b. Actual sales for December and budgeted sales for the next four months are as follows: December (actual) January February March April $260,000 $380,000 $410,000 $280,000 $210,000 c. Sales are collected as follows: 25% collected in cash at the time of the sale 75% on credit and collected in the month following sale The accounts receivable at December 31 are a result of December credit sales. d. The company's gross margin as a percent of sales is 40% In other words, cost of goods sold is 60% of sales. e. Monthly expenses are budgeted as follows: Salaries and wages $35,000 per month Advertising $50,000 per month Shipping 4% of sales Other expenses 3% of sales Depreciation, including depreciation on new assets acquired during the quarter, will be $42,000 for the quarter. All selling and administrative expenses, except depreciation, are paid in cash in the month they are incurred. f. Each month's ending inventory should equal 20% of the following month's cost of goods sold g. Inventory purchases are paid for as follows: 50% in the month of the purchase with the remaining balance paid in the following month. h. During February, the company will purchase a new copy machine for $2,100 cash. During March, the company will purchase other equipment for $76,000 cash. i. Cash dividends paid in January will be $35,000 j. Management wants to maintain a current cash balance of $20,000 The company has an agreement with the local bank that allows the company to borrow in increments of $1,000 at the beginning of the month. The monthly interest rate on the loan is 1% For simplicity, assume that the interest is not compounded. The company would, as far as it is able, repay the loan plus any accumulated interest at the end of the quarter. Required: Using the data above, complete the following statements and schedules for the first quarter using the formats given below. To receive full credit, all amounts below must be entered as formulas or cell references, except for the financing section of the cash budget. Submissions using formulas or cell references for the financing section may receive up to 3 points extra credit, dependent upon the degree to which the formulas can be used for any and all possible scenarios. (Hint: IF statements.) 1. Schedule of expected cash collections January February March Quarter Cash sales Credit sales check figure: Total cash collections Total cash collections = $1,055,000 2a. Merchandise purchases budget January February March Quarter Budgeted cost of goods sold Desired ending inventory Total needs Beginning inventory Required purchases 2b. Schedule of expected cash disbursements for merchandise purchases January February March Quarter December purchases January purchases check figure: February purchases Total cash disbursements March purchases for purchases = Total cash disbursements for purchases $621,600 3. Cash budget January February March Quarter Beginning cash balance Cash collections Total cash available Cash disbursements: Inventory purchases Selling & admin. expenses Equipment purchases Cash dividends Total cash disbursements Excess (deficiency) of cash Financing: Borrowing Repayment of principal Interest Total financing check figure: Ending cash balance Mar. 31 cash balance = $37,650 4. Prepare an absorption costing income statement for the quarter ended March 31 in the space below. check figure: Net income = $55,350 5. Prepare a balance sheet as of March 31 in the space below. check figure: A = L + SE = $658,950Explanation / Answer
Answer. Sales Budget Jan Feb Mar Total Total Sales in $ 380,000 410,000 280,000 1,070,000 Cash Sales - 25% 95,000 102,500 70,000 267,500 Credit Sales - 75% 285,000 307,500 210,000 802,500 Answer 1. Schedule of Expected Cash Collections from Sales Jan Feb Mar Total Cash Sales - 25% 95,000 102,500 70,000 267,500 Credit Sales 195,000 285,000 307,500 787,500 Total cash Collections 290,000 387,500 377,500 1,055,000 Answer 2a. Merchandise Production Budget Jan Feb Mar Total Budgeted Cost of Goods Sold 228,000 246,000 168,000 642,000 Add: Desired Ending Inventory 49,200 33,600 25,200 25,200 Total Needs 277,200 279,600 193,200 750,000 Less: Beginning Inventory (45,600) (49,200) (33,600) (45,600) Required Purchases 231,600 230,400 159,600 621,600 Answer 2b. Schedule of Cash payments to Suppliers Jan Feb Mar Total Cash Payment Dec Purchases 79,800 79,800 Jan Purchases 115,800 115,800 231,600 Feb Purchases 115,200 115,200 230,400 Mar Purchases 79,800 79,800 Total Cash Payment to Suppliers 195,600 231,000 195,000 621,600 Answer 3. Cash budget Jan Feb Mar Total Opening cash Balance 48,000 20,800 61,500 48,000 Add: receipts Collection from Customers 290,000 387,500 377,500 1,055,000 Total Cash available 338,000 408,300 439,000 1,103,000 Less: Cash Disbursements Inventory Purchases 195,600 231,000 195,000 621,600 Selling & Admn. Exp. 111,600 113,700 104,600 329,900 Equipment Purchases - 2,100 76,000 78,100 Cash Dividends 35,000 - - 35,000 Total Cash Disbursement 342,200 346,800 375,600 1,064,600 Excess (deficiency) of Cash (4,200) 61,500 63,400 38,400 Financing: Add: Borrowings 25,000 - - 25,000 Less: Repayment of Principal - - (25,000) (25,000) Less: Payment of interet - - (750) (750) Total Financing 25,000 - (25,750) (750) Ending Cash Balance 20,800 61,500 37,650 37,650 Payment of Selling & Admn. Exp. Jan Feb Mar Total Salaries & Wages 35,000 35,000 35,000 105,000 Advertising 50,000 50,000 50,000 150,000 Shipping - 4% of Sales 15,200 16,400 11,200 42,800 Other Expenses - 3% of Sales 11,400 12,300 8,400 32,100 Cash Paid - Selling & Admn. Exp. 111,600 113,700 104,600 329,900 Answer 4. Income Statement For the Quarter ended Mar 31 Sales 1,070,000 Cost of Goods Sold - 60% of Sales 642,000 Gross Margin 428,000 Selling & Admn Expenses Salaries & Wages 105,000 Advertising 150,000 Shipping - 4% of Sales 42,800 Other Expenses - 3% of Sales 32,100 Depreciation 42,000 371,900 Net Operating Income 56,100 Less: Interest Expenses 750 Net Income 55,350 Answer 5. Balance Sheet As on Quarter Ended March 31 Assets Cash 37,650 Accounts Receivable 210,000 Inventory 25,200 272,850 Property, Plant & Equipment Building & Equipment 350,000 Add: Purchases 78,100 428,100 Less: Depreciation (42,000) 386,100 Total Assets 658,950 Liabilities & Stockholders' Equity Liabilities Current Liabilities Accounts Payable 79,800 Other Liabilities - Total Liabilities 79,800 Stockholders' Equity Common Stock 450,000 Retained Earnings 129,150 Total Stockholders' Equity 579,150 Total Laibilities & Stockholders' Equity 658,950