Question

ONLY 2, 3, and 4. Already solved 1.
Case 8-33 Master Budget with Supporting Schedules [LO8-2, LO8-4, LO8-8, LO8-9, LO8-10] 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-$16 per pair. Actual sales of earrings for the last three months and budgeted sales for the next six months follow (in pairs of earrings): June (budget) July (budget) August (budget) September (budget) January (actual) February (actual) March (actual) April (budget) May (budget) 23,400 29,400 43,400 68,400 103,400 53,400 33,400 31,400 28,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 $5.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.

Answer 1

Solution Requirement 1a. : Sales budget, by month and in total Budgeted sales units Selling price per unit Total sales Sales Budget April 68,400 $16 $1,094,400 May 103,400 $161 $1,654,400 June | 53,400 | $16 $854,400 Quarter 225,200 $16 $3,603,200 Requirement 1b. : Schedule of Expected cash collections, by month and in total June | $0 Quarter $47,040 February Sales March Sales April Sales May Sales June Sales Total cash collections Earrings Unlimited Schedule of Expected Cash collections April May $47,040 $0 $486,080 | $218,880 $766,080 $0 | $330,880 $0 $0 $752,000 $1,166,400 $109,440 $1,158,080 $170,880 $1,438,400 $1,094,400 $1,488,960 $170,880 $3,356,800 Requirement 1c. : Merchandise purchase budget in units and in dollars Budgeted Sales Units 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 68,400 41,3601 109,760 27,360 82,400 $5.70 $469,680 May | 103,400 21,360 124,760 41,360 83,4001 $5.70 $475,380 June | Quarter 53,4001 225,200 13,3601 13,360 66,760 238,560 21,360 27,360 45,400 211,200 $5.70 $5.70 $258,780 $1,203,840

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 $117,000 $0 $234,840 $234,840 $ 237,690 $0 $0 $351,840 $472,530 June $0 $0 $237,690 $129,390 $367,080 Quarter $117,000 $469,680 $475,380 $129,390 $1,191,450 Requirement 2: Cash Budget June $109,828 $1,438,400 $1,548,228 Quarter $91,000 $3,356,800 $3,447,800 Earrings Unlimited Cash Budget For the Three Months Ending June 30 April May Beginning cash balance $91,000 $67,134 Add: Collections from customers $752,000 $1,166,400 Total cash available $843,000 $1,233,534 Less: Cash disbursements: Merchandise purchases $351,840 $472,530 Advertising $370,000 $370,000 Rent $35,000 $35,000 Salaries $140,000 $140,000 Commissions $43,776 $66,176 Utilities $15,500 $15,500 Equipment purchases $0 $24,500 Dividends paid $27,750 $0 Total cash disbursements $983,866 $1,123,706 Excess (deficiency) of cash available over disbursements ($140,866) $109,828 Financing: Borrowings $208,000 $0 Repayments $0 Interest $0 Total financing $208,000 $0 Ending cash balance $67,134 $109,828 $367,080 $370,000 $35,000 $140,000 $34,176 $15,500 $57,000 $0 $1,018,756 $529,472 $1,191,450 $1,110,000 $105,000 $420,000 $144,128 $46,500 $81,500 $27,750 $3,126,328 $321,472 $0 $0 ($208,000) ($6,240)| ($214,240) $315,232 $208,000 ($208,000) ($6,240) ($6,240) $315,232

Requirement 3 : Budgeted Income Statement for the three-month period ending June 30 $3,603,200 $1,283,640 $144,128 $1,427,768 $2,175,432) Earrings Unlimited Budgeted Income Statement For the Three-months Ended June 30 Sales Revenue Variable expenses: Cost of Goods sold (225,200 units x $5.70) Sales Commissions ($3,603,200 x 4%) Total Variable expenses Contribution Margin Fixed expenses: Advertising Rent Salaries Utilities Insurance ($4,700 x 3month) Depreciation ($31,000 x 3month) Interest expense Total Fixed expenses Net Income $1,110,000 $105,000 $420,000 $46,500 $14,100 $93,000 $6,240 $1,794,840 $380,592 Requirement 4 : Budgeted Balance sheet as of June 30 Earrings Unlimited Budgeted Balance Sheet June 30 Assets Cash Accounts Receivable Inventory Prepaid Insurance ($29,500 - $14,100) Property and equipment (net) ($1,120,000+ $81,500 - $93,000) Total assets $315,232 $848,960 $76,152 $15,400 $1,108,500 $2,364,244 Liabilities and Stockholder's Equity Accounts Payable Dividends Payable Common stock Retained Earnings ($714,262 +$380,592 - $27,750) Total liabilities and stockholders' equity $129,390 $27.750 $1,140,000 $1,067,104 $2,364,244

To see the formulas, please see the below images:

1 Solution 3 Requirement la. : Sales budget, by month and in total 5 6 7 Sales Budget April 68400 16 =C7*08 Quarter -SUM(C7:E7) Budgeted sales units Selling price per unit Total sales 8 May 103400 16 1=D7*D8 June 53400 16 =E7*E8 =F7*F8 9 10 11 12 Requirement 1b.: Schedule of Expected cash collections, by month and in total 14 15 16 Earrings Unlimited Schedule of Expected Cash collections April May =29400*16 10% 0 =43400*16*70% -43400*16*10% =C920% =C9*70% =D9 20% 17 February Sales March Sales April Sales May Sales June Sales Total cash collections 18 19 20 21 22 23 24 June lo o =C9 10% | =D9*70% -E92096 =SUM(E16:E20) Quarter -SUM(C16:516) -SUM(C17:E17) -SUM(C18:E18) -SUM(C19:E19) -SUM(C20:E20) =SUM(F16:F20) =SUM(C16:C20) =SUM(D16:D20) Requirement 1c. : Merchandise purchase budget in units and in dollars 25 26 27 28 29 30 31 32 33 34 Budgeted Sales Units 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 May =C7 =D7 =D7*40% =E7*40% =C28+C29 =D28+029 =C284096 =D28*4096 =C30-C31 =D30-D31 5.7 =C32*C33 |=D32*D33 June =E7 1=33400*40% =E28+E29 =E28*40% =E30-E31 5.7 I=E32 E33 Quarter -SUM(C28:E28) =E29 =F28+F29 =C31 =F30-F31 5.7 =F32*F33

Requirement 1d.: Schedule of expected cash disbursements for merchandise purchases, by month and in total 36 37 38 40 Accounts Payable April purchases May purchases June purchases Total cash payments Earrings Unlimited Budgeted Cash Disbursements for Merchandise Purchases April May June 117000 lo lo =C34/2 =C34/ 2 0 =D34/2 |=D34/2 =E34/2 =SUM(C41:044) SUM(D41:044) =SUM(E41:144) Quarter -SUM(C41:E41) |-SUM(C42:E42) =SUM(C43:E43) -SUM(C44:E44) =SUM(F41:F44) 44 45 46

C D E F Requirement 2: Cash Budget 47 48 49 51 52 53 Quarter =C53 -SUM(C54:E54) -SUM(F53:F54) 54 Add 55 56 58 59 60 61 Earrings Unlimited Cash Budget For the Three Months Endine lune 30 | April May June Beginning cash balance 91000 =C72 =D72 Add: Collections from customers =C21 =D21 -E21 Total cash available -SUM(C53:054) -SUM(D53:D54) -SUM(E53:E54) Less: Cash disbursements: Merchandise purchases 045 2045 =F45 Advertising 370000 370000 370000 Rent 35000 35000 35000 Salaries 140000 140000 140000 Commissions =C9*4% =D9*496 |-E9*4% Utilities 15500 15500 1155 Equipment purchases 24500 57000 | Dividends paid 27750 lo Total cash disbursements -SUM(C57:C64) T-SUM(D57:064) T-SUM(E57:E64) Excess (deficiency) of cash available over disbursements =C55-C65 =D55-D65 -ES5-E65 Financing Borrowines 1 =141000+67000 lo l o Repayments lo 1.208000 Interest --(C68 19693) Total financing -SUM(C68:C70) T-SUM(D68:D70) T-SUM(E68:E70) Ending cash balance |=C66+071 =D66+D71 =E66+E71 62 I-SUM(C57:E57) -SUM(C58:E58) -SUM(C59:E59) =SUM(C60:E60) -SUM(C61:E61) -SUM(C62:E62) | -SUM(C63:E63) =SUM(C64:E64) -SUM(F57:F64) =F55-F65 64 69 70 71 72 73 -SUM(C68:168) -SUM(C69:E69) ESUM(C70:E70) -SUM(F68:F70) =F66+F71

74 Requirement 3: Budgeted Income Statement for the three-month period ending June 30 75 76 78 =F9 80 81 82 83 84 -SUM(C81:C82) =D79-D83 Earrings Unlimited Budgeted Income Statement For the Three months Ended June 30 Sales Revenue Variable expenses: Cost of Goods sold (225,200 units x $5.70) =F7*5.7 Sales Commissions ($3,603,200 x 4%) -D79494 Total Variable expenses Contribution Margin Fixed expenses: Advertising =F58 Rent =F59 Salaries =F60 Utilities =F62 Insurance ($4,700 x 3month) =47003 Depreciation ($31,000 x 3month) =310003 Interest expense -F70 Total Fixed expenses Net Income 86 co -SUM(C86:092) =D84-093 94 96

97 Requirement 4:Budgeted Balance Sheet as of June 30 98 99 101 102 2 Earrings Unlimited 100 Budgeted Balance Sheet June 30 Assets 103 Cash 104 Accounts Receivable 105 Inventory 106 Prepaid Insurance ($29,500 - $14,100) 107 Property and equipment (net) ($1,120,000+ $81,500 - $9: 108 Total assets =F9-SUM(F18:F20 =E29*5.7 -29500-090 =1120000+F63-9 =SUMD103:D107 109 110 Liabilities and Stockholder's Equity 111 Accounts Payable 112 Dividends Pavable 113 Common stock 114 Retained Earnings ($714,262 +$380,592 - $27,750) 115 Total liabilities and stockholders' equity 116 1=E34-E44 27750 1140000 =714262+D94-01 =SUM(D111:0114