Homework Parker Products manufactures a variety of household products. The compa
ID: 2752556 • Letter: H
Question
Homework
Parker Products manufactures a variety of household products. The company is considering introducing a new detergent. The company’s CFO has collected the following information about the proposed product.
The project has an anticipated economic life of 4 years.
The company will have to purchase a new machine to produce the detergent. The machine has an up-front cost (t = 0) of $2 million. The machine will be depreciated on a straight-line basis to a value of $600,000 over 4 years. The company anticipates that after four years, its salvage value will equal $250,000.
If the company goes ahead with the proposed product, it will have an effect on the company’s net working capital. At the outset, t = 0, inventory will increase by $140,000 and accounts payable will increase by $40,000. At t = 4, the net working capital will be recovered after the project is completed.
The detergent is expected to generate sales revenue of $1 million the first year (t = 1), $2 million the second year (t = 2), $2 million the third year (t = 3), and $1 million the final year (t = 4). Each year the variable operating costs (not including depreciation) are expected to equal 40 percent of sales revenue. The project would increase the company’s fixed costs by $120,000 per year.
The new detergent is expected to reduce the after-tax cash flows of the company’s existing products *+by $100,000 a year (t = 1, 2, 3, and 4).
The company’s overall WACC is 10 percent. However, the proposed project is riskier than the average project for Parker; the project’s WACC is estimated to be 12 percent.
The company’s tax rate is 40 percent.
Develop a spreadsheet model and use it to find the project’s NPV and IRR.
GOAL SEEK
Find the amount of annual fixed costs that will result in zero NPV for the project. Print the parameter area.
c. ONE-WAY DATA TABLE
Now conduct a sensitivity analysis to determine the sensitivity of NPV and IRR to changes in the variable operating costs. What will be the NPV and IRR, if variable operating costs varies from 25% of sales revenue to 45% of sales revenue in increments of 5%. Format the IRR cells to 2 decimal points (e.g. 6.35%). Print the data table with the formula row/column hidden.
d. TWO-WAY DATA TABLE
What will be the NPV, if salvage value varies from $200,000 to $280,000 in increments of $10,000 and fixed costs varies from $100,000 to $160,000 in increments of $10,000. Print the data table with the formula cell hidden
e. SCENARIO SUMMARY
Now conduct a scenario analysis. Use the following scenarios and prepare a scenario report. Print the scenario summary after hiding the current values column.
Worst
Base
Best
Machine Cost
2,000,000
2,000,000
2,000,000
Life
4
4
4
BV at the end of project
600,000
600,000
600,000
Salvage value
200,000
250,000
300,000
Sales Year 1
800,000
1,000,000
1,200,000
Sales Year 2
1,600,000
2,000,000
2,400,000
Sales Year 3
1,600,000
2,000,000
2,400,000
Sales Year 4
800,000
1,000,000
1,200,000
Variable Opr. Costs/Sales
40%
35%
30%
Fixed Costs
140,000
120,000
100,000
Reduction of ATCF
100,000
100,000
100,000
Change in Inventory
140,000
140,000
140,000
Change in A/P
40,000
40,000
40,000
Tax Rate
30%
30%
30%
Discount Rate
12%
12%
12%
Homework
Parker Products manufactures a variety of household products. The company is considering introducing a new detergent. The company’s CFO has collected the following information about the proposed product.
The project has an anticipated economic life of 4 years.
The company will have to purchase a new machine to produce the detergent. The machine has an up-front cost (t = 0) of $2 million. The machine will be depreciated on a straight-line basis to a value of $600,000 over 4 years. The company anticipates that after four years, its salvage value will equal $250,000.
If the company goes ahead with the proposed product, it will have an effect on the company’s net working capital. At the outset, t = 0, inventory will increase by $140,000 and accounts payable will increase by $40,000. At t = 4, the net working capital will be recovered after the project is completed.
The detergent is expected to generate sales revenue of $1 million the first year (t = 1), $2 million the second year (t = 2), $2 million the third year (t = 3), and $1 million the final year (t = 4). Each year the variable operating costs (not including depreciation) are expected to equal 40 percent of sales revenue. The project would increase the company’s fixed costs by $120,000 per year.
The new detergent is expected to reduce the after-tax cash flows of the company’s existing products *+by $100,000 a year (t = 1, 2, 3, and 4).
The company’s overall WACC is 10 percent. However, the proposed project is riskier than the average project for Parker; the project’s WACC is estimated to be 12 percent.
The company’s tax rate is 40 percent.
Develop a spreadsheet model and use it to find the project’s NPV and IRR.
GOAL SEEK
Find the amount of annual fixed costs that will result in zero NPV for the project. Print the parameter area.
c. ONE-WAY DATA TABLE
Now conduct a sensitivity analysis to determine the sensitivity of NPV and IRR to changes in the variable operating costs. What will be the NPV and IRR, if variable operating costs varies from 25% of sales revenue to 45% of sales revenue in increments of 5%. Format the IRR cells to 2 decimal points (e.g. 6.35%). Print the data table with the formula row/column hidden.
d. TWO-WAY DATA TABLE
What will be the NPV, if salvage value varies from $200,000 to $280,000 in increments of $10,000 and fixed costs varies from $100,000 to $160,000 in increments of $10,000. Print the data table with the formula cell hidden
e. SCENARIO SUMMARY
Now conduct a scenario analysis. Use the following scenarios and prepare a scenario report. Print the scenario summary after hiding the current values column.
Worst
Base
Best
Machine Cost
2,000,000
2,000,000
2,000,000
Life
4
4
4
BV at the end of project
600,000
600,000
600,000
Salvage value
200,000
250,000
300,000
Sales Year 1
800,000
1,000,000
1,200,000
Sales Year 2
1,600,000
2,000,000
2,400,000
Sales Year 3
1,600,000
2,000,000
2,400,000
Sales Year 4
800,000
1,000,000
1,200,000
Variable Opr. Costs/Sales
40%
35%
30%
Fixed Costs
140,000
120,000
100,000
Reduction of ATCF
100,000
100,000
100,000
Change in Inventory
140,000
140,000
140,000
Change in A/P
40,000
40,000
40,000
Tax Rate
30%
30%
30%
Discount Rate
12%
12%
12%
Worst
Base
Best
Machine Cost
2,000,000
2,000,000
2,000,000
Life
4
4
4
BV at the end of project
600,000
600,000
600,000
Salvage value
200,000
250,000
300,000
Sales Year 1
800,000
1,000,000
1,200,000
Sales Year 2
1,600,000
2,000,000
2,400,000
Sales Year 3
1,600,000
2,000,000
2,400,000
Sales Year 4
800,000
1,000,000
1,200,000
Variable Opr. Costs/Sales
40%
35%
30%
Fixed Costs
140,000
120,000
100,000
Reduction of ATCF
100,000
100,000
100,000
Change in Inventory
140,000
140,000
140,000
Change in A/P
40,000
40,000
40,000
Tax Rate
30%
30%
30%
Discount Rate
12%
12%
12%
Explanation / Answer
Answer:
Computation of projects NPV and IRR Year Machine Cost Increase in WC Sales Variable Oprating Cost Depreciation Fixed Cost Reduction of ATCF of Net Cash Flow after Tax WACC 12% PV of Cash Flow (140000-40000) (40% of Sales) SLM (20-6)/4 Existing project (Net Revenue in 4 years * (1-t)) (i.e. DF) 0 -$2,000,000 -$100,000 -$2,100,000 1.000 -$2,100,000 1 $1,000,000 -$400,000 $350,000 -$120,000 -$100,000 $438,000 0.893 $391,071 2 $2,000,000 -$800,000 $350,000 -$120,000 -$100,000 $798,000 0.797 $636,161 3 $2,000,000 -$800,000 $350,000 -$120,000 -$100,000 $798,000 0.712 $568,001 4 $1,000,000 -$400,000 $350,000 -$120,000 -$100,000 $438,000 0.636 $278,357 4 Salvage Value of Machinery $250,000 0.636 $158,880 4 Reduction in Working Capital $100,000 0.636 $63,552 NPV of the project -$3,979 Since NPV of project is negative $ 3979 hence the project is not viable. Computation of IRR Since we have negative NPV it means that IRR is less than WACC. To Find if we use low discount rate say 10% Year Machine Cost Increase in WC Sales Variable Oprating Cost Depreciation Fixed Cost Reduction of ATCF of Net Cash Flow after Tax WACC 10% PV of Cash Flow (140000-40000) (40% of Sales) SLM (20-6)/4 Existing project (Net Revenue in 4 years * (1-t)) (i.e. DF) 0 -$2,000,000 -$100,000 -$2,100,000 1.000 -$2,100,000 1 $1,000,000 -$400,000 $350,000 -$120,000 -$100,000 $438,000 0.909 $398,182 2 $2,000,000 -$800,000 $350,000 -$120,000 -$100,000 $798,000 0.826 $659,504 3 $2,000,000 -$800,000 $350,000 -$120,000 -$100,000 $798,000 0.751 $599,549 4 $1,000,000 -$400,000 $350,000 -$120,000 -$100,000 $438,000 0.683 $299,160 4 Salvage Value of Machinery $250,000 0.683 $170,753 4 Reduction in Working Capital $100,000 0.683 $68,301 NPV of the project $95,450 Computation of IRR = Low rate+ NPV at low rate/(NPV at low rate - NPV at higher Rate) x (Diff. in Rate) Computation of IRR = 10 + $95450/($95450 - (-$3979)) x(12-10) = 10 + 95450/ 99429 0.959981494 2 11.919963 Computation of NPV at IRR Year Machine Cost Increase in WC Sales Variable Oprating Cost Depreciation Fixed Cost Reduction of ATCF of Net Cash Flow after Tax WACC 11.92% PV of Cash Flow (140000-40000) (40% of Sales) SLM (20-6)/4 Existing project (Net Revenue in 4 years * (1-t)) (i.e. DF) 0 -$2,000,000 -$100,000 -$2,100,000 1.000 -$2,100,000 1 $1,000,000 -$400,000 $350,000 -$120,000 -$100,000 $438,000 0.893 $391,351 2 $2,000,000 -$800,000 $350,000 -$120,000 -$100,000 $798,000 0.798 $637,070 3 $2,000,000 -$800,000 $350,000 -$120,000 -$100,000 $798,000 0.713 $569,220 4 $1,000,000 -$400,000 $350,000 -$120,000 -$100,000 $438,000 0.637 $279,154 4 Salvage Value of Machinery $250,000 0.637 $159,334 4 Reduction in Working Capital $100,000 0.637 $63,734 NPV of the project (Rounding off Diff.) -$137Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.