School of Management
MN-3526 Spreadsheets and Databases for Business Analysis
Individual Coursework Assignment
1) Individual Coursework Assignment 1
This assignment is worth 50% of the overall module mark.
A Profit and Loss statement based on Sales Revenue and Costs provided. Your solution should be calculated in an Excel spreadsheet. You may use appropriate Excel functions (such as NPV) or program the calculations yourself. You should submit a Word processed report to show calculations and your own understandings of the calculations and relevant topics, by giving a detailed account of the methods you have used, the reasons you have chosen those particular methods, the meaning of any values calculated, and the conclusions you have drawn.
You must complete a Profit and Loss statement for a five-year period in which the Sales Revenue and Costs in the categories of Labour, Material and Overheads are listed in the following table (all quantities in thousands of pounds):
There is also an initial plant cost in Year 0 of 1000 (=£1,000,000), which is equally spread over the five years, producing the same depreciation each year.
(a) You need to work out Total Costs, Trading Profit, Taxation and Net Profit for each of the five trading years. Use a taxation rate of 30%.
(b) Then calculate the Net Cash Flow from the Trading Profit, Depreciation and Taxations.
(c) From the Net Cash Flow you should calculate the Net Present Value (NPV), using a discount rate of 10%, and finally calculate the Internal Rate of Return (IRR).
(d) If the depreciation is computed using the double-declining balance method, with a salvage of 80 (=£80,000) by the end of Year 5, calculate the corresponding NPV and IRR. Explain their differences from the answers in (c).
Key Marking criteria will include:
Accuracy: Accuracy of the Excel calculations and the methods used
Assignment Structure: clarity of structure and presentation
Writing Quality: Readability and ability to convey key message(s) concisely and logically Insightfulness of Analysis: Interest and usefulness of findings, conclusions drawn
Understanding: Demonstration of understanding of the key topics
Evaluation: Evidence of independent thinking and critical awareness Overall Quality of Assignment
Individual Coursework Assignment 1 must be submitted by 3pm on Friday 23rd March via Turnitin. Please note:
The maximum file size that can be uploaded is 20mb. If your file is larger than this it is usually because you have included a lot of images – you should either remove some if possible, or else convert them to a more efficient format to bring the file size down (e.g. .png or .gif).
You should ensure your student number is in the title of the filename for the work you submit/upload.
The School of Management operates a late penalty system as follows: when work is submitted late with no prior authorisation, a penalty of 10 marks will be deducted from the actual mark for each calendar day, or part of a day, by which the deadline is exceeded. After seven days from the initial deadline, a mark of zero will be awarded. Late work should be submitted electronically in the usual way.
If the penalty for late submission exceeds the mark awarded for the component then the component will receive a final mark of 0%.
Digital Submission of Coursework Instructions
Logon to Blackboard.
Access the appropriate Module site.
Click the Assignment menu button which appears on the left of the screen.
In this folder you will see a file entitled ‘Student Declaration form’. You need to complete
this form and incorporate it as the first page of your coursework (not two separate files).
Click Coursework. Please read the statement of originality before you click “submit”. By submitting work you are agreeing to this statement and confirming it to be true.
Complete the dialogue box with your forename and surname
To submit your coursework, locate the correct file on your computer by clicking the
“browse” button and enter a title for the coursework (we suggest the module code and your
student ID MN3526 123456). Click SUBMIT
You will then be asked to check if the document is the one you wish to submit and if so click
You will then receive a message saying “paper successfully complete”.
When your submission has been successful you will be given an 8-digit submission ID
number. You must keep a note of this number, either by writing it down or taking a screenshot. If there are any problems with your submission, we will need this number before we can resolve them.
If you experience any difficulties submitting your work via Turnitin please contact the Student Hub straight away at SoMAssessment@swansea.ac.uk