Question

The Williams Supply Company sells for $50 one product that it purchases for $20. Budgeted sales in total dollars for the year are $3,000,000. The sales information needed for preparing the July budget follows:

Month Sales Revenue
May $175,000
June 240,000
July 295,000
August 320,000

Account balances at July 1 include these:

Cash $125,000
Merchandise inventory 47,200
Accounts receivable (sales) 84,530
Accounts payable (purchases) 47,200

The company pays for one-half of its purchases in the month of purchase and the remainder in the following month. End-of-month inventory must be 40% of the budgeted sales in units for the next month. A 2% cash discount on sales is allowed if payment is made during the month of sale. Experience indicates that 60% of the billings will be collected during the month of sale, 25% in the following month, 12% in the second following month, and 3% will be uncollectible. Total budgeted selling and administrative expenses (excluding bad debts) for the fiscal year are estimated at $1,200,000, of which three-fourths is fixed expense (inclusive of a $36,000 annual depreciation charge). Fixed expenses are incurred evenly during the year. The other selling and administrative expenses vary with sales. Expenses are paid during the month incurred.

Required:
a. Prepare a schedule of estimated cash collections for July.
b. Prepare a schedule of estimated July cash payments for purchases.
c. Prepare schedules of July selling and administrative expenses, separately identifying those requiring cash disbursements.
d. Prepare a schedule of cash receipts over disbursements assuming no equipment purchases or loan payments.

Answer 1

The Williams Supply Company

a. Estimated Cash Collections for July

58% sales month (60% -2%) $171,100 ($295,000 * 58%) July

25% ffg month 60,000 ($240,000 * 25%) June

12% second month 21,000 ($175,000 * 12%) May

Estimated cash collections = $252,100

b. Estimated July Cash Payments for Purchases:

July

Cost of purchases $122,000

50% purchase month 61,000

50% ffg month 47,200

Total payment for purchases $108,200

c. July Selling and Administrative Expenses:

Monthly fixed expenses $72,000

Variable expenses ($5 * 5,900) 29,500

Total selling and admin expenses $101,500

d. Cash Receipts Over Disbursements for July:

Beginning cash balance $125,000

Total cash receipts 252,100

Total cash available $377,100

Cash Disbursements:

Purchases $108,200

Selling and Admin. 101,500

Total cash disbursements $209,700

Cash balance $167,400

Step-by-step explanation:

a) Data and Calculations:

Selling price of product = $50 per unit

Purchase cost of product = $20 per unit

Total budgeted sales for the year = $3,000,000

Total budgeted sales for the year (units) = 60,000 units

Month Sales Revenue Unit Sales

May $175,000 3,500 ($175,000/$50)

June 240,000 4,800 ($240,000/$50)

July 295,000 5,900 ($295,000/$50)

August 320,000 6,400 ($320,000/$50)

July 1 Account Balances:

Cash = $125,000

Merchandise inventory = $47,200

Accounts receivable (sales) = $84,530

Accounts payable (purchases) = $47,200

Payment of Purchases:

50% purchase month

50% ffg month

Cash collections from sales:

58% sales month (60% -2%)

25% ffg month

12% second month

Ending inventory = 40% of the budgeted sales in units in the next month

Total budgeted selling and administrative expenses (excluding bad debts) = $1,200,000

Fixed expense = $864,000 ($1,200,000 * 3/4) - $36,000

Monthly fixed expenses = $72,000 ($864,000/12)

Variable selling expenses = $300,000 ($1,200,000 - $900,000)

Variable selling expenses per unit = $5 ($300,000/60,000)

Purchases Budget

June July

Ending inventory 2,360 2,560

Sales 4,800 5,900

Units available for sale 7,160 8,460

Beginning inventory 1,920 2,360

Purchases 5,240 6,100

Cost of purchases $104,800 $122,000 (6,100 * $20)