Faculty of Business and Law
Higher Education Division
FIN20014 Financial Management
Semester 2 2019
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:
Submit soft copy of your assignment via ‘turnitin’ link provided on Canvas by 10:00pm on 11 October Friday.
Late Submissions:
You must contact the Unit Convenor via email for any extension needed under ONLY emergency situation. Assignments cannot be submitted online after 10:00 pm on Friday 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 the following documents IN SOFT COPY
SUBMISSION [ALL MERGED IN ONE FILE ONLY]:
• an assignment COVER sheet Student will forfeit assignment mark if there is any incorrect information on cover sheet
• a copy of the FORMAL report
• a copy of the EXCEL spreadsheet displaying VALUES
• a copy of the EXCEL spreadsheet displaying excel FORMULAS
Online Submission: Link and details will be available on Canvas.
THIS ASSIGNMENT REQUIRES NO HARDCOPY SUBMISSION.
Details of Assignment
SONICJET Company produces small powerboats and faces the challenge to build and market environment-friendly green powerboat. 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 powerboats in future; however, that will take long time. However, SONICJET is not relying on that new technology to build greener powerboats. Rather, the company is now evaluating a new project to produce V-powerboats that will leave excessive carbon footprint in the water.
SONICJET invested $400,000 in the last year for designing its V-powerboat. Additional $150,000 was incurred 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 SONICJET Company has instructed to re-evaluate the project. The following further revised estimates are provided relating to this V-powerboat project.
The company requires additional plant for building the V-powerboat and the plant can be procured from a local importer at a cost of $12,000,000. Additional transportation and installation cost would be $40,000 and $160,000 respectively. The plant would have economic life of five years and will be depreciated using straight line rate of 15 per cent for tax purposes. At the end of the project life of five years, the plant is estimated to be sold for $4,500,000.
In addition to the plant, the project will require an initial investment in stock (inventory) of $200,000. Furthermore, projected tied up amount with debtors (accounts receivable) would be $190,000 and it would be partially offset by $40,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. There will be initial market penetration expenses of $100,000 at the beginning of the project.
Considering positive responses received during promotional programs, and the economy price of $30,000 per boat only, sales manager of SONICJET is very optimistic to sell 400 V-powerboats in the first year. Due to competition and water pollution issues, annual sales will decrease by 30 boats every year during remaining life of the project. Within the range of producing 200 to 400 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. As per allocation from Head office of the company, this production plant requires to pay $120,000 per year for fixed factory overhead.
SONICJET company is planning to finance this project by issuing 10% debenture of $5 million and the remaining required investment would be financed by equity. Starting the V-powerboat project will stop other monthly earnings of $5,000 from the production facility of the SONICJET Company. Selling V-Powerboat will also increase monthly sales of powerboat parts for $20,000. Cost of production for these parts would be 40 per cent of sales revenue from parts.
The company uses required rate of return considering its weighted average cost of capital (WACC) that varies from 15 to 20 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.5 years.
A new environment protection group, Clean-Our-Waterways, is trying to negotiate with the management of SONICJET Company to stop the V-Powerboat project due to its excessive carbon emission. In this context, company managers have identified another T-Powerboat project that would be relatively more environment friendly. Initial total investment for this T-Powerboat project would be the same as V-Powerboat project and projected future cash flows (after all adjustments) for this six-year project would be as follows:
Year-1: $3,600,000; Year-2: $4,200,000; Year-3: $4,600,000;
Year-4: $5,000,000; Year-5: $5,600,000; Year-6: $6,100,000;
Before taking final decision in the upcoming meeting, the CFO of SONICJET Company requires a clear explanation of all relevant issues relating to the V-Powerboat 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 V-Powerboat and T-Powerboat projects with respect to the results of capital budgeting methods using both 15 and 20 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 SONICJET Company 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 written report (1500 words, +/- 10%) outlining your recommendation as to whether SONICJET 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 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 spreadsheet
ii. Use of excel formulae in organised spreadsheet
iii. Correct application of theoretical model
• Overall presentation of answer including the written report.
* Carefully read the Report Format Guide (on page-4) and Marking Rubric (on Canvas) for required components and presentation of formal report.
Submission Guidelines for Excel Assignment
Total Marks for the assignment = 20 marks Organised Excel Spreadsheet = 10 marks and
Concise Formal Business Report (1500 +/- 10% words) = 10 marks
The attached marking rubric (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.
SOFT COPY SUBMISSION ONLY: [NO HARD COPY SUBMISSION]
Submit soft copy of your assignment
• Containing (i) assignment cover sheet; (ii) a copy of the formal report; (iii) a copy of the excel spreadsheet displaying values; and (iv) a copy of the excel spreadsheet displaying excel formulas.
• Submit by 10:00pm on 11 October Friday using the link provided on Canvas.
• Submit ONLY ONE FILE containing all required documents..
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 [ Must be correctly 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 Marking Rubric provided on Canvas under Excel Assignment for required components and presentation of formal report.
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. Can I upload more than one file via Turnitin?
A. NO. You can upload ONLY ONE FILE via Turnitin on Canvas. You cannot change the file once uploaded.
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 assignment are discussed in relevant lectures (Week-5 & 6) for 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. eLearning video of week-6 will guide you about the way to prepare cash flow table using Excel.
Q5. I don’t know how to take copy-paste from Excel spreadsheet to Doc file.
A. Google it! For different versions of Excel, different ways are there. All you can find in Google and Youtube.
Q6. Can I send my works on assignment to any teaching staff for having any comment and/or guidance?
A. NO. 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.
Q7. What would be the content and structure of my report?
A. A sample of format is provided on page-4 and the marking rubric is available on Canvas under Excel assignment. If you go through these documents, it will give you the 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.
Q8. Can I submit hard copy of assignment in the drop box? A. NO. There will be no drop box.
Q9. How do I know the name of my tutor?
A. Tutorial timetable with tutor’s name and contact email are available on Canvas under Modules Unit Information.
Q10. 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 case of plagiarism. In the case of identified plagiarism, further steps would follow the University Policies.