Paws and Claws is a veterinary clinic operating in the western suburbs of Sydney. Recently, John Tilley,

Fantastic news! We've Found the answer you've been seeking!

Question:

Paws and Claws is a veterinary clinic operating in the western suburbs of Sydney. Recently, John Tilley, the manager of the clinic, has been concerned about cash flow shortages, which arose quite unexpectedly in the last three months of 2003. The clinic bank 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 plan for client consultations, purchases of supplies, cash shortages and cash surpluses in 2004. The following data are available:

Revenue earned is as follows:

November 2003 $80,000 (actual)

December 2003 90,000 (actual)

January 2004 30,000 (budget)

February 2004 90,000 (budget)

March 2004 80,000 (budget)

The opening cash balance for January 2004, is a deficit of $40,000.

During 2003, 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 received. In 2004, new credit policies are expected to result in collections of 60%, 20%, 10% and 10% respectively.

The cost of veterinary supplies was $30,000 in December and is budgeted as $50,00 curred and half in the following month. Salaries are $40,000 per month and other costs of $30,000 are paid in the month they are incurred.

REQUIRED