accounting due in 5 hours

1

University of Stirling Division of Accounting & Finance

ACCU9A5: Accounting, Information and Employment Autumn 2016

Excel Spreadsheet Assignment

DUE DATE: Friday 21st October 2016

Deliver the files and documentation by 12.00 noon on this date.

This assignment counts for 20% of the overall module assessment

Ava urgently needs your help. She is about to bid for a contract to clean a group of car

showrooms for the 12 month period ending 31 December 2017 and needs you to advise her

as to how much to bid. She has stated she has very little funds available and thus needs

the support of her bank manager. The manager has expressed his interest in supporting the

project but has requested to see quarterly cash flow projections and other information in

order to arrive at a decision.

After an initial meeting with Ava, 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.

REQUIRED

1. Using Excel write a suitable model to produce the quarterly cash flow projections for

the bank manager. Include any other financial information you consider the bank

manager would want to see in order to assess Ava’s application. The model must be a

practical model which will enable Ava to examine the financial impact of changing some

of the variables.

(60 marks)

(Marks will be awarded for the use of spreadsheet logic, presentation and layout of the

projections and ease of use.)

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

 Advise Ava on what amount she should bid for the contract and what the

implications are for both over and under bidding for the contract.

 Advise Ava 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.

(40 marks)

(Total: 100 marks)

2

Information provided by Ava,

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 Succeed. Look in the parameters file under coursework assignments.

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

month of each quarter.

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

3. X cleaners will be employed during the first quarter rising to (X+4) in the second and

subsequent quarters. Their rate of pay is £Y.00 per hour during the first quarter rising

by £0.10 each quarter (final quarter’s pay is therefore £Y.30) Each cleaner will work

for 15 hours per week (13 weeks per quarter) and will receive a Christmas bonus of £100

each.

4. In addition to the above, 2 supervisors will be employed 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.

5. Eight cleaning machines costing £Z each will be required at the start of the contract.

After 6 months, a further 2 more machines will be needed. The rate of inflation for the

machines is 5% per quarter. Ava is to depreciate the machines at 50% per annum in her

accounts.

6. A motor vehicle is to be leased at £300 per month.

7. Ava wants to clear (net of tax) at least £1,000 per month on the contract.

8. £8,000 is to be introduced into the business at the start of the contract.

9. Ava 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, insurance, materials, motor expenses etc. etc.

Please hand in to the essay box (4B112)

1. A working copy of your final version of the spreadsheet saved on a memory stick

labelled with your registration number. [Please note that if you use your own

memory stick, it cannot be returned until after external examiner approval

in January 2017]. To ensure correct identification of your file, use your

registration number as the name of the file (before handing in the file check

that it can be opened!). Please also deliver a second copy of your file via Succeed.

2. A printout of your spreadsheet in a suitable format that can be presented to

Ava/bank manager. Include your workings as part of this printout and clearly

label them so as to differentiate them from the main financial projections. To

3

ensure correct identifications of your printouts, ensure your registration number

is on each page.

3. The printed report (using Excel or Word) that has been described in Section 2

of this assignment. Please hand this in together with the memory stick and

printout of your projections in such a way that they will not become separated.

Remember to include your registration number on the report.

Additional info.docx

Additional info

In the current Excel assignment one of the pieces of information

provided by Ava is "Ava wants to clear £1,000 (net of tax) at

least per month on the contract.".  A question has arisen about

what tax rate to use.  As far as this assignment is concerned

detailed tax knowledge is not expected and thus we are taking the

view that it is irrelevant whether or not tax is applied to this

figure.  It was anticipated that if a student was to apply tax

then a straight rate of 20% would have been used.

It is recommended that you do NOT include calculations involving

Personal Allowances (don't know Ava's other income), Income Tax,

Class 2 National Insurance and Class 4 National Insurance."

X number of cleaners 22, Y £ pay rate 6.5, Z cost of machines 660

Estimates can be used, a loan can be borrowed if needed and profit and loss account may need to be done to show to the bank manager 

Micro soft word must be used when typing the report,

please also send me a manual of the model you will create on excel separately