s a veterinary clinic operating i ecently John Tilley, the concerned about cash flow shortages, which arose quite Recently John Tilley, the manager of the unexpectealy in t in Auckland. the last Cash budpeting: veterinary clinic account went into overdraft and incurred inter on to that the main source of the cash flow difficulties is a lack of attenti outstanding client accounts and the practice of purchasing expensive veterinary supplies in lad Tilley has asked you to help design a spreadsheet to help with planning for client consultat burchases of supplies cash shortages and cash surpluses for next year. The following data are avs 72 inic has been three months of charges. Tilley believes the past year The clinic bank quantities at irregular intervals Revenue earned for the past and budget year is as follows: $200000 (actual) November 225000 (actual) 75000 (budget) January 225000 (budget) February 200000 (budget) March During the past year, 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 $75 000 in December and is budgeted as $125 000 in February Half of the suppliers' accounts are paid in the month they are incurred and half in the following month. Salaries of S100000 per month and other costs of $75000 are paid in the month they are incurred Required: 1 Use Excel to create a spreadsheet to complete a cash budget for January to March of the budget year, using the format below Cash Budget January February Opening cash balance March $/200000) Cash received from consultations: Two months ago Last month Current month Total cash receipts Cash Budget January February March Cash payment Supplies: Previous month purchases Current month purchases Salaries Other costs Total cash payments Closing cash balance n ta hain encure that the cash balance of the clinic remains