FIN20014 Financial Management: Individual Assignment Sem-2, 2020
Faculty of Business and Law
Higher Education Division
FIN20014 Financial Management
Semester 2 2020
The objective of this assignment is to encourage the students to use excel spreadsheets to aid in problem-solving. Students are asked to solve a capital budgeting problem using an excel spreadsheet.
Weighting: 20% of total assessment.
Due Date:
11 October Sunday by 10:00 pm.
Late Submissions:
You must contact the Unit Convenor via email for any extension needed under the ONLY emergency situation. Assignments cannot be submitted online after 10:00 pm on Sunday 11 October unless an extension has been approved.
Format:
The assignment is a problem-solving exercise using an excel spreadsheet with additional discussion on findings considering both quantitative measures and qualitative issues.
Documents:
Students should submit two files IN SOFT COPY SUBMISSION:
a Word Version copy of the FORMAL report of 1500 +/- 10% words
(follow the suggested structure on Page-5)
the EXCEL file (Follow the structure discussed in eLearning video of
Week-6, show all digits of figures, DO NOT reduce figures in $million)
Online Submission:
Follow the link available on Canvas.
Details of Assignment
CHEMIONE Inc. has a long reputation for manufacturing reliable drugs. The company has been spending on research and development (R&D) for a next-generation diabetes drug since July 2018. Although the firm has spent $1.60 million for R&D during the last 2 years, it is now predicted that achieving a target result in this research may take another 4 to 5 years’ time. In the meantime, despite the huge controversy, the company is planning to introduce the revolutionary pre-version of this drug, called D-SAFE (commercial name), which might cause long-term health hazards for some patients due to yet to be known reasons. Before introducing this product in the market, the Chief Financial Officer (CFO) of CHEMIONE Inc. is asking for a detailed analysis of the D-SAFE project.
The production line for D-SAFE can be started after renovating one existing section of the factory. The project is expected to run for five years when the targeted final version of D-SAFE will be ready to introduce. Required renovation can be conducted immediately at a cost of $157,000 that includes installation cost of new plant and equipment (P&E). The company has decided to capitalise total renovation costs to new P&E. The pre-launching expenses required for the project will be a one-off payment at the beginning and estimated to be $370,000. The project requires continuing quality assurance inspection that will cost $5,000 per month.
A local distributor of a Swedish company can immediately supply all required parts and accessories of the new P&E for a total charge of $5,100,000 including import duty of $250,000. In addition, for new P&E, transportation cost is estimated to be $43,000. These P&E would be
1 | P a g e
FIN20014 Financial Management: Individual Assignment Sem-2, 2020
depreciated using a tax allowable straight-line rate of 15% on prime cost. The company can sell P&E at the termination of the project for $520,000.
It is projected that the annual sales of D-SAFE would be 48,000 cartons, which will require CHEMIONE to operate at 80% of its capacity when the variable operating cost will be 45% of sales. The selling price per carton will be $120. Annual fixed operating costs, excluding depreciation, will be $940,000. It is estimated that the production line will be required to operate at full capacity after the first three years due to increasing demand. Variable operating cost at full capacity would be 40% of sales.
The existing section of the factory, where the new P&E will be installed, is in use by a subcontractor who pays a monthly rent of $8,000. This rent income for CHEMIONE will discontinue once the new production line D-SAFE will commence its operation. It is also estimated that the new production line will require an initial increased investment of $108,000 in stock (inventory) and $46,000 in debtors (accounts receivables) that are offset by an increase in creditors (accounts payable) of $54,000. There will be no further investment in net working capital (NWC) until its final recovery at the end of project life.
The company uses the required rate of return considering its weighted average cost of capital (WACC) that varies from 15 to 19 percent in recent times. The analyst is confused about the rate to be effective for the project; however, she has decided to use a 15 percent required rate to evaluate this project. The corporate tax rate is 30%. The required discounted payback period is 4 years.
Considering the probable consequences of introducing pre-version of the drug, company managers have identified another D-CONT project that would be based on a safe but relatively less effective traditional treatment plan. The initial investment for this D-CONT project would be the same as the D-SAFE project and projected future net cash flows would be as follows:
Year-1: $2,375,000;
Year-4: $1,520,000;
Year-2: $2,084,000;
Year-5: $1,349,000;
Year-3: $1,840,000;
Year-6: $1,130,000.
Before taking the final decision in the upcoming meeting, the CFO of CHEMIONE Inc. requires a clear explanation of all relevant issues relating to the D-SAFE project. Particularly a FORMAL REPORT is enquired by the CFO to include a detailed analysis of cash flows and explanations of results of capital budgeting methods that are commonly used in evaluating projects. If the project is initiated, the CFO would also be interested to check whether the project would be feasible to contribute $24,000 annual expenditure for ongoing R&D activities of the firm.
Furthermore, in a separate section within the report, the CFO is interested to review a detailed comparison between D-SAFE and D-CONT projects with respect to the results of capital budgeting methods using both 15 and 19 percent required rates, crossover rate, and all relevant factors that can assist in making the final decision.
Required
Using Excel Spreadsheet (as explained in the eLearning video of Week-6), prepare a full analysis to be presented to the CFO of CHEMIONE Inc. in evaluating whether either project should be started or not. Your analysis should include the following
Table of cash flows (Show all digits, do not convert amounts to $ in million or thousand)
Use of excel formulae where appropriate (refer eLearning video of Week-6)
A formal report (1500 +/- 10% words, word-count excludes table of content, executive summary, tables, references, and appendices) outlining your recommendation as to whether CHEMIONE Company should proceed with either project. Justify your recommendations using quantitative and qualitative issues and your analysis of
2 | P a g e
FIN20014 Financial Management: Individual Assignment Sem-2, 2020
probable risks and benefits relating to the project. A comparative statement using 15 percent and 19 percent required rate is to be presented in a separate section in the report.
Marks will be awarded for:
Set out of spreadsheet (watch eLearning video of week-6)
i.Ease of reading the spreadsheet
ii.Use of excel formulae in an organised spreadsheet
iii.Correct application of the theoretical model
The overall presentation of answer including the written report.
* Carefully read the Report Format Guide (see below) and Marking Rubric (on Canvas) for required components and presentation of the formal report.
Total Marks for the assignment = 20 marks Organised Excel Spreadsheet = 10 marks and
Concise Formal Business Report (1500 +/- 10% words) = 10 marks
Late assignments will be determined based on the SOFT COPY SUBMISSION time as recorded in TURNITIN. Late Assignments cannot be uploaded unless an arrangement has been made directly with the Unit Convenor, Miraj Ahmmod (sahmmod@swin.edu.au)
SUGGESTED FORMAT FOR ASSIGNMENT REPORT
Components of a FORMAL report are expected in the assignment structure. Following table shows one example of major sections: (other sections may be added, if needed)
Structure Example
COVER PAGE [NOT REQUIRED]
Executive Summary
Table of Contents
Body
1.Introduction
2.Findings
2.1 Quantitative (with explanation of results)
2.2 Qualitative
3.Recommendations and Justifications
4.Detail Comparison and Further Recommendations
5.Conclusion
References: List of all in-text references used in the report
Appendix
Workings
Extract from the Excel Spreadsheet displaying
3 | P a g e
FIN20014 Financial Management: Individual Assignment Sem-2, 2020
FREQUENTLY ASKED QUESTIONS and SUGGESTIONS:
Q1. Do I need to submit a hard copy of the assignment?
A.NO. Only soft copy submission would be marked. Hard copy submission would not be marked.
Q2. How many files are to be uploaded?
A.TWO. The copy of the formal report in Word Version and the Excel file showing the table of cash flows with calculations using Excel formulas as explained in elearning video of Week-6.
Q3. Can I get any help in understanding the assignment question?
A.Whilst we (the members of the Finance teaching panel) understand your concern, we cannot answer to any direct query you may ask relating to the assignment. The problem has specifically been included in the assignment for students to ponder and respond. It is expected that you have clarified all assumptions you may have made in your assignment report.
Q4. Are there specific resources that can help me understand the assignment questions?
A.YES. Issues relating to the assignment are discussed in relevant lectures / collaborate sessions (Week-5 & 6) multiple times and/or in different ways. To assist you with understanding the concepts of finding Net Cash Flows, it is suggested that you review the lecture illustrations discussed on relevant topics (4a & 4B). You can also check relevant past exam questions and solutions, Sunburst Case and solutions, as well as your text and lecture materials for that topic. All these resources, included recorded lectures and notes written in lectures, are available on Canvas. The eLearning video of week-6 will guide you about the way to prepare cash flow table using Excel.
Q5. Can I send my works on assignment to any teaching staff for having any comment and/or guidance?
A.NO. This assignment is an individual assessment item and members of the Finance teaching panel can neither view the spreadsheet nor make comments on it before submission.
Q6. What would be the content and structure of my report?
A.A sample of format is provided on page-3 and the marking rubric is available on Canvas under Excel assignment. If you go through these documents, it will give you an idea about the structure and content expected in your report. You are required to provide necessary calculations, results, logical explanations of findings, and justifications to convince the manager of the firm to support your recommendations.
Q7. What will happen if my assignment is similar to another submission?
A.Both the similar assignments will be sent to a review committee for identifying the probable cases of plagiarism. In the case of identified plagiarism, further steps would follow the University Policies.
4 | P a g e