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 comprehensive budgets for the upcoming second quarter in order to show management the benefits that can be gained from an integrated budgeting program. 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—$19 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) 24,000 June (budget) 54,000 February (actual) 30,000 July (budget) 34,000 March (actual) 44,000 August (budget) 32,000 April (budget) 69,000 September (budget) 29,000 May (budget) 104,000 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 $6 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, with no discount, and payable within 15 days. The company has found, however, that 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: Sales commissions 4% of sales Fixed: Advertising $ 400,000 Rent $ 38,000 Salaries $ 146,000 Utilities $ 17,000 Insurance $ 5,000 Depreciation $ 34,000 Insurance is paid on an annual basis, in November of each year. The company plans to purchase $26,000 in new equipment during May and $60,000 in new equipment during June; both purchases will be for cash. The company declares dividends of $30,000 each quarter, payable in the first month of the following quarter. A listing of the company’s ledger accounts as of March 31 is given below: Assets Cash $ 94,000 Accounts receivable ($57,000 February sales; $668,800 March sales) 725,800 Inventory 165,600 Prepaid insurance 31,000 Property and equipment (net) 1,150,000 Total assets $ 2,166,400 Liabilities and Stockholders’ Equity Accounts payable $ 120,000 Dividends payable 30,000 Common stock 1,200,000 Retained earnings 816,400 Total liabilities and stockholders’ equity $ 2,166,400 The company maintains a minimum cash balance of $70,000. All borrowing is done at the beginning of a month; any repayments are made at the end of a month. The 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 1% 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 $70,000 in cash. Required: 1. Prepare a master budget for the three-month period ending June 30. Include the following detailed budgets: 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. (Round "Unit cost" answers to 2 decimal places.) d. A schedule of expected cash disbursements 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 $70,000 (Cash deficiency, repayments and interest should be indicated by a minus sign.) 3. A budgeted income statement for the three-month period ending June 30. Use the contribution approach. 4. A budgeted balance sheet as of June 30.

Answer 1

(a) Sales Budget
	April	May	June	Total
Budgeted Sales - units	69000	104000	54000	227000
Selling Price per unit	19	19	19	19
Budgeted Sales	1311000	1976000	1026000	4313000
(b)   Schedule of cash receipts
	April	May	June	Total
Credit Sales
February sales	57000			57000
March Sales	585200	83600		668800
April Sales	262200	917700	131100	1311000
May Sales		395200	1383200	1778400
June Sales			205200	205200
Total Collections	904400	1396500	1719500	4020400
Receivables	1132400	1711900	1018400	1018400
(c) Inventory Purchase Budget
	April	May	June	Total
Budgeted sales units	69000	104000	54000	227000
Add: Desired ending inventory	41600	21600	12000	12000
(40% of next month's cost)
Total goods neded	110600	125600	66000	239000
Less: Beginning inventory	27600	41600	21600	27600
Budgeted purchase units	83000	84000	44400	211400
Cost per unit of inventory ($)	6	6	6	6
Total cost of purchases	498000	504000	266400	1268400
(d) Cash payment budget for purchases
	April	May	June	Total
Budgeted Purchases	498000	504000	266400	1268400
Payments:
March purchases	120000			120000
April purchases	249000	249000		498000
May purchases		252000	252000	504000
June purchases			133200	133200
Total payments	369000	501000	385200	1255200
Accounts Payable	249000	252000	133200	133200
(e) Operating expenses budget
	April	May	June	Total
Sales commission(4% of sales)	52440	79040	41040	172520
Advertising expense	400000	400000	400000	1200000
Rent expense	38000	38000	38000	114000
Salaries expense	146000	146000	146000	438000
Utilities expense	17000	17000	17000	51000
Insurance expense	5000	5000	5000	15000
Depreciation expense	34000	34000	34000	102000
Total operating expense	692440	719040	681040	2092520
Schedule of cash payments for operating expenses
	April	May	June	Total
Sales commission(4% of sales)	52440	79040	41040	172520
Advertising expense	400000	400000	400000	1200000
Rent expense	38000	38000	38000	114000
Salaries expense	146000	146000	146000	438000
Utilities expense	17000	17000	17000	51000
Cash payment for operating expense	653440	680040	642040	1975520
EARRINGS LIMITED
Cash Budget
	April	May	June	Total
Beginning Balance	94000	70960	132920	94000
Add: Cash receipts	904400	1396500	1719500	4020400
Cash available for disbursements	998400	1467460	1852420	4114400
Less: Payments
for purchase of inventory	369000	501000	385200	1255200
For operating expenses	653440	680040	642040	1975520
for purchase of equipment		26000	60000	86000
For dividend	30000			30000
for interest		2500		2500
Total payments	1052440	1209540	1087240	3349220
Receipts minus payments	-54040	257920	765180	765180
Minimum cash balance	70000	70000	70000	51000
Excess / (Shortage)	-124040	187920	695180	714180
Financing activitiy
Borrowing / (Repayments)	125000	-125000		0
Repayments			0	0
Total Financing	125000	-125000	0	0
Ending cash balance	70960	132920	765180	765180
EARRINGS LIMITED
Contribution margin income statement
For the quarter ending June 30
Sales Revenue		4313000
Variable expenses:
Cost of goods sold	1362000
Sales commission	172520
Total variable expenses		1534520
Contribution margin		2778480
Fixed expenses:
Advertising expense	1200000
Rent expense	114000
Salaries expense	438000
Utilities expense	51000
Insurance expense	15000
Depreciation expense	102000
total operating expenses		1920000
Net operating income		858480
Interest expense		2500
Net income		855980
EARRINGS LIMITED
Budgeted Balance sheet
as at June 30
Assets
Cash		765180
Accounts Receivable		1018400
Inventory		72000
Prepaid insurance		16000
Property and equipment , net
Beginning balance, net	1150000
Add: Purchases	86000
	1236000
Less: Depreciation	102000	1134000
Total Assets		3005580
Liabilities and owners' equity
Accounts Payable		133200
Dividend Payable		30000
Total liabilities		163200
Capital stock		1200000
Retained earnings
Balance as at April 1	816400
Net income	855980
	1672380
Dividends	30000	1642380
Total Liabilities and equity		3005580