Trying to understand how to find the following below using a scenario, sensativi
ID: 2718853 • Letter: T
Question
Trying to understand how to find the following below using a scenario, sensativity, and break-even analysis in Excel:
Project Parameters:
Suppose, we can sell 50,000 cans of shark attractant per year at a price of $4.00 per can. It costs us about $2.50 per can to make the attractant. A new product such as this one typically has only a three-year life. We require a 20% return on new products.
Fixed costs for the project will run $12,000 per year.
We will need to invest a total of $90,000 in manufacturing equipment. For simplicity, we will assume that this $90,000 will be fully depreciated over the three year life of the project.
The project will require an initial $20,000 investment in net working capital.
The tax rate is 34%.
Questions:
1. Set-up the problem below in the space provided. Calculate the NPV. Calculate the IRR.
2. Do a Scenario Analysis with the three following scenarios:
a. Best Case - Price per can $6, Cost per can $1.50
b. Base Case - Price per can $4, Cost per can $2.50
c. Worst Case - Price per can $4, Cost per can $3.50
3. Do a Sensitivity Analysis where you vary the number of cans from 20,000 to 80,000.
4. Do the following break-even analyses using the base case:
a. Break-even price so that NPV = 0
b. Break-even quantity so the NPV = 0
c. Break-even cost per can so that NPV = 0
Scenario:
Number of cans 50000 Price per can 4 Cost per can 2.5 Fixed Cost 12000 Tax rate 34% Discount rate 20% Initial Investment 90000 0 1 2 3 Revenue $ 200,000 COGS $ 125,000 Gross Profit $ 75,000 Fixed Cost $ 12,000 EBITDA $ 63,000 Depreciation EBIT Tax Net Income NCS $ (90,000) NWC $ (20,000) $ 20,000 CFFA NPV Hint: It should be $10,648 for the Base Case. IRRExplanation / Answer
Answer : 1 Calculation of annual income :
Sales 4*50000 200000
Less Cost of goods sold 2.5 *50000 125000
Gross Income 75000
Fixed cost 12000
Net Income 63000
Deperication 90000/3 30000
EBT 33000
TAX 33000*34% 11220
EAT 21780
Annual cash flow EAT+ Deprication 51780
Present value of cash in flow @ 20% for 3 Years 51780*2.106 = 109048.68
Add Present value of working capital value refund at 3rd Year 20000* 0.579 = 11580.00
less Intial investment 90000.00+20000.00 110000.00
NPV 10628.68
Calculation of IRR
Present value of cash in flow @ 20% for 3 Years 51780*2.106 = 109048.68
Add Present value of working capital value refund at 3rd Year 20000* 0.579 = 11580.00
less Intial investment 90000.00+20000.00 110000.00
NPV 10628.68
Present value of cash in flow @ 24% for 3 Years 51780*1.952 = 101074.56
Add Present value of working capital value refund at 3rd Year 20000* 0.512 = 10240
less Intial investment 90000.00+20000.00 110000.00
NPV 1314.56
It is clear from above calculation that NPV is near to zero at 25%. Hence its IRR is 25%.
Answer-2 Scenirio Anlaysis.
Answer -3
Answer -4
Breakeven Price = $ 3.85 /unit
Answer 4 (B) break even quantity = 45000 units
Answer 4 (c) Breakeven cost = $ 2.65
Scenario Analysis Best case Base case Worst case Unit sold 50000.00 50000.00 50000.00 Sale price 6.00 4.00 4.00 Revenue 300000.00 200000.00 200000.00 Cost of goods sold /unit 1.50 2.50 3.50 Cost of goods sold 75000.00 125000.00 175000.00 Gross profit 225000.00 75000.00 25000.00 Less fixed cost 12000.00 12000.00 12000.00 Net Income 213000.00 63000.00 13000.00 Less Depreciation 30000.00 30000.00 30000.00 EBT 183000.00 33000.00 -17000.00 Tax @ 34% 62220.00 11220.00 0.00 EAT 120780.00 21780.00 -17000.00 Cash inflow (EAT +Dep) 150780.00 51780.00 13000.00 Present value @20% 3Years 2.11 2.11 2.11 Present value of cash inflow 317542.68 109048.68 27378.00 Present value of WC 20000*.579 11580.00 11580.00 11580.00 Total Present value 329122.68 120628.68 38958.00 Less Initial Investment 90000+20000 110000.00 110000.00 110000.00 Net present Value 219122.68 10628.68 -71042.00Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.