Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Hi There, Can you please help clarify an excel project you completed on 7/17/15? I am curious how you calculated the NPV Analysis Grid: NPV

Hi There, Can you please help clarify an excel project you completed on 7/17/15? I am curious how you calculated the NPV Analysis Grid: NPV vs Discount Rate & Salvage Value Ranges. There is not a formula in the excel spreadsheet. For example, how did you determine that the NPV was $1,148 for both grids? Thanks!image text in transcribed

Yellow highlighted cells are cells for inputs. Team should verify all other calculations & formats A B C D E F G H 1 Inputs 2 ATSV old @ t=0 375 ATSV formula = SV-[(SV-BV)tax rate] 3 Equipment 2000 4 Tax Credit 100 5 Depreciaton per year 300 6 Sales period 1 1200 growth: g yrs 2-3 = 15% g yrs 4-6 = 5% 7 CoGS %of sales 45% 8 SG&A exp. %of sales 10% 9 ATSV new @ t=6 300 10 11 Operating Life CFs 12 Time 0 1 2 3 4 5 6 13 Sales $1,200 $1,380 $1,587 $1,666 $1,750 $1,837 14 - COGS -540 -621 -714 -750 -787 -827 15 - SG&A expenses -120 -138 -159 -167 -175 -184 16 - Depreciation -300 -300 -300 -300 -300 -300 17 = EBIT 240 321 414 450 487 527 18 -Taxes (40%) -96 -128 -166 -180 -195 -211 19 = Net Income 144 193 248 270 292 316 20 + Depreciation 300 300 300 300 300 300 21 = Operating CF 444 493 548 570 592 616 22 23 Time 0 Investments 24 Equipment -2,000 25 ATSV old 375 26 Tax credit 100 27 NWC 200 28 29 Terminal Non-OCF: 30 ATSV new @ t=6 300 31 NWC 200 32 = Net Cash Flow -$1,325 $444 $493 $548 $570 $592 $1,116 33 = Cummulative CF -$1,325 -$881 -$388 $160 $730 $1,322 $2,438 34 35 Cost of Capital 12% 36 NPV $1,118 37 IRR = 34.5% 38 PBP = 3.05 39 PI = 40 41 NPV Data Table 42 Rates 43 44 45 46 47 48 49 50 51 52 53 54 55 56 NPV Scenario / Risk Analysis: Complete the grids below to report 5x5 Grids of NPV vs input variable changes noted NPV Analysis Grid: NPV vs Discount Rate & Salvage Value Ranges SV -> SV-20% SV-10% SV Base SV+10% SV+20% 300 ATSV -> ATSV-20% ATSV-10% ATSV Base ATSV+10% ATSV+20% $NPV in Cells: Cost of Capital 4% $1,487 $1,510 $1,534 $1,558 $1,582 6% $1,289 $1,310 $1,331 $1,352 $1,373 8% $1,111 $1,130 $1,148 $1,167 $1,186 10% $950 $967 $984 $1,001 $1,018 12% $805 $820 $836 $851 $866 NPV Analysis Grid: NPV vs Discount Rate & Year 1 Sales Ranges 1.84 -$184 NPV $179 $160 $145 $130 $553 -$183 -$184 -$183 $1,901 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 -20%Sales -10%Sales BaseSales +10%Sales +20%Sales Yr.1 Yr.1 Yr.1 Yr.1 Yr.1 Sales Yr.1 -> -20% -10% Base +10% +20% $NPV in Cells: Cost of Capital 4% $1,167 $1,351 $1,534 $1,718 $1,901 6% $988 $1,160 $1,331 $1,502 $1,674 8% $828 $988 $1,148 $1,309 $1,469 10% $683 $834 $984 $1,134 $1,285 12% $553 $694 $836 $977 $1,118

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored 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

Recommended Textbook for

Financial Modeling

Authors: Simon Benninga

2nd Edition

0262024829, 9780262024822

More Books

Students also viewed these Finance questions

Question

1. Too understand personal motivation.

Answered: 1 week ago