University of Stirling
Division of Accounting and Finance
ACCU9AE: Accounting Information and Employment Autumn 2017
Excel Spreadsheet Assignment (20% of overall grade) – Released Friday 29th
DUE DATE: 11am Friday 20th October 2017
One of your friends, Mystique, has found out you are studying accountancy and that you have
acquired basic spreadsheet skills. She has decided to start a business of printing her own artistic
designs on fleeces but needs to produce a business plan for the bank manager in order to raise a
loan. Mystique has already formed a private limited company and has introduced £8,000 in to the
business bank account. Of this £8,000, £1,000 is to be the initial share capital and the balance of
£7,000 is a short-term interest free loan to her. It is her intention to start trading as from 1
You have agreed to produce the financial projections for her based on the information Mystique
1. Using Excel, construct a financial model that includes, as a minimum, the projected
cash flow, profit and loss and balance sheet figures for the first year of trading i.e.
until 31 December 2018. The statements should show quarterly figures with annual
totals, where appropriate.
(Marks will be awarded for the use of spreadsheet logic, presentation and layout of
the projections and ease of use)
2. Using either Word or Excel, write a short report to Mystique to include the following:
a. Using your model produce financial ratios, which may be useful to Mystique
bearing in mind the reason for the projections. The report should explain why you
have selected these ratios and the relevance of the trends to Mystique. Include
any other comments you consider would be relevant to Mystique.
b. Mystique has expressed an interest in using the model to see the financial impact
of changing some of the assumptions. Write a concise user guide (report) on how
she would use the model to see the impact of changing the gross margin and other
assumptions. Do remember Mystique has limited Excel and accounting knowledge.
c. Explain to Mystique the advantages and disadvantages of using Excel to produce
the financial projections as compared to producing them manually using “pen and
(Total : 100 marks)
(In addition to the technical content marks will be awarded for presentation)
Please hand in to the coursework box (3B44)
1. A working copy of your final version of the spreadsheet saved on a memory stick clearly
labelled with your registration number. 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 Canvas (Link available in
Excel Coursework Assignment).
2. A printout of your financial projections (spreadsheet) in a suitable format to enable
Mystique to show them to her 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 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.
Assignment will be marked anonymously – please use your registration number only
Information provided by Mystique:
Please note that each student will be working with a slightly different set of information.
There is a unique set of the three parameters A, B and C for each student. You can find
your particular values on Canvas.
1. The first quarter’s sales are expected to be £2,500 doubling every quarter.
2. The gross profit margin on sales is expected to be 40%.
3. Of each quarter’s sales, A% are cash sales.
4. Bad debts are on credit sales, and are anticipated to be B% each quarter.
5. 60% of the credit sales are paid in the quarter in which the sale is made. The remaining
debtors, after bad debts, are collected in the following quarter.
6. Sufficient stock is purchased to cover the next quarter’s sales. In the first quarter assume
the first two quarter’s purchases are made (assume the final quarter’s purchases are
7. Purchases are paid for in the quarter bought.
8. Gross staff salaries are £2,250 per quarter and paid in the quarter incurred.
9. Distribution costs are 10% of sales and are paid in the quarter of the sale.
10. Bank interest charged is 1% per quarter calculated on the balance at the end of the previous
quarter. It is payable in the following quarter i.e. the quarter in which it is charged. No
interest is receivable on credit (positive) balances.
11. The company buys machinery costing £9,000 in the first quarter.
12. A van costing £5,000 is purchased in the third quarter.
13. The company depreciation policy is to depreciate assets at C% per annum on a straight-line
14. The company incurs a quarterly telephone bill of £525, which is usually paid in the quarter it
is incurred. However, the fourth quarter’s bill is £600 and will not be paid until January 2019.
15. Ignore the effect of corporation tax, VAT, PAYE and inflation.
16. Include any other expenses Mystique has obviously omitted from the above information.
Accounting Information and Employment Autumn 2017