Reference no: EM131974005 , Length: word count:1500

Financial Management 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.

Details of Assignment

PENTAG 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, PENTAG is not relying on that new technology to build greener powerboats. Rather, the company is now evaluating a new project to produce Q-powerboats that will leave excessive carbon footprint in the water.

PENTAG invested $500,000 in the last year for designing its Q-powerboat. Additional $100,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 PENTAG Company has instructed to re-evaluate the project. The following further revised estimates are provided relating to this Q-powerboat project.

The company requires additional plant for building the Q-powerboat 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-powerboats 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-Powerboat will also increase annual sales of powerboat parts for $500,000. Cost of production for these parts would be 40 per cent of sales revenue from parts. Starting the Q-powerboat 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-Powerboat project due to its excessive carbon emission. In this context, company managers have identified another S-Powerboat project that would be relatively more environment friendly. Initial investment for this S-Powerboat project would be the same as Q-Powerboat 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-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 Q-Powerboat and S-Powerboat 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) 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.

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

Attachment:- Assignment File.rar