Question

Weller Industrial Gas Corporation supplies acetylene and other compressed gases to industry. Data regarding the store's operations follow:

• Sales are budgeted at $350,000 for November, $370,000 for December, and $360,000 for January.
• Collections are expected to be 80% in the month of sale and 20% in the month following the sale.
• The cost of goods sold is 69% of sales.
• The company desires an ending merchandise inventory equal to 80% of the cost of goods sold in the following month.
• Payment for merchandise is made in the month following the purchase.
• Other monthly expenses to be paid in cash are $20,100.
• Monthly depreciation is $19,900.
• Ignore taxes.

Balance Sheet October 31
Assets
Cash	$	20,900
Accounts receivable		81,900
Merchandise inventory		193,200
Property, plant and equipment (net of $585,000 accumulated depreciation)		995,000
Total assets	$	1,291,000
Liabilities and Stockholders' Equity
Accounts payable	$	194,900
Common stock		500,000
Retained earnings		596,100
Total liabilities and stockholders' equity	$	1,291,000

Required:
a. Prepare a Schedule of Expected Cash Collections for November and December.

b. Prepare a Merchandise Purchases Budget for November and December.

c. Prepare Cash Budgets for November and December.

d. Prepare Budgeted Income Statements for November and December.

e. Prepare a Budgeted Balance Sheet for the end of December.

	November December Sales Schedule of Expected Cash Collections Accounts receivable November sales December sales Total cash collections

	November December Budgeted cost of goods sold Total needs Required purchases

	November December Cash disbursements for merchandise Other monthly cash expenses Total cash disbursements Beginning cash balance Add cash receipts Total cash available Less cash disbursements Excess (deficiency) of cash available over disbursements Financing Ending cash balance

Prepare Budgeted Income Statements for November and December.

	November December Sales Cost of goods sold Other monthly expenses Depreciation

Prepare a Budgeted Balance Sheet for the end of December.

	Balance Sheet December 31 Assets    Cash Accounts receivable Inventory Property, plant and equipment (net of accumulated depreciation) Total assets Liabilities and Stockholders' Equity Accounts payable Common stock Retained earnings Total liabilities and stockholders' equity

Answer 1

Answer -

a. Answer -

	November	December
Sales	$350000	$370000
Schedule of expected cash collections:
Accounts receivable	$81900
November sales	$280000	$70000
December sales		$296000
Total cash collections	$361900	$366000

Calculation:

As per given information, cash collections are 80% in the month of sale and 20% in the month following the sale.

So,

1. November sales cash collection in November:

= November sales * 80%

= $350000 * 80%

= $280000

2. November sales cash collection in December:

= November sales * 20%

= $350000 * 20%

= $70000

3. December sales cash collection in December:

= December sales * 80%

= $370000 * 80%

= $296000

4. Total cash collection in November:

= Accounts receivable + November sales cash collection in November

= $81900 + $280000

= $361900

5. Total cash collection in November:

= November sales cash collection in December + December sales cash collection in December

= $70000 + $296000

= $366000

b. Answer -

	November	December
Budgeted cost of goods sold	$241500	$255300
Add: Desired ending merchandise inventory	$204240	$198720
Total needs	$445740	$454020
Less: Beginning merchandise inventory	$193200	$204240
Required purchases	$252540	$249780

Calculation:

1. Budgeted cost of goods sold:

Cost of goods sold is 69% of sales.

November:

= Sales * 69% = $350000 * 69% = $241500

December:

= Sales * 69% = $370000 * 69% = $255300

January:

= Sales * 69% = $360000 * 69% = $248400

2. Desired ending merchandise inventory:

Desired ending merchandise inventory is equal to 80% of the cost of goods sold in the following month.

So,

November:

= December cost of goods sold * 80%

=$255300 * 80% = $204240

December:

= January cost of goods sold * 80%

=$248400 * 80% = $198720

3. Total needs:

November:

= Budgeted cost of goods sold + Desired ending merchandise inventory

= $241500 + $204240 = $445740

December:

= Budgeted cost of goods sold + Desired ending merchandise inventory

= $255300 + $198720 = $454020

4. Required purchases:

November:

= Total needs - Beginning merchandise inventory

= $445740 - $193200 (merchandise inventory) = $252540

Note: Referred balance sheet as of October 31 for merchandise inventory.

December:

= Total needs - Beginning merchandise inventory

= $454020 - $204240 = $249780

Note: Beginning merchandise inventory = Desired ending inventory (November)

c. Answer -

	November	December
Cash disbursement for merchandise	$194900	$252540
Other monthly cash expenses	$20100	$20100
Total cash disbursements	$215000	$272640
Beginning cash balance	$20900	$167800
Add: Cash receipts	$361900	$366000
Total cash available	$382800	$533800
Less: Cash disbursements	$215000	$272640
Excess (deficiency) of cash available over disbursements	$167800	$261160
Financing	$0	$0
Ending cash balance	$167800	$261160

Calculation:

1. Total cash disbursement:

November:

= Cash disbursement for merchandise + Other monthly expense

= $194900 (account payable) + $20100 = $215000

Note: Referred balance sheet as of October 31 for account payable.

December:

= Cash disbursement for merchandise + Other monthly expense

= $252540 + $20100 = $272640

Note: Cash disbursement for merchandise = Required purchases (November) or Account payable for November

2. Total cash available:

November:

= Beginning cash balance + Cash receipts

= $20900 + $361900 = $382800

Note: For beginning cash balance referred balance sheet as of October 31 and for cash receipts referred schedule of expected cash collection (total cash collection).

3. Excess (deficiency) of cash available over disbursement:

November:

= Total cash available - Total cash disbursements

= $382800 - $215000 = $167800

4. Total cash available:

December:

= Beginning cash balance + Cash receipts

= $167800 + $366000 = $533800

Note: Beginning cash balance = Ending cash balance (November).

For cash receipts referred schedule of expected cash collection (total cash collection).

5. Excess (deficiency) of cash available over disbursement:

December:

= Total cash available - Total cash disbursements

= $533800 - $272640 = $261160

d. Answer -

	November	December
Sales	$350000	$370000
Cost of goods sold	$241500	$255300
Gross margin	$108500	$114700
Other monthly expenses	$20100	$20100
Depreciation	$19900	$19900
Net operating income	$68500	$74700

Calculation:

1. Gross margin:

November:

= Sales - Cost of goods sold

= $350000 - $241500

= $108500

December:

= Sales - Cost of goods sold

= $370000 - $255300

= $114700

2. Net operating income:

November:

= Gross margin - Other monthly expenses - Depreciation

= $108500 - $20100 - $19900

= $68500

December:

= Gross margin - Other monthly expenses - Depreciation

= $114700 - $20100 - $19900

= $74700

e. Answer -

Balance Sheet
As of December 31
Assets:
Cash	$261160
Accounts receivable	$74000
Inventory	$198720
Property, plant and equipment (net of accumulated depreciation)	$955200
Total assets	$1489080
Liabilities and Stockholders equity:
Accounts payable	$249780
Common stock	$500000
Retained earnings	$739300
Total liabilities and stockholders equity	$1489080

Calculation:

1. Cash:

Cash = $261160

Referred cash budget for ending cash balance for December

2. Accounts receivable:

Accounts receivable = $74000

Cash collections are 80% in the month of sale and 20% in the month following the sale.

So,

December sales cash collection in January:

= December sales * 20%

= $370000 * 20%

= $74000

3. Inventory:

Inventory = $198720

Referred merchandise purchases budget for desire ending inventory for December

4. Property, plant and equipment, net:

= Beginning balance - Depreciation expenses for November - Depreciation expenses for November

= $995000 - $19900 - $19900 = $955200

5. Account payable:

Account payable = $249780

Referred merchandise purchases budget for required purchases for December

6. Retained earnings:

= Beginning balance of retained earnings + Net operating income (November) + Net operating income (December)

= $596100 + $68500 + $74700

= $739300