For the Week 9 Budgeting All problem, why do you not Include September in the Sales Budget?

eLearning Question 1 EARRINGS UNLIMITED You have just been hired as a new management trainee by Earrings Unlimited, a distributor of earrings to various retail outlets located in shopping malls across the country. In the past the company has done very little in the way of budgeting and at certain times of the year has experienced a shortage of cash. Since you are well trained in budgeting, you have decided to prepare comprehensive budgets for the upcoming second quarter in order to show management the benefits that can be gained from integrated budgeting program. To this end, you have worked with accounting and other areas to gather the information assembled below: The company sells many styles of earrings, but all are sold for the same price-$l0 per pair. Actual sales of earrings for the last three months and budgeted sales for the next six months follow (in pairs of earrings): January (actual) February (actual) March (actual) April May June July August September $ $ $ $ $ $ $ $ $ 20,000 26,000 40,000 65,000 100,000 50,000 30,000 28,000 25,000 The concentration of sales before and during May is due to Mother's Day. Sufficient inventory should be on hand at the end of each month to supply 40% of the earrings sold in the following month. Suppliers are paid $4 for a pair of earrings. One-half of a month's purchases is paid for in the month of purchase; the other half is paid for in the following month. All sales are on credit, with no discount and payable within 15 days. The company has found, however, that only 20% of a month's sales are collected in the month of sale. An additional 70% is collected in the following month, and the remaining 10% is collected in the second month following sale. Bad debts have been negligible. Monthly operating expenses for the company are given below: Variable monthly expenses: Sales commissions (% of sales) Fixed monthly expenses: Advertising Rent Salaries Utilities Insurance (12 months paid in November) Depreciation 4% $ $ $ $ $ 200,000 18,000 106,000 7,000 3,000 $ 14,000 Insurance is paid on an annual basis, in November of each year. 1 The company plans to purchase $16,000 in new equipment during May and $40,000 in new equipment during June; both purchases will be for cash. The company declares dividends of $15,000 each quarter, payable in the first month of the following quarter. A listing of the company's ledger accounts as of March 31 is given below: Balance sheet at March 31: Assets Cash Accounts receivable Inventory Prepaid insurance Property and equipment (net) Total assets $74,000 346,000 104,000 21,000 950,000 $1,495,000 Liabilities and Stockholders' Equity Accounts payable Dividends payable Capital stock $100,000 15,000 800,000 580,000 Retained earnings Total liabilities and stockholders' equity $1,495,000 The company maintains a minimum cash balance of $50,000. All borrowing is done at the beginning of a month; any repayments are made at the end of a month. The company has an agreement with a bank that allows the company to borrow in increments of $1 ,000 at the beginning of each month. The interest rate on these loans is 1% per month and for simplicity we will assume that interest is not compounded. At the end of the quarter, the company would pay the bank all of the accumulated interest on the loan and as much of the loan as possible (in increments of $1,000), while still retaining at least $50,000 in cash. Required: Prepare a master budget for the three-month period ending June 30. Include the following detailed Budgets 1 a. A sales budget, by month and in total. b. A schedule of expected cash collections from sales, by month and in total. c. A merchandise purchases budget in units and in dollars. Show the budget by month and in total. d. A schedule of expected cash disbursements for merchandise purchases, by month and in total. 2 A cash budget. Show the budget by month and in total. Determine any borrowing that would be needed to maintain the minimum cash balance of $50,000. 3 A budgeted income statement for the three-month period ending June 30. Use the contribution approach. 4 A budgeted balance sheet as of June 30 2 3 4 5 6 eLearning Question 1 EARRINGS UNLIMITED Budgets April May June Requirement 1a. Sales budget: Budgeted sales in units Selling price per unit Total sales 65,000 100,000 50,000 $ 10 $ 10 $ 10 $ 650,000 $ 1,000,000 $ 500,000 Correct! Correct! Correct! Requirement 1b. Schedule of expected cash collections: February sales $ 26,000 March sales $ 280,000 $ 40,000 April sales $ 130,000 $ 455,000 $ 65,000 May sales $ 200,000 $ 700,000 June sales $ 100,000 Total cash collections $ 436,000 $ 695,000 $ 865,000 Correct! Correct! Correct! Requirement 1c. Merchandise purchases budget: 65,000 100,000 50,000 Budgeted unit sales 40,000 20,000 12,000 Add desired ending inventory Total needs 105,000 120,000 62,000 26,000 40,000 20,000 Less beginning inventory 79,000 80,000 42,000 Required purchases in units Unit cost $ 4 $ 4 $ 4 Required dollar purchases $ 316,000 $ 320,000 $ 168,000 Correct! Correct! Correct! Requirement 1d. Budgeted cash disbursements for merchandise purchases: Accounts payable $ 100,000 April purchases $ 158,000 $ 158,000 May purchases $ 160,000 $ 160,000 June purchases $ 84,000 Total cash disbursements $ 258,000 $ 318,000 $ 244,000 Correct! Correct! Correct! Quarter 215,000 $ 10 $ 2,150,000 Correct! $ $ $ $ $ $ 26,000 320,000 650,000 900,000 100,000 1,996,000 Correct! 215,000 12,000 227,000 26,000 201,000 $ 4 $ 804,000 Correct! $ $ $ $ $ 100,000 316,000 320,000 84,000 820,000 Correct! Requirement 2: EARRINGS UNLIMITED Cash Budget For the Three Months Ending June 30 Cash balance, beginning Add receipts from customers Total cash available Less disbursements: Merchandise purchases Advertising Rent Salaries Commissions (4% of sales) $ $ $ April 74,000 436,000 510,000 $ $ $ May 50,000 695,000 745,000 $ $ $ June 50,000 865,000 915,000 $ $ $ Quarter 74,000 1,996,000 2,070,000 $ $ $ $ $ 258,000 200,000 18,000 106,000 26,000 $ $ $ $ $ 318,000 200,000 18,000 106,000 40,000 $ $ $ $ $ 244,000 200,000 18,000 106,000 20,000 $ $ $ $ $ 820,000 600,000 54,000 318,000 86,000 1 Utilities Equipment purchases Dividends paid Total disbursements Excess (deficiency) of receipts over disbursements Financing: Borrowings Repayments Interest Total financing Cash balance, ending $ $ $ $ 7,000 15,000 630,000 $ $ $ $ 7,000 16,000 705,000 $ $ $ $ 7,000 40,000 635,000 $ $ $ $ 21,000 56,000 15,000 1,970,000 -$ 120,000 $ 40,000 $ 280,000 $ 100,000 $ $ $ $ $ 170,000 170,000 50,000 Correct! $ $ $ $ $ 10,000 $ $ 180,000 -$ 180,000 -$ 180,000 -$ 5,300 -$ 5,300 10,000 -$ 185,300 -$ 5,300 50,000 $ 94,700 $ 94,700 Correct! Correct! Correct! Requirement 3: EARRINGS UNLIMITED Budgeted Income Statement For the Three Months Ending June 30 Sales in units Sales Variable expenses: Cost of goods sold Commissions Contribution margin Fixed expenses: Advertising Rent Salaries Utilities Insurance Depreciation Net operating income Less interest expense Net income $ $ 860,000 86,000 $ $ $ $ $ $ 600,000 54,000 318,000 21,000 9,000 42,000 $ 215,000 2,150,000 $ $ 946,000 1,204,000 $ $ -$ $ 1,044,000 160,000 5,300 154,700 Correct! EARRINGS UNLIMITED Budgeted Balance Sheet June 30 Assets Cash Accounts receivable Inventory Prepaid insurance Property and equipment, net Total assets $ $ $ $ $ $ 94,700 500,000 48,000 12,000 964,000 1,618,700 2 Correct! Liabilities and Equity Accounts payable, purchases Dividends payable Capital stock, no par Retained earnings Total liabilities and equity $ $ $ $ $ 84,000 15,000 800,000 719,700 1,618,700 Correct! Accounts receivable at June 30: May sales June sales Total $ $ $ 100,000 400,000 500,000 Correct! Retained earnings at June 30: Balance, March 31 Add net income Total Less dividends declared Balance, June 30 $ $ $ $ $ 580,000 154,700 734,700 15,000 719,700 Correct! 3 eLearning Question 2 WESTEX PRODUCTS West Products is a wholesale distributor of industrial cleaning products. When the treasurer of Westex Products approached the company's bank later in the current year seeking short-term financing, he was told that money was very tight and that any borrowing over the next year would have to be supported by a detailed statement of cash collections and disbursements. The treasurer also was told that it would be very helpful to the bank if borrowers would indicate the quarters in which they would be needing funds, as well as the amount that would be needed, and the quarters in which repayments could be made.Because the treasurer is unsure as to the particular quarters in which bank financing will be needed, he has assembled the following information to assist in preparing a detailed cash budget: a. Budgeted sales and merchandise purchases for next year, a well as actual sales and purchases for the last quarter of the current year, are: Sales Current year Fourth quarter actual Next year First quarter estimated Second quarter estimated Third quarter estimated Fourth quarter estimated Merchandise Purchases $ 200,000 $ 126,000 $ $ $ $ 300,000 400,000 500,000 200,000 $ $ $ $ 186,000 246,000 305,000 126,000 b. The company normally collects 65% of a quarter's sales before the quarter ends and another 33% in the following quarter. The remainder is uncollectible. This pattern of collections is now being experienced in the current year's fourth-quarter actual data. c. 80% of a quarter's merchandise purchases are paid for within the quarter. The remainder is paid for in the following quarter. d. Selling and administrative expenses for next year are budgeted at $50,000 per quarter plus 15% of sales. Of the fixed amount, $20,000 each quarter is depreciation. e. The company will pay $10,000 in dividends each quarter. f. Land purchases of $75,000 will be made in the second quarter, and purchases of $48,000 will be made in the third quarter. These purchases will be for cash. g. The Cash account contained $10,000 at the end of the current year. The treasurer feels that this represents a minimum balance that must be maintained. h. The company has an agreement with a local bank that allows the company to borrow in increments of $1,000 at the beginning of each quarter, up to a total loan balance of $100,000. The interest rate on these loans is 2.5% per quarter and for simplicity we will assume that interest is not compounded. The company would, as far as it is able, repay the loan plus accumulated interest at the end of the year. i. At present, the company has no loans outstanding. Required: 1 Prepare the following by quarter and in total for next year: 1 a. A schedule of expected cash collections. 2 3 b. A schedule of expected cash disbursements for merchandise purchases. Compute the expected cash disbursements for selling and administrative expenses, by quarter and in total, for next year. Prepare a cash budget, by quarter and in total, for next year. 2 eLearning Question 2 WESTEX PRODUCTS Requirement 1a: Schedule of expected cash collections Current year - Fourth quarter sales: Next year - First quarter sales: Next year - Second quarter sales: Next year - Third quarter sales: Next year - Fourth quarter sales: Total cash collections $ $ $ Next Year's Quarter Second Third First 66,000 195,000 $ $ 261,000 Correct! $ 99,000 260,000 359,000 Correct! $ $ 132,000 325,000 $ 457,000 Correct! Fourth $ $ $ 165,000 130,000 295,000 Correct! Requirement 1b: Schedule of expected cash disbursements for merchandise purchases for next year Current year - Fourth quarter purchases: Next year - First quarter purchases: Next year - Second quarter purchases: Next year - Third quarter purchases: Next year - Fourth quarter purchases: Total cash payments $ $ Next Year's Quarter Second Third First 25,200 148,800 $ 174,000 Correct! 37,200 196,800 $ 49,200 244,000 Fourth 293,200 Correct! 61,000 100,800 $ 161,800 Correct! Quarter Second Third $ 400,000 $ 500,000 Fourth 200,000 $ 234,000 Correct! $ $ Requirement 2: Budgeted selling and administrative expenses for next year Budgeted sales Variable expense rate Variable expenses Fixed expenses Total expenses Less depreciation Cash disbursements $ First 300,000 $ 15% 15% 15% 15% $ 45,000 $ 60,000 $ 75,000 $ 30,000 50,000 50,000 50,000 50,000 95,000 110,000 125,000 80,000 20,000 20,000 20,000 20,000 $ 75,000 $ 90,000 $ 105,000 $ 60,000 Correct! Correct! Correct! Correct! Requirement 3: Cash budget for next year Cash balance, beginning Add collections from sales Total cash available Less disbursements: Merchandise purchases $ $ $ First 10,000 261,000 271,000 $ 174,000 1 Quarter Second Third $ 12,000 $ 10,000 $ 359,000 $ 457,000 $ 371,000 $ 467,000 $ $ $ Fourth 10,800 295,000 305,800 $ $ 161,800 234,000 $ 293,200 Selling and administrative expenses Dividends Land Total disbursements Excess (deficiency) of receipts over disbursements Financing: Borrowings Repayments Interest Total financing Cash balance, ending $ $ $ $ $ $ $ $ $ $ 75,000 10,000 259,000 $ $ $ $ 90,000 10,000 75,000 409,000 $ $ $ $ 105,000 10,000 48,000 456,200 $ $ $ $ 60,000 10,000 231,800 12,000 -$ 38,000 $ 10,800 $ 74,000 48,000 48,000 10,000 Correct! $ $ $ $ $ 12,000 Correct! $ $ $ $ $ 2 $ -$ 48,000 -$ 3,600 -$ 51,600 10,800 $ 22,400 Correct! Correct! $ $ $ $ $ $ Total 66,000 294,000 392,000 490,000 130,000 1,372,000 Correct! $ $ $ $ $ $ Total 25,200 186,000 246,000 305,000 100,800 863,000 Correct! $ Year 1,400,000 15% $ 210,000 200,000 410,000 80,000 $ 330,000 Correct! $ $ $ Year 10,000 1,372,000 1,382,000 $ 863,000 3 $ $ $ $ 330,000 40,000 123,000 1,356,000 $ 26,000 $ -$ -$ -$ $ 48,000 48,000 3,600 3,600 22,400 Correct! 4