Question

In: Finance

Calculations must be done in Excel – You must create your own spreadsheet do not copy...

Calculations must be done in Excel – You must create your own spreadsheet do not copy and paste someone else’s.

Polycorp Limited Steel Division is considering a proposal to purchase a new machine to manufacture a new product for a potential three year contract. The new machine will cost $1.9 million. The machine has an estimated life of three years for accounting and taxation purposes. Installation will cost a further $120,000. The contract will not continue beyond three years and the equipment has an estimated salvage value at the end of three years of $300,000. The tax rate is 30 percent and is payable in the year in which profit is earned. An investment allowance of twenty percent on the outlay plus installation costs is available. The after tax cost of capital is 14%pa. Addition current assets of $90,000 are required immediately for working capital to support the project.

Assume that this amount less $10,000 is recovered in full at the end of the three-year life of the project. The new product will be charged $170,500 of allocated head office administration costs each year even though head office will not actually incur any extra costs to manage the project. This is in accordance with the firm’s policy of allocating all corporate overhead costs to divisions. The Steel Division will incur extra marketing and administration cash outflows of $158,000 per year for the project. An amount of $200,000 has been spent on a pilot study and market research for the new product. The projections provided here are based on this work. Projected sales in the first year for the new product are 40,000 units at $150 per unit per year. Unit sales are expected to increase by 5%pa for years two and three. Cash operating expenses are estimated to be 75 percent of sales (excludes marketing and administration, and head office items). Except for initial outlays, assume cash flows occur at the end of each year (unless otherwise stated).

Assume diminishing value depreciation for tax purposes.

Required:

Construct a table showing net cash flow after tax (NCFAT). Do not forget to Calculate the NPV. Is the project acceptable? Why or why not? Conduct a sensitivity analysis showing how sensitive the project is to operating expenses and to the cost of capital. Explain your results. Write a short report explaining your calculation of relevant net cash flows after tax, justifying your selection of cash flows. Be sure to state clearly any assumptions made (implicit and explicit).

NOTE:

Method required is as follows this is just an example.

Method example

Beginners Method (two tables one for tax and one for cash flows)

TAX

0

1

2

3

Cash Benefits

16,000

20,000

12,000

Inv Allow 20%

-5,600

Deprec

-11,200

-6,720

-4,032

Loss on Sale

-5,548

Taxable Income

-800

13,280

2,420

Tax @ 30%

240

-3,984

-726

Cash Flows

0

1

2

3

Tax

240

-3,984

-726

Cash Benefits

16,000

20,000

12,000

Salvage Value

500

Outlay

-28,000

NCFAT

-28,000

16,240

16,016

11,774

DCF @ 15%

-28,000

14,122

12,110

7,742

NPV

5,974

Solutions

Expert Solution

Following points are worth noting
1) An investment allowance of twenty percent on the outlay plus installation costs is available
2) The contract will not continue beyond three years and the equipment has an estimated salvage value at the end of three years of $300,000
          So the Depreciation will be provided for 3 years
3) An investment allowance of twenty percent on the outlay plus installation costs is available.
4) The after tax cost of capital is 14%pa.  
5) Addition to current assets of $90,000 & Recovery is $80,000
6) $170,500 of allocated head office administration costs each year even though head office will not actually incur any extra costs
            So it is Irrelevant for the computation
7) An amount of $200,000 has been spent on a pilot study and market research - So this also a Sunk Cost
Table to find out Cash benefits
Year> 0 1 2 3
Sales $           6,000,000.00 $        6,300,000.00 $        6,615,000.00
Less:   Cash operating Expenses @ 75% of sales $           4,500,000.00 $        4,725,000.00 $        4,961,250.00
Less:    Marketing and administration cash outflows $              158,000.00 $            158,000.00 $            158,000.00
Cash Benefits per year $           1,342,000.00 $        1,417,000.00 $        1,495,750.00
Table to find Tax Amount per year
Year> 0 1 2 3
Particulars-  
Cash benefit $           1,342,000.00 $        1,417,000.00 $        1,495,750.00
Investment Allowance @20% $            (500,000.00) $                             -   $                             -  
Depreciation @ 33.333% $            (673,333.33) $         (673,333.33) $         (673,333.33)
Profit on sale of Asset $                                -   $                             -   $            300,000.00
Taxable Income $              168,666.67 $            743,666.67 $        1,122,416.67
Tax @30% $              (50,600.00) $         (223,100.00) $         (336,725.00)
Table to find out Net Cahs flow After tax & NPV
Year> 0 1 2 3
Particulars-  
Tax $              (50,600.00) $         (223,100.00) $         (336,725.00)
Cash Benefits $           1,342,000.00 $        1,417,000.00 $        1,495,750.00
Salvage Value $            380,000.00
Outlay $        (2,110,000.00)
Net Cash Flow After Tax $        (2,110,000.00) $           1,291,400.00 $        1,193,900.00 $        1,539,025.00
Discounted Cash flow @14% $        (2,110,000.00) $           1,132,807.02 $            918,667.28 $        1,038,798.04
NPV ( Net Present Value) $980,272.34
Sensitivity to operating Expense = 981272.34/ PVAnnuity Factor
PVAnnuity Factor = (((1+r)^n)-1)/ ((1+r)^n) (r) 2.3216320271
Annual Change (Sensitivity) to Operating Expense $422,234.16

Which means, Annual increase in Operating cost till $422,234.16 will not adversely affect the organization. Increase in anual operating cost beyond this level will adversely affect the organization.

Sensitivity to Cost of Capital New rate = 39.363%, that is 39.363%-14% = 25.363% .

that is Cost of capital can go upto 39.363% that is may go upto 25.363% over the expected rate of capital.

To find this We have to worked out the IRR. For IRR, we have to worked out Negative NPV by increasing the discount rate., then we will get NPV of -$17,569.6064139939.

Then we can intrapolate the Rate lying between 14% and 40%.

For this We can use the formulae = LR+((NPV@LR)/(NPV@LR - NPV@HR))* (HR-LR).

LR = 14%, HR = 40%, NPV@LR= $981,272.34, NPV@HR = -$17,569.6064139939.

So 14%+($981,272.34/($981,272.34 - -$17,569.6064139939.))*(40-14)

=39.363%


Related Solutions

Project 2 Calculations must be done in Excel – You must create your own spreadsheet (do...
Project 2 Calculations must be done in Excel – You must create your own spreadsheet (do not copy and paste someone else’s). This question should be done using Method 1 as outlined in lecture 6 (i.e. Tax Effects, then Cash Flows then NPV) As the financial advisor to All Star Manufacturing you are evaluating the following new investment in a manufacturing project: - The project has a useful life of 12 years. Land costs $6m and is estimated to have...
Create an Excel spreadsheet that can do the calculations for the credit card below. Using formulas...
Create an Excel spreadsheet that can do the calculations for the credit card below. Using formulas and the drag function of Excel (see the notes), find the values for 1 year of charges. You can start this by taking the finance charge in month 1. With a beginning balance of $2500 and no additional charges, there is no grace-period, you will just pay the minimum payment each month for the next year. The APR is 24.99% and the minimum payment...
Barry and Samantha Harris – Retirement Savings You must work on your own spreadsheet with Excel...
Barry and Samantha Harris – Retirement Savings You must work on your own spreadsheet with Excel skills or concepts. Barry and Samantha are starting to take their retirement planning seriously. They are both 45 and plan to retire in 20 years at the age of 65. They expect to live 15 years in retirement (a life expectancy of 80). Between their 401k accounts they currently have $66,000 in retirement savings. They currently have a combined income of $80,000 per year...
Calculations must be done in Excel As the financial advisor to Upmarket Car Rentals you are...
Calculations must be done in Excel As the financial advisor to Upmarket Car Rentals you are evaluating the following types of cars to add to the fleet: - Speedster: - A sporty convertible with a cost of $120,000 and a useful life of 4 years. It will produce rental income of $80,000 per year and operating costs of $15,000 per year. A major service is required after 2 years costing $20,000. A salvage value of $30,000 is expected after 4...
a. Use the multiple regression spreadsheet provided in the Excel spreadsheet to do the following: create...
a. Use the multiple regression spreadsheet provided in the Excel spreadsheet to do the following: create a new data set that is a subset of this larger provided data set. The new data set should include the dependent variable (VALUE) and the following independent variables (AGE, LOTSIZE, RMS, MOD KITCH, MOD BATH, AIRCON and FIREPL). b. For this new data set, use the Correlation option of Data Analysis in Excel and produce the correlation matrix associated with the dependent variable...
Calculations must be done in Excel - Using the capital budgeting method of; Tax Effects, then...
Calculations must be done in Excel - Using the capital budgeting method of; Tax Effects, then Cash flows, then NPV. As the financial advisor to All Star Manufacturing you are evaluating the following new investment in a manufacturing project: - The project has a useful life of 12 years. Land costs $6m and is estimated to have a resale value of $10m at the completion of the project. Buildings cost $5m, with allowable depreciation of 10% pa reducing balance and...
Calculations must be done in Excel Polycorp is considering an investment in new plant of $3.2...
Calculations must be done in Excel Polycorp is considering an investment in new plant of $3.2 million. The project will be partially financed with a loan of $2,000,000 which will be repaid over the next five years in equal annual end of year instalments at a rate of 6.20 percent pa. The rest of the project will be financed by equity. Assume straight-line depreciation over a five-year life, and no taxes. The project’s cash flows before loan repayments and interest...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A firm that is in the 35% tax bracket forecasts that it can retain $4 million of new earnings plans to raise new capital in the following proportions: 60% from 30-year bonds with a flotation cost of 4% of face value. Their current bonds are selling at a price of 91...
create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A company is evaluating the purchase of a machine to improve product quality and output levels. The new machine would cost $1.6 million and would be depreciated for tax purposes using the straight-line method over an estimated six-year life to its expected salvage value of $100,000. The new machine would require...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel...
Create an Excel spreadsheet to organize your answers to the following problem, and submit your Excel file as an attachment by clicking on the appropriate button on this page. A company with EBIT of $6,000,000 is considering two financing alternatives. The first alternative would have interest expense of $2,000,000 and 1,000,000 common shares outstanding, whereas the second would have interest expense of $3,800,000 but only 750,000 shares outstanding. The company is in the 35% tax bracket. Part 1: Construct the...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT