Isla urgently needs your help. An accountancy firm has decided to contract out the cleaning of their offices. They have contacted a number of potential contractors (cleaning businesses) and have asked them to provide a quotation to clean the offices for the 12 month period ending 31 December 2023. Isla wants you to advise her on to how much to quote (bid) for this work that is Isla will clean the offices for a period of 12 months in return for a set fee, to be negotiated between Isla and the accountancy firm.

She has stated she has very little funds available and thus needs the support of her bank manager. The manager has expressed their interest in supporting the project but has requested to see quarterly cash flow projections and other relevant financial information in order to arrive at a decision.

After an initial meeting with Isla where she provided some initial information, you ascertain that the contract is renewable every year and once the contractor has their foot in the door they are likely to be awarded the contract on an annual basis. She has explained the importance of being able to use the model to see the effects of changing some of the variables used in her business e.g. staff numbers, staff rates..

REQUIRED

Using Excel write a suitable accounting model to produce the quarterly cash flow projections and other relevant financial information for the bank manager in order that they may assess Islas application. The model must be a practical model which will enable Isla to examine the financial impact of changing some of the variables.

(Marks will be awarded for the use of spreadsheet logic, presentation and layout of the projections and ease of use.)

(60 marks)

2. Prepare a written report to Isla, which provides the following information:

Advise Isla on what amount she should quote (bid) for the contract and what the implications are for both over and under quoting (bidding) for the contract.

Advise Isla on what amount of funding she should request from the bank and why it may be different to the contract bid price.

The projections on their own are not a lot of use to the bank manager. List the other information and detailed assumptions the bank manager will want to see/know before deciding on whether or not to provide the appropriate funding.

(Marks will be awarded for answering the requirements in an appropriate manner and presentation.)

(40 marks)

(Total: 100 marks)

Information provided by Isla

Please note that each student will be working with a slightly different set of information.

There is a unique set of the three parameters X, Y and Z for each student.

You can find your particular values on Canvas. Look in the parameters file under coursework assignments.

The contract price will be received in four equal instalments on the last day of the last month of each quarter.

The rate of inflation is 2% per annum and the quarterly bank interest rate is 1%.

X cleaners will be employed during the first quarter rising to (X+4) in the second, third and fourth quarters. Their rate of pay is Y.YY per hour during the first quarter rising by 0.25 each quarter (final quarters pay is therefore Y.YY+0.75) Each cleaner will work for 15 hours per week (13 weeks per quarter) and will receive a Christmas bonus of 300 each.

In addition to the above, two part-time supervisors will be employed from 1 January on a starting gross annual salary of 15,000 each. They are to receive a 10% salary increase at the start of the final quarter.

Ten cleaning machines costing Z each will be required at the start of the contract. After six months, a further two more machines will be needed. The rate of inflation for the machines is 5% per quarter. Isla is to depreciate the machines at 50% per annum in her accounts.

A motor vehicle is to be rented/hired at 275 per month from the start of the contract.

Isla wants to make a profit of at least 12,000 per annum on the contract to cover her time.

2,000 is to be introduced into the business at the start of the contract by Isla.

Ignore VAT, Income tax and National Insurance.

Isla has asked you to use your knowledge and experience (common sense !!) to include estimates for the other overheads expected to be incurred in a contract of this nature e.g. machine maintenance, motor expenses etc. etc.