Question

The Cash Budget

Once we see how the components of the cash budget are calculated, it is just a matter of putting together an entire cash budget. It almost seems redundant to say, but a cash budget includes only cash items. So if a company makes sales on account, those are not included in the cash budget until cash is received on account. Similarly, a company may have non cash expenses. These are not included in the cash budget.

A company expects sales of $100,000 in July and expects sales of $115,000 in August. The company provided the following information:

a. Sales in the previous three months were:

April	$88,000
May	90,000
June	95,000

Of the sales, 10% are cash sales, the remaining are on account. The company experiences the following accounts receivable payment pattern: 25% in the month of sale, 50% in the month after sale, 20% in the second month after sale.

b. Purchases of goods are made the month before the anticipated sale at a rate of 60% of sales. Of monthly purchases, 25% are paid in the month of purchase, and the remaining 75% in the following month.

c. Wages for staff total $6,450 per month.

d. Telecommunications and utilities are $1,900 per month.

e. The property tax bill of $6,000 is due in August.

f. Insurance is paid quarterly at $1,200 per quarter. The next payment is due July 15.

g. Depreciation is $2,000 per month.

h. Advertising is budgeted at $2,000 per month in the summer months.

i. Professional fees (legal, bookkeeping, etc.) average $600 per month.

j. Maintenance is $800 per month.

k. Office supplies average $150 per month.

l. The owner took out a loan from her family and is paying it back at the rate of $4,000 per month.

m. The cash balance on July 1 was $2,190.

Develop a cash budget for July by filling in the following table. If an amount box does not require an entry, enter "0" or leave the cell "blank".

Beginning cash balance, July 1			$
Add: Cash sales
Payments on accounts receivable:
May	$
June
July
Cash available			$
Cash Disbursements:
Purchases	$
Wages
Telecommunications
Property tax
Insurance
Depreciation
Advertising
Professional fees
Maintenance
Office supplies
Loan payment
Total disbursements			$
Cash balance, July 31			$

Answer 1

Beginning cash balance July 1			2190
Add: cash sales		10000
Payments on accounts receivable
May	59850
June	77715
July	81450	219015
Cash available		229015	231205
Cash disbursements
Purchase	62250
Wages	6450
Telecommunications	1900
Property tax	0
Insurance	1200
Depreciation	0
Advertising	2000
Professional fees	600
Maintenance	800
Office supplies	150
Loan payments	4000
Total disbursements		79350

Cash balance July 31

151855

Assuming that in July a cash sale of $100,000 was realized

Cash sales =10% of 100000

= 10000

Accounts receivable

May

=50% of 90% of sales in April + 25% of 90% of sales in may

= 50/100*90/100*88000 + 25/100*90/100*90000

=39600+20250

=59850

June

=20% of 90% of sales in April+ 50% of 90% of sales in may + 25% of 90 % of sales in June

=20/100*90/100*88000+50/100*90/100*90000+25/100*90/100*95000

=15840+40500+21375

=77715

July

=20% of 90% of sales in may + 50% of 90% of sales in June + 25% of 90% of sales in July

= 20/100*90/100*90000+50/100*90/100*95000+25/100*90/100*100000

=16200+42750+22500

=81450

Total accounts receivable =219015

Cash available = opening cash balance + cash sales + accounts receivable

=2190+10000+219015=231205

Cash paid for Purchases in july

=75% of 60% of sales in July + 25% of 60% of sales in August

=75/100*60/100*100000+25/100*60/100*115000

=45000+17250

=62250

Depreciation is a non cash expense

Property tax is due in August

Hence both are 0

Total cash disbursements = purchases +wages+telecommunications+property tax+insurance+depreciation+advertising+ professional fees+ maintenance+ office supplies+ loan payments

62250+6450+1900+0+1200+0+2000+600+800+150+4000

=79350

Closing cash balance = cash available -cash disbursements

=231205-79350

=151855