Question

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 a master budget for the upcoming second quarter. 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-$13 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 (budget) May (budget) 21,400 27,400 41,400 66,400 101.400 June (budget) July (budget) August (budget) September (budget) 51.400 31.400 29.400 26,400 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.70 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. 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

Answer 1

Solution Requirement 1a. : Sales budget, by month and in total Earrings Unlimited Sales Budget April 66,400 $13 $863,200 Budgeted unit sales Selling price per unit Total sales May 101,400 $13 $1,318,200 June 51,400 $131 $668,200 Quarter 219,200 $13 $2,849,600 Requirement 1b. : Schedule of Expected cash collections, by month and in total February Sales March Sales April Sales May Sales June Sales Total cash collections Earrings Unlimited Schedule of Expected Cash collections April $35,620 $376,740 $172,640 $0 May $0 $53,820 $604,240 $263,640 $0 $921,700 June $0 $o $86,320 $922,740 $133,640) $1,142,700 Quarter $35,620 $430,560 $863,200 $1,186,380 $133,640 $2,649,400 $585,000 Requirement 1c. : Merchandise purchase budget in units and in dollars Budgeted unit sales Add: Desired ending inventory Total needs Less: Likely beginning inventory Required purchases (in units) Unit cost Required dollar purchases Earrings Unlimited Merchandise Purchase Budget April 66,400 40,560 106,9601 26,5601 80,400 May | 101,400 20,560 121,960 40,5601 81,400 $4.70 $382,580 June | 51,400 12,560 63,960 20,560 43,400 $4.700 $203,980 Quarter 219,200 12,560 231,760 26,560 205,200 $4.70 $964,440 $4.70 $377,880

Requirement 1d. : Schedule of expected cash disbursements for merchandise purchases, by month and in total Accounts Payable April purchases May purchases June purchases Total cash payments Earrings Unlimited Budgeted Cash Disbursements for Merchandise Purchases April May $107,000 $0 $ 188,940) $188,940 $0 $191,2901 $0 $o $295,940 $380,230 June $0 $0 $191,290 $101,990 $293,280 Quarter $107,000 $377,880 $382,580 $101,990 $969,450 Requirement 2: Cash Budget May $57,782 $921,700 $979,482 June $101,524 $1,142,700 $1,244,224 Quarter $81,000 $2,649,400 $2,730,400 Earrings Unlimited Cash Budget For the Three Months Ending June 30 April Beginning cash balance $81,000 Add: Collections from customers $585,000 Total cash available $666,000 Less: Cash disbursements: Merchandise purchases $295,940 Advertising $270,000 Rent $25,000 Salaries $120,000 Commissions $34,528 Utilities $ 10,500 Equipment purchases $0 Dividends paid $20,250 Total cash disbursements $776,218 Excess (deficiency) of cash available over disbursements ($110,218) Financing: Borrowings $168,000 Repayments $0 Interest Total financing $168,000 Ending cash balance $57,782 $380,230| $270,000 $25,000 $120,000 $52,728 $10,500 $19,500 $0 $877,958 $101,524 $293,280 $270,000 $25,000 $120,000 $26,728 $10,500 $47,000 $0 $792,508 $451,716 $969,450 $810,000 $75,000 $360,000 $113,984 $31,500 $66,500 $20,250 $2,446,684 $283,716 $o $o $0 $0 $0 $101,524 $0 ($168,000) ($5,040) ($173,040) $278,676 $168,000 ($ 168,000) ($5,040) ($5,040) $278,676

Requirement 3 : Budgeted Income Statement for the three-month period ending June 30 $2,849,600 $1,030,2401 $113,984 $1,144,224 $1,705,376 Earrings Unlimited Budgeted Income Statement For the Three-months Ended June 30 Sales Revenue Variable expenses: Cost of Goods sold (219,200 units x $4.70) Sales Commissions ($2,849,600 x 4%) Total Variable expenses Contribution Margin Fixed expenses: Advertising Rent Salaries Utilities Insurance ($3,700 x 3 month) Depreciation ($21,000 x 3 month) Interest expense Total Fixed expenses Net Income $810,000 $75,000 $360,000 $31,500 $11,100 $63,000 $5,040 $1,355,640 $349,736 Requirement 4: Budgeted Balance Sheet as of June 30 Earrings Unlimited Budgeted Balance Sheet June 30 Assets Cash Accounts Receivable Inventory (12,560 units x $4.70) Prepaid Insurance ($24,500 - $11,100) Property and equipment (net) ($1,020,000 + $66,500 - $63,000) Total assets $278,676 $666,380 $59,032 $13,400 $1,023,500 $2,040,988 Liabilities and Stockholder's Equity Accounts Payable Dividends Payable Common stock Retained Earnings ($649,262 + $349,736 - $20,250) Total liabilities and stockholders' equity $ 101,9901 $20,250 $940,000 $978,748 $2,040,988

To see the formulas, please see the below images:

E Solution 3 Requirement la. : Sales budget, by month and in total Earrings Unlimited Sales Budget April 66400 13 | =C8*C9 Quarter IESUMIC8:58) ESUM Budgeted unit sales Selling price per unit Total sales 9 10 May 101400 13 =D8+D9 June 151400 13 =E8*E9 13 =F87F9 11 12 Requirement 1b. : Schedule of Expected cash collections, by month and in total 16 17 Earrings Unlimited Schedule of Expected Cash collections April May =27400*1310% =41400*13*70% -41400*13*10% =C10*2096 |=C10*70% =D10*2096 February Sales March Sales April Sales May Sales June Sales Total cash collections 19 June Quarter |-SUM(C17:E17) O | -SUM(C18:E18) | =C10*1096 |-SUM(C19:519) =D10*70 | -SUM(C20:E20) =E10*20% |-SUM(C21:E21) SUM(E17:E21) =SUM(F17:F21) | 20 21 22 =SUM(C17:021) =SUM(D17:021) 23 24 25 Requirement 1c. : Merchandise purchase budget in units and in dollars 27 28 29 Budgeted unit sales 30 Add: Desired ending inventory 31 Total needs 32 Less: Likely beginning inventory 33 Required purchases in units) 34 Unit cost 35 Required dollar purchases Earrings Unlimited Merchandise Purchase Budget April May =C8 =D8 D8 405 -E84096 =C29+C30 =D29+D30 =C29*4096 =D29*40% =C31-C32 =D31-D32 4.7 =C33*C34 =D33D34 June =E8 =31400*40% =E29+E30 =E294096 =E31-E32 Quarter SUM(C29:E29) | =E30 =F29+F30 |=C32 =F31-F32 4.7 =F33*F34 4.7 4.7 =F23 E34 36

37 Requirement 1d.: Schedule of expected cash disbursements for merchandise purchases, by month and in total 38 39 40 41 42 43 44 45 46 Earrings Unlimited Budgeted Cash Disbursements for Merchandise Purchases April May 107000 =C35/2 2 =C35/ 0 =D35/2 Accounts Payable April purchases I May purchases June purchases Total cash payments June Quarter lo | -SUM(C42:E42) | -SUM(C43:E43) | =D35/2 |-SUM(C44:E44) | =E35/2 |-SUM(C45:E45) SUM(E42:E45) SUM(F42:F45) =SUM(C42:C45) =SUM(D42:D45) 47

48 Requirement 2: Cash Budget 49 June Quarter 7 3EC54 -E22 -SUM(C55:E55) =SUM(E54:E55) I-SUM(F54:F55) Earrings Unlimited Cash Budget For the Three Months Ending June 30 April May 54 Beginning cash balance 81000 I= C73 Add: Collections from customers =C22 =D22 56 Total cash available -SUM(C54:C55) -SUM(D54:D55) 57 Less: Cash disbursements: Merchandise purchases =C46 =D46 Advertising 270000 =C59 60 Rent 25000 1-C60 Salaries 120000 =C61 Commissions =C10*496 =D10*4% Utilities 10500 -C63 Equipment purchases 19500 Dividends paid 20250 66 Total cash disbursements -SUM(C58:C65) -SUM(D58:D65) 67 Excess (deficiency) of cash available over disbursements =C56-C66 =D56-D66 68 Financing: 69 Borrowings =111000+570000 70 | Repayments o 711 Interest 72 Total financing =SUM(C69:C71) -SUM(D69:D71) 73 Ending cash balance =C67+c72 =D67+D72 74 63 =E46 -SUM(C58:E58) =C59 -SUM(C59:E59) =C60 -SUM(C60:E60) =C61 -SUM(C61:E61) =E10*4% -SUM(C62:E62) =C63 -SUM(C63:E63) 47000 -SUM(C64:E64) -SUM(C65:E65) I-SUM(E58:E65) I-SUM(F58:F65) =E56-E66 |=F56-F66 64 65 -SUM(C69:E69) =-C69 =SUM(C70:E70) -(C69*1953) SUM(C71:E71) -SUM(E69:E71) I-SUM(F69:F71) =E67+E72 =F67+F72

75 Requirement 3 : Budgeted Income Statement for the three-month period ending June 30 80 =F10 84 -SUM(C82:C83) =D80-D84 Earrings Unlimited Budgeted Income Statement For the Three-months Ended June 30 Sales Revenue Variable expenses: 82 | Cost of Goods sold (219,200 units x $4.70) =F8*4.7 83 =D80*496 | Sales Commissions ($2,849,600 x 4%) Total Variable expenses Contribution Margin 86 Fixed expenses: =F59 Advertising Rent =F60 Salaries =F61 90 Utilities =F63 91 Insurance ($3,700 x 3 month) =370003 92 Depreciation ($21,000 x 3 month) =21000*3 Interest expense --F71 94 Total Fixed expenses 95 I Net Income 96 87 88 89 93 -SUM(C87:093) =D85-D94

97 Requirement 4: Budgeted Balance Sheet as of June 30 98 99 Earrings Unlimited Budgeted Balance Sheet une 0 Assets Cash 104 Accounts Receivable 105 Inventory (12,560 units x $4.70) 106 Prepaid Insurance ($24,500-$11,100) 107 Property and equipment (net) ($1,020,000+$ 66,500 - $63 108 Total assets =F73 |=F10-SUM(F19:F21) =E30*4.7 =24500-091 =1020000+F64-C92 =SUM(D103:D107) 109 110 Liabilities and Stockholder's Equity 111 Accounts Payable 112 Dividends Payable 113 Common stock 114 Retained Earnings ($649,262 +$ 349,736 - $20,250) 115 Total liabilities and stockholders' equity =E35-E45 20250 940000 =649262+D95-D112 SUMD111:D114) 116