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.
Part A
Part B
Part C
Part D
(a) Prepare a schedule of estimated cash collections for July.
(b) Prepare a schedule of estimated July cash payments for purchases. Hint: Start by doing a purchase budget.
(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.

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

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)