Question

The company sells many styles of earrings, but all are sold for the asame price-$10 per pair. Actual sales of earrings for the last 3 months and budgeted sales for the next 6 months follow. (in pairs of earings)

January (actual) 20,000 June (budget) 50,000

Febuary (actual) 26,000 July (budget) 30,000

March (actual) 40,000 August (budget) 28,000

April (budget) 65,000 September (budget) 25,000

May (budget) 100,000

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 months purcheses are paid for in the month of purches; 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 the months 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:

Sales commision: 4% of sales

Fixed:

Advertising: $200,00

Rent: $18,000

Salaries: $106,000

Utilities: $7,000

Insurance: $3,000

Depreciation: $14,000

Insurence is paid on an annual basis, in november of each year. The company plans to purches $16,000 in new equipment during May and $40,000 in new equipment during June: both purcheses 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 31st is given below:

   Assets

Cash: $74,000

Accounts recievable ($26,000 Febuary sales; $320,000 March Sales): 346,000

Inventory: 104,000

Prepaid Insurance: 21,000

Property and equipment (net): 950,000

Total Assets: $1,495,000

   Liabilities and stockholder equity

Accounts payable: $100,000

Dividends payable: $15,000

Common stock: 800,000

Retained Earnings: 580,000

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 the month; any repayments are made at the end of the month.

he 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 !% 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.

Prepare a master budget for the three-month period ending June 30th. 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 disbursement 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 3 month period ending June 30th. Use the contribution approach.

4.) A budgeted balance sheet as of June 30th.

Answer 1

Solution Requirement 1a. : Sales budget, by month and in total Budgeted sales units Selling price per unit Total sales Sales Budget April 65,000 $10 $650,000 May 100,000 $100 $1,000,000 June Quarter 50,000 215,000 $10_ $10 $500,000 $2,150,000 Requirement 1b. : Schedule of Expected cash collections, by month and in total May June Schedule of Expected Cash collections April $26,000 $280,000 $130,000 $0 $0 February Sales March Sales April Sales May Sales June Sales Total cash collections $40,000 $455,000 $200,000 $0 $695,000 $o Quarter $26,000 $320,000 $650,000 $900,000 $100,000 $1,996,000 $0 $65,000 $700,000 $100,000 $865,000 $0 $436,000 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 Merchandise Purchase Budget April 65,000 40,000 105,000 26,000 79,000 $4.00 $316,000 May 100,000 20,000 120,000 40,000 80,000 $4.00 $320,000 June 50,000 12,000 62,000 20,000 42,000 $4.00 $168,000 Quarter 215,000 12,000 227,000 26,000 201,000 $4.00 $804,000

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 Budgeted Cash Disbursements for Merchandise Purchases April May $100,000 $0 $ 158,000 $ 158,000 $160,000 $0 $258,000 $318,000 June $0 $0 $ 160,000 $84,000 $244,000 Quarter $100,000 $316,000 $320,000 $84,000 $820,000 $0 Requirement 2: Cash Budget May $50,000 $695,000 $745,000 June $50,000 $865,000 $915,000 Quarter $74,000 $1,996,000 $2,070,000 Cash Budget For the Three Months Ending June 30 April Beginning cash balance $74,000 Add: Collections from customers $436,000 Total cash available $510,000 Less: Cash disbursements: Merchandise purchases $258,000 Advertising $200,000 Rent $18,000 Salaries $106,000 Commissions $26,000 Utilities $7,000 Equipment purchases $0 Dividends paid $15,000 Total cash disbursements $630,000 Excess (deficiency) of cash available over disbursements ($120,000) Financing Borrowings $ 170,000 Repayments Interest Total financing $170,000 Ending cash balance $50,000 $318,000 $200,000 $18,000 $106,000 $40,000 $7,000 $16,000 $0 $705,000 $40,000 $244,000 $200,000 $18,000 $106,000 $20,000 $7,000 $40,000 $0 $635,000 $280,000 $820,000 $600,000 $54,000 $318,000 $86,000 $21,000 $56,000 $15,000 $1,970,000 $100,000 $10,000 $0 $0 $10,000 $50,000 $0 ($180,000) $5,300) ($185,300) $94,700 $180,000 ($180,000) ($5,300) ($5,300) $94,700

Requirement 3 : Budgeted Income Statement for the three-month period ending June 30 $2,150,000 $860,000 $86,000 $946,000 $1,204,000 Budgeted Income Statement For the Three-months Ended June 30 Sales Revenue Variable expenses: | Cost of Goods sold (215,000 units x $4) Sales Commissions ($2,150,000 x 4%) Total Variable expenses Contribution Margin Fixed expenses: Advertising Rent Salaries Utilities Insurance ($3,000 x 3month) Depreciation ($14,000 x 3month) Interest expense Total Fixed expenses Net Income $600,000 $54,000 $318,000 $21,000 $9,000 $42,000 $5,300 $1,049,300 $154,700 Requirement 4: Budgeted Balance Sheet as of June 30 Budgeted Balance Sheet June 30 Assets Cash Accounts Receivable Inventory (12,000 units x $4) Prepaid Insurance ($21,000 - $9,000) Property and equipment (net) ($950,000+ $56,000 - $42,000) Total assets $94,700 $500,000 $48,000 $ 12,000 $964,000 $1,618,700 Liabilities and Stockholder's Equity Accounts Payable Dividends Payable Common stock Retained Earnings ($580,000+ $154,700 - $15,000) Total liabilities and stockholders' equity $84,000 $15,000 $800,000 $719,700 $1,618,700

To see the formulas, please see the below images:

1 Solution 3 Requirement la. : Sales budget, by month and in total 5 6 June 7 8 Budgeted sales units Selling price per unit Total sales Sales Budget April May 65000 100000 10 10 =C7*08 =D7*D8 50000 10 =E7 E8 Quarter -SUM(C7:E7) 10 =F7F8 9 10 11 Requirement 1b.: Schedule of Expected cash collections, by month and in total 12 13 14 15 16 17 18 19 20 February Sales March Sales April Sales May Sales June Sales Total cash collections Schedule of Expected Cash collections April May =26000*10*10% 0 =40000*10*70% -40000*10*10% =C9*2096 =C9*70% |=D9*20% June TO o =C9 10% =D97096 -E9*20 1 =SUM(E15:E19) Quarter =SUM(C15:E15) -SUM(C16:E16) -SUM(C17:E17) | -SUM(C18:E18) -SUM(C19:E19) | =SUM(F15:F19) =SUM(C15:C19) =SUM(D15:119) 21 26 B 22 Requirement 1c. : Merchandise purchase budget in units and in dollars 23 24 Merchandise Purchase Budget 25 April May Budgeted Sales Units 1=C7 =D7 27 Add: Desired ending inventory =D7*40% =E7*40% 28 Total needs -C26+C27 =D26+D27 29 Less: Likely beginning inventory =C264096 =D26*4096 30 Required purchases (in units) =C28-C29 |=D28-D29 31 Unit cost 32 Required dollar purchases =C30*C31 =D30*D31 June =E7 =30000*40% =E26+27 =E26*40% =E28-E29 Quarter -SUM(C26:E26) =E27 =F26+F27 =C29 =F28-F29 =E30*E31 =F30+F31 33

34 35 Requirement 1d.: Schedule of expected cash disbursements for merchandise purchases, by month and in total 36 37 38 39 Budgeted Cash Disbursements for Merchandise Purchases April May June 100000 -C32/2 =C32/2 =D32/2 =D32/2 =E32/2 -SUM(C38:041) SUM(D38:041) =SUM(E38:E41) Accounts Payable April purchases May purchases June purchases Total cash payments 40 41 42 43 Quarter =SUM(C38:38) -SUM(C39:E39) -SUM(C40:140) SUM(C41:E41) =SUM(F38:F41) | Requirement 2 : Cash Budget 48 49 50 51 52 June =D68 -E20 =SUM(E49:E50) Quarter |=C49 -SUM(C50:E50) -SUM(F49:F50) 53 54 55 56 Cash Budget For the Three Months Ending June 30 April May Beginning cash balance 74000 =C68 Add: Collections from customers =C20 =D20 Total cash available |-SUM(C49:C50) T-SUM(D49:050) Less: Cash disbursements: Merchandise purchases |=C42 I=D42 Advertising 200000 200000 Rent 18000 18000 Salaries 106000 106000 Commissions =C9*496 =D9*496 Utilities 7000 7000 Equipment purchases 16000 Dividends paid 15000 Total cash disbursements -SUM(C53:C60) I-SUM(D53:060) Excess (deficiency) of cash available over disbursements =C51-C61 =D51-061 Financing: Borrowings = 120000+50000 10000 Repayments Interest Total financing =SUM(C64:C66) -SUM(D64:D66) Ending cash balance =C62+C67 |=D62+D67 =E42 200000 18000 106000 =E9*496 7000 40000 57 58 59 -SUM(C53:E53) -SUM(C54:E54) | -SUM(C55:E55) -SUM(C56:E56) -SUM(C57:E57) =SUM(C58:E58) -SUM(C59:E59) -SUM(C60:E60) -SUM(F53:F60) =F51-F61 60 61 62 63 -SUM(E53:160) =E51-E61 64 65 66 67 68 -SUM(C64:E64) --C64-064 -SUM(C65:165) --(C64 1963HD64.1962) SUM(C66:E66) -SUM(E64:E66) -SUM(F64:F66) =E62+E67 =F62+F67 69

72 75 78 79 70 Requirement 3 : Budgeted Income Statement for the three-month period ending June 30 71 Budgeted Income Statement 73 For the Three-months Ended June 30 74 Sales Revenue =F9 Variable expenses: 76 | Cost of Goods sold (215,000 units x $4) =F7*4 77 Sales Commissions ($2,150,000 x 4%) =D74*4% Total Variable expenses -SUM(C76:077) Contribution Margin =D74-D78 80 Fixed expenses: 81 Advertising =F54 Rent =F551 Salaries =F56 84 Utilities =F58 85 Insurance ($3,000 x 3month) =300003 86 Depreciation ($ 14,000 x 3month) =1400003 Interest expense =-F66 88 Total Fixed expenses -SUM(C81:C87) 89 Net Income =D79-188 90 82 83 87

91 Requirement 4: Budgeted Balance Sheet as of June 30 Budgeted Balance Sheet June 30 Assets Cash 97 Accounts Receivable 98 Inventory (12,000 units x $4) 99 Prepaid Insurance ($21,000-$9,000) 100 Property and equipment (net) ($950,000+$56,000-$42,0 101 Total assets 102 103 Liabilities and Stockholder's Equity Accounts Payable 105 Dividends Payable 106 Common stock 107 Retained Earnings ($580,000+$154,700 - $15,000) 108 Total liabilities and stockholders' equity =F68 =F9-SUM(F17:F19) -E274 =21000-C85 -950000+F59-C86 =SUM(D96:D100) =E32-E41 15000 800000 =580000+D89-D105 SUM(D104:D107)