Work Assignment (analysis of financial data using spreadsheets)-
Introduction
This is an individual coursework assignment to help student in distinguish key financial concepts as used in companies' reports and to understand the linkages between the different types of financial report. This will assist quantity surveying and construction management students to gain knowledge and understanding in reporting and critically analyse of financial data. On the other hand, it will create a base for understanding of basic accounting and financial principles, to examine issues of finance applicable to the construction firm and its projects and apply spreadsheet techniques to analyse financial problems.
Outcomes of the assignment
Distinguish key financial concepts as used in company reports and understand the linkages between the different types of financial report.
TASK:
In this assignment, students will have hands-on experience in developing an integrated framework for controlling, monitoring, and analysis of company financial information. In addition the task aims to help the student understand financial issues from a corporate management perspective. It will provide the platform for continuing management development.
Data:
All required data are given in the attached "CF-Coursework-Data" Excel sheet. Assuming the current date is January 2017, the up to date financial reports' (Balance Sheet, Income Statement and Statement of Cash Flow) data are given in Table1, Table2, and Table 3 respectively. Also, the additional transaction data are given in Table 4.
Requirements:
The students are required to develop a report (not less than 10 pages) along with Excel spreadsheets as an aid in preparing and updating of financial reports. Each student is requested to:
1. Submit a proposal in one page (maximum in two pages) to discuss in brief main types of financial reports along with discussing in detail the idea (what do you understand, what are the inputs and outputs) behind CW as a reporting excel tool to monitor the financial flow of any construction company. The proposal mark is 10% (maximum) of the CW marks and should be submitted by students during the 1st week (from the uploading date of the assignment by the instructor to the Moodle).
2. Use the given transaction data (Table 4) to update the missed items in the final financial reports and critically discuss the company financial performance trend (use Excel Formulas to do any required calculations and create Excel charts as a helping tool in the analysis of financial trend "Horizontal Analysis").
3. Use Excel (along with creating charts) to evaluate the depreciation of the equipment at the end of every year (2014, 2015, and 2016) of its service life using Declining Balance (DB), and Sum-of Years Digits (SYD) methods.
4. Based on the financial ratios' calculations, critically discuss in detail the company financial position and the linkages between the different types of financial report (use Excel Formulas to calculate financial ratios and use Excel charts to support your discussion).
Notes:
a. A student may be called for VIVA to discuss his work "if the instructor have a doubt regarding the source of the work (outsourced)".
b. Any missing/incorrect data should be reasonably assumed after getting instructor approval.
c. A student should use his own data without change otherwise his report will not marked.
Report Format
• Number of pages: Maximum 20 pages including attached Excel sheets and references list and the appendix (if any).
• Line spacing: Generally use 1.5 x line spacing.
• Start with the title page including your name and/or ID as requested.
• It is preferable to place figures within the text rather than group them at the end of the report.
• Give a sequential number to all figures (Fig. 1, Fig. 2, Fig. 3, etc.)
• Add a brief, informative caption to all inserted figures and tables.
• All figures and all tables should be introduced and discussed in detail.
• Unclear figures will not accepted.
• Follow Harvard referencing style and references should be cited in the report body.
• All report pages should have numbers (e.g. Page 1 of 15, Page 2 of 15, etc.)
• Any inserted text as images will not accepted.
• Any tricky process to reduce the similarity ratio will not be accepted.
Report Sections
The Report should contain the following sections:
• Abstract (Executive summary)
• Table of contents
• Introduction
• Report body
• Conclusions
• References
1. Abstract (minimum 1/2 page)
The abstract must be brief, but it has to tell the following:
• The main objective.
• Methodology (write about the main sections involved in the report).
• Short conclusion (one or two sentences are recommended).
2. Table of content
• Include heading, subheading, page numbers, list of figures and tables.
3. Introduction (minimum 1 pages)
The introduction is very important as it sets the context for the report. Summarize the brief and focus on its significance, state the report's aim (objective), and describe how the report is organized (methodology). It is not usual to detail findings or recommendations in the introduction.
4. Report body (minimum 8 pages)
The previous sections (abstract, table of contents, introduction) are preliminary sections. Case study's body section is organized as follows:
• The headings are informative and descriptive.
• Discuss the conceptual framework of the essay topic.
• Expound or elaborate the concept by presenting sub -topics relevant to the main topic.
5. Conclusions (minimum 1/2 page)
Every report should include a concluding statement/s on the subject of the report. Restate the aim of the report and state how you have achieved it. Present the main findings and key recommendations in a summarized. You should also restate the limitations of the report.
6. References
This is a list of all the sources of information you have referred to in the report (follow Harvard references format); minimum 8 references. All references listed at the references list should be provided in the text properly.
7. Appendix
Any additional figures, tables, and photos if required.
Attachment:- Data_Spreadsheets.xlsx