1. INDIVIDUAL ASSIGNMENT:

You are working as a Business Analyst in a Small Consulting firm and your CEO has asked you to provide a report on the valuation of a listed company. Given your CEO’s request, you decided to propose and use a business analytics (BA) tool to visually analyse the organization’s database from the previous 5 years.

The assignment is to develop a Business Analytics solution that contains:-

1. Data Extraction Transformation and Loading (ETL) processes.
2. Business Intelligence Dashboards for visual reporting of the company’s performance.
3. Propose strategies for overall business process improvement in 2020.

Your tasks:

1. You are to choose a company listed on the UK Stock Market to analyse,
2. Build an analytic toolkit for valuing the chosen company using accounting based valuation models and submit the toolkit on Blackboard.

3. Based on your analysis and valuation results write a report for the CEO on the chosen company.

The report must include the following:

An Executive Summary: Report which models you used to answer the questions above and summarize the key results of the analysis (Note this must be non-technical).

Business Environment – Description of business, management and governance, industry overview and competitive position.

Model Building, Documentation and Validation:

Document your analytic tool, assumptions and the valuation model and discuss steps that could be taken to improve the analysis including any reservations or justification of decisions (Note this must be non-technical).

Business Process Management and Key Challenges: Discuss key characteristics and challenges of the company using relevant ratio analysis over a 5 year period. Split your analysis into sub-sections to discuss Profitability, Activity/Efficiency, Liquidity, Gearing and Investor ratios.

Findings and Recommendations: Provide the findings from your valuation toolkit and recommend strategies to the CEO on how the company can enhance its value. Be specific with your recommendations.

Your will require processing the following:

1. Introduce and provide descriptions of the main Information Systems used as Data Source in the selected organization.
2. Select and choose any analytical tool like Microsoft Excel Tool, PowerPivot and Solver.
3. Extract, Load and Transform 5-years historical data (P&L, Balance Sheet and Cash flow statements) of the chosen company in Excel
4. Develop an analytical tool in Excel and provide a valuation of the chosen company using both the Residual Income based and the Dividend Based valuation methods.
5. Identify and discuss the company’s current problems and develop a BA solution to synthesize the organization’s performance. (Hint: Refer to Industry averages or competitors position)
6. Based on the information derived from the BA solution and the challenges identified, propose new strategies for the company’s direction and improvement in the following year.
7. Provide an executive report communicating your results to the CEO. Your report should include a dashboard with diagrams and illustrations wherever applicable using the Microsoft PowerPivot or any other appropriate excel toolkit.

1. REPORT REQUIREMENTS
2. This is an Individual assignment.
3. The length of the report should not exceed 1,500 words.
4. The report should have a word count at the end.
5. The report should be prepared using font type: Times New Roman, font size: 12 (except titles and headings) and 1.5 line spacing.
6. The report has to be well presented and should be typed. Submission of reports that are unprofessional in its outlook (disorganized, inconsistent look, varying coloured paper and size) will not fare well when marks are allocated.
7. The report should have a one (1”) margin all around the page as illustrated below:

1. The report should consist of your own work. Any ideas extracted from other sources must be referenced and cited within the report (Harvard Referencing).
2. Deliverables:

• Must submit softcopy of the report on Blackboard.
• Must submit Excel toolkit of the Analytic solution on Blackboard.

1. The format of the report must contain the following:

• Cover Page
• DMU logo
• Full Name of the student and Student Identification Number
• Module code and Module title
• Assignment title
• Name of lecturer
• Submission date
  • Table of Contents
• Must be generated using built in or automated table of contents
• The table of contents should contain the topic, title and the reference page number attached to each topic.
  • Citation and References (Harvard Referencing)
  • Appendices
• Evidence of the source of the dataset(s)

C. MARKING CRITERIA

The assignment will contribute 40%, towards the FINAL assessment, as mentioned in the Module Description. The following criteria would  be used in assessing the your outputs.

Explanatory notes

Report structure and Documentation: This includes executive summary, introduction, business environment, model building documentation and validation, conclusion and recommendation, appendix and reference of the report. Materials in report are presented professionally using correct grammar, punctuation and spelling. Report includes the cover page, table of content, page numbering, headings, level of language proficiency and; proper sequencing and flow of facts in the report.

Business Environment: A summary description of the business, the management, governance, industry overview and competitive position.

Data Extraction, Transformation and Loading: This includes capturing the data in excel, transforming the data using common size and ratio analysis and performing validation checks. You must submit excel sheet to score marks here.

Model Building and Documentation: This includes making valid assumptions, including a calculation sheet, proforma statements and building the valuation model for a final valuation estimate. You must submit excel sheet to score marks here. You do not have to print the excel sheets. Transport relevant tables and graphics to report.

BA dashboard, Visualizations and Reporting: This includes the use of appropriate diagrams, charts and pictures used as supporting facts in the report mainly exported from Microsoft PowerPivot toolkit or excel charts. You do not have to print the excel sheets. Transport relevant tables and graphics to report.

Research Analysis, Key Challenges and BPM: This includes analysis and evaluation of facts by providing relevant content in the report and identifying challenges to the business and making appropriate recommendations for performance improvement of the business. [Hint: consider the ratios, provide scenarios use the excel solver to demonstrate how the business value can be enhanced]

Lifelong Learning and Information Management Skills: This includes proper use of citation and referencing; following the Harvard Referencing System for all facts and diagrams used and evidence of research and reading. This includes the relevance, complete, accuracy, organised, meaningful, up-to-date and coherence of the facts included.

Please take note that Plagiarism is a serious offence and will be treated strictly as per rules and regulations of DMU. You are required to submit your assignment on blackboard with the accompanying reports and excel worksheet before 12.00noon on the submission date.

D.                                                               SAMPLE PROJECT REPORT TEMPLATE

There is a limit of 1,500 words.

To:          CEO

From:    ABC

Date:     22/03/2020

Title:      Analytic Valuation of XXX PLC.

1.       Executive Summary

2. Business Environment
   • Business Description
   • Governance and Management
   • Industry Overview
   • Competitor
   • Market Positioning

3.       Data, ETL

• xxx
• xxx

4. Valuation Model Documentation and Validation

4.1

4.2

5.       BPM and Key Challenges

6.       Findings and Recommendation

7.       Appendix

8.       References