Question

Vonn Company, a furniture store, prepares its master budget on a quarterly basis. The following data have been assembled to assist in preparing the master budget for the quarter:

• As of the end of the prior quarter, September 30, the company’s general ledger showed the following account balances:
  • Cash $62,000 (debit)
  • Accounts receivable $480,000 (debit)
  • Inventory $78,000 (debit)
  • Buildings and equipment, net $570,000 (debit)
  • Accounts payable $193,000 (credit)
  • Capital stock $300,000 (credit)
  • Retained earnings $619,000 (credit)
• Actual sales for September and budgeted sales for the next four months are as follows: September $280,000, October $400,000, November $600,000, December $300,000 and January $200,000.
• Sales are 20% for cash and 80% on credit. All payments on credit sales are collected in the month following sale. The accounts receivable at December 31 are a result of December credit sales.
• The company’s gross margin is 40% of sales. (In other words, cost of goods sold is 60% of sales.)
• Monthly expenses are budgeted as follows: salaries and wages, $27,000 per month; advertising, $70,000 per month; shipping, 5% of sales; other expenses, 3% of sales. Depreciation, including depreciation on new assets acquired during the quarter, will be $42,000 per quarter.
• Each month’s ending inventory should equal 25% of the following month’s cost of goods sold.
• One half of the month’s inventory purchases is paid for in the month of purchase; the other half is paid in the following month.
• During November, the company will purchase a new copy machine for $1,700 cash. During December, other equipment will be purchased for cash at a cost of $84,500.
• During October, the company will declare and pay $45,000 in cash dividends.
• Management wants to maintain a minimum cash balance of $30,000. The company has an agreement with a local 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. The company would, as far as it is able, repay the loan plus accumulated interest at the end of the quarter.

Required tasks:

Using the data above, populate the following statements and schedules for the October-December quarter. Submit your responses in an Excel spreadsheet neatly organized. If desired, you may modify the schedule formats as necessary to show your work as long as all of the content is included.

Schedule of Expected Cash Collections
	October	November	December	Quarter
Cash sales
Credit sales
Total Collections

Merchandise Purchases Budget
	October	November	December	Quarter
Budgeted Cost of Goods Sold
Add desired ending inventory
Total needs
Less beginning inventory
Required purchases

Schedule of Expected Cash Disbursements-Merchandise Purchases
	October	November	December	Quarter
September purchases
October purchases
November purchases
December purchases
Total disbursements

Schedule of Expected Cash Disbursements-Selling and Administrative Expenses
	October	November	December	Quarter
Salaries and wages
Advertising
Shipping
Other expenses
Total disbursements

Cash Budget
	October	November	December	Quarter
Cash balance, beginning
Add cash collections
Total cash available
Less cash disbursements
For inventory
For selling & admin exp.
For purchase of equipment
For cash dividends
Total cash disbursements
Excess (deficiency) of cash
Financing needed
Borrowings
Repayments
Interest
Net borrowings
Cash balance, ending

1. Schedule of expected cash collections
2. Merchandise purchases budget
3. Schedule of expected cash disbursements for merchandise purchases
4. Schedule of expected cash disbursements for selling and administrative expenses
5. Cash budget:

Answer 1

Schedule of Expected Cash Collections October November December Quarter Cash Sales 80,000 120,000 60,000 260,000 Credit Sales 480,000 320,000 480,000 1,280,000 Total Collections 560,000 440,000 540,000 1,540,000 Merchandise Purchases Budget October November December Quarter Budgeted Cost of Goods Sold 240,000 360,000 180,000 780,000 Add: Desired Ending Invento 90,000 45,000 30,000 30,000 Total Needs 330,000 405,000 210,000 810,000 Less: Beginning Invento 78,000 90,000 45,000 78,000 Required Purchases 252,000 315,000 165,000 732,000 Schedule of Expected Cash Disbursements - Merchandise Purchases October November December Quarter September Purchases 193,000 193,000 October Purchases 126,000 126,000 252,000 November Purchases 157,500 157,500 315,000 December Purchases 82,500 82,500 Total Disbursements 319,000 283,500 240,000 842,500

Schedule of Expected Cash Disbursements Selling and Administrative Expenses October November December Quarter Salaries and Wages 27,000 27,000 27,000 81,000 Advertisin 70,000 70,000 70,000 210,000 Shippin 20,000 30,000 15,000 65,000 Other Expenses 12,000 18,000 9,000 39,000 Total Disbursements 129,000 145,000 121,000 395,000

Cash Budget October November December Quarter Cash Balance, Beginnin 62,000 129,000 138,800 62,000 Add: Cash Collections 560,000 440,000 540,000 1,540,000 Total Cash Available 622,000 569,000 678,800 1,602,000 Less: Cash Disbursements: For Invento 319,000 283,500 240,000 842,500 For Selling and Admin. Expense 129,000 145,000 121,000 395,000 For Purchase of Equipment 1,700 84,500 86,200 For Cash Dividends 45,000 45,000 Total Cash Disbursements 1,368,700 493,000 430,200 445,500 Excess (Deficiency) of Cash 129,000 138,800 233,300 233,300 Financing Needed Borrowings Repayments Interest Net Borrowings Cash Balance, Endin 129,000 138,800 233,300 233,300

Answer 2

The other answer made an error in cash sales that messed up the rest of the sheet. Here are the correct numbers:

Schedule of Expected Cash Collections
	October	November	December	Quarter
Cash sales	80,000	120,000	60,000	260,000
Credit sales	224,000	320,000	480,000	1,024,000
Total Collections	304,000	440,000	540,000	1,284,000

Merchandise Purchases Budget
	October	November	December	Quarter	Jan
Budgeted Cost of Goods Sold	240,000	360,000	180,000	780,000	120,000
Add desired ending inventory	90,000	45,000	30,000	165,000
Total needs	330,000	405,000	210,000	945,000
Less beginning inventory	78,000	90,000	45,000	213,000
Required purchases	252,000	315,000	165,000	732,000

Schedule of Expected Cash Disbursements-Merchandise Purchases
	October	November	December	Quarter
September purchases	193,000			193,000
October purchases	126,000	126,000		252,000
November purchases		157,500	157,500	315,000
December purchases			82,500	82,500
Total disbursements	319,000	283,500	240,000	842,500

Schedule of Expected Cash Disbursements-Selling and Administrative Expenses
	October	November	December	Quarter
Salaries and wages	27,000	27,000	27,000	81,000
Advertising	70,000	70,000	70,000	210,000
Shipping	20,000	30,000	15,000	65,000
Other expenses	12,000	18,000	9,000	39,000
Total disbursements	129,000	145,000	121,000	395,000

Cash Budget
	October	November	December	Quarter
Cash balance, beginning	62,000	30,410	30,000	62,000
Add cash collections	304,000	440,000	540,000	1,284,000
Total cash available	366,000	470,410	570,000	1,406,410
Less cash disbursements
For inventory	319,000	283,500	240,000	842,500
For selling admin exp.	129,000	145,000	121,000	395,000
For purchase of equipment	0	1,700	84,500	86,200
For cash dividends	45,000	0	0	45,000
Total cash disbursements	493,000	430,200	445,500	1,368,700
Excess (deficiency) of cash	-127,000	40,210	124,500	37,710
Financing needed	157,000	0	0	157,000
Borrowings	159,000	0	0	159,000
Repayments		10,210	94,500	104,710
Interest	1,590	0	0	1,590
Net borrowings	157,410	148,790	54,290	360,490
Cash balance, ending	30,410	30,000	30,000	30,000