Financial Management

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

  • November 3, 2017 Financial Statements and Reports(Securities Investment & Commission) : 632344
  • November 22, 2017 Management Of Financial Resources And Performance : 630913
  • August 17, 2017 Accounting for Managers to Make Decisions:590074
  • June 2, 2018 Property Planning and Spatial Analysis: 709452
  • November 20, 2017 Modelling of Motors : 649479
  • July 25, 2017 Workplace Policy and Procedure-588540
  • June 12, 2017 RFID Technologies:580304
  • June 12, 2017 Legal Aspects of International Trade:562001
  • June 12, 2017 Advanced Management Accounting:567983
  • August 21, 2017 New Product Management:599646
  • November 3, 2017 Global Marketing ( Faux Facy Cosmetics) : 594680
  • June 2, 2018 ABC Analysis : 731822
  • June 12, 2017 Business Plan:554962
  • June 12, 2017 Corporate Governance Oversight and Regulation in Australia:561849
  • June 4, 2018 Property Valuation Foundation Concepts: 711810
  • January 31, 2018 Effect of Creative Accounting on shareholders wealth : 668195
  • November 2, 2017 Project Charter Program : 636855
  • February 13, 2018 Business management (Project Report: Financial Accounting ) : 157734
  • April 5, 2018 Evaluation of the Milwaukee Brewers Baseball Team : 684838
  • December 26, 2017 The Construction Work And Plan Building : 659900