In: Accounting
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.
2). For maintaining cash balance positive, Tilley should try to
make cash payments when there is surplus cash available in excess
of a particular limit. Supplies should be purchased in the month
when there is excess availability of cash and should instant
payment or try to buy supplies in small quantities but regularly
which will decrease the cash outflow in a single payment. Further
Tilley should try to recover consultation payments as soon as
possbile and try to reduce the % of baddebts.