appy Pets is veterinary clinic operating in Auckland. RecentlyJohn Tilley, the manager of the clinic, has been concerned about cash flow shortages, which arose quite unexpectedly in the last three months of the past year. the clinic ban account went into overdraft and incurred interest charges. Tilley believes that the main source of cash flow difficulties is a lack of attention to outstanding client accounts and the practice of purchasing expensive veterinary supplies in large quantities at irregular intervals.

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

Revenue earned for the past and budget year is as follows:

Nov $ 200,000 (actual)

Dec 225,000 (actual)

Jan 75,000 (budget)

Feb 225,000 (budget)

Mar 200,000 (budget)

During the past year, 50% of consultation revenue was collected in the month of the veterinary visit, 20% in the following month. 20% in the second month, and 10% was not recevived. In budget year, new credit policies are expected to result in collections of 60%. 20%, 10% and 10% respectively.

The cost veterinary supplies was $75,000 in Dec and is budgeted as $125,000 in Feb. Half of the suppliers' accounts are paid in the month they are incurred and half in the following month. Salaries of $100,000 per month and other costs of $75,000 are paid in the month they are incurred.

1. use Excel to create a spreadsheet to complete a cash budget for Jan to Mar of the budget year, using the format below.

Cash budget

Jan Feb Mar

Openning cash balance $(200,000)

Cash received from consultations

Two months ago

Last month

Current month

Total cash receipts

Cash payment

Supplies:

Previous month purchases

Current month purchases

Salaries

Other costs

Total Cash payments

Closing Cash balance

2. What further steps could tilley take to help ensure that the cash balance of the clinic remains positive? In your answer, consider ways in in which budgeting may assist.