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 $14 per pair. Actual sales of earrings for the last three months and budgeted sales for the next six months follow (in pairs of earrings): February (actual) March (actual) April (budget) May (budget) 20.300 26,800 40,80 65,860 June (budget) July (budget) August (budget) September (budget) 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.40 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: Advertising 5 $ $ $ . 22,00 114, 9,000 Utilities Insurance

Answer 1

Solution Requirement 1a. : Sales budget, by month and in total Earrings Unlimited Sales Budget April 65,800 $14 $921,200 June 50,800 Budgeted unit sales Selling price per unit Total sales May 100,800| $141 $1,411,200 $14 Quarter 217,400 $14 $3,043,600 $711,200 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 $37,520 $399,8401 $184,2401 o $0 $621,600 May $0 $57,120 $644,840 $282,240 $0 $984,200 June Quarter $0 $37,520 $0 $456,960 $92,120 $921,200 $987,8401 $1,270,080 $142,240 $142,240 $1,222,200 $2,828,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 65,800 40,320 106,120 26,3201 79,800 $4.40 $351,120 May 100,800 20,3201 121,120 40,3201 80,8001 $4.40 $355,520 June 50,800 12,320 63,120 20,3201 42,800 $4.40 $188,320 Quarter 217,400 12,320 229,720 26,320 203,400 $4.40 $894,960

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 $104,000 $0 $175,560 $175,560 $0 $177,760 $0 $279,560 $353,320 June $0 $0 $177,760 $94,160 $271,920 Quarter $104,000 $351,120 $355,520 $94,160 $904,800 Requirement 2: Cash Budget June $225,624 $1,222,200 $1,447,824 Quarter $78,000 $2,828,000 $2,906,000 Earrings Unlimited Cash Budget For the Three Months Ending June 30 April May Beginning cash balance $78,000 $54,192| Add: Collections from customers $621,600 $984,200 Total cash available $699,600 $1,038,392 Less: Cash disbursements: Merchandise purchases $279,560 $353,320 Advertising $240,000 $240,000 Rent $22,000 $22,000 Salaries $114,000 $114,000 Commissions $36,848 $56,448 Utilities $9,000 $9,000 Equipment purchases $0 $18,000 Dividends paid $18,000 $0 Total cash disbursements $ 719,408 $812,768 Excess (deficiency) of cash available over disbursements ($19,808) $225,624 Financing: Borrowings $ 74,0001 $0 Repayments $0 Interest $0 Total financing $74,000 $0 Ending cash balance $54,192 $225,624| $271,920 $240,000 $22,000 $114,000 $28,448 $9,000 $44,000 $0 $729,368 $718,456 $904,800 $720,000 $ 66,000 $342,000 $121,744 $27,000 $62,000 $18,000 $2,261,544 $644,456 $0 SO/ $0 ($ 74,000)| ($2,220) $76,220) $642,236 $74,000 ($74,000) ($2,220) ($2,220) $642,236

Requirement 3 : Budgeted Income Statement for the three-month period ending June 30 $3,043,600 $956,5601 $121,744 $1,078,304 $1,965,296 Earrings Unlimited Budgeted Income Statement For the Three-months Ended June 30 Sales Revenue Variable expenses: Cost of Goods sold (217,400 units x $4.40) Sales Commissions ($3,043,600 x 4%) Total Variable expenses Contribution Margin Fixed expenses: Advertising Rent Salaries Utilities Insurance ($3,400 x 3 month) Interest expense Total Fixed expenses Net Income $720,000 $66,000 $342,000 $27,000 $10,200 $2,2201 $1,167,420 $797,876 Requirement 4: Budgeted Balance Sheet as of June 30 Earrings Unlimited Budgeted Balance Sheet June 30 Assets Cash Accounts Receivable Inventory (12,320 units x $4.40) Prepaid Insurance ($23,000 - $10,200) Property and equipment (net) ($990,000+ $62,000) Total assets $642,236 $710,080 $54,208 $12,800 $1,052,000 $2,471,324 Liabilities and Stockholder's Equity Accounts Payable Dividends Payable Common stock Retained Earnings ($699,288 + $797,876 - $18,000) Total liabilities and stockholders' equity $94,160 $18,000 $880,000 $1,479,164 $2,471,324 Note: In the given question, monthly depreciation expense figure is not visible, however there should be monthly depreciation expense. The Income statement and balance sheet is prepared ignoring the depreciation expense for the quarter.

To see the formulas, please see the below images:

1 Solution 3 Requirement la. : Sales budget, by month and in total 5 6 Earrings Unlimited Sales Budget April 65800 June 50800 May 100800 14 =D8*D9 Budgeted unit sales Selling price per unit Total sales Quarter | -SUM(C8:E8) 14 =F9+E9 9 =C8*09 =E8*E9 10 11 Requirement 1b.: Schedule of Expected cash collections, by month and in total 14 15 16 17 18 19 February Sales March Sales April Sales May Sales June Sales Total cash collections Earrings Unlimited Schedule of Expected Cash collections April May =26800*14*10% 10 0 |-40800*14*70% -40800*1410% =C10*20% =C10*70% lo =D10*2096 June Quarter |SUM(C17:E17) jo -SUM(C18:E18) =C10*10% -SUM(C19:519) =D10*70% -SUM(C20:E20) 1=E10*20% -SUM(C21:E21) =SUM(E17:E21) =SUM(F17:F21) | 20 21 =SUM(C17:021) =SUM D17:D21) 24 Requirement 1c. : Merchandise purchase budget in units and in dollars 26 27 31 28 29 Budgeted unit sales 30 Add: Desired ending inventory Total needs Less: Likely beginnine inventory 33 Required purchases (in units) 34 Unit cost 35 Required dollar purchases 36 Earrings Unlimited Merchandise Purchase Budget April May =C8 1=D8 =D8*408 =E8*40% =C29+C30 =D29+D30 |=C29*40% =D29*40% I=C31-C32 =D31-D32 4.4 14.4 =C33*C34 =D33D34 June =E8 |=30800*40% E 29+E30 =E29*40% =E31-E32 4.4 =E33*E34 Quarter -SUM(C29:E29) || =E30 =F29+F30 =C32 =F31-F32 32 4.4 |=F33*F34

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

C D E F Requirement 2: Cash Budget June Quarter =D73 =C54 -E22 -SUM(C55:E55) | -SUM(E54:E55) I-SUM(F54:F55) 57 58 99 Earrings Unlimited Cash Budget For the Three Months Endine June 30 April May Beginning cash balance 78000 =C73 Add: Collections from customers =C22 =D22 Total cash available -SUM(C54:C55) I-SUM(D54:055) Less: Cash disbursements: Merchandise purchases =C46 |=D46 Advertising 240000 240000 Rent 22000 22000 Salaries 114000 114000 Commissions =C10*496 =D10*4% Utilities 9000 9000 Equipment purchases lo 18000 Dividends paid 18000 lo Total cash disbursements -SUM(C58:C65) SUM(D58:D65) Excess (deficiency) of cash available over disbursements -C56-C66 =D56-D66 Financing Borrowings 1=20000+540000 O Repayments o Interest Total financing -SUM(C69:C71) SUM(D69:D71) Ending cash balance =C67+072 |=D67+D72 =E46 -SUM(C58:E58) 240000 -SUM(C59:59) | 22000 -SUM(C60:E60) 114000 -SUM(C61:E61) =E10*4% -SUM(C62:E62) 9000 -SUM(C63:E63) | 44000 I-SUM(C64E64) I-SUM(C65:E65) SUM(E58:E65) SUM(F58:F65) =E56-E66 =F56-F66 64 65 66 67 68 69 70 71 72 T -SUM(C69:969) --C69 =SUM(C70:E70) --C69*1953) -SUM(C71:E71) -SUM(E69:E71) I-SUM(F69:F71) | =E67+E72 |=F67+F72 73 74

DE F Requirement 3 : Budgeted Income Statement for the three-month period ending June 30 75 =F10 T-SUM(C82:C83) =D80-084 Earrines Unlimited 78 Budgeted Income Statement For the Three-months Ended June 30 Sales Revenue Variable expenses: 82 Cost of Goods sold (217,400 units x $4.40) =F8*4.4 83 Sales Commissions ($3,043,600 x 4%) =D80*4% 841 Total Variable expenses 85 Contribution Margin Fixed expenses: Advertising I=F59 88 Rent F60 Salaries =F61 90 Utilities =F63 91 Insurance ($3,400 x 3 month) =3400*3 92 Interest expense |--F71 Total Fixed expenses 94 Net Income 86 89 93 |-SUM(C87:092) =D85-D93 95

97 96 Requirement 4: Budgeted Balance Sheet as of June 30 98 Earrings Unlimited Budgeted Balance Sheet une 30 101 Assets 102 I Cash 103 Accounts Receivable 104 Inventory (12,320 units x $4.40) 105 Prepaid Insurance ($23,000 - $10,200) 106 Property and equipment (net) ($990,000+ $62,000) 107 Total assets 108 109 Liabilities and Stockholder's Equity 110 Accounts Payable 111 Dividends Payable 112 Common stock 113 Retained Earnings ($699,288 +$797,876 - $18,000) 114 Total liabilities and stockholders' equity 115 =F73 =F10-SUM(F19:F21) =E30*4.4 =23000-C91 -990000+F64 =SUM(D102:D106) =E35-E45 18000 880000 -699288+D94-D111 =SUM(D110:D113) Note: In the given question, monthly depreciation expense figure is not visible, however there should be monthly depreciation expense. The Income statement and balance sheet is prepared ignoring the depreciation expense for the quarter. 116