Question

Provide formulas, procedure in excel

QUESTION 6

1. Bramble Corporation is a small wholesaler of gourmet food products. Data regarding the store's operations follow:
   • Sales are budgeted at $370,000 for November, $350,000 for December, and $340,000 for January.
   • Collections are expected to be 55% in the month of sale and 45% in the month following the sale.
   • The cost of goods sold is 70% of sales.
   • The company would like maintain ending merchandise inventories equal to 60% 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,300.
   • Monthly depreciation is $15,300.
   • Ignore taxes.

Balance Sheet October 31
Assets
Cash	$	20,300
Accounts receivable		70,300
Merchandise inventory		155,400
Property, plant and equipment, net of $572,300    accumulated    depreciation		1,094,300
Total assets	$	1,340,300
Liabilities and Stockholders' Equity
Accounts payable	$	254,300
Common stock		820,300
Retained earnings		265,700
Total liabilities and stockholders' equity	$	1,340,300

2. Required:
3. 1. Prepare the cash receipts for December.
4. 2. Prepare the cash disbursements for December.
5. 3. Calculate the difference between cash receipts and cash disbursements for December.

Answer 1

Answer is given below

I have given excel Screenshot which shows all Cell reference and formulas also. If you need anything more do let me know please

November December $370,000 $350,000 January $340,000 Sales Cash Collections November Sales December Sales Total December $166,500 $192,500 $359,000 Sales November December $370,000 $350,000 $259,000 $245,000 January $340,000 $238,000 COGS 2. Purchase Budget COGS Add: Closing Stock November December $259,000 $245,000 $147,000 $142,800 $ 406,000 $387,800 $ 155,400 $147,000 $250,600 $240,800 Less: Opening stock Purchase Cash payment to Vendors December $250,600 November Purchase December Month Cash Receipts Cash collection from Customers $359,000 Cash Disbursements Cash payments to Vendors Monthly expenses Total Disbursements $250,600 $24,300 $274,900 Difference $84,100

Cash Budget - Comprehensive - Microsoft Excel - 0 X Home Insert Page Layout View Formulas Data Review Ruler Ruler Formula Bar Gridlines Headings Message Bar Show/Hide Formula Bar 9 A 2 2 Split View Side by side Zoom D Normal Page Page Break Custom Full Layout Preview Views Screen Workbook Views c4 for Macros 100% Zoom to Selection Zoom New Window Hide Unhide Arrange Freeze All Panes - ET Synchronous Scrolling Reset Window Position Window Save Switch Workspace Windows Macros E F G H I J K L M - November 370000 December 350000 January 340000 Sales 1. Cash Collections November Sales December Sales December =E6'45% =F6'55% - SUM(E10:E11) Total Sales COGS November 370000 -E16'70% December ber 350000 =F16'70% January 340000 =G16'70% 17 2. Purchase Budget 19 20 21 COGS Add: Closing Stock November -E6'70% -F17"60% -SUM(E21:E22) -E21"60% =E23-E24 December =F6'70% -G17"60% - SUM(F21:F22) -E22 =F23-F24 Less: Opening stock Purchase Cash pament to Yendors 29 November Purchase December -E25 30 31 32 December Month Cash Receipts Cash collection from Customers -E12 34 Cash Disbursements Cash payments to Vendors Monthly expenses Total Disbursements -E29 24300 - SUM(E36:E37) Difference =E33-E38 Sheet2 cash disbursements CASE STUDY Cash Budget only Diff budget short one Diff type Sharp Bicycle ► N Ready DO . 60% 1 0 10:36 ENG Type here to search IN 02-11-2019 52 |