Microsoft Word - FIN20014_Assignment with Submission Guideline_Revised
FIN20014 Financial Management: Individual Assignment Sem-1, 2018
FIN20014 Financial Management: Individual Assignment Sem-1, 2018
Finance assignment:
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.
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 the following documents IN HARD COPY SUBMISSION:
· an assignment COVER sheet
ectly submitted>
· a copy of the FORMAL report
· a copy of the EXCEL spreadsheet displaying VALUES
· a copy of the EXCEL spreadsheet displaying excel FORMULAS
Details of Assignment
PENTAG Company produces small powe
oats and faces the challenge to build and market environment-friendly green powe
oat. Dr Pascal Goulpie, the director of PlanetSolar, stated that building an environment-friendly boat is possible for sure, but the market and demand are still niche right now. Many experts believe that resin-infusion technology will lead the industry for cleaner powe
oats in future; however, that will take long time. However, PENTAG is not relying on that new technology to build greener powe
oats. Rather, the company is now evaluating a new project to produce Q-powe
oats that will leave excessive ca
on footprint in the water.
PENTAG invested $500,000 in the last year for designing its Q-powe
oat. Additional $100,000 was incu
ed to promote the boat to a number of distributors. However, considering a few major changes in the previous estimates, the Chief Financial Officer (CFO) of PENTAG Company has instructed to re-evaluate the project. The following further revised estimates are provided relating to this Q-powe
oat project.
The company requires additional plant for building the Q-powe
oat and the plant can be procured from a local importer at a cost of $20,000,000. Additional transportation and installation cost would be $800,000. The plant would have economic life of six years and will be depreciated using straight line rate of 12 per cent for tax purposes. At the end of the project life of six years, the plant is estimated to be sold for $3,000,000.
In addition to the plant, the project will require an initial investment in stock (inventory) of $500,000. Furthermore, projected tied up amount with debtors (accounts receivable) would be $380,000 and it would be partially offset by $180,000 increase in creditors (accounts payable). There will be no further investment in net working capital (NWC) until its final recovery at the end of project life.
Considering positive responses received during promotional programs, and the economy price of $30,000 per boat only, sales manager of PENTAG is very optimistic to sell 650 Q-powe
oats in the first year. Due to competition and water pollution issues, annual sales will decrease by 50 boats every year during remaining life of the project. Within the range of producing 300 to 700 boats per year, variable cost of production is estimated to be 40% of sales revenue. The company will produce boats equal to the number of sales units estimated in a year. Head office of the company will allocate $200,000 for fixed factory overhead per year to this production plant.
PENTAG company is planning to finance this project by issuing 10% debenture of $10 million and the remaining required investment would be financed by equity. Selling Q-Powe
oat will also increase sales of powe
oat parts for $500,000. Cost of production for these parts would be 40 per cent of sales revenue from parts. Starting the Q-powe
oat project will stop other monthly earnings of $10,000 from the production facility of the PENTAG Company.
The company uses required rate of return considering its weighted average cost of capital (WACC) that varies from 20 to 25 per cent in recent time. Management has decided to use 20 per cent required rate to evaluate this project. Corporate tax rate is 30%. The required discounted payback period is 4 years.
A new environment protection group, Save the Waterways, is trying to negotiate with the management of PENTAG Company to stop the Q-Powe
oat project due to its excessive ca
on emission. In this context, company managers have identified another S-Powe
oat project that would be relatively more environment friendly. Initial investment for this S-Powe
oat project would be the same as Q-Powe
oat project and projected future cash flows would be as follows:
Year-1: $6,400,000; Year-2: $7,400,000; Year-3: $7,900,000;
Year-4: $8,600,000; Year-5: $9,300,000; Year-6: $11,100,000;
Before taking final decision in the upcoming meeting, the CFO of PENTAG Company requires a clear explanation of all relevant issues relating to the Q-Powe
oat project. Particularly a FORMAL REPORT is enquired by the CFO to include a detail analysis of cash flows and explanations of results of capital budgeting methods that are commonly used in evaluating projects.
Furthermore, in a separate section in the report, CFO is interested to review the details of the comparison between Q-Powe
oat and S-Powe
oat projects with respect to the results of capital budgeting methods using both 20 and 25 per cent required rates, crossover rate and all relevant factors that can assist in taking final decision.
Required
Using Excel Spreadsheet, prepare a full analysis to be presented to the CFO of PENTAG Company. in evaluating whether either project should be started or not. Your analysis should include the following
· Table of cash flows
· Use of excel formulae where appropriate (refer eLearning video of Week-6)
· A written report (1500 words, +/- 10%) outlining your recommendation as to whether PENTAG Company should proceed with either project. Justify your recommendations using quantitative and qualitative issues and your analysis of probable risks and benefits relating to the project. Comparison statement is to 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 spreadsheet
ii. Use of excel formulae in organised spreadsheet
iii. Co
ect application of theoretical model
· Overall presentation of answer including the written report.
* Carefully read the Report Format Guide (on page-4) and Marking Ru
ic (on page-5) for required components and presentation of formal report.
The attached marking ru
ic (Excel Assignment Information Page-5) should be considered by students when preparing their assignment for submission.
Students are asked to solve a capital budgeting problem using an excel spreadsheet.
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 [ Must be co
ectly filled in; otherwise, you will forfeit marks] 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 Appendix
Workings
Exact Copy of Excel Spreadsheet displaying values (not edited in Word doc)*
Exact Copy of Excel Spreadsheet displaying Excel formulas (not edited in Word doc)*
* Only the sizes of font and columns of the Excel Spreadsheet can be edited in Word Doc.
Carefully read the following Marking Ru
ic for required components and presentation of formal report.
1 | P a g e
1 | P a g e
1 | P a g e