Calculate NPV, Payback, Discounted Payback
Please put the the names that I should use in the formula (for example, for NPV, should I be using the sum of the incremental cash flow + incremental cash flow in t=0?) please be specific.
Year WACC: (from calculation below) Corporate Marginal Tax rate: 9.1% 22.0% 10,790,572 5,977,977 478,937 11,491,959 6,309,085 473,189 New product line information Year 1 revenues: Annual Revenue Growth rate: Year 1 COGS %. Annual reduction in COGS%: | 6,520,000 6.5% 59.4% 0.5% Revenue on new Product Line COGS: Sales, General & Admin Costs Depreciation (from schedule below) EBIT Taxes Net Income Add Back: Depreciation (from schedule) Less: Erosion Costs (from schedule) Operating Cash Flows 6,520,000 3,872,880 527,500 1,961,322 158,298 34,826 123,472 1,961,322 248,3161 1,836,478 6,943,800 4,089,898 521,170 3,206,082 (873,350) (192,137) (681,213) 3,206,082 264,209 2,260,660 7,395,147 4,318,766 514,916 2,040,522 520,943 114,608 406,336 2,040,522 281,118 2,165,740 7,875,832 4,560,106 508,737 1,307,746 1,499,242 329,833 1,169,409 1,307,746 299,110 2,178,045 8,387,761 4,814,575 502,632 1,158,938 1,911,616 420,555 1,491,060 1,158,938 318,253 2,331,746 8,932.965 5,082,857 496,601 766,106 2,587,401 569,228 2,018, 173 766,106 338,621 2,445,658 9,513,608 5,365,675 490,641 373,274 3,284,018 722,484 2,561,534 373,274 360,293 2,574,515 10,131,992 5,663,784 484,754 186,010 3,797,445 835,438 2.962.007 186,010 383,351 2,764,666 4,333,658 953,405 3,380,254 4,709,684 1,036,131 3,673,554 0 433,990 3,239,563 0 Initial Incremental SGA Annual Reduction in SG&A Increase in NWC required: 527,500 1.2% 690,000 407,886 2,972,368 Enter all revenues and costs as positive numbers, then account for inflows versus outflows in the subtotals and totals. It is possible for Taxes to be negative Start-up costs: Training on new product line Prior research into new prod. line | 990,000 398,600 Incremental Costs: Capital Spending Start-up Costs After-Tax Proceeds of Disposals Change in Net Working Capital Relevant Opportunity costs: Incremental Cash Flow 11,000,000 990,000 588,652 Annual warehouse rental lost: L375,000 690,000 398,600 747,878 398,600 1,862,060 398,600 1,767,140 398,600 1,779,445 398,600 1,933,146 398,600 2,047,058 398,600 2,175,915 398,600 2,366,066 398,600 2,573,768 690,000 398,600 3,530,963 (11,401,348) Existing Product Lines Erosion: Revenue erosion year 1: Increased erosion per year: COGS %: 874,600 6.4% 63.6% Discounted Cash Flows: Cumulative Cash Flow: Cumulative Discounted Cash Flow: (11,401,348) 1 685,246 747,878 (10,716,102) 1,563,241 2,609,938 (9,152,861)| 3 1,359,312 ,629,200 (7,793,549) 1,254,148 3,546,585 (6,539,401) 1,248,374 3.712.591 (5,291,0271 1,211,230 3980 204 (4,079,797 1,179,653 4.222.974 (2,900,144) 1.175,318 4,541,981 (1,724,826) 1,171,423 4,939,834 (553,403) 6 1,472,495 ,104,731 | 919,091 MACRS Life New Equipment Required: Cost machine 1 6,820,000 machine 2 4,180,000 Net Present Value IRR Payback Discounted Payback Profitability Index: 9,382,091 10.7% #N/A #N/A 1.82 7 Disposal of Existing machinery Cost machine a 5,000,000 machine b 3,500,000 Book Value Proceeds 401,600 | 410,000 200,000 | 175,000 9 10 Schedule: G/L on Disposals (net of Tax) Schedule: Revenue Erosion Costs Lost Revenue Related COGS Gross profit Taxes Net Erosion Costs Gain/(Loss) 8,400 (25,000) 874,600 556,246 318,354 70,038 248,316 Tax on G/L Net Proceeds (1.848) 408,152 5,500 180,500 588,652 930.574 591,845 338,729 74,520 264,209 990.131 6 29,723 360,408 79,290 281,118 1,053,500 670,026 383,474 84,364 299,110 1,120,924 712,907 408,016 89,764 318,253 1,192,663 758,533 434,129 95,508 338,621 1,268,993 807,080 461,913 101,621 360,293 1,350,209 858,733 491,476 108,125 383,351 1,436,622 913,692 522,930 115,045 407,886 machine a machine b Total 1,528,566 9 72.168 556,398 122.408 433,990 Schedule: Depreciation machine 1 (5 year MACRS Life) machine 2 (7 year MACRS Life) Total 1,364,000 597,322 1,961,322 2.182.400 1,023,682 3,206,082 1,309,440 7 31,082 2,040,522 785,664 522,082 1,307,746 7 85,664 373,274 1,158,938 392,832 373,274 7 66,106 373,274 3 73,274 186,010 186,010 1 Year WACC: (from calculation below) Corporate Marginal Tax rate: 9.1% 22.0% 10,790,572 5,977,977 478,937 11,491,959 6,309,085 473,189 New product line information Year 1 revenues: Annual Revenue Growth rate: Year 1 COGS %. Annual reduction in COGS%: | 6,520,000 6.5% 59.4% 0.5% Revenue on new Product Line COGS: Sales, General & Admin Costs Depreciation (from schedule below) EBIT Taxes Net Income Add Back: Depreciation (from schedule) Less: Erosion Costs (from schedule) Operating Cash Flows 6,520,000 3,872,880 527,500 1,961,322 158,298 34,826 123,472 1,961,322 248,3161 1,836,478 6,943,800 4,089,898 521,170 3,206,082 (873,350) (192,137) (681,213) 3,206,082 264,209 2,260,660 7,395,147 4,318,766 514,916 2,040,522 520,943 114,608 406,336 2,040,522 281,118 2,165,740 7,875,832 4,560,106 508,737 1,307,746 1,499,242 329,833 1,169,409 1,307,746 299,110 2,178,045 8,387,761 4,814,575 502,632 1,158,938 1,911,616 420,555 1,491,060 1,158,938 318,253 2,331,746 8,932.965 5,082,857 496,601 766,106 2,587,401 569,228 2,018, 173 766,106 338,621 2,445,658 9,513,608 5,365,675 490,641 373,274 3,284,018 722,484 2,561,534 373,274 360,293 2,574,515 10,131,992 5,663,784 484,754 186,010 3,797,445 835,438 2.962.007 186,010 383,351 2,764,666 4,333,658 953,405 3,380,254 4,709,684 1,036,131 3,673,554 0 433,990 3,239,563 0 Initial Incremental SGA Annual Reduction in SG&A Increase in NWC required: 527,500 1.2% 690,000 407,886 2,972,368 Enter all revenues and costs as positive numbers, then account for inflows versus outflows in the subtotals and totals. It is possible for Taxes to be negative Start-up costs: Training on new product line Prior research into new prod. line | 990,000 398,600 Incremental Costs: Capital Spending Start-up Costs After-Tax Proceeds of Disposals Change in Net Working Capital Relevant Opportunity costs: Incremental Cash Flow 11,000,000 990,000 588,652 Annual warehouse rental lost: L375,000 690,000 398,600 747,878 398,600 1,862,060 398,600 1,767,140 398,600 1,779,445 398,600 1,933,146 398,600 2,047,058 398,600 2,175,915 398,600 2,366,066 398,600 2,573,768 690,000 398,600 3,530,963 (11,401,348) Existing Product Lines Erosion: Revenue erosion year 1: Increased erosion per year: COGS %: 874,600 6.4% 63.6% Discounted Cash Flows: Cumulative Cash Flow: Cumulative Discounted Cash Flow: (11,401,348) 1 685,246 747,878 (10,716,102) 1,563,241 2,609,938 (9,152,861)| 3 1,359,312 ,629,200 (7,793,549) 1,254,148 3,546,585 (6,539,401) 1,248,374 3.712.591 (5,291,0271 1,211,230 3980 204 (4,079,797 1,179,653 4.222.974 (2,900,144) 1.175,318 4,541,981 (1,724,826) 1,171,423 4,939,834 (553,403) 6 1,472,495 ,104,731 | 919,091 MACRS Life New Equipment Required: Cost machine 1 6,820,000 machine 2 4,180,000 Net Present Value IRR Payback Discounted Payback Profitability Index: 9,382,091 10.7% #N/A #N/A 1.82 7 Disposal of Existing machinery Cost machine a 5,000,000 machine b 3,500,000 Book Value Proceeds 401,600 | 410,000 200,000 | 175,000 9 10 Schedule: G/L on Disposals (net of Tax) Schedule: Revenue Erosion Costs Lost Revenue Related COGS Gross profit Taxes Net Erosion Costs Gain/(Loss) 8,400 (25,000) 874,600 556,246 318,354 70,038 248,316 Tax on G/L Net Proceeds (1.848) 408,152 5,500 180,500 588,652 930.574 591,845 338,729 74,520 264,209 990.131 6 29,723 360,408 79,290 281,118 1,053,500 670,026 383,474 84,364 299,110 1,120,924 712,907 408,016 89,764 318,253 1,192,663 758,533 434,129 95,508 338,621 1,268,993 807,080 461,913 101,621 360,293 1,350,209 858,733 491,476 108,125 383,351 1,436,622 913,692 522,930 115,045 407,886 machine a machine b Total 1,528,566 9 72.168 556,398 122.408 433,990 Schedule: Depreciation machine 1 (5 year MACRS Life) machine 2 (7 year MACRS Life) Total 1,364,000 597,322 1,961,322 2.182.400 1,023,682 3,206,082 1,309,440 7 31,082 2,040,522 785,664 522,082 1,307,746 7 85,664 373,274 1,158,938 392,832 373,274 7 66,106 373,274 3 73,274 186,010 186,010 1
