Question

You have just been contracted as a new management trainee by Earrings Unlimited, a distributor of earrings to various retail outlets 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 - $10 per pair. Actual sales of earrings for the last three months and budgeted sales for the next six months follow:

January (actual)	20,000
February (actual)	26,000
March (actual)	40,000
April (budget)	65,000
May (budget)	100,000
June (budget)	50,000
July (budget)	30,000
August (budget)	28,000
September (budget)	25,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 bracelets sold in the following month.

Variable Expenses:
Sales Commissions	4% of sales
Fixes expenses:
Advertising	$200,000
Rent	$18,000
Salaries	$106,000
Utilities	$7,000
Insurance	$3,000
Depreciation	$14,000

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

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

Other relevant data is given below:
Cash balance as of March 31	$74,000
Inventory balance as of March 31	$104,000
Merchandise purchases for March	$200,000

The company maintains a minimum cash balance of at least $50,000 at the end of each month. 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 the exact amount needed 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 will pay the bank all of the accrued interest on the loan and as much of the loan as possible while still retaining at least $50,000 in cash.

Required:

Prepare a cash budget for the three-month period ending June 30. Include the following detailed budgets:

1) 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.

Answer 1

Sales Budget:	Apr	May	June	Qtr
Sales(units)	65000	100000	50000	215000
Selling price	10	10	10	10
Total Sales	650000	1000000	500000	2150000
Schedule of expected Collections:			Apr	May	June	Qtr
Feburary Sales	260000	10%	26000			26000
March sales	400000	70/10%	280000	40000		320000
April sales	650000	20/70/10%	130000	455000	65000	650000
May sales	1000000	20/70%		200000	700000	900000
June Sales	500000	20%			100000	100000
Total Cash Collections			436000	695000	865000	1996000
Merchandise Purchase Budget	Apr	May	June	Qtr
Budgeted unit sales	65000	100000	50000	215000
Add:desired ending inventory(40%)	40000	20000	12000	12000
Total Needs	105000	120000	62000	227000
Less:Opening Inventory	26000	40000	20000	26000
Required purchases	79000	80000	42000	201000
Cost of purchases @4	316000	320000	168000	804000
June ending inventory = July slaes * 40%
=30000*40%=12000
Budgeted Cash Disbursements for purchases
	Apr	May	June	Qtr
Accounts Payable	100000			100000
April Purchases	158000	158000		316000
May purchases		160000	160000	320000
June Purchases			84000	84000
Total cash Payaments	258000	318000	244000	820000
Cash Budget
	Apr	May	June	Qtr
Cash Balance	$                74,000	$               50,000	$                50,000	$                    74,000
Add:collections form customers	$              436,000	$             695,000	$              865,000	$              1,996,000
Total Cash Available	$              510,000	$             745,000	$              915,000	$              2,070,000
Less:Disbursements:
Merchandise purchase	$              258,000	$             318,000	$              244,000	$                  820,000
Advertising	$              200,000	$             200,000	$              200,000	$                  600,000
Rent	$                18,000	$               18,000	$                18,000	$                    54,000
Salaries	$              106,000	$             106,000	$              106,000	$                  318,000
Commissions(4% *sales)	$                26,000	$               40,000	$                20,000	$                    86,000
Utilities	$                  7,000	$                  7,000	$                  7,000	$                    21,000
Equipment		$               16,000	$                40,000	$                    56,000
Dividends Paid	$                15,000			$                    15,000
Total Disbursements	$              630,000	$             705,000	$              635,000	$              1,970,000
Excess(deficiency) of receipts	$           (120,000)	$               40,000	$              280,000	$                  100,000
Financing
Borrowings	$              170,000	$               10,000		$                  180,000
Repayments			$           (180,000)	$               (180,000)
Interest			$                (5,300)	$                    (5,300)
Total Financing	$              170,000	$               10,000	$           (185,300)	$                    (5,300)
Cas Balance , Ending	$                50,000	$               50,000	$                94,700	$                    94,700
Interest=	170000*12%*3/12=		5100
	100000*12%*2/12=		200
			5300