Question

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.00 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	$	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.

The company’s balance sheet 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:

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

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 $70,000.

2. A budgeted income statement for the three-month period ending June 30. Use the contribution approach.

3. A budgeted balance sheet as of June 30.

Answer 1

Cash Budget
				April		May	June	2nd Quarter
	Beginning Balance			94000		70960	260420	94000
	Cash receipts from customers			904400		1396500	1719500	4020400
	Total cash available			998400		1467460	1979920	4114400
	Cash payments
	Disbursement to suppliers in cash			369000		501000	390000	1260000
	Dividends			30000				30000
	Equipement purchase					26000	60000	86000
	Variable Sales commissions			52440		79040	41040	172520
	Fixed cash operating Income			601000		601000	601000	1803000
	Total cash payments			1052440		1207040	1092040	3351520
	Surplus/(Deficiet)			-54040		260420	887880	762880
	Minimum Cash balance required			70000		70000	70000	70000
	Borrowing(Repayment) of Loan			125000		0	-125000	0
	Interest paid						3750	3750
	Ending Cash balance			70960		260420	759130	759130
	Budgeted Income statement for the three months period ending June 30.
	Revenue from sales		4313000
	Less :
	Cost of goods sold
	Beginning inventory	165600
	Add : purchase of inventory	1278000
	Total inventory available	1443600
	Less : Ending Inventory	81600
	Cost of goods sold		1362000
	Gross profit		2951000
	Operating expenses
	Variable selling commissions	172520
	Advertising	1200000
	Rent	114000
	Salaries	438000
	Utilities	51000
	Insurance	15000
	Deprecation	102000
	Total operating expenses		2092520
	Operating profit		858480
	Less ; Interest expense		3750
	Net income		854730
	Balance sheet as of June 30.
	Assets
	Cash	759130
	Accounts receivable	1018400
	Inventory	81600
	Prepaid Insurance	16000
	Property & equipment	1134000
	Total Assets	3009130
	Liabilities & stockholder's equity
	Accounts payable	138000
	Dividends payable	30000
	Common stock	1200000
	Retained earnings	1641130
	Total liabilities & stockholder's equity	3009130

Monthly Sales Budgets				April		May	June	2nd Quarter
Sales in units				69000		104000	54000	227000
Sales price				19		19	19
Sales Revenue in				1311000		1976000	1026000	4313000
Monthly Mechandise Purchase Budgets				April		May	June	2nd Quarter
Sales in units				69000		104000	54000	227000
Plus Desired ending inventory				41600		21600	13600	21000
Total needed				110600		125600	67600	248000
Less Beginning Inventory				27600		41600	21600	16000
Number of units to produce				83000		84000	46000	213000
Purchase price of each pair of earrings				6		6	6	6
Purchase Value				498000		504000	276000	1278000
Calculation of cash receipts from customers
Monthly Sales Budgets				April		May	June	2nd Quarter
Sales Revenue in				1311000		1976000	1026000	4313000
Credit sales from
Credit sales made in the same month				262200		395200	205200	862600
Credit sales made one month ago				585200		917700	1383200	2886100
Credit sales made two month ago				57000		83600	131100	271700
Total cash collection				904400		1396500	1719500	4020400
Calculation of payment to suppliers
				April		May	June	2nd Quarter
Total cost of purchases				498000		504000	276000	1278000
Cash Payment for current month's purchases				249000		252000	138000	639000
Cash Payment for prior month's purchases				120000		249000	252000	621000
Total Cash payment				369000		501000	390000	1260000