This assessment is an individual client report.  You must analyse the data, provided by the Client in the CW2 Excel file. The file contains a sample of companies from the UK chemical manufacturing sector. You must write a report using your analysis of the data provided, that meet the requirements of the Client as set out below.  The data set for this assessment is in the CW2 Excel file in the CW2 folder, in the assessment section of Course Resources. You will also find some notes on the measurement of the variables used in a separate Word file. You are required to analyse the data provided using Excel only.  You should use the results of your Excel analysis to create fit for purpose tables and charts, which must be embedded in the body of the report to support your discussion and analysis for the client.

 

The deadline for submission is 13^th January 2021 at 11.59pm. You can submit before the deadline. You should write the report in Word and submit it via the Turnitin link in the Assessment section of Course Resources. Do not submit an Excel file. You will receive individual feedback as comments, summary comments and rubric comments on your report in Turnitin within 3 weeks of the submission date.

Coursework is marked anonymously. When submitting your coursework, you should use your Student ID number as the ‘title’ in the submission process. Do not use your name.  Also, on your front sheet, you should use your ID number and not your name.  This also applies if you are submitting work late with an EEC or LSR.

You can submit your work before the deadline.  If you miss the deadlines your work will be deemed as late.  Penalties apply unless you have been granted additional time from  a Study Plan, or from a submission for  Exceptional Extenuating Circumstances (EEC) or Late Submission Request (LSR) all of which require evidence. The new undergraduate regulations on late submission penalties are available in section F6 at:

https://www.derby.ac.uk/media/derbyacuk/assets/departments/the-registry/academic-regulations-2018-aug/Part-F-Asst-regs-for-UG-progs-2019-20-web-v1-updated-following-LSR-amendment-v1-LSR-amends.pdf

 

Assessment Requirements

 

You must write an individual report analysing only the data provided. The data is real UK data from a specialist database.  There are more than 2,000 chemical manufacturers registered in the UK, but consistent data on the variables in this data set is only available for the sample you have in the data set for the latest year available, 2019.  The data includes measurements of performance, size, and other variables. The overall aim of your analysis is to help the client understand the UK chemical manufacturing sector.  The client is thinking about investing in this sector and wants you to analyse the available data provided in the Excel file.  You should use your analysis to provide the client with a better understanding of the performance potential of different groups of companies in the sector.  Your report must provide only the analysis that the client has requested, and your discussion must explain why the analysis might be relevant to the client as they think about investing in the sector.  Your job is not to decide whether the client should invest or not, or to do financial analysis for the client. Your job is to provide the analysis that the client requests, and provide recommendations drawn directly from the analysis. Only use the data provided.  Do not add new or additional data or information from other sources.  Only use the techniques we have covered in the lectures.  Do not use evidence or techniques that we have not covered in the lectures. You can only use EXCEL spreadsheet for your analysis that we have used in the lectures and/or computer labs.

 

Any evidence that students have colluded or copied each other or used the work of a source without reference and citation will be investigated and may result in a plagiarism investigation.  If you use techniques we have not covered in the lectures, it will be treated as a major failure to meet the requirements of the CW2 brief.

The Client’s Requirements

The client is a business investor.  This client has not invested in the UK chemical manufacturing sector before.  The client wants you to analyse the data they have provided and present the analysis as a business report of no more than 2,500 (excluding tables, charts and appendices). The report should help the client in their aim to better understand company performance in the UK chemical sector and how the information from this data set might be used when thinking about investing in the sector.  The client has requested the following from you:

 

 

 

 

The client requires you to provide:

 

1. An analysis of the distribution of the profit rate across all companies in the data set.
2. An analysis of the distribution of the profit rate, comparing the large and SME (small & medium sized) firms in the data set.
3. An analysis of the distribution of the profit rate, comparing manufacturers in the data set by their export activity.
4. An analysis of the distribution of Return on Capital Employed comparing large and SME manufacturers in the data set.
5. An analysis of the export activity of the manufacturers in the data set against their size, measured by whether they are large or SME companies
6. An analysis of the extent to which the profit margin across all manufacturers in the data set is associated with the other quantitative variables in the data set.
7. An analysis of the extent to which the profit margin for manufacturers in the data set that can be predicted using the other quantitative variables in the data set.

 

 

1. In order to meet the client’s requirements, you MUST use all the following methods in your analysis in the report:

 

1. Analysis of required variable distributions (using descriptive statistics and histograms) with appropriate explanations and comments on what your findings imply for the Client’s aim.
2. Hypothesis tests of the difference of means with appropriate explanation and comments on what the tests results imply in relation to the Client’s aim.
3. Cross tab analysis with appropriate probability pattern discussion, and associated Chi squared test with explanations of what the results imply in relation to the Client’s aim.
4. Correlation analysis with appropriate explanations of what the results imply in relation to the Client’s aim.
5. Bivariate regression analysis with appropriate explanations of what the results imply in relation to the Client’s aim. The regression output should be reported and the interpretation of the results is required.

Please include all EXCEL output in the appendix.

 

2. You must choose which techniques and variables to use for each of the client’s stated requirements.

 

3. Each part of the analysis must be explained. You must say why you chose the variables, why you chose the technique, give appropriate explanations of any results (any calculations should be put in the appendix and not in the body of the report). You need to ensure you interpret the results of techniques used but you do not need to explain calculations to the client. You must provide a full discussion of what your results mean for the client within each part of the analysis (not at the end of the report). Failure to do this will result in very low marks. Marks are mainly awarded for presenting and interpreting the output of Excel for the Client in terms of what it means, not for getting Excel to produce the output. For example, if you say the distribution is skewed, you need to show the evidence and explain why this might be of interest to the client in terms of their understanding performance in the UK chemical manufacturing sector and the client’s plans to invest in the sector.

 

4. Any tables or charts used must be ‘fit for purpose’ – You must adapt the Excel tables and charts so that they meet the client needs and are fit for presentation in a formal business report. When you have done this, you can cut and paste them into the report. Any fit for purpose charts or tables should be put in the body of the report (not in the appendix) and every table and chart should be numbered, with titles, and have beneath it, a full explained and discussion saying what the table or chart shows and how this relates to the client’s aim.

 

The report must be set out as follows:

 

5. Write your analysis as a typed report with an appropriate title page, contents page, section headings, numbered and labelled figures and tables, and appendices. You do not need an Executive Summary.
6. You must provide an introduction to your report. Your introduction must state your overall aim and your objectives in order to meet the client’s requirements.
7. You must provide evidence in an appendix showing how you did the analysis and how your results were calculated. Normally this means providing a ‘screen shot’ of part of an excel sheet showing the Excel results. This evidence should be put in the appendix.  Do not include a screen shot of all the data.
8. You must provide a conclusion with a summary of your results and recommendations. Your conclusion should summarise what you have already discussed in your analysis, and summarise your recommendations, which should already have been discussed within the analysis. Summarise your findings. Clearly state whether your analysis has met your objectives and why.  If you think your report has some weakness, explain what it is and why.  Your recommendations must be linked to your conclusions. You will gain marks for this type of critical review.
9. The word count (excluding tables and appendices) is a MAXIMUM OF 2,500 words for the whole report. This report can be well done in fewer than 2,500 words if well written and well focused.  You should choose how many words are appropriate for each section in your report.  You must provide a word count on the Front Page of the Report.
10. The word count (excluding tables and appendices) is a MAXIMUM OF 2,500 words for the whole report. This report can be well done in fewer than 2,500 words if well written and well focused.  You should choose how many words are appropriate for each section in your report.  You must provide a word count on the Front Page of the Report.  If you exceed the word limit you will lose marks as follows:
    1. 10 marks deducted for exceeding the word limit by up to 250 words
    2. 20 marks deducted for exceeding the word limit by 251-500 words

• 30 marks deducted for exceeding the word limit by 501-750 words

1. 50 marks deducted for exceeding the word limit by more than 750 words.

11. You should reference your sources. You are not required to read about the UK chemical manufacturing sector.  But you are expected to cite sources that relate to your Excel analysis and statistical techniques.   You should cite and reference all materials you use including lecture notes, web pages, on-line books, hard copy books, etc.  A citation should be provided whenever you are using a statistical technique. These will include text books and web sites.Remember, the client may want to consult a source if they are not familiar with your technique, so a citation in a report can be used to guide the reader to some supporting material.  References to sources must be provided using the Harvard method in the text of the report and all sources should be fully identified in a bibliography at the end of the report.

 

 

 

 

The criteria for marking

 

The following table shows the criteria and weighting used to award the final grade for your report. Detailed feedback will be provided individually on your reports in Turnitin within 3 weeks of the deadline submission date.

 

Quantitative Skills for Business 5EC511

 

Coursework 2 Rubric

 

 

 

 

 

 

 

 

 

 

Criteria	Weighting	0-34	35-39	40-49	50-59	60-69	70-79	80-100
Introduction, conclusions, and recommendations	15%	Invalid statement of purpose (aim) method and conclusions and recommendations not drawn from the analysis.	A weak statement of purpose (aim) method and conclusions and recommendations not clearly drawn from the analysis.	A basic statement of purpose (aim) method and conclusions and recommendations drawn from the analysis. But limited and lacking links to the appropriate content of the analysis	A good statement of purpose (aim) method and conclusions and recommendations drawn from the analysis. May be too long	Very good introduction, conclusions, and recommendations. A clear and purposeful statement of purpose (aim) method and conclusions and recommendations drawn from the analysis. Short and clear.	Excellent introduction, conclusions, and recommendations. A very clear and purposeful statement of purpose (aim) method and conclusions and recommendations drawn from the analysis. Short and clear.	Outstanding introduction, conclusions, and recommendations. Exceptional statement of purpose (aim) method and conclusions and recommendations drawn from the analysis. Short and clear.
Quality of report style including ‘fit for purpose’ presentation of results, charts and tables, logic and argument, etc.	15%	Inappropriate style and no valid logic in argument.	A weak style, poor presentation, and limited argument. Considerable weakness in argument and presentation style.	A fair style, presentation and argument. Considerable weakness in argument and presentation style.	A good style, presentation and argument. Some weakness in argument and presentation style.	A very good style, presentation, and argument with some minor weakness.	Excellent style, presentation, and argument with some minor weakness.	Outstanding style, presentation, and argument with some minor weakness.
Choice and use of appropriate techniques and variables in Excel	30%	Incorrect and invalid techniques and variable choice.	Poor use of variables with limited choice of techniques in Excel for the variables. Many important elements of analysis missing or incorrect.	A weak use of variables with limited choice of techniques in Excel for the variables. Several important elements of analysis missing or incorrect.	Good use of most of the appropriate variables with some relevant choice of techniques in Excel for the variables. Some important analysis missing or incorrect.	Very good use of all the appropriate variables with good choice of techniques in Excel for the variables. Some small errors or incorrect approach.	Excellent use of all the appropriate variables with good choice of techniques in Excel for the variables. Few error	Exceptional use of all the appropriate variables with appropriate techniques in Excel for the variables. No errors.
Appropriate analysis of results leading to achievement of stated aims	30%	Insufficient use of basic analysis. Important variables missing, weak link to the aims with limited interpretation. Many omissions/errors.	Minimum level of basic analysis. Some relevant variables missing, only weak link to the aims with basic interpretation. Several omissions/errors.	Evidence of basic analysis of some relevant variables for the aims with basic interpretation. Several omissions/errors.	Good try at quality analysis of all relevant variables for the aims with full interpretation. Some small omissions/errors.	Very good analysis of all relevant variables for the aims with full interpretation.	Very high-quality analysis of all relevant variables for the aims with full interpretation	Exceptional quality analysis of all relevant variables for the aims with full interpretation
Following general requirements set out in the brief	10%	Has not followed the brief in most part. Many omissions and errors in relation to the brief provided. Maybe too long or lacking thought on presentation. Poor attention to detail/use of Excel.	Partly correct but some omissions and errors in relation to the brief provided. Maybe too long or lacking thought on presentation. Poor attention to detail/use of Excel.	Generally correct but some omissions and errors in relation to the brief provided. Maybe too long or lacking thought on presentation.	Good attention to the required details in the brief, including word count, referencing, diagram use etc. Some small error or omissions.	Very good attention to the required details in the brief, including word count, referencing, diagram use etc.	Excellent attention to the detail required in the brief, including word count, referencing, diagram use etc.	Exceptional attention to the detail required in the brief, including word count, referencing, diagram use etc.

 

Solution: