XYZ is a distributor of earrings to retail outlets in shopping malls across the US. The company has had cash shortages in the past due to insufficient budgeting. As the newly hired management trainee, you have taken the initiative to prepare a master budget for the second quarter by collaborating with the accounting and other relevant departments. The company sells various styles of earrings at a fixed price of $20 per pair. The company has provided information on actual and budgeted sales for the next six month, which is reported below:

January (actual) February (actual) March (actual) April (budgeted) May (budgeted)

40,000 June (budgeted) 52,000 July (budgeted) 80,000 August (budgeted) 130,000 September (budgeted) 200,000

100,000 60,000 56,000 50,000

The significant concentration of sales expected before and during May, which is Mother's Day. The company should maintain enough inventory at the end of each month to fulfill 40% of the following month's sales. The suppliers are paid $8 for a pair of earrings, with half of the purchases paid for in the month of purchase and the remaining half paid for in the following month. All sales are on credit, and the company collects only 20% of sales in the same month, with 70% collected in the following month, and the remaining 10% collected in the second month following sale. Bad debts have not been an issue in the past. The monthly operating expenses for the company are also listed.:

Variable: Sales commissions 4% of sales

Fixed: Advertising . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $400,000

Rent . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $36,000 Salaries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $212,000 Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $14,000 Insurance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $6,000 Depreciation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $28,000

The insurance premium is paid annually in November. The company intends to buy $32,000 worth of new equipment in May and $80,000 worth of new equipment in June, both of which will be paid in cash. The company declares dividends of $30,000 each quarter, which will be payable in the first month of the subsequent quarter.

The companys balance sheet as of March 31 is given below:

Assets

Cash. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $ 148,000 Accounts receivable ($52,000 February sales; $640,000 March sales) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 692,000 Inventory. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208,000 Prepaid insurance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42,000 Property and equipment (net) . . . . . . . . . . . . . . . . . . . . . . . . . . 1900,000 Total assets. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $2,990,000 Liabilities and Stockholders Equity

Accounts payable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $ 200,000 Dividends payable. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30,000 Common stock. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1600,000 Retained earnings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1160,000 Total liabilities and stockholders equity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . $2,990,000

The company has a policy of keeping a minimum cash balance of $100,000. All borrowing is done at the start of a month, and any repayments are made at the end of the month. The company has an agreement with a bank that permits borrowing in $2,000 increments at the beginning of each month, with an interest rate of 1% per month. Interest is assumed to be non-compounding. At the end of the quarter, the company will pay the bank all of the interest accumulated on the loan and as much of the loan as possible (in increments of $2,000), while still retaining at least $100,000 in cash.

UPDATE: the beginning inventory of April should be the ending inventory value of March (from the balance sheet) divided by the unit purchase price 208000/8=26000 units.

Required: 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 $100,000.