Questions:
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. FIN20014_OUA_Financial Management: Individual Assignment Study Period 1, 2018 3 | P a g e 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. Correct application of theoretical model • Overall presentation of answer including the written report. * Carefully read the Report Format and Marking Rubric on page-5 for required components and presentation of formal report
Answers:
Capital budgeting decision is one of the most widely used decision making tool. We are provided with a decision making case for a company PENTAG, in which the management is to take important decision regarding production of one or other type of product. In our discussion in the below report, we have implement qualitative and quantitative research on the options available with the company. Based on both the aspects the company is required to the final decision.
2.1 Quantitative
The company has the option of producing Q-powerboats. The management of the company after detailed study has been able to collect financial data on the expected cash flows of the company. We have conducted capital budgeting technique in respect to this proposal in order to come to conclusion on financial viability of the project.
Net Present Value
We have calculated the net present value of the said project. Net present value is the capital budgeting tool under which the difference between the present values of cash outflows are deducted from present value of cash inflows (Adelaja, 2015). This represents the excess cash flow earned over invested amount. If the amount is positive then the project seems viable and should be accepted, but if the project NPV is negative it should be rejected. NPV is calculated using the following formula:
The net present value of the investment opportunity for PENTAG for production of Q-Power board with a required of 20% resulted in $5812640. This indicates that the project is likely to generate value for the company. Based on these calculations, the project is expected to create value for the company and should be accepted.
Pay-back period
Pay-back period is the capital budgeting tool that helps the investor determine the time period within which the invested amount in a project will be recovered (Bierman & Smidt, 2010). For example, we have a project with life of 5 years, which requires 10000 investments in the beginning, the pay-back period will calculate the time span within which this invested amount of $ 10000 will be recovered from the cash flow of the project. Pay-back period is calculated using the following formula:
Pay-back period | = | a+(b/c) |
Where, | ||
A | = | the period with last negative cumulative cash flow |
B | = | the amount of last cumulative cash flow |
C | = | the cash flow just after the period a |
The pay-back period of the said project amounted to 2.53 years. The required pay-back for this project by the management is 4 years. Therefore, we see that the company will recover its invested amount before its required time. Based on this the project seems worthy of acceptance.
Internal rate of return
Internal rate of return is the hidden rate of return that is actually earned on the project. The internal rate is calculated by equating the cash outflows and inflow, then using the method of trial and error the hidden rate of return in calculated (Datar, 2016). If IRR is more than the required rate of return then the project should be accepted and if it is lower than it should be rejected. For the given project of production of Q-Powerboats the internal rate of return is 31%. Since the IRR is more the required return the project should be accepted.
2.2 Qualitative
Besides the financial aspects, there are other qualitative factors also which the company should take into consideration before accepting a new project. (Seitz & Ellison, 2009) Few of these factors have been discussed in the below report:
- Impact on the environment: it is important that before a new project is implemented, the environmental impact of such project is evaluated. In the given case the project is expected to have increased carbon emissions which are to harm the environment. Besides from social obligation, the company might have to face penalties for harming the environment. Hence before a final call impact on the environment should be evaluated(Dayananda, Irons, Harrison, Herbohn, & Rowland, 2008).
- Strategic factors: few capital budgeting decisions involve execution of projects which are not related to the direct goals of the company. The management should not lose sight of the main objects of the company while taking such decision(Holtzman, 2013).
- Employee Morale: the health of the employees plays a very important in growth of business. Before taking up a new project the management should evaluate the effect of such project on employees. If they think that employees can take up and execute and take up the new project willingly, then only the new project should be accepted.(Menifield, 2014)
- Future demand for the new product: the management should evaluate the market for the new product before launching a new product. A proper market research should be conducted so that the management can evaluate the life of the demand for new product. If the demand is not expected to last for a long term period then executing a new project will be in vain.(Noreen, 2015)
Therefore, before a new project is executed, it is important that the management studies all the aspects properly. (Shapiro, 2007)Effect of all major factors should be consideration before taking the final capital budgeting decision.
Capital budgeting decision is based on a lot of detailed investigation. (Peterson & Fabozzi, 2012) There are various rules and regulations which are required to be followed in order to evaluate the feasibility of the project properly. For example, the expense which been already incurred by the company on market research and project development have not taken into consideration while taking the decision. This is so because these expenses have already been incurred and the decision o f acceptance or rejection of project will not affect the expense. These expenses are sunk cost, and are not considered in evaluating the capital budgeting projects. (Siciliano, 2015)
Taking all the above data into consideration we can see that the project is to create value for the company. It is expected to generate profits up to $5.8 million. The pay- back period of the project is lesser than the required pay-back period by the managers. Hence, we would recommend the management to accept the project of production of Q-powerboats.
The management has been approached by an environmental group to take into consideration another project of S-powerboats in place of Q power boats, since they are environmental friendly. The management wants to do a comparative study on the resulted of both the projects. In our discussion below we have made a detailed study on comparative results of both the projects.
We have conducted net present value analysis of both the project at the required return of 20 % and 25%. The project which provides the highest return to the company should be opted for. The following table shows the NPV results from both the projects:
Particulars | Q-Power boats | S-Power Boats |
NPV @ 20% | 58,12,640 | 11,65,321 |
NPV @ 25% | 27,86,179 | -17,31,545 |
We see that the company is expected to have positive net present value form production of Q-powerboats with the required return of both 20% and 25%. But the project of s power boats are to have positive NPV only when the required return is 20%, if the company has a required return of 25% the project will have negative NPV. From the above table we can see the NPV for the company is earned with production of Q-Power boats when its required rate of return is 20%. Therefore, company should opt for production of Q –power boats since it would get higher returns with same investment in this option.
Also if we calculate the internal rate of return for both the project we see that the IRR for production of Q power boats is 31% and that of S-Power boats is 22%. Therefore, since IRR is more for production of Q-Power boats, this project should be opted for.
The crossover rate is the rate for which the net present values for two similar projects are same. (Rivenbark, Vogt, & Marlowe, 2009) It is the point at which either of the projects can be opted for when we calculate the crossover rate for both of the projects we get the rate to be -19%. This means that when the required rate of return by the company will be -19% the net present value of both the projects will be same.
From the above analysis we are clear that the company should opt for production of Q power boats. The company should take into consideration all the qualitative and quantitative factors into consideration before executing the final plan. Since the capital budgeting decisions are based on a lot of assumptions the company should keep a margin for uncertainties also.
Adelaja, T. (2015). Capital Budgeting: Investment Appraisal Techniques Under Certainty. Chicago: CreateSpace Independent Publishing Platform .
Bierman, H., & Smidt, S. (2010). The Capital Budgeting Decision. Boston: Routledge.
Datar, S. (2016). Horngren’s Cost Accounting: A Managerial Emphasis. Hoboken: Wiley.
Dayananda, D., Irons, R., Harrison, S., Herbohn, J., & Rowland, P. (2008). Capital Budgeting: Financial Appraisal of Investment Projects. Cambridge: Cambridge University Press.
Holtzman, M. (2013). Managerial Accounting For Dummies. Hoboken, NJ: Wiley.
Menifield, C. E. (2014). The Basics of Public Budgeting and Financial Management: A Handbook for Academics and Practitioners. Lanham, Md.: University Press of America.
Noreen, E. (2015). The theory of constraints and its implications for management accounting. Great Barrington, MA: North River Press.
Peterson, P. P., & Fabozzi, F. J. (2012). Capital Budgeting. New York, NY: Wiley.
Rivenbark, W. C., Vogt, J., & Marlowe, J. (2009). Capital Budgeting and Finance: A Guide for Local Governments. Washington, D.C.: ICMA Press.
Seitz, N., & Ellison, M. (2009). Capital Budgeting and Long-Term Financing Decisions. New York: Thomson Learning.
Shapiro, A. C. (2007). Capital Budgeting and Investment Analysis. New Jersey: Wiley.
Siciliano, G. (2015). Finance for Nonfinancial Managers. New York: McGraw-Hill.
Calculation of Cost of Asset | |
Particulars | Amount |
Cost of asset | 200,00,000 |
Add: Installation Charges | 8,00,000 |
208,00,000 | |
SLM depreciation rate | 12% |
SLM depreciation charge per year | 24,96,000 |
WDV of asset at the end of year 6 | 58,24,000 |
Less: Salvage value | 30,00,000 |
Loss on sale of asset | 28,24,000 |
Tax savings on above loss | 8,47,200 |
Calculation of Net Working Capital | |
Particulars | Amount |
Investment in stock | 5,00,000 |
Investment in debtors | 3,80,000 |
Investment in creditors | -1,80,000 |
Net working capital employed | 7,00,000 |
Calculation of net increase in sale of powerboat parts due to new project | |
Particulars | Amount |
Sales | 5,00,000 |
Less: Variable Cost | 2,00,000 |
Incremental sales | 3,00,000 |
Calculation of Net Present Value and Internal Rate of return | |||||||
Particulars | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
Units for sale | 650 | 600 | 550 | 500 | 450 | 400 | |
– Initial Cash Flow | |||||||
Equipment Cost | -208,00,000 | ||||||
Working Capital | -7,00,000 | ||||||
Net Initial Cash flow- A | -215,00,000 | – | – | – | – | – | |
– Operating Cash Flows | |||||||
Sales Price per unit | – | 195,00,000 | 180,00,000 | 165,00,000 | 150,00,000 | 135,00,000 | 120,00,000 |
Add: Increase in revenue from power boat parts | – | 3,00,000 | 3,00,000 | 3,00,000 | 3,00,000 | 3,00,000 | 3,00,000 |
Less: Opportunity Cost | – | -10,000 | -10,000 | -10,000 | -10,000 | -10,000 | -10,000 |
Less: Variable cost per unit | – | -78,00,000 | -72,00,000 | -66,00,000 | -60,00,000 | -54,00,000 | -48,00,000 |
Less: Fixed Cost | – | -2,00,000 | -2,00,000 | -2,00,000 | -2,00,000 | -2,00,000 | -2,00,000 |
Less: Depreciation | – | -24,96,000 | -24,96,000 | -24,96,000 | -24,96,000 | -24,96,000 | -24,96,000 |
Profit Before Tax | – | 92,94,000 | 83,94,000 | 74,94,000 | 65,94,000 | 56,94,000 | 47,94,000 |
Less : Tax @ 30% | – | -27,88,200 | -25,18,200 | -22,48,200 | -19,78,200 | -17,08,200 | -14,38,200 |
Profit after tax | – | 65,05,800 | 58,75,800 | 52,45,800 | 46,15,800 | 39,85,800 | 33,55,800 |
Cash flow after tax (PAT+Depreciation) | – | 90,01,800 | 83,71,800 | 77,41,800 | 71,11,800 | 64,81,800 | 58,51,800 |
Net Operating Cash flow- B | – | 90,01,800 | 83,71,800 | 77,41,800 | 71,11,800 | 64,81,800 | 58,51,800 |
– Terminal Cash Flow | |||||||
Salvage Value | – | – | – | – | – | 30,00,000 | |
Tax Savings on Salvage | – | – | – | – | – | 8,47,200 | |
Working Capital | – | – | – | – | – | 7,00,000 | |
Net Terminal Cash flow- C | – | – | – | – | – | 45,47,200 | |
Cash Flow from Activities (A+B+C) | -215,00,000 | 90,01,800 | 83,71,800 | 77,41,800 | 71,11,800 | 64,81,800 | 103,99,000 |
PV Factor @ 20% | 1.000000 | 0.833333 | 0.694444 | 0.578704 | 0.482253 | 0.401878 | 0.334898 |
PV of Cash Flows | -215,00,000 | 75,01,500 | 58,13,750 | 44,80,208 | 34,29,688 | 26,04,890 | 34,82,604 |
Net Present Value | 58,12,640 | ||||||
Calculation of Pay back Period | |||||||
Year | Cash Flow | Cumulative cash flow | |||||
0 | -21500000 | -21500000 | |||||
1 | 9001800 | -12498200 | |||||
2 | 8371800 | -4126400 | |||||
3 | 7741800 | 3615400 | |||||
4 | 7111800 | 10727200 | |||||
5 | 6481800 | 17209000 | |||||
6 | 10399000 | 27608000 | |||||
Pay back period | = | 2.53 | |||||
Pay back period | = | a+(b/c) | |||||
Where, | |||||||
a | = | the period with last negative cumulative cash flow | |||||
b | = | the amount of last cumulative cash flow | |||||
c | = | the cash flow just after the period a | |||||
Calculation of Internal rate of return | |||||||
Year | Cash Flows | ||||||
0 | -215,00,000 | ||||||
1 | 90,01,800 | ||||||
2 | 83,71,800 | ||||||
3 | 77,41,800 | ||||||
4 | 71,11,800 | ||||||
5 | 64,81,800 | ||||||
6 | 103,99,000 | ||||||
IRR | 31% |
Calculation of Net Present Value and Internal Rate of return | |||||||
Particulars | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
Units for sale | 650 | 600 | 550 | 500 | 450 | 400 | |
– Initial Cash Flow | |||||||
Equipment Cost | -208,00,000 | ||||||
Working Capital | -7,00,000 | ||||||
Net Initial Cash flow- A | -215,00,000 | – | – | – | – | – | |
– Operating Cash Flows | |||||||
Sales Price per unit | – | 195,00,000 | 180,00,000 | 165,00,000 | 150,00,000 | 135,00,000 | 120,00,000 |
Add: Increase in revenue from power boat parts | – | 3,00,000 | 3,00,000 | 3,00,000 | 3,00,000 | 3,00,000 | 3,00,000 |
Less: Opportunity Cost | – | -10,000 | -10,000 | -10,000 | -10,000 | -10,000 | -10,000 |
Less: Variable cost per unit | – | -78,00,000 | -72,00,000 | -66,00,000 | -60,00,000 | -54,00,000 | -48,00,000 |
Less: Fixed Cost | – | -2,00,000 | -2,00,000 | -2,00,000 | -2,00,000 | -2,00,000 | -2,00,000 |
Less: Depreciation | – | -24,96,000 | -24,96,000 | -24,96,000 | -24,96,000 | -24,96,000 | -24,96,000 |
Profit Before Tax | – | 92,94,000 | 83,94,000 | 74,94,000 | 65,94,000 | 56,94,000 | 47,94,000 |
Less : Tax @ 30% | – | -27,88,200 | -25,18,200 | -22,48,200 | -19,78,200 | -17,08,200 | -14,38,200 |
Profit after tax | – | 65,05,800 | 58,75,800 | 52,45,800 | 46,15,800 | 39,85,800 | 33,55,800 |
Cash flow after tax (PAT+Depreciation) | – | 90,01,800 | 83,71,800 | 77,41,800 | 71,11,800 | 64,81,800 | 58,51,800 |
Net Operating Cash flow- B | – | 90,01,800 | 83,71,800 | 77,41,800 | 71,11,800 | 64,81,800 | 58,51,800 |
– Terminal Cash Flow | |||||||
Salvage Value | – | – | – | – | – | 30,00,000 | |
Tax Savings on Salvage | – | – | – | – | – | 8,47,200 | |
Working Capital | – | – | – | – | – | 7,00,000 | |
Net Terminal Cash flow- C | – | – | – | – | – | 45,47,200 | |
Cash Flow from Activities (A+B+C) | -215,00,000 | 90,01,800 | 83,71,800 | 77,41,800 | 71,11,800 | 64,81,800 | 103,99,000 |
PV Factor @ 25% | 1.000000 | 0.800000 | 0.640000 | 0.512000 | 0.409600 | 0.327680 | 0.262144 |
PV of Cash Flows | -215,00,000 | 72,01,440 | 53,57,952 | 39,63,802 | 29,12,993 | 21,23,956 | 27,26,035 |
Net Present Value | 27,86,179 |
Calculation of Net Present Value and Internal Rate of return | |||||||
Particulars | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
Units for sale | 650 | 600 | 550 | 500 | 450 | 400 | |
– Initial Cash Flow | |||||||
Equipment Cost | -208,00,000 | ||||||
Working Capital | -7,00,000 | ||||||
Net Initial Cash flow- A | -215,00,000 | – | – | – | – | – | |
– Operating Cash Flows | |||||||
Cash flow from operations | 64,00,000 | 74,00,000 | 79,00,000 | 86,00,000 | 93,00,000 | 111,00,000 | |
Less: Depreciation | – | -24,96,000 | -24,96,000 | -24,96,000 | -24,96,000 | -24,96,000 | -24,96,000 |
Profit Before Tax | – | 39,04,000 | 49,04,000 | 54,04,000 | 61,04,000 | 68,04,000 | 86,04,000 |
Less : Tax @ 30% | – | -11,71,200 | -14,71,200 | -16,21,200 | -18,31,200 | -20,41,200 | -25,81,200 |
Profit after tax | – | 27,32,800 | 34,32,800 | 37,82,800 | 42,72,800 | 47,62,800 | 60,22,800 |
Cash flow after tax (PAT+Depreciation) | – | 52,28,800 | 59,28,800 | 62,78,800 | 67,68,800 | 72,58,800 | 85,18,800 |
Net Operating Cash flow- B | – | 52,28,800 | 59,28,800 | 62,78,800 | 67,68,800 | 72,58,800 | 85,18,800 |
– Terminal Cash Flow | |||||||
Salvage Value | – | – | – | – | – | 30,00,000 | |
Tax Savings on Salvage | – | – | – | – | – | 8,47,200 | |
Working Capital | – | – | – | – | – | 7,00,000 | |
Net Terminal Cash flow- C | – | – | – | – | – | 45,47,200 | |
Cash Flow from Activities (A+B+C) | -215,00,000 | 52,28,800 | 59,28,800 | 62,78,800 | 67,68,800 | 72,58,800 | 130,66,000 |
PV Factor @ 20% | 1.000000 | 0.833333 | 0.694444 | 0.578704 | 0.482253 | 0.401878 | 0.334898 |
PV of Cash Flows | -215,00,000 | 43,57,333 | 41,17,222 | 36,33,565 | 32,64,275 | 29,17,149 | 43,75,777 |
Net Present Value | 11,65,321 | ||||||
Calculation of Net Present Value and Internal Rate of return | |||||||
Particulars | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
Units for sale | 650 | 600 | 550 | 500 | 450 | 400 | |
– Initial Cash Flow | |||||||
Equipment Cost | -208,00,000 | ||||||
Working Capital | -7,00,000 | ||||||
Net Initial Cash flow- A | -215,00,000 | – | – | – | – | – | |
– Operating Cash Flows | |||||||
Cash flow from operations | 64,00,000 | 74,00,000 | 79,00,000 | 86,00,000 | 93,00,000 | 111,00,000 | |
Less: Depreciation | – | -24,96,000 | -24,96,000 | -24,96,000 | -24,96,000 | -24,96,000 | -24,96,000 |
Profit Before Tax | – | 39,04,000 | 49,04,000 | 54,04,000 | 61,04,000 | 68,04,000 | 86,04,000 |
Less : Tax @ 30% | – | -11,71,200 | -14,71,200 | -16,21,200 | -18,31,200 | -20,41,200 | -25,81,200 |
Profit after tax | – | 27,32,800 | 34,32,800 | 37,82,800 | 42,72,800 | 47,62,800 | 60,22,800 |
Cash flow after tax (PAT+Depreciation) | – | 52,28,800 | 59,28,800 | 62,78,800 | 67,68,800 | 72,58,800 | 85,18,800 |
Net Operating Cash flow- B | – | 52,28,800 | 59,28,800 | 62,78,800 | 67,68,800 | 72,58,800 | 85,18,800 |
– Terminal Cash Flow | |||||||
Salvage Value | – | – | – | – | – | 30,00,000 | |
Tax Savings on Salvage | – | – | – | – | – | 8,47,200 | |
Working Capital | – | – | – | – | – | 7,00,000 | |
Net Terminal Cash flow- C | – | – | – | – | – | 45,47,200 | |
Cash Flow from Activities (A+B+C) | -215,00,000 | 52,28,800 | 59,28,800 | 62,78,800 | 67,68,800 | 72,58,800 | 130,66,000 |
PV Factor @ 20% | 1.000000 | 0.800000 | 0.640000 | 0.512000 | 0.409600 | 0.327680 | 0.262144 |
PV of Cash Flows | -215,00,000 | 41,83,040 | 37,94,432 | 32,14,746 | 27,72,500 | 23,78,564 | 34,25,174 |
Net Present Value | -17,31,545 | ||||||
Calculation of Internal rate of return | |||||||
Year | Cash Flows | ||||||
0 | -21500000 | ||||||
1 | 5228800 | ||||||
2 | 5928800 | ||||||
3 | 6278800 | ||||||
4 | 6768800 | ||||||
5 | 7258800 | ||||||
6 | 13066000 | ||||||
IRR | 22% |
Cross over rate
Year | Cash flow from Q-Power Boats | Cash flow from S-Power Boats | Difference in cash flows |
0 | -21500000 | -21500000 | 0 |
1 | 9001800 | 5228800 | -3773000 |
2 | 8371800 | 5928800 | -2443000 |
3 | 7741800 | 6278800 | -1463000 |
4 | 7111800 | 6768800 | -343000 |
5 | 6481800 | 7258800 | 777000 |
6 | 10399000 | 13066000 | 2667000 |
Cross over rate | -19% | ||
NPV at crossover rate | |||
-19% | 91551967.79 | 91551950.16 |
Excel working- formula sheet:
Calculation of Cost of Asset | |
Particulars | Amount |
Cost of asset | 20000000 |
Add: Intallation Charges | 800000 |
=SUM(B4:B5) | |
SLM depreciation rate | 0.12 |
SLM depreciation charge per year | =+B6*0.12 |
WDV of asset at the end of year 6 | =+B6-(B8*6) |
Less: Salvage value | 3000000 |
Loss on sale of asset | =+B10-B11 |
Tax savings on above loss | =+B12*0.3 |
Calculation of Net Working Capital | |
Particulars | Amount |
Investment in stock | 500000 |
Investment in debtors | 380000 |
Investment in creditors | -180000 |
Net working capital employed | =SUM(B17:B19) |
Calculation of net increase in sale of powerboat parts due to new project | |
Particulars | Amount |
Sales | 500000 |
Less: Variable Cost | =+B24*0.4 |
Incremental sales | =+B24-B25 |
Calculation of Net Present Value and Internal Rate of return | |||||||
Particulars | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
Units for sale | 650 | =+C4-50 | =+D4-50 | =+E4-50 | =+F4-50 | =+G4-50 | |
– Initial Cash Flow | |||||||
Equipment Cost | =-Workings!B6 | ||||||
Working Capital | =-Workings!B20 | ||||||
Net Initial Cash flow- A | =SUM(B7:B8) | =SUM(C7:C8) | =SUM(D7:D8) | =SUM(E7:E8) | =SUM(G7:G8) | =SUM(H7:H8) | |
– Operating Cash Flows | |||||||
Sales Price per unit | =687*B4 | =+C4*30000 | =+D4*30000 | =+E4*30000 | =+F4*30000 | =+G4*30000 | =+H4*30000 |
Add: Increase in revenue from power boat parts | 0 | =+Workings!$B$26 | =+Workings!$B$26 | =+Workings!$B$26 | =+Workings!$B$26 | =+Workings!$B$26 | =+Workings!$B$26 |
Less: Opportunity Cost | 0 | -10000 | -10000 | -10000 | -10000 | -10000 | -10000 |
Less: Variable cost per unit | =-325*B4 | =-C12*0.4 | =-D12*0.4 | =-E12*0.4 | =-F12*0.4 | =-G12*0.4 | =-H12*0.4 |
Less: Fixed Cost | 0 | -200000 | -200000 | -200000 | -200000 | -200000 | -200000 |
Less: Depreciation | 0 | =-Workings!$B$8 | =-Workings!$B$8 | =-Workings!$B$8 | =-Workings!$B$8 | =-Workings!$B$8 | =-Workings!$B$8 |
Profit Before Tax | =+SUM(B12:B17) | =+SUM(C12:C17) | =+SUM(D12:D17) | =+SUM(E12:E17) | =+SUM(F12:F17) | =+SUM(G12:G17) | =+SUM(H12:H17) |
Less : Tax @ 30% | =-B18*0.3 | =-C18*0.3 | =-D18*0.3 | =-E18*0.3 | =-F18*0.3 | =-G18*0.3 | =-H18*0.3 |
Profit after tax | =+SUM(B18:B19) | =+SUM(C18:C19) | =+SUM(D18:D19) | =+SUM(E18:E19) | =+SUM(F18:F19) | =+SUM(G18:G19) | =+SUM(H18:H19) |
Cash flow after tax (PAT+Depreciation) | =-B17+B20 | =-C17+C20 | =-D17+D20 | =-E17+E20 | =-F17+F20 | =-G17+G20 | =-H17+H20 |
Net Operating Cash flow- B | =+B21 | =+C21 | =+D21 | =+E21 | =+F21 | =+G21 | =+H21 |
– Terminal Cash Flow | |||||||
Salvage Value | 0 | 0 | 0 | 0 | 0 | =+Workings!B11 | |
Tax Savings on Salvage | 0 | 0 | 0 | 0 | 0 | =+Workings!B13 | |
Working Capital | 0 | 0 | 0 | 0 | 0 | =-B8 | |
Net Terminal Cash flow- C | =SUM(B25:B27) | =SUM(C25:C27) | =SUM(D25:D27) | =SUM(E25:E27) | =SUM(G25:G27) | =SUM(H25:H27) | |
Cash Flow from Activities (A+B+C) | =+B9+B22+B28 | =+C9+C22+C28 | =+D9+D22+D28 | =+E9+E22+E28 | =+F9+F22+F28 | =+G9+G22+G28 | =+H9+H22+H28 |
PV Factor @ 25% | =1/(1.25^B3) | =1/(1.25^C3) | =1/(1.25^D3) | =1/(1.25^E3) | =1/(1.25^F3) | =1/(1.25^G3) | =1/(1.25^H3) |
PV of Cash Flows | =+B30*B32 | =+C30*C32 | =+D30*D32 | =+E30*E32 | =+F30*F32 | =+G30*G32 | =+H30*H32 |
Net Present Value | =+SUM(B34:H34) |
Calculation of Net Present Value and Internal Rate of return | |||||||
Particulars | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
Units for sale | 650 | =+C4-50 | =+D4-50 | =+E4-50 | =+F4-50 | =+G4-50 | |
– Initial Cash Flow | |||||||
Equipment Cost | =-Workings!$B$6 | ||||||
Working Capital | =-Workings!$B$20 | ||||||
Net Initial Cash flow- A | =SUM(B7:B8) | =SUM(C7:C8) | =SUM(D7:D8) | =SUM(E7:E8) | =SUM(G7:G8) | =SUM(H7:H8) | |
– Operating Cash Flows | |||||||
Cash flow from operations | 6400000 | 7400000 | 7900000 | 8600000 | 9300000 | 11100000 | |
Less: Depreciation | 0 | =-Workings!$B$8 | =-Workings!$B$8 | =-Workings!$B$8 | =-Workings!$B$8 | =-Workings!$B$8 | =-Workings!$B$8 |
Profit Before Tax | =+SUM(B12:B13) | =+SUM(C12:C13) | =+SUM(D12:D13) | =+SUM(E12:E13) | =+SUM(F12:F13) | =+SUM(G12:G13) | =+SUM(H12:H13) |
Less : Tax @ 30% | =-B14*0.3 | =-C14*0.3 | =-D14*0.3 | =-E14*0.3 | =-F14*0.3 | =-G14*0.3 | =-H14*0.3 |
Profit after tax | =+SUM(B14:B15) | =+SUM(C14:C15) | =+SUM(D14:D15) | =+SUM(E14:E15) | =+SUM(F14:F15) | =+SUM(G14:G15) | =+SUM(H14:H15) |
Cash flow after tax (PAT+Depreciation) | =-B13+B16 | =-C13+C16 | =-D13+D16 | =-E13+E16 | =-F13+F16 | =-G13+G16 | =-H13+H16 |
Net Operating Cash flow- B | =+B17 | =+C17 | =+D17 | =+E17 | =+F17 | =+G17 | =+H17 |
– Terminal Cash Flow | |||||||
Salvage Value | 0 | 0 | 0 | 0 | 0 | =+Workings!$B$11 | |
Tax Savings on Salvage | 0 | 0 | 0 | 0 | 0 | =+Workings!$B$13 | |
Working Capital | 0 | 0 | 0 | 0 | 0 | =-B8 | |
Net Terminal Cash flow- C | =SUM(B21:B23) | =SUM(C21:C23) | =SUM(D21:D23) | =SUM(E21:E23) | =SUM(G21:G23) | =SUM(H21:H23) | |
Cash Flow from Activities (A+B+C) | =+B9+B18+B24 | =+C9+C18+C24 | =+D9+D18+D24 | =+E9+E18+E24 | =+F9+F18+F24 | =+G9+G18+G24 | =+H9+H18+H24 |
PV Factor @ 20% | =1/(1.2^B3) | =1/(1.2^C3) | =1/(1.2^D3) | =1/(1.2^E3) | =1/(1.2^F3) | =1/(1.2^G3) | =1/(1.2^H3) |
PV of Cash Flows | =+B26*B28 | =+C26*C28 | =+D26*D28 | =+E26*E28 | =+F26*F28 | =+G26*G28 | =+H26*H28 |
Net Present Value | =+SUM(B30:H30) | ||||||
Calculation of Net Present Value and Internal Rate of return | |||||||
Particulars | 0 | 1 | 2 | 3 | 4 | 5 | 6 |
Units for sale | 650 | =+C37-50 | =+D37-50 | =+E37-50 | =+F37-50 | =+G37-50 | |
– Initial Cash Flow | |||||||
Equipment Cost | =-Workings!$B$6 | ||||||
Working Capital | =-Workings!$B$20 | ||||||
Net Initial Cash flow- A | =SUM(B40:B41) | =SUM(C40:C41) | =SUM(D40:D41) | =SUM(E40:E41) | =SUM(G40:G41) | =SUM(H40:H41) | |
– Operating Cash Flows | |||||||
Cash flow from operations | 6400000 | 7400000 | 7900000 | 8600000 | 9300000 | 11100000 | |
Less: Depreciation | 0 | =-Workings!$B$8 | =-Workings!$B$8 | =-Workings!$B$8 | =-Workings!$B$8 | =-Workings!$B$8 | =-Workings!$B$8 |
Profit Before Tax | =+SUM(B45:B46) | =+SUM(C45:C46) | =+SUM(D45:D46) | =+SUM(E45:E46) | =+SUM(F45:F46) | =+SUM(G45:G46) | =+SUM(H45:H46) |
Less : Tax @ 30% | =-B47*0.3 | =-C47*0.3 | =-D47*0.3 | =-E47*0.3 | =-F47*0.3 | =-G47*0.3 | =-H47*0.3 |
Profit after tax | =+SUM(B47:B48) | =+SUM(C47:C48) | =+SUM(D47:D48) | =+SUM(E47:E48) | =+SUM(F47:F48) | =+SUM(G47:G48) | =+SUM(H47:H48) |
Cash flow after tax (PAT+Depreciation) | =-B46+B49 | =-C46+C49 | =-D46+D49 | =-E46+E49 | =-F46+F49 | =-G46+G49 | =-H46+H49 |
Net Operating Cash flow- B | =+B50 | =+C50 | =+D50 | =+E50 | =+F50 | =+G50 | =+H50 |
– Terminal Cash Flow | |||||||
Salvage Value | 0 | 0 | 0 | 0 | 0 | =+Workings!$B$11 | |
Tax Savings on Salvage | 0 | 0 | 0 | 0 | 0 | =+Workings!$B$13 | |
Working Capital | 0 | 0 | 0 | 0 | 0 | =-B41 | |
Net Terminal Cash flow- C | =SUM(B54:B56) | =SUM(C54:C56) | =SUM(D54:D56) | =SUM(E54:E56) | =SUM(G54:G56) | =SUM(H54:H56) | |
Cash Flow from Activities (A+B+C) | =+B42+B51+B57 | =+C42+C51+C57 | =+D42+D51+D57 | =+E42+E51+E57 | =+F42+F51+F57 | =+G42+G51+G57 | =+H42+H51+H57 |
PV Factor @ 20% | =1/(1.25^B36) | =1/(1.25^C36) | =1/(1.25^D36) | =1/(1.25^E36) | =1/(1.25^F36) | =1/(1.25^G36) | =1/(1.25^H36) |
PV of Cash Flows | =+B59*B61 | =+C59*C61 | =+D59*D61 | =+E59*E61 | =+F59*F61 | =+G59*G61 | =+H59*H61 |
Net Present Value | =+SUM(B63:H63) | ||||||
Calculation of Internal rate of return | |||||||
Year | Cash Flows | ||||||
0 | -21500000 | ||||||
1 | 5228800 | ||||||
2 | 5928800 | ||||||
3 | 6278800 | ||||||
4 | 6768800 | ||||||
5 | 7258800 | ||||||
6 | 13066000 | ||||||
IRR | =+IRR(B69:B75) |
Cross over rate
Year | Cash flow from Q-Power Boats | Cash flow from S-Power Boats | Difference in cash flows |
0 | -21500000 | -21500000 | =+C3-B3 |
1 | 9001800 | 5228800 | =+C4-B4 |
2 | 8371800 | 5928800 | =+C5-B5 |
3 | 7741800 | 6278800 | =+C6-B6 |
4 | 7111800 | 6768800 | =+C7-B7 |
5 | 6481800 | 7258800 | =+C8-B8 |
6 | 10399000 | 13066000 | =+C9-B9 |
Cross over rate | =+IRR(D3:D9,0) | ||
NPV at crossover rate | |||
-0.194638 | =+NPV(A14,B4:B9)+B3 | =+NPV(A14,C4:C9)+C3 |
Related Assignment Samples
Previous answers to this question
This is a preview of an assignment submitted on our website by a student. If you need help with this question or any assignment help, click on the order button below and get started. We guarantee authentic, quality, 100% plagiarism free work or your money back.