Individual Coursework Assignment
1) Individual Coursework Assignment 1
Coursework Brief
A Profit and Loss statement based on Sales Revenue and Costs provided. Your solution should be calculated in an Excel spreadsheet using appropriate Excel formulae or functions (such as NPV) based on the module contents on spreadsheets. You should submit a Word-processedreport following a typical report structure, to show the calculations and your own understandings of the relevant topics, by giving a detailed account of the methods you haveused, the reasons you have chosen those particular methods, the meaning of the values calculated, and the conclusions you have drawn. The Excel spreadsheet is NOT required for submission, but snapshots of your Excel worksheet, zooming different portions of the worksheet, should be included in the Word report to demonstrate the formulae/functions in your calculations, as well as the Excel worksheet design, and to facilitate the explanation and discussion.
You must complete a Profit and Loss statement in which the Sales Revenue and Costs in the categories of Labour, Material and Overhead are listed in the following table (all quantities in thousands of pounds):
| Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | |
| Sales Revenue | 700 | 650 | 500 | 450 | 570 |
| Costs: | |||||
| Labour | 50 | 80 | 40 | 60 | 70 |
| Material | 130 | 170 | 140 | 190 | 150 |
| Overhead | 20 | 20 | 20 | 20 | 20 |
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.
- You need to first work out Total Costs, Trading Profit, Taxation and Net Profit for each of the five trading years. Use a taxation rate of 20%. Then calculate the NetCash Flow, the Net Present Value using a discount rate of 10%, and finally the Internal Rate of Return. Explain and justify the formulae/functions used in thecalculations, with specific focus on those highlighted in bold.
- Create suitable Excel Charts for two of the calculated values in (a). Then explain and justify the Chart types and formats used and discuss some of the information obtained from the Charts.
- Write suitable functions (using =VLOOKUP, HLOOKUP and/or MATCH) in the table below, so that when entries in columns Cost Category and Year are entered, column Cost Value is automatically filled out. Use =IF() function to ensure that the
5
cells remain blank if no Cost Category or Year is entered. Explain and justify the functions used in the column Cost Value.
| Cost Category a | Year b | Cost value |
- Entries for Cost Category: Labour, Material, Overhead, Depreciation;
- Entries for Year: Year 1 – Year 5.
Key Marking criteria will include: (See the rubric on the last page of Handbook)
- Calculation: Correctness of the Excel calculations
- Explanation: Description and justification of the methods used
- Analysis: Interest and usefulness of findings, conclusions drawn
- Understanding: Demonstration of understanding of the key topics
- Evaluation: Evidence of independent thinking and critical awareness
- Organisation: Clarity of structure and use of figures
- Writing: Readability and ability to convey ideas concisely and logically
- Overall Quality of Assignment
