Answered step by step
Verified Expert Solution
Question
1 Approved Answer
I need help finding how to calculate in excel the the cells for the sensitivity analysis Deluxe usually evaluates its investments using an after-tax discount
I need help finding how to calculate in excel the the cells for the sensitivity analysis
Deluxe usually evaluates its investments using an after-tax discount rate of 8%. The proposed project is considered to be riskier than average and so a risk-adjusted rate of 9% will be used for this project.
Depreciable Capital | $30,000,000.00 | |||||
Residual Value (10%) | $3,000,000.00 | |||||
Depre. / year | $9,000,000.00 | |||||
Year | ||||||
Present | 2016 | 2017 | 2018 | |||
Fixed Capital | $(30,000,000.00) | |||||
Net Working Capital | $(16,000,000.00) | |||||
Total | $(46,000,000.00) | |||||
EBT | $14,000,000.00 | $17,000,000.00 | $22,000,000.00 | |||
Tax 25% | $3,500,000.00 | $4,250,000.00 | $5,500,000.00 | |||
Net Earnings | $10,500,000.00 | $12,750,000.00 | $16,500,000.00 | |||
Add Depre. | $9,000,000.00 | $9,000,000.00 | $9,000,000.00 | |||
After Tax Op Cash Flow | $19,500,000.00 | $21,750,000.00 | $25,500,000.00 | |||
After Tax Salvage Value | $2,250,000.00 | |||||
Return of Net Working Cap | $16,000,000.00 | |||||
Total | $18,250,000.00 | |||||
Total After Tax | $(46,000,000.00) | $19,500,000.00 | $21,750,000.00 | $43,750,000.00 | ||
NPV @ 9% | $115,979,475.36 | |||||
IRR | 32% | |||||
Sensitivity Analysis | ||||||
NPV value with 10%, 20%, 30% plus/minus change in EBT, Discount rate and tax rate | ||||||
Plus 10% | Minus 10% | Plus 20% | Minus 20% | Plus 30% | Minus 30% | |
EBT | $119,290,022.85 | $112,668,927.88 | $122,600,570.33 | $109,358,380.39 | $125,911,117,82 | $106,047,832.91 |
Discount rate | $114,711,167.81 | $117,285,354.71 | $113,479,016.71 | $118,630,289.98 | $112,281,671.72 | $120,015,838.08 |
Tax rate | $118,790,133.12 | $121,241,543.04 | $117,564,428.16 | $122,467,248.00 | $116,338,723.20 | $123,692,952.96 |
IRR value with 10%, 20%, 30% plus/minus change in EBT and tax rate | ||||||
Plus 10% | Minus 10% | Plus 20% | Minus 20% | Plus 30% | Minus 30% | |
EBT | 35% | 29% | 38% | 26% | 41% | 23% |
Tax rate | 31% | 33% | 30% | 35% | 29% | 36% |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started