Question

nsert Design Layout Managerial Project 2 (1) - Saved to this PC Mailings Review View Help References Walter, Autum Tell me what you want to do Ro 10.5 - A A A A - ak x. * A... A.- E 21 3 . . AaBbCcDc AaBbcDc AaBbC6 AaBb 1 Normal 1 No Spac... Heading 1 Headin Font Paragraph Project Two: Styles 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): January (actual) February (actual) March (actual) April (budget) May (budget) 21,600 27,600 41,600 66,600 101,600 June (budget) July (budget) August (budget) September (budget) 51, 600 31,600 29,600 26,600 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.80 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% 15 collected in the second month following sale. Bad debts have been negligible. Monthly operating expenses for the company are given below: Variable: See 4 Sales COBione Fixed: of sales es E e e

Answer 1

Solution Requirement 1a. : Sales budget, by month and in total Earrings Unlimited Sales Budget April 66,600 $14 $932,400 Budgeted unit sales Selling price per unit Total sales May 101,600 $14 $1,422,400 June 51,600 $14 $722,400 Quarter 219,800 $14 $3,077,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 $38,640 $407,6801 $186,480 $0 | May $0 $58,240 $652,6801 $284,480 $0 $995,400 June $0 $0 $93,240 $995,680 $144,480 $1,233,400 Quarter $38,640 $465,920 $932,400 $1,280,160 $144,480 $2,861,600 $o $632,800 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,600 40,6401 107,2401 26,640 80,600 $4.800 $386,880 May 101,600 20,640 122,240 40,640 81,600 $4.80 $391,680 June | 51,600 12,6401 64,240 20,640 43,600 $4.800 $209,280 Quarter 219,800 12,640 232,440 26,640 205,800 $4.80 $987,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 $108,000 $0 $ 193,440 $ 193,440 $0 $195,840 $0 $o $301,440 $389,280 June $0 $0 $195,840 $104,640 $300,480 Quarter $ 108,000 $386,880 $391,680 $104,640 $991,200 Requirement 2: Cash Budget June $148,288 $1,233,400 $1,381,688 Quarter $82,000 $2,861,600 $2,943,600 Earrings Unlimited Cash Budget For the Three Months Ending June 30 April May Beginning cash balance $82,000 $58,064 Add: Collections from customers $632,800 $995,400 Total cash available $714,800 $1,053,464 Less: Cash disbursements: Merchandise purchases $301,440 $389,280 Advertising $280,000 $280,000 Rent $26,000 $26,000 Salaries $122,000 $122,000 Commissions $37,296 $56,896 Utilities $11,000 $11,000 Equipment purchases $0 $20,000 Dividends paid $21,000 $0 Total cash disbursements $798,736 $905,176 Excess (deficiency) of cash available over disbursements ($83,936) $148,288 Financing: Borrowings $ 142,000 $0 Repayments $0 $0 Interest $0 $0 Total financing $ 142,000 $0 Ending cash balance $58,064 $148,288 $300,480 $280,000 $26,000 $122,000 $28,896 $11,000 $48,000 $0 $816,376 $565,312 $991,200 $840,000 $78,000 $366,000 $123,088 $33,000 $68,000 $21,000 $2,520,288 $423,312 $0 ($142,000) $4,260) ($146,260) $419,052 $ 142,000 ($142,000) ($4,260) ($4,260) $419,052

Requirement 3 : Budgeted Income Statement for the three-month period ending June 30 $3,077,200 $1,055,040 $123,088 $1,178,128 $1,899,072 Earrings Unlimited Budgeted Income Statement For the Three-months Ended June 30 Sales Revenue Variable expenses: Cost of Goods sold (219,800 units x $4.80) Sales Commissions ($3,077,200 x 4%) Total Variable expenses Contribution Margin Fixed expenses: Advertising Rent Salaries Utilities Insurance ($3,800 x 3 month) Depreciation ($22,000 x 3 month) Interest expense Total Fixed expenses Net Income $840,000 $78,000 $366,000 $33,000 $11,400 $66,000 $4,260 $1,398,660 $500,412 Requirement 4: Budgeted Balance Sheet as of June 30 Earrings Unlimited Budgeted Balance Sheet June 30 Assets Cash Accounts Receivable Inventory (12,640 units x $4.80) Prepaid Insurance ($25,000 - $11,400) Property and equipment (net) ($1,030,000 + $68,000 - $ 66,000) Total assets $419,052 $720,160 $60,672 $13,600 $1,032,000 $2,245,484 Liabilities and Stockholder's Equity Accounts Payable Dividends Payable Common stock Retained Earnings ($680,432 + $500,412 - $21,000) Total liabilities and stockholders' equity $104,640 $21,000 $960,000 $1,159,844 $2,245,484

To see the formulas, please see the below images:

Solution 3 Requirement la. : Sales budget, by month and in total von Earrings Unlimited Sales Budget 1 April 66600 14 =C8*C9 5 9 Budgeted unit sales Selling price per unit Total sales May 101600 14 =D8*D9 June 1600 14 =E8*E9 Quarter TSUM(C8:E8) 14 =F8*F9 10 11 Requirement lb. : Schedule of Expected cash collections, by month and in total 16 17 18 19 20 February Sales March Sales April Sales May Sales June Sales Total cash collections Earrings Unlimited Schedule of Expected Cash collections April May =27600*14*10% O O |-41600*14*70% -41600*14*10% O =C10*2096 |=C10*70% =D10*20% 10 | SUM(C17:021) SUM(D17:D21) June Quarter SUM(C17:E17) | S UM(C18:118) =C10*10% =SUM(C19:19) =D10*70% SUM(C20:E20) =E10*2096 I-SUM(C21:E21) | SUM(E17:E21) -SUM(F17:F21) Requirement 1c. : Merchandise purchase budget in units and in dollars 30 31 Earrings Unlimited Merchandise Purchase Budget April May I=C8 =D8 =D8*40% =E8 40% =C29+C30 =D29+D30 =C29*40% =D29*40% =C31-C32 =D31-D32 4.8 4.8 =C33*C34 =D33D34 Budgeted unit sales Add: Desired ending inventory Total needs Less: Likely beginning inventory Required purchases in units) Unit cost Required dollar purchases June -ES =31600*4096 =E29+30 |=E29*40% =E31-32 4.8 1=E33*E34 | Quarter -SUM(C29:E29) | E30 =F29+F30 =C32 =F31-F32 4.8 F33*F34 32 33 34 35 36

37 Requirement id. Schedule of expected cash disbursements for merchandise purchases, by month and in total 40 41 Quarter 42 Accounts Payable 43 April purchases I May purchases 45 June purchases 46 Total cash payments Earrings Unlimited Budgeted Cash Disbursements for Merchandise Purchases April May 108000 lo =C35/2 =C35/2 =D35/2 44 June 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 June Quarter =D73 -C54 =E22 SUM(C55:55) -SUM(E54:E55) -SUM(F54:F55) 57 58 59 60 61 62 63 64 65 66 Earrings Unlimited Cash Budget For the Three Months Ending June 30 April May Beginning cash balance 82000 =C73 Add: Collections from customers =C22 =D22 Total cash available -SUM(C54:C55) -SUM(D54:D55) Less: Cash disbursements: Merchandise purchases =C46 =D46 Advertising 280000 |=C59 Rent 26000 =C60 Salaries 122000 =C61 Commissions =C10*4% =D10*4% Utilities 11000 |=C63 Equipment purchases 20000 Dividends paid 21000 Total cash disbursements -SUM(C58:C65) -SUM(D58:D65) Excess (deficiency) of cash available over disbursements =C56-C66 =D56-D66 Financing: Borrowines =58000+840000 Repayments Interest Total financing -SUM(C69:C71) T-SUM(D69:D71) Ending cash balance =C67+C72 1 =D67+D72 =E46 -SUM(C58:E58) =C59 -SUM(C59:59) =C60 =SUM(C60:E60) =C61 -SUM(C61:E61) =E10*4% SUM(C62:E62) =C63 I-SUM(C63:E63) 48000 -SUM(C64:E64) -SUM(C65:E65) SUM(E58:E65) ISUM(F58:F65) =E56-E66 =F56-F66 67 68 69 71 72 73 74 lo -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 76 -F10 -SUM(C82:C83) =D80-D84 Earrings Unlimited Budgeted Income Statement 79 For the Three-months Ended June 30 80 Sales Revenue 81 Variable expenses: 82 Cost of Goods sold (219,800 units x $4.80) =F8*4.8 83 Sales Commissions ($3,077,200 x 4%) =D80*496 84 Total Variable expenses 85 Contribution Margin 86 Fixed expenses 87 Advertising =F59 88 Rent =F60 89 Salaries =F61 90 Utilities |=F63 91 Insurance ($3,800 x 3 month) 3800*3 92 Depreciation ($ 22,000 x 3 month) -22000*3 93 Interest expense --F71 94 Total Fixed expenses 95 I Net Income 96 -SUM(C87:093) =D85-D94

C D 97 Requirement 4: Budgeted Balance Sheet as of June 30 98 100 101 Earrings Unlimited Budgeted Balance Sheet June 30 102 =F73 |=F10-SUM(F19:F21) =E30*4.8 1 =25000-091 -1030000+F64-C92 1 =SUM(D103:D107) 103 Cash 104 Accounts Receivable 105 Inventory (12,640 units x $4.80) 106 Prepaid Insurance ($25,000-$11,400) 107 Property and equipment (net) ($1,030,000+ $68,000 - $66 108 Total assets 109 Liabilities and Stockholder's Equity 111 Accounts Payable 112 Dividends Payable 113 Common stock 114 Retained Earnings ($680,432 +$500,412 - $21,000) 115 Total liabilities and stockholders' equity 116 110 =E35-E45 21000 960000 =680432+D95-D112 SUM(D111:D114)