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—$13 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) 21,400 June (budget) 51,400

February (actual) 27,400 July (budget) 31,400

March (actual) 41,400 August (budget) 29,400

April (budget) 66,400 September (budget) 26,400

May (budget) 101,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 $4.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.

Monthly operating expenses for the company are given below:

Variable:

Sales commissions 4 % of sales

Fixed:

Advertising $ 270,000

Rent $ 25,000

Salaries $ 120,000

Utilities $ 10,500

Insurance $ 3,700

Depreciation $ 21,000

Insurance is paid on an annual basis, in November of each year.

The company plans to purchase $19,500 in new equipment during May and $47,000 in new equipment during June; both purchases will be for cash. The company declares dividends of $20,250 each quarter, payable in the first month of the following quarter.

The company’s balance sheet as of March 31 is given below:

Assets

Cash $ 81,000

Accounts receivable ($35,620 February sales; $430,560 March sales) 466,180

Inventory 124,832

Prepaid insurance 24,500

Property and equipment (net) 1,020,000

Total assets $ 1,716,512

Liabilities and Stockholders’ Equity

Accounts payable $ 107,000

Dividends payable 20,250

Common stock 940,000

Retained earnings 649,262

Total liabilities and stockholders’ equity $ 1,716,512

The company maintains a minimum cash balance of $57,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 $57,000 in cash.

Requirement:

4. A budgeted balance sheet as of June 30.

Answer 1

Expected Sales budget:
	April	May	June	Quarter
Budgeted unit sales	66400	101400	51400	219200
Selling price per unit	13	13	13	13
Total sales	863200	1318200	668200	2849600
Expected Cash collection budget:
	April	May	June	Quarter	AR Balance
collections:
Feb	35620			35620
Mar	376740	53820		430560
Apr	172640	604240	86320	863200
May		263640	922740	1186380	131820
June			133640	133640	534560
Total	585000	921700	1142700	2649400	666380
Expected merchandise purchases budget:
	April	May	June	Quarter
Budgeted unit sales	66400	101400	51400	219200
add:C. Stock	40560	20560	12560	12560
total need	106960	121960	63960	231760
less:O. Stock	26560	40560	20560	26560
Expected Purchases	80400	81400	43400	205200
Purchase value	377880	382580	203980	964440
Expected Disbursement for Purchases:
	April	May	June	Quarter	AP ending
Payments:
March	107000			107000
April	188940	188940		377880
May		191290	191290	382580
June			101990	101990	101990
Total	295940	380230	293280	969450
Exp. Cash Budget:
	April	May	June	Quarter
Op Bal.	81000	57782	57000	81000
Add:Collections	585000	921700	1142700	2649400
cash available	666000	979482	1199700	2730400
Less:Disbursements:
For purchases	295940	380230	293280	969450
For commis.	34528	52728	26728	113984
For advert.	270000	270000	270000	810000
For rent	25000	25000	25000	75000
For Salaries	120000	120000	120000	360000
For Utilities	10500	10500	10500	31500
For equip		19500	47000	66500
For Dividend	20250			20250
Total Disbursements	776218	877958	792508	2446684
excess of cash over disbur.	-110218	101524	407192	283716
Borrowings:	168000			168000
Repayments		-42844	-125156	-168000
Interests		-1680	-1252	-2932	168000*1%	(168000-42844)*1%
Cl Bal.	57782	57000	280784	280784
Exp. Income Statement for quarter ended June 30:
Sales Revenue	2849600
COGS	1030240
Gross Profit	1819360
Less: expenses:
commis.	113984
advert.	810000
rent	75000
Salaries	360000
Utilities	31500
Depreciation	63000
Insurance	11100
Interest	2932
Total expenses	1467516
Net Income	351844
Expected Balance sheet as on June 30:
Assets	amount $
Cash	280784
AR	666380
Inventory	59032
Prepaid Ins.	13400
PPE, net	1023500	1020000+66500-63000
total Assets	2043096
Liabilities:	amount $
AP	101990
Divi Payable	20250
CS	940000
RE:
O Bal.	649262
Net Income	351844
Less:dividend	-20250
Total Liabilities	2043096