Cash Budget, Pro Forma Balance Sheet

Bernard Creighton is the controller for Creighton Hardware Store. In putting together the cash budget for the fourth quarter of the year, he has assembled the following data.

Sales

July (actual)	$100,000
August (actual)	120,000
September (estimated)	90,000
October (estimated)	100,000
November (estimated)	135,000
December (estimated)	150,000

Each month, 20 percent of sales are for cash, and 80 percent are on credit. The collection pattern for credit sales is 20 percent in the month of sale, 50 percent in the following month, and 30 percent in the second month following the sale.

Each month, the ending inventory exactly equals 40 percent of the cost of next month's sales. The markup on goods is 33.33 percent of cost.

Inventory purchases are paid for in the month following purchase.

Recurring monthly expenses are as follows:

Salaries and wages	$10,000
Depreciation on plant and equipment	4,000
Utilities	1,000
Other	1,700

Property taxes of $15,000 are due and payable on September 15.

Advertising fees of $6,000 must be paid on October 20.

A lease on a new storage facility is scheduled to begin on November 2. Monthly payments are $5,000.

The company has a policy to maintain a minimum cash balance of $10,000. If necessary, it will borrow to meet its short-term needs. All borrowing is done at the beginning of the month. All payments on principal and interest are made at the end of the month. The annual interest rate is 9 percent. The company must borrow in multiples of $1,000.

A partially completed balance sheet as of August 31 is given below. (Accounts payable is for inventory purchases only.)

	Assets	Liabilities & Owners Equity
Cash	$ ?
Accounts receivable	?
Inventory	?
Plant and equipment	431,750
Accounts payable		$ ?
Common stock		220,000
Retained earnings		268,750
Totals	$ ?	$ ?

Required:

1. Complete the balance sheet given in part (j).

Creighton Hardware Store
Balance Sheet
August 31
	Assets	Liabilities & Owners' Equity
Cash	$
Accounts receivable
Inventory
Plant and equipment	431,750
Accounts payable		$
Common stock		220,000
Retained earnings		268,750
Totals	$	$

Feedback

Remember A = L + OE

2. Bernard wants to see how the company is doing prior to starting the month of December. Prepare a cash budget for the months of September, October, and November and for the three-month period in total (the period begins on September 1). Enter repayments, interest, cash deficiencies and negative total financing as negative amounts. If amount is zero, enter "0".

Creighton Hardware Store
Cash Budget
For the Period Ending November 30
	September	October	November	Total
Beginning cash balance	$	$	$	$
Cash collections
Total cash available	$	$	$	$
Disbursements:
Accounts payable	$	$	$	$
Salaries and wages
Utilities
Other
Property taxes
Advertising fees
Lease
Total disbursements	$	$	$	$
Minimum cash balance
Total cash needs	$	$	$	$
Excess (deficiency)	$	$	$	$
Financing:
Borrowings	$			$
Repayments		$
Interest
Total financing	$	$		$
Ending cash balance	$	$	$	$

Feedback

The Schedule of Cash collections will include Cash sales, Credit sales and Total collections.

Complete a supporting schedule of cash collections.

Creighton Hardware Store
Cash collections
For the Period Ending November 30
	September	October	November	Total
Cash sales	$	$	$	$
Credit sales:
Current month
Prior month
From two months ago
Total collections	$	$	$	$

Feedback

The Schedule of Cash collections will include Cash sales, Credit sales and Total collections.

3. Prepare a pro forma balance sheet as of November 30.

Creighton Hardware Store
Pro Forma Balance Sheet
November 30
Assets:
	$
Liabilities:
		$
Owners' Equity:
Totals	$	$