Understanding Relationships, Cash Budget, Pro Forma Balance Sheet

Ryan Richards, controller for Grange Retailers, has assembled the following data to assist in the preparation of a cash budget for the third quarter of the year:

1. Sales:

   May (actual)	$100,000
   June (actual)	120,000
   July (estimated)	90,000
   August (estimated)	100,000
   September (estimated)	135,000
   October (estimated)	110,000

2. Each month, 30% of sales are for cash and 70% are on credit. The collection pattern for credit sales is 20% in the month of sale, 50% in the following month, and 30% in the second month following the sale.
3. Each month, the ending inventory exactly equals 50% of the cost of next month's sales. The markup on goods is 25% of cost.
4. Inventory purchases are paid for in the month following the purchase.
5. Recurring monthly expenses are as follows:

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

6. Property taxes of $15,000 are due and payable on July 15.
7. Advertising fees of $6,000 must be paid on August 20.
8. A lease on a new storage facility is scheduled to begin on September 2. Monthly payments are $5,000.
9. 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 a month. The annual interest rate is 9%. The company must borrow in multiples of $1,000.
10. A partially completed balance sheet as of June 30 follows. (Note: Accounts payable is for inventory purchases only.)

    Cash	$ ?
    Accounts receivable	?
    Inventory	?
    Plant and equipment, net	425,000
    Accounts payable				$ ?
    Common stock				210,000
    Retained earnings				268,750
    Total	$ ?			$ ?

Required:

1. Complete the balance sheet given in Item j.

Grange Retailers Balance sheet June 30

	Assets	L and OE
Cash	$fill in the blank 8901d3f67fdc022_1
Accounts receivable	fill in the blank 8901d3f67fdc022_2
Inventory	fill in the blank 8901d3f67fdc022_3
Plant and equipment, net	425,000
Accounts payable		$fill in the blank 8901d3f67fdc022_4
Common stock		210,000
Retained earnings		268,750
Total	$fill in the blank 8901d3f67fdc022_5	$fill in the blank 8901d3f67fdc022_6

2. Prepare a cash budget for each month in the third quarter and for the quarter in total (the third quarter begins on July 1). Prepare a supporting schedule of cash collections. If an amount is zero, enter "0" or leave the entry box blank.

Grange Retailers Cash Budget For the Quarter Ending September 30

	July	August	September	Total
Beginning cash balance	$fill in the blank ec1f72f6a057fd6_1	$fill in the blank ec1f72f6a057fd6_2	$fill in the blank ec1f72f6a057fd6_3	$fill in the blank ec1f72f6a057fd6_4
Cash collections	fill in the blank ec1f72f6a057fd6_5	fill in the blank ec1f72f6a057fd6_6	fill in the blank ec1f72f6a057fd6_7	fill in the blank ec1f72f6a057fd6_8
Total cash available	$fill in the blank ec1f72f6a057fd6_9	$fill in the blank ec1f72f6a057fd6_10	$fill in the blank ec1f72f6a057fd6_11	$fill in the blank ec1f72f6a057fd6_12
Cash disbursements:
Purchases	$fill in the blank ec1f72f6a057fd6_13	$fill in the blank ec1f72f6a057fd6_14	$fill in the blank ec1f72f6a057fd6_15	fill in the blank ec1f72f6a057fd6_16
Salaries and wages	fill in the blank ec1f72f6a057fd6_17	fill in the blank ec1f72f6a057fd6_18	fill in the blank ec1f72f6a057fd6_19	fill in the blank ec1f72f6a057fd6_20
Utilities	fill in the blank ec1f72f6a057fd6_21	fill in the blank ec1f72f6a057fd6_22	fill in the blank ec1f72f6a057fd6_23	fill in the blank ec1f72f6a057fd6_24
Other	fill in the blank ec1f72f6a057fd6_25	fill in the blank ec1f72f6a057fd6_26	fill in the blank ec1f72f6a057fd6_27	fill in the blank ec1f72f6a057fd6_28
Property taxes	fill in the blank ec1f72f6a057fd6_29	fill in the blank ec1f72f6a057fd6_30	fill in the blank ec1f72f6a057fd6_31	fill in the blank ec1f72f6a057fd6_32
Advertising fees	fill in the blank ec1f72f6a057fd6_33	fill in the blank ec1f72f6a057fd6_34	fill in the blank ec1f72f6a057fd6_35	fill in the blank ec1f72f6a057fd6_36
Lease	fill in the blank ec1f72f6a057fd6_37	fill in the blank ec1f72f6a057fd6_38	fill in the blank ec1f72f6a057fd6_39	fill in the blank ec1f72f6a057fd6_40
Total disbursement	$fill in the blank ec1f72f6a057fd6_41	$fill in the blank ec1f72f6a057fd6_42	$fill in the blank ec1f72f6a057fd6_43	$fill in the blank ec1f72f6a057fd6_44
Minimum cash balance	fill in the blank ec1f72f6a057fd6_45	fill in the blank ec1f72f6a057fd6_46	fill in the blank ec1f72f6a057fd6_47	fill in the blank ec1f72f6a057fd6_48
Total cash needs	$fill in the blank ec1f72f6a057fd6_49	$fill in the blank ec1f72f6a057fd6_50	$fill in the blank ec1f72f6a057fd6_51	$fill in the blank ec1f72f6a057fd6_52
Excess (deficiency)	$fill in the blank ec1f72f6a057fd6_53	$fill in the blank ec1f72f6a057fd6_54	$fill in the blank ec1f72f6a057fd6_55	$fill in the blank ec1f72f6a057fd6_56
Financing:
Borrowings	$fill in the blank ec1f72f6a057fd6_57	$fill in the blank ec1f72f6a057fd6_58	$fill in the blank ec1f72f6a057fd6_59	$fill in the blank ec1f72f6a057fd6_60
Repayments	fill in the blank ec1f72f6a057fd6_61	fill in the blank ec1f72f6a057fd6_62	fill in the blank ec1f72f6a057fd6_63	fill in the blank ec1f72f6a057fd6_64
Interest	fill in the blank ec1f72f6a057fd6_65	fill in the blank ec1f72f6a057fd6_66	fill in the blank ec1f72f6a057fd6_67	fill in the blank ec1f72f6a057fd6_68
Total financing	$fill in the blank ec1f72f6a057fd6_69	$fill in the blank ec1f72f6a057fd6_70	$fill in the blank ec1f72f6a057fd6_71	$fill in the blank ec1f72f6a057fd6_72
Ending cash balance	$fill in the blank ec1f72f6a057fd6_73	$fill in the blank ec1f72f6a057fd6_74	$fill in the blank ec1f72f6a057fd6_75	$fill in the blank ec1f72f6a057fd6_76
Cash collections:
Cash sales	$fill in the blank ec1f72f6a057fd6_77	$fill in the blank ec1f72f6a057fd6_78	$fill in the blank ec1f72f6a057fd6_79	$fill in the blank ec1f72f6a057fd6_80
Credit sales:
Current month	fill in the blank ec1f72f6a057fd6_81	fill in the blank ec1f72f6a057fd6_82	fill in the blank ec1f72f6a057fd6_83	fill in the blank ec1f72f6a057fd6_84
Prior month	fill in the blank ec1f72f6a057fd6_85	fill in the blank ec1f72f6a057fd6_86	fill in the blank ec1f72f6a057fd6_87	fill in the blank ec1f72f6a057fd6_88
From two months ago	fill in the blank ec1f72f6a057fd6_89	fill in the blank ec1f72f6a057fd6_90	fill in the blank ec1f72f6a057fd6_91	fill in the blank ec1f72f6a057fd6_92
Total collections	$fill in the blank ec1f72f6a057fd6_93	$fill in the blank ec1f72f6a057fd6_94	$fill in the blank ec1f72f6a057fd6_95	$fill in the blank ec1f72f6a057fd6_96

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

Grange Retailers Balance Sheet September 30

	Assets	L and OE
Accounts payableCashCommon stockRetained earnings	$- Select -
Accounts payableAccounts receivableCommon stockRetained earnings	- Select -
Accounts payableCommon stockInventoryRetained earnings	- Select -
Accounts payableCommon stockPlant and equipmentRetained earnings	- Select -
Accounts payableAccounts receivableCashInventoryPlant and equipment		$- Select -
Accounts receivableCashCommon stockInventoryPlant and equipment		- Select -
Accounts receivableCashInventoryPlant and equipmentRetained earnings		- Select -
Total	$fill in the blank a13ff9fb4066f84_15	$fill in the blank a13ff9fb4066f84_16

4. Discuss why a bank might require a cash budget for businesses that are seeking short-term loans. Determine what other financial reports might be useful for a loan decision. Also, discuss how the reliability of cash budgets and other financial information can be determined.

Cash budgets are important in loan decisions to help determine the companys

ability to repay the loanability to generate profitsassess the revenues generated

.

A statement of cash flowsBalance sheetIncome statement

would also be helpful to see how cash has been generated and used in the past. Another key report is the

A statement of cash flowsBalance sheetIncome statement

so that the loan officer can assess the current level of indebtedness and the assets that would be available to claim should the company default. Reliability of historical financial reports can be increased through an

audit by an independent CPAAudit by the Bank Manager