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—$10 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) 20,000 June (budget) 50,000 February (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 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 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 $ 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. The company’s balance sheet as of March 31 is given below: Assets Cash $ 74,000 Accounts receivable ($26,000 February 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 Stockholders’ 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 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 $50,000 in cash. Required: Prepare a master budget for the three-month period ending June 30. Include the following detailed schedules: 1. a. A sales budget, by month and in total. b. A schedule of expected cash collections, 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 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 $50,000. 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

Answer a
Sales Budget
	April	May	June	Total
Sales in Units	65,000	100,000	50,000	215,000
Sp Per Unit	10	10	10	10
Total Sales in $	650,000	1,000,000	500,000	2,150,000
Answer b
Schedule of Expected Cash Collections from Sales
	April	May	June	Total
Collection from Accounts Receivables
Feb Sales	26,000			26,000
March Sales	280,000	40,000	-	320,000
April Sales	130,000	455,000	65,000	650,000
May Sales		200,000	700,000	900,000
June Sales Sales			100,000	100,000
Total cash Collections	436,000	695,000	865,000	1,996,000
Answer c
Merchandise Purchase Budget
	April	May	June	Total
Sales In units	65,000	100,000	50,000	215,000
Add: Closing Inventory in units	40,000	20,000	12,000	12,000
Total Needs	105,000	120,000	62,000	227,000
Less: opening Inventory in uints	(26,000)	(40,000)	(20,000)	26,000
Required Purchases in Units	79,000	80,000	42,000	253,000
Price per paid of Earings	4.00	4.00	4.00	4.00
Total Purchases in $	316,000	320,000	168,000	804,000
Answer d
Schedule of Cash payments to Suppliers
	April	May	June	Total
Cash Payment
Accounts Payable - March	100,000			100,000
April Purchases	158,000	158,000		316,000
May Purchases		160,000	160,000	320,000
June Purchases			84,000	84,000
Total Cash Payment to Suppliers	258,000	318,000	244,000	820,000
Answer e
Selling & Admn. Budget
	April	May	June	Total
Sales Comm. - 4%	26,000	40,000	20,000	86,000
Fixed
Advt.	200,000	200,000	200,000	600,000
Rent (Fixed)	18,000	18,000	18,000	54,000
Salary Expense	106,000	106,000	106,000	318,000
Utilities	7,000	7,000	7,000	21,000
Insurance	3,000	3,000	3,000	9,000
Dep.	14,000	14,000	14,000	42,000
Total	374,000	388,000	368,000	1,130,000
Schedule of Cash payments of Selling & Admn. Budget
	April	May	June	Total
Sales Comm. - 4%	26,000	40,000	20,000	86,000
Fixed
Advt.	200,000	200,000	200,000	600,000
Rent (Fixed)	18,000	18,000	18,000	54,000
Salary Expense	106,000	106,000	106,000	318,000
Utilities	7,000	7,000	7,000	21,000
Total	357,000	371,000	351,000	1,079,000
Cash budget
	April	May	June	Total
Opening cash Balance	74,000	50,000	50,000	74,000
Add: receipts
Collection from Customers	436,000	695,000	865,000	1,996,000
Total Cash available	510,000	745,000	915,000	2,070,000
Less: Disbursements
Cash Disbursement - Accounts Payable	258,000	318,000	244,000	820,000
Selling & Admn. Exp.	357,000	371,000	351,000	1,079,000
Purchase of Equipment	-	16,000	40,000	56,000
Dividend Paid	15,000	-	-	15,000
Total Disbursement	630,000	705,000	635,000	1,970,000
Cash Balance Closing	(120,000)	40,000	280,000	100,000
Add: Finance from Bank	170,000	10,000		180,000
Less: Payment to Bank	-		(180,000)	(180,000)
Less: Payment of interet - Bank loan	-		(5,400)	(5,400)
Net Cash Balance Closing	50,000	50,000	94,600	94,600
Income Statement
For the Qtr Ending June 30
Sales		2,150,000
Less: Variable Cost
Cost of Goods Sold	860,000
Sales Comm. - 4 % of Sales	86,000	946,000
Contribution		1,204,000
Less: Fixed Cost
Advt.	600,000
Rent (Fixed)	54,000
Salary Expense	318,000
Utilities	21,000
Insurance	9,000
Dep.	42,000	1,044,000
Operating Profit		160,000
Less: Interest Expenses		5,400
Net Income		154,600
Balance Sheet
As on June 30
Assets
Current Assets
Cash	94,600
Accounts receivables	500,000
Prepaid Insurance	12,000
Inventory	48,000	654,600
Fixed Assets
Property & Equipment	1,006,000
Less: Dep.	(42,000)	964,000
Total Assets		1,618,600
Liabilities
Accounts Payable	84,000
Dividends Payable	15,000
Total liabilities		99,000
Shareholders's Equity
Common Stock	800,000
Retained Earnings	719,600
Total Stockholders equity		1,519,600
Total liabilities & Stockholders' Equity		1,618,600		-
Schedule of Retained Earnings
As on June 30
Opening Balance		580,000
Add: net income		154,600
Less: Dividend declared		(15,000)
Closing Balance		719,600