Answered step by step
Verified Expert Solution
Link Copied!

Question

00
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

blur-text-image

Get Instant Access with AI-Powered Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Students also viewed these Finance questions