Perfect Gas Corporation supplies compressed gases to business customers. Data regarding the store's operations given:
- Sales are budgeted at $345,000 for November, $230,000 for December, and $320,000 for January.
- Collections are expected to be 60 percent in the month of sale, 34% in the month subsequent the sale, and 6% uncollectible.
- The cost of goods sold is 70% of sales.
- The company desires ending merchandise inventory to same 80% of the subsequent month's cost of goods sold. Payment for merchandise is made in the month subsequent 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 7 percent interest. Consider interest accrues at the starting 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.
Prepare the subsequent budgets for November and December and total for the two months in good form in excel.
a. Create a Schedule of Expected Cash Collections
b. Purpose a Merchandise Purchases Budget
c. Plan Cash Budgets
d. Prepare Budgeted Income Statements
e. Purpose a Budgeted Balance Sheet
Purpose a letter/memo to the company's CEO with specific recommendations regarding your budget.