Faculty of Business, Law and Entrepreneurship
Faculty of Business, Law and Entrepreneurship
FIN20014 Financial Management
Assignment 2
Semester 1 2024
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 copies via Turnitin through Canvas by or before 11.59 pm on May 5 (Sunday).
Late Submissions:
You must contact the Unit Convenor via email (sahmmod@swin.edu.au) before the due date for any extension needed under ONLY emergencies. Assignments cannot be submitted online after 11.59 pm on May 5 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: (i) a WORD version of the FORMAL report (following the suggested structure on Page 4), and (ii) an EXCEL file with spreadsheet displaying VALUES (keep to two decimal points if needed) using Excel FORMULAS (as explained in the eLearning video of week 6 on Canvas)
Submission:
Follow the link available on Canvas. The assignment requires soft copy submission ONLY.
Details of the Assignment
Prepared by Dr Miraj Ahmmod
Similar problems relating to this assignment have been discussed in the weekly recorded lectures of weeks 5 and 6. It is strongly recommended that students watch the recorded lectures and eLearning videos of weeks 5 and 6 before attempting this assignment. Students not learning weekly topics regularly and consistently will require a longer time to solve the problems of this assignment. These assignment questions have been prepared for students to ponder and respond to. Students will forfeit their marks for not following the instructions provided on pages 3 and 4 of this document.
Question-1
Hypothetical Company Project
AUTO FORCE Ltd is an automation firm that invests much in research and development (R&D) before releasing new equipment, typically designed to save labour time. AUTO FORCE Ltd serves the manufacturing industry through the automation of production facilities. The firm recently spent $700,000 developing AFP-N1, an automatic food preparation system. However, a few random test runs revealed that AFP-N1 is not yet market-ready because it has difficulty distinguishing residue from fine output. Such irregular interruptions need a long restart time. During the reset phase, companies that purchase and install AFP-N1 may lose a significant amount of output. Nonetheless, given the level of rivalry, AUTO FORCE Ltd officials want to release the AFP-N1 machine to be the market leader against other competitors. A local importer will sell AUTO FORCE the equipment required to construct the plant that will manufacture machine AFP-N1 for $15,600,000. AUTO FORCE Ltd must pay an additional $1,900,000 in installation fees while the transportation costs of $500,000 would be paid by the supplier of the plant. The plant's economic life would be six years, and it would be depreciated at a straight-line rate of 15% on prime cost each year.
According to the marketing director at AUTO FORCE Ltd, 300 units of the AFP-N1 machine can be sold in the first year, with sales dropping by 25 units each year for the remainder of the project. The projected selling price per unit is $125,000. As long as at least 200 units are produced and sold each year, the variable cost of production is predicted to be 60% of sales revenue. Once the production and sales drop below 200 units, the variable cost of production will increase to 70% of sales revenue. The fixed costs of operating this factory would be $5,000,000 per year. The production of AFP-N1 will most likely require an initial inventory of $650,000. Also, as sales increase, $525,000 will be locked up in accounts receivables, but this will be substantially offset by a $175,000 increase in accounts payable. The project management intends to maintain the same level of net working capital (NWC) throughout the project's duration. This indicates that there will be no additional fresh investments in NWC during the project's lifetime.
The new plant will be located in a manufacturing space that is currently utilised for storage. Currently, this space yields a net income of $25,000 a month; however, it will be discontinued due to the new plant. Additionally, when AUTO FORCE Ltd sells machine AFP-N1, its annual income from automation consulting fees will decrease by $75,000. After the economic life of this project, the plant would be transferred (sold) to another project for a price of $2,200,000.
If a firm purchases AUTO FORCE Ltd.s AFP-N1 machine, it will eventually replace many of its unskilled and semi-skilled employees with a few skilled ones to increase production efficiency. An Association of Labour Unions is discouraging and opposing firms installing AFP-N1 because it will result in many people losing their employment since they will be redundant. In response to the Association's worries, AUTO FORCE Ltd.s management has identified another project that would manufacture semi-automatic machines SAP-N5 and will require both semi-skilled and skilled people. The initial total investment for this SAP-N5 project would be 10% lower than the total initial cost of the AFP-N1 project, and the predicted future net cash flows (after all adjustments) for this six-year project would be as follows: Year-1: $3,250,000; Year-2: $5,200,000; Year-3: $6,300,000; Year-4: $7,500,000; Year-5: $8,100,000 and Year-6: $4,500,000.
The company utilises its weighted average cost of capital (WACC) to determine its required rate of return. Recently, the WACC of the company is fluctuating between 14% and 18%. Management has chosen to utilise both rates in evaluating this project. The applicable tax rate for this company is 30%. The company also uses a discounted payback period benchmark of 4 years. Before reaching a final decision in the upcoming meeting, the Chief Finance Officer (CFO) of AUTO FORCE Ltd is seeking a comprehensive explanation of the key components of the AFPN1 project. The CFO requests a formal report that includes a thorough analysis of cash flows and clear explanations of the results, utilising appropriate capital budgeting procedures commonly employed for project evaluation. In addition, the CFO is interested in examining the specific details of the comparison between the AFP-N1 and SAP-N5 projects. This analysis will involve evaluating the results of appropriate capital budgeting methods, considering both a 14% and 18% required rate, determining the crossover rate, and considering all quantitative and qualitative factors that can contribute to making a final decision. This comparative discussion of findings is to be presented in a separate section of the report.
Required
Using Excel Spreadsheet, prepare a full analysis to be presented to the CFO of AUTO FORCE Ltd in evaluating whether either project should be started or not. Your analysis should include the following:
(i) Table of cash flows (Show all digits, do not convert amounts to $ in million or thousand) using Excel formulae where appropriate;
(ii) A formal report (3000 4000 words),
Introduction;
Explanation of decision relating to the AFP-N1 project using the lower of two rates;
Explanation of decision relating to the AFP-N1 project using the higher of two rates;
Explanation of qualitative issues relating to the decision for the AFP-N1 project;
Comparison between AFP-N1 and SAP-N5 projects considering both rates and the crossover rate;
Final recommendations;
Font type: Times New Roman, Size 11, Spacing 1.15. The word count does not include the table of content, executive summary, tables, references and appendices) outlining your recommendations as to whether AUTO FORCE Ltd should proceed with either project. Justify your recommendations using quantitative and qualitative issues and your analysis of probable risks and benefits relating to the projects. A comparative statement using the 14% and 18% required rates is to be presented in a separate section in the report.
Carefully read the Report Format Guide (on page 4) and Marking Rubric for the required components and presentation of the formal report.
Question-2
Download the Corporate Governance Principles and Recommendations (CGPR) using this link (CGPR Document). Write a one-page summary, using your own words, about the possible impacts of "Principle-3 Recommendations" (as suggested in the CGPR document) on achieving shareholders wealth maximisation goal through capital budgeting decisions discussed in the recorded lectures.
INSTRUCTIONS Preparing your answers in the Excel File for this Assignment
Use only one spreadsheet of the Excel file to show your workings and Cash flow tables.
Watch the eLearning video of Week 6 (on How to Use Excel Spreadsheet for Capital Budgeting) as available on Canvas under Modules of Week 6 and prepare your Excel file in the same way by using Excel formulas. [You may not get any marks for the Excel presentation if the ways explained in the eLearning video are not followed appropriately.]
Preparing your formal report for the final submission
In a Word doc file, write a formal report of 9-10 pages (A4 size, normal margin, Times New Roman, font size 11 or 12, spacing 1.15 or 1.5) with the following details and headings:
Suggested Format of the Report
Cover page: Write ONLY Unit Code, Unit Title, Assignment-2, Your Full Name, ID
Executive Summary: On page 2 (Briefly mention the objectives and findings of all questions - Max 1 page)
Table of Contents: On page 3
Question 1 Report: On Pages 4-7 (See required in question 1)
Question 2 CGRP Summary: On Page 8
Conclusion: On Page 9
References: On Page 10 (APA 7 style only)
Appendix (if applicable): On Page 11
