Review Problem: Completing a Master Budget for the 2^ndQuarter, Pages 355-359 (Chapter 9: Budgeting))of your Text book:Managerial Accountingby Garrison, Libby and Webb, Eleventh Canadian Edition.

Required:

Create the following components of the Master Budget for ROCKET COMPANY in a Microsoft Excel Spreadsheet for the 2^ndQuarter. To get full marksall Excel Cells have to be properly linked using Formulas and proper statement format has to be followed.

Prepare ONLY the following budgets.You do not haveto do thecollection schedules,cash budgetor thebudgeted balanced sheet.

Link the excel formulas so that question 5 can be answered without recreating the entire budget.

1. SALES BUDGET-Monthly and in total
2. MERCHANDISE PURCHASE BUDGET-monthly and in total
3. SELLING AND ADMINISTRATION BUDGET-monthly and in total
4. INCOME STATEMENT for the Quarter ended June 30th
5. What would be the operating income if the gross margin is reduced from 40% to 35%? What is the ending merchandise inventory?

follow- lay $3,000. 3. Chapter 9 Budgeting Schedule of expected cash disbursements for selling and administrative expenses: any has an agree- t the beginning of Salaries and wages .. . April May th. The company June Shipping . . ... . . . ... . $ 7,500 Total terest each month Advertising . .. .. . . . . . . . 4,200 Other expenses . . . . . .... 6,000 Total cash disbursements for selling and . . . . . .. . . ... 2,800 quarter: administrative expenses . .. ... .. . . . . . . . . .. $20,500 4. Cash budget: une April May July June Cash balance, beginning. ....... Total Add cash collections. ... . . . $ 9,000 62,000 Total cash available. . Less cash disbursements: . . . ... 71,000 For inventory purchases ....... . . . . . . ... . 40,650 For selling and administrative expenses . .... . 20,500 For equipment purchases . . . ...... 11,500 For dividends ...... . . ...... Total cash disbursements . . . . . . 72,650 Excess (deficiency) of cash ... . . . . $ (1,650) Financing Total Borrowings* . 9,747 Repayments . . 0 Interest * * (97) Total financing . .. . . . . . . . . 9,650 Cash balance, ending . .. $ 8,000 * April: ($1,650) + X - 0.01X = $8,000; X = $9,747 (rounded) * *April: $9,747 x 1% = $97 5. Prepare an absorption costing income statement for the quarter ending June 30, as shown in Schedule 9. 6. Prepare a balance sheet as at June 30. Total Solution to Review Problem 1. Sales budget: July March (actual) April May June $70,000 $85,000 $90,000 $50,000 $60,000 $18,000 $10,000 Total Sales $17,000 $12,000 $14,000 72,000 40,000 Cash (20%) . . .. . . . . . 68,000 48,000 56,000 Credit (80%) .. . . . . . . .... All credit sales are collected in the month following the sale. May June Total April $17,000 $18,000 $ 49,000 Total Schedule of Expected Cash Collections $14,000 68,000 172,000 Cash sales ....... .. . . . . . . . . .... . . .. . . .... 48,000 56,000 $86,000 $221,000 $18,300 Credit sales ...... . . . . . . . . . .. . . .. . . . .... $62,000 $73,000 44,700 . . . Total collections . . . ......REVIEW PROBLEM: COMPLETING A MASTER BUDGET The following data are for preparation of a second-quarter master budget for Rocket Company, a mer- chandising operation that prepares its master budget on a quarterly basis. a. At the end of the previous quarter (March 31), the organization's balance sheet showed the follow- ing account balances: Cash . . . Accounts receivable . .. $ 9,000 48,000 Inventory . . ... 12,600 Buildings and equipment (net) 214,100 Accounts payable . . $ 18,300 Common shares . .. 190,000 Retained earnings 75,400 $283,700 $283,700 b. Actual sales for March and budgeted sales for April through July are as follows: $60,000 March (actual) $70,000 April. . . $85,000 May $90,000 June $50,000 July ... Sales are 20% for cash and 80% on credit. All payments on credit sales are collected in the month C. following the sale. The accounts receivable at March 31 are a result of March credit sales. The company's gross margin is 40% of sales. (In other words, the cost of goods sold is 60% of d. sales.) Monthly expenses are budgeted as follows: salaries and wages, $7,500 per month; shipping, 6% of e. sales; advertising, $6,000 per month; other expenses, 4% of sales. Depreciation, including depre- ciation on new assets acquired during the quarter, will be $6,000 for the quarter.356 Chapter 9 Budgeting Each month's ending inventory should equal 30% of the following month's cost of goods sold Sched Hall of a month's inventory purchases are paid for in the month of purchase and half in the follow 3. ing month. h. Equipment purchases during the quarter will be as follows: April $11,500, and May $3,000. Dividends totalling $3,500 will be declared and paid in June. Salarie Management wants to maintain a minimum cash balance of $8,000. The company has an agree Shippi tient with a local bank that allows the company to borrow necessary amounts at the beginning of Advert each month, up to a total loan balance of $20,000. The interest rate is 1% per month. The company Other will, as far as it is able, repay the loan at the end of each month and must pay interest each monk Total on the total loan balance outstanding for that month. adr Required: Using the data above, complete the following statements and schedules for the second quarter: Cas 4. 1. Sales budget and a schedule of expected cash collections: Cash March (actual) April May June July Add Tot Sales Budget Total Sales ..... $60,000 $70,000 Les Cash (20%) . ... $12,000 $14,000 . . . . . Credit (80%) . ........ . 56,000 . . . . . 48,000 All credit sales are collected in the month following the sale. Fir April May June Total Schedule of Expected Cash Collections Cash sales . . $14,000 Credit sales . .... 48,000 Total collections . . ........ . . . . $62,000 2. a. Merchandise purchases budget: 5 . April May .June Total 6 . Budgeted cost of goods sold. . ... $42,000* $51,000 Add desired ending inventory. 15,300 S Total needs . 57,300 Less beginning inventory . . .... . 12,600 1 . Required purchases. . . . . . . $ 44,700 *$70,000 sales x 60% = $42,000 $51,000 x 30% = $15,300 b. Schedule of expected cash disbursements for merchandise purchases: April May June Total For March purchases. . ...... For April purchases $ 18,300 $18.300 22,350 44.700 For May purchases . ... $22,350 For June purchases . . . . . . . . . . Total cash disbursements for purchases . ....... $40,650358 Chapter 9 Budgeting 2. a. Merchandise purchases budget: Income sta 5. April May June Total Budgeted cost of goods sold. ....... . . . . . . . . . $42,000 $51,000 Add desired ending inventory*. . . . . . . . . . . . .. 15,300 16,200 $54,000 $147,000 9,000 . . . . . . . . .... 57,300 67,200 Total needs . .. ... 63,000 9,000 Less beginning inventory . ... . . . . . . . . . . . .. 12,600 15,300 16,200 156,000 Sales . ... $44,700 $51,900 $46,800 12,600 Required purchases. ... . . . . . . . . . .. Cost of goo $143,400 Beginnin *At April 30: $51,000 x 30% = $15,300 Add pur At June 30: $50,000 July sales X 60% x 30% = $9,000 Goods a Ending b. Schedule of cash disbursements for purchases: Gross mar Selling an April May June Salarie Total Shippir For March purchases. ... $18,300 Adverti For April purchases . 22,350 $22,350 $ 18,300 14, 700 Deprec For May purchases . 25,950 $25,950 51,900 Other For June purchases 23,400 23,400 Operating Total cash disbursements . $40,650 $48,300 $49,350 $138,300 Less inte Net inco 3. Schedule of cash disbursements for selling and administrative expenses: Note: C Sales of April May June Total Salaries and wages . ..... $ 7,500 $ 7,500 $ 7,500 $22,500 Shipping (6% of sales) ..... 4,200 5,100 5,400 14,700 5,000 6,000 6,000 18.000 6. Balan Advertising . ...... .. . Other expenses (4% of sales) ... 2,800 3,400 3,600 9.800 Total cash disbursements for selling and administrative expenses . . . . . . . . . $20,500 $22,000 $22,500 $65,000 4. Cash budget: Curren April May June Total Cas Acc Cash balance, beginning. . . .. . $ 9,000 $ 8,000 $ 8,000 $ 9,000 Inv Add cash collections . . ... . 62,000 73,000 86,000 221,000 Total Total cash available. . . . . . . 71,000 81,000 94,000 230,000 Build Less disbursements: Total For inventory purchases . 40,650 48,300 49,350 138,300 For selling and administrative expenses . . .. .. 20,500 22,000 22,500 65.000 For equipment purchases 14.500 11,500 3,000 0 For dividends ..... . . . 3,500 3.500 . . . . Curre Total disbursements . .... 221,300 . . . . 72,650 73,300 75,350 8, 700 Ac Excess (deficiency) of cash (1,650) 7,700 18,650 Shar Financing: Borrowings* . 10,148 . . . . . 9,747 401 R Repayments . ... 0 (10,148) (10,148) 0 (299) Tota Interest* * (97) (101) (101) (299 * Re Total financing .. 9,650 300 (10,249 $ 8,401 Add Cash balance, ending ..... $ 8,000 $ 8,000 $ 8,401 Tot Les * April: ($1,650) + X - 0.01X = $8,000; X = $9,747 (rounded) May: $7,700 + X - 0.01X - $97 (interest on April loan) = $8,000; X = $401 (rounded) Ret * *April: $9,747 x 1% = $97; May and June: ($9,747 + $401) x 1% = $101Chapter 9 Budgeting 359 ROCKET COMPANY Income Statement For the Quarter Ended June 30 Sales . . . . . . . . . . . . .. ..... Cost of goods sold (CGS): Beginning inventory (given). . . . . Add purchases (Part 2) .... $245,000 Goods available for sale . . . . . $ 12,600 Ending inventory (Part 2) . . .. 143,400 156,000 Gross margin . . .. . . . . . . . . Selling and administrative expenses: 9,000 . . . .. 147,000 Salaries and wages (Part 3) . ... 98,000 Shipping (Part 3) ... . . . 22,500 Advertising (Part 3) ........ 14,700 Depreciation . . .......... 18,000 Other expenses (Part 3) . . . 6,000 Operating income . . ..... . . . . . 9,800 71,000 Less interest expense (Part 4) . 27,000 Net income . ... . 299 Note: CGS can also be computed as follows: $ 26,701 Sales of $245,000 x 60% = $147,000 6. Balance sheet: ROCKET COMPANY Balance Sheet June 30 Assets Current assets: $ 8,401 Cash (Part 4) . .. . . . .. .......... 72,000 Accounts receivable (80% x $90,000) . . . 9.000 Inventory ( Part 2) .. .. . .. 89,401 Total current assets ............ 222,600 Buildings and equipment, net ($214,100 + $14,500 - $6,000) . ...... . .. ............... $ 312,001 Total assets . Liabilities and Shareholders' Equity $ 23,400 Current liabilities: Accounts payable (Part 2: 50% x $46,800) ............... Shareholders' equity: $190,000 . . . . . .. ..... 288,601 Common shares 98,601 . ........... $ 312,001 Retained earnings*. Total liabilities and shareholders' equity . .... .............. $ 75,400 26,701 Retained earnings, beginning 102, 101 Add net income. . ..... .. . . . . . . . . 3,500 Total . . . . . . . . . . . . .. .. . . . ....... . . . . . . $ 98,601 Less dividends . . . . . . . .. . .. ...... Retained earnings, ending