Friends of Animals is a veterinary clinic located in Perth. The manager, Jay Taylor, of the clinic has been concerned about the cash flow of the business that arose quite unexpectedly in the last three months of the past year. The business bank account went into an overdraft balance and incurred interest charges. Taylor believes that the main source of the cash flow difficulties is the lack of attention to outstanding client accounts and the practice of purchasing expensive veterinary supplies in large quantities at irregular intervals.

Taylor has asked you to help design a spreadsheet to help with planning for client consultations, purchases of supplies, cash shortages and cash surpluses for next year. The following data are available:

Revenue is as follows:

May

$180,000

(actual)

June

255,000

(actual)

July

90,000

(budget)

August

310,000

(budget)

September

175,000

(budget)

During the past year (ended 30 June), 50 per cent of consultation revenue was collected in the month of the veterinary visit, 20 per cent in the following month, 20 per cent in the second month, and 10 per cent was not received. In the budget year, new credit policies are expected to result in collections of 60 per cent, 20 per cent, 10 per cent and 10 per cent, respectively.

The cost of veterinary supplies was $123,000 in June and is budgeted as $142,000 in August. Half of the suppliers' accounts are paid in the month they are incurred and half in the following month. Salaries of $84,000 per month and other costs of $103,000 are paid in the month they are incurred. The cash balance at the end of June was an overdraft of $200,000. To improve its cash flow, the business plans to apply for a long-term loan of $100,000 in July.

Required:

Prepare a Cash Budget for the period July to September of the budget year, and the total for the quarter, using Excel Spreadsheet. T