I need to find the NPV. I need to know how to plug it into excel but also how to
ID: 2693823 • Letter: I
Question
I need to find the NPV. I need to know how to plug it into excel but also how to do it by hand. Please help! I cannot get the image to upload, so this is the best I can do.
DATA
Cost of plant and equipment 7,900,000
Shipping and installation 100,000
Tax rate 34%
Required rate of return 15%
Sales Price per unit (yrs 1-4) 300
Sales Price per unit (yr 5) 260
Variable cost per unit 180
Annual fixed cost 200,000
Depreciation life 5
Section 1: Calculate EBIT Year 1 2 3 4 5
Units Sold 70,000 120,000 140,000 80,000 60,000
Sales Revenue 21,000,000 36,000,000 42,000,000 24,000,000 15,600,000
Less: Variable Costs 12,600,000 21,600,000 25,200,000 14,400,000 10,800,000
Less: Fixed Costs 200,000 200,000 200,000 200,000 200,000
Equals: EBDIT 8,200,000 14,200,000 16,600,000 9,400,000 4,600,000
Less: Depreciation 1,600,000 1,600,000 1,600,000 1,600,000 1,600,000
Equals: EBIT 6,600,000 12,600,000 15,000,000 7,800,000 3,000,000
Taxes 2,244,000 4,284,000 5,100,000 2,652,000 1,020,000
Section 2: Calculate Operating Cash Flow
EBIT 6,600,000 12,600,000 15,000,000 7,800,000 3,000,000
Less: Taxes 2,244,000 4,284,000 5,100,000 2,652,000 1,020,000
Plus: Depreciation 1,600,000 1,600,000 1,600,000 1,600,000 1,600,000
Operating Cash Flow 5,956,000 9,916,000 11,500,000 6,748,000 3,580,000
Section 3: Calculate Net Working Capital
Revenue: 21,000,000 36,000,000 42,000,000 24,000,000 15,600,000
Initial Working Capital Requirement 100,000
Net Working Capital Needs: 2,100,000 3,600,000 4,200,000 2,400,000 1,560,000
Liquidation of Working Capital 1,560,000
Change in Working Capital: 100,000 2,000,000 1,500,000 600,000 (1,800,000) (2,400,000)
Section 4: Calculate Free Cash Flow
Operating Cash Flow 5,956,000 9,916,000 11,500,000 6,748,000 3,580,000
Minus: Change in Net Working Capital 100,000 2,000,000 1,500,000 600,000 (1,800,000) (2,400,000)
Minus: Change in Capital Spending 8,000,000 0 0 0 0 0
Free Cash Flow: (8,100,000) 3,956,000 8,416,000 10,900,000 8,548,000 5,980,000
PV = 3,440,000 6,363,705 7,166,927 4,887,347 2,973,117
NPV= ?????????
IRR = 77%
I need to find the NPV. I need to know how to plug it into excel but also how to do it by hand. Please help! I cannot get the image to upload, so this is the best I can do.
Explanation / Answer
please post 1 question at a time