Question

Provide process and formulas in excel

QUESTION 3

Bramble Corporation is a small wholesaler of gourmet food products. Data regarding the store's operations follow:

• Sales are budgeted at $210,000 for November, $190,000 for December, and $180,000 for January.
• Collections are expected to be 50% in the month of sale and 50% in the month following the sale.
• The cost of goods sold is 55% of sales.
• The company would like maintain ending merchandise inventories equal to 45% 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 $22,700.
• Monthly depreciation is $13,700.
• Ignore taxes.

Balance Sheet October 31
Assets
Cash	$	22,500
Accounts receivable		72,500
Merchandise inventory		51,975
Property, plant and equipment, net of $574,500    accumulated depreciation		1,096,500
Total assets	$	1,243,475
Liabilities and Stockholders' Equity
Accounts payable	$	256,500
Common stock		822,500
Retained earnings		164,475
Total liabilities and stockholders' equity	$	1,243,475

Required:

Prepare the dircet material budget for December merchandise purchases:

Answer 1

- m December January 190000 180000 104500 99000 in Sales Cost of goods sold (55% of sales) Add: Desired ending merchandise inventory (45% of next month's cost of goods sold) Total needed Less: Beginning merchandise inventory (45% of this month's cost of goods sold) Budgeted merchandise purchases $ 44550 149050 47025 102025 0

Excel formulas:

Sales Cost of goods sold (55% of sales) December 190000 =C2*0.55 January 180000 =D2*0.55 Add: Desired ending merchandise inventory (45% of next month's cost of goods sold) Total needed =D3*0.45 =C3+04 Less: Beginning merchandise inventory (45% of this month's cost of goods sold) Budgeted merchandise purchases $ =C3*0.45 =C5-C6