Expected Cash Collections

Perfect Gas Corporation supplies compressed gases to industry customers. Data regarding the store's operations follow:
• Sales are budgeted at $345,000 for November, $230,000 for December, and $320,000 for January. 
• Collections are expected to be 60% in the month of sale, 34% in the month following the sale, and 6% uncollectible.
• The cost of goods sold is 70% of sales.
• The company desires ending merchandise inventory to equal 80% of the following 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 $12,000.
• Monthly depreciation is $16,000.
• Equipment purchases of $110,000 were paid in cash in November.
• Dividends of $35,000 were declared and paid in December.
• Any borrowings must be in $1,000 increments at 6% annual interest. Assume interest accrues at the beginning of the month and is paid at the end of the month. The company must maintain a minimum cash balance of $5,000.
• Ignore income taxes.
Statement of Financial Position
October 31
ASSETS
Cash $ 22,000
Accounts receivable (net of allowance for uncollectible accounts) 82,000
Merchandise inventory 166,400
Property, plant and equipment (net of $658,000 accumulated depreciation) 1,170,000
Total assets $ 1,440,400
LIABILITIES AND STOCKHOLDERS’ EQUITY
Accounts payable $ 199,000
Common stock 840,000
Retained earnings 401,400
Total liabilities and stockholders’ equity $ 1,440,400
Prepare the following budgets for each month November and December and total for the two months in good form in excel. 
a. Prepare a Schedule of Expected Cash Collections 
b. Prepare a Merchandise Purchases Budget 
c. Prepare a Cash Budget 
d. Prepare Budgeted Income Statements 
e. What is budgeted accounts receivable and budgeted accounts payable at December 31? 
f. Prepare a budgeted balance sheet at December 31 (it must balance)

Prepare a letter/memo to the company’s CEO providing two specific and relevant recommendations regarding your budget and the expected performance of the company.

Check figure: Cash at December 31 = $5,225 and December's Net Income=$27,025
The company will need to borrow and repay during the budgeting period.