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.
For Payback Period and Discount Payback I need to use the =match function
**I dont need the number answer. I need help on what sections I need to be using for the formulas, and how to do the formula. Answer alone is not sufficient
WACC: (from calculation below) 9.1% Year Corporate Marginal Tax rate: 22.0% New product line information Revenue on new Product Line COGS: 6,520,000 6,943,800 3.872,880 7,395,147 7,875,832 1,089,898 4,318,766 8,387,761 8,932,965 4,560,106 5,082,857 9,513,608 5,365,675 10,131,992 10,790,572 Year 1 revenues: 6,520,000 Sales, General & Admin Costs 527,500 621,170 608,737 502,632 496,601 5,663,784 490,641 5,977,977 11,491,959 6,309,085 Annual Revenue Growth rate: 6.5% EBIT 1,961,322 158,298 3,206,082 2,040,522 478,937 Year 1 COGS %: 59.4% Depreciation (from schedule below) Annual reduction in COGS%: 0.5% (873,350) 520,943 1,307,746 373,274 Taxes 1,499,242 1,158,938 766,106 1,911,616 2,587,401 3,284,018 186,010 34,826 Net Incom (192,137) 114,608 329,833 3,797,445 420,555 569,228 1,709,684 835,438 953,405 1,036,131 Initial Incremental SGA 527,500 123,472 Add Back: Depreciation (from schedule) (681,213) 1,169,409 1,491,060 2,018,173 Annual Reduction in SG&A 1.2% Less: Erosion Costs (from schedule) 1,961,322 248,316 3,206,082 2,040,522 2,561,534 1,307,746 2,962,007 264,209 281,118 ,158,938 299,110 766,106 318,253 373,274 3,673,554 Increase in NWC required: 690,000 Operating Cash Flows 1,836,478 2,260,660 360,293 2,165,740 2,178,045 2,331,746 383,351 2,445,658 407,886 2,574,515 2,764,666 2,972,368 3,239,563 Start-up costs: Incremental Costs: 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 Training on new product line Prior research Capital Spending 398,600 11,000,000 Start-up Costs 990,000 Annual warehouse rental lost: After-Tax Proceeds of Disposals 588,652 Change in Net Working Capital 690,000 690,000 Existing Product Lines Erosion: Relevant Opportunity costs: Incremental Cash Flow 398,600 (11,401,348) 747,878 398,600 398,600 1,862,060 398,600 1,767,140 1,779,445 398,600 2,047,058 398,600 398,600 874,600 2,175,915 398,600 Revenue erosion year 1: 2,366,066 2,573,768 3,530,963 Increased erosion per year: 6.4% Discounted Cash Flows COGS %: 63.6% 685,246 1,563,241 Cumulative Cash Flow 1,254,148 1,248,374 1,211,230 1,175,318 1,171,423 1.472,495 Cumulative Discounted Cash Flow: 747.878 3,629,200 3,546,585 (10,716,102) 3,712,591 9,152,861) 7,793,549 3,980,204 4,222,974 (6,539,401) (5,291,027) 4,079,797 1,939.834 (2,900,144) 5.104,73 (1,724,826) (553,403) New Equipment Required: Net Present Value Cost MACRS Life 9,382,091 6,820,000 IRR machine 1 10.7% Payback machine 2 4, 180,000 NIA Discounted Payback Profitability Index: Disposal of Existing machinery 1.82 Cost Book Value | Proceeds machine a 5,000,000 401,600 410,000 machine b 3,500,000 200,000 175,000 Schedule: G/L on Disposals (net of Tax) Schedule: Revenue Erosion Costs Lost Revenue Related COGS 874,600 930,574 990, 131 556,246 1,053,500 591,845 1, 120,924 629,723 1, 192,663 1,268,993 670,026 ,350,209 1,528,566 Gain/(Loss) Tax on G/L Net Proceeds Gross pr machine a 8,400 408,152 Taxes 318,354 338,729 712,907 758,533 807,080 858,733 913,692 972,168 machine b (25,000) 70,038 360,408 383,474 Net Erosion Costs 74,520 79,290 408,016 434,129 248,316 84,364 264,209 89,764 461,913 95,508 191,476 522,930 556,398 281,118 338,621 108, 125 115,045 122,408 Tota 588,652 299,110 318,253 360,293 383,351 407,886 Schedule: Depreciation machine 1 (5 year MACRS Life) machine 2 (7 year MACRS Life) 1,364,000 2,182,400 1,309,440 785,664 Total 597,322 1,023,682 85,664 1,961,322 3,206,082 731,082 392,832 522,082 2,040,522 1,307,746 373,274 1,158,938 766,106 186,010 373,274 186,010
