Question

Provide formulas and procedure in excel

QUESTION 5

1. Bramble Corporation is a small wholesaler of gourmet food products. Data regarding the store's operations follow:
   • Sales are budgeted at $400,000 for November, $380,000 for December, and $370,000 for January.
   • Collections are expected to be 45% in the month of sale and 55% in the month following the sale.
   • The cost of goods sold is 75% of sales.
   • The company would like maintain ending merchandise inventories equal to 65% of the next month's cost of goods sold. Payment for merchandise is made in the month following the purchase.
   • Other monthly expenses to be paid in cash are $24,600.
   • Monthly depreciation is $15,600.
   • Ignore taxes.

Balance Sheet October 31
Assets
Cash	$	20,600
Accounts receivable		70,600
Merchandise inventory		195,000
Property, plant and equipment, net of $572,600    accumulated depreciation		1,094,600
Total assets	$	1,380,800
Liabilities and Stockholders' Equity
Accounts payable	$	254,600
Common stock		820,600
Retained earnings		305,600
Total liabilities and stockholders' equity	$	1,380,800

2. Required:
   
   Prepare the December cash disbursements for merchandise purchases.

Answer 1

Answer:  December cash disbursements for merchandise purchases : $ 290,250

Merchandise Purchases Budget
	November	December	January
Cost of Goods Sold ( 75 % of Budgeted Sales )	$ 300,000	$ 285,000	$ 277,500
Add: Desired Ending Inventory ( 65 % of next month COGS )	185,250	180,375	???
Total Inventory Needed	485,250	465,375
Less: Beginning Inventory	195,000	185,250
Budgeted Merchandise Purchases	$ 290,250	$ 280,125