Question

Weller Industrial Gas Corporation supplies acetylene and other compressed gases to industry. Data regarding the store's operations follow: • Sales are budgeted at $370,000 for November, $390,000 for December, and $380,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 74% 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 $21,700. • Monthly depreciation is $21,500. • Ignore taxes Balance Sheet October 31 Assets Cash Accounts receivable Merchandise inventory Property, plant and equipment (net of $599,000 accumulated depreciation) Total assets $ 22,500 83,500 219,040 1,009,000 $1,334, 040 Liabilities and Stockholders' Equity Accounts payable Common stock Retained earnings Total liabilities and stockholders' equity $ 196,500 670,000 467,540 $1,334,040 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. Complete this question by entering your answers in the tabs below. А Required Required Required Required Required с D Prepare a Schedule of Expected Cash Collections for November and December. November December Sales Schedule of Expected Cash Collections Accounts receivable November sales December sales | Total cash collections S

Answer 1

1. Schedule of Expected Cash collection

Here Sales collections term is 80% of sales to be collected in the same month and 20% in following month .

Hence ,

Expected collection of November =

Accounts receivable of Oct to be collected on November = $83,500

Sales collection in November ($370,000 x 80%)    = $296,000

Total    $379500

and Expected collections of December =

November sales to be collected on December    =$74,000

($370,000 x 20%)

Sales collection in December ($390,000 x 80%) =$312,000   

Total Collections    $386,000

Hence ,

	November	December
Sales	$ 370,000	$ 390,000
Schedule of Expected cash collection
Accounts Receivable	$ 83,500
November sales	$ 296,000	$ 74,000
December Sales		$ 312,000
Total Collections	$ 379,500	$ 386,000

b.

Here , cost of goods sold is 74% of sales and ending inventory is 80% of cost of goods sold of following months

Hence , Cost of Goods sold for November = $370,000 x 74 % = $273,800

Cost of Goods sold for December = $390,000 x 74% =$288,600

Cost of Goods sold for January = $380,000 x 74% =$281,200

Hence,

Ending inventory of November = Cost of goods sold Dec x 80% = $288,600 x 80% =$230,880.

Ending inventory of December = Cost of goods sold of Jan x 80% =$281,200 x 80% =$224,960

Hence,

Purchase Budget
	November	December
Cost of Goods Sold	$ 2,73,800	$ 2,88,600
Add : Ending Inventory	$ 2,30,880	$ 2,24,960
Total	$ 5,04,680	$ 5,13,560
Less : Beginning Inventory	$ 2,19,040	$ 2,30,880
Total Purchase	$ 2,85,640	$ 2,82,680

c.

	November	December
Beginning Cash Balance	$ 22,500	$ 1,83,800
Add : Cash Receipts
From Customer	$ 3,79,500	$ 3,86,000
Total	$ 4,02,000	$ 5,69,800
Less :Cash Payment
Purchase payment	$ 1,96,500	$ 2,85,640
Other Monthly Expenses	$ 21,700	$ 21,700
Total Payment	$ 2,18,200	$ 3,07,340
Ending Cash Balance	$ 1,83,800	$ 2,62,460

Working Notes : -

Terms of purchase payment is current month payment in next month

Hence , payment in November = Accounts payable of Oct = $196,500

Payment in December = Purchase of November = $285,640. (refer purchase budget).

d. We will prepare the income statement base data calculated above.

Budgeted Income statement
	November	December
Sales Revenue	$ 3,70,000	$ 3,90,000
Cost of Goods sold	$ 2,73,800	$ 2,88,600
Gross Profit	$ 96,200	$ 1,01,400
Operating Expenses
Other monthly expenses	$ 21,700	$ 21,700
Depreciation	$ 21,500	$ 21,500
Total Operating Expenses	$ 43,200	$ 43,200
Net Income	$ 53,000	$ 58,200

e.

Budgetted Balance Sheet
	November	December
Assets
Cash	$ 1,83,800	$ 2,62,460
Accounts Receivable	$ 74,000	$ 78,000
Inventory	$ 2,30,880	$ 2,24,960
Property plant , equipment	$ 9,87,500	$ 9,66,000
Total Assets	$ 14,76,180	$ 15,31,420
Liabilities and stockholders equity
Accounts payable	$ 2,85,640	$ 2,82,680
Common Stock	$ 6,70,000	$ 6,70,000
Retained earnings	$ 5,20,540	$ 5,78,740
Total Liabilities and stockholders equity	$ 14,76,180	$ 15,31,420

Note :-

Accounts receivable 20% of current month sales as per question

Accounts payable purchase of current month

Retained earning = (Opening balance + Net income )

Hence November retained earnings = ($467,540 +$53,000)= $520,540

December retained earnings = $520,540 + $58,200 =$578,740

November Property ,plant equipment = ($1009,000- Depreciation) = ($1009,000-$21500)=$987,500

December Property ,plant equipment = ($987,500-$21500)= $9,66,000