AutoSave Home Insert Draw Page Layout Formulas Paste K24 CapBud AssignTemplate V7a POST Tell me Comments Share BD Insert Delete % .00 0 Conditional Format Formatting as Table Cell Styles Format Sort & Filter Find & Select Sensitivity Analyze Data Data Review View Automate 11 A^ Wrap Text v General Merge & Center Calibri (Body) B I U Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? Yes No fx 3 D E F G H | J K L M N P Q R S T U V W X Y Z AA AB AC AD AE AF AG A B 1 1 2 2 Yellow highlighted cells are cells for inputs. Team should verify all other calculations & formats 3 3 C 4 4 D Inputs E F G H | J K L M N 5 5 ATSV old @ t=0 ATSV formula = 6 6 Equipment 7 7 Tax Credit 8 8 Depreciaton per year 9 9 Sales period 1 growth: g yrs 2-3 = 15% g yrs 4-6 = 5% P Q R Names of all members who contributed to overall project: 1 2 3 4 S T Include an NPV Profile Note: Only 1 NPV Profile for base case model should be completed 10 10 COGS % of sales 5 11 11 SG&A exp. %of sales 6 12 12 ATSV new @t=6 13 13 14 14 Operating Life CFs 15 15 Time 4 5 16 16 Sales 17 17 - COGS 18 19 18-SG&A expenses 19 Depreciation 20 20 EBIT 21 21-Taxes (40%) 22 22 Net Income 6 Some Tips for NPV Scenario / Risk Analysis = NPV of Full Project under different "What if?" Assumptions for COGS% (Top Table) & SalesYr1 (Bottom Table): 1. Complete the tables below to report NPVs of Projec'ts Net CFs as you change the column input variable (COGS% or SalesYr1) in the origianl CF Model (to left) one at a time. 2. Top Table => Change CoGS% in the base model (cell D10) to the scenario column's new "What if?" value & then copy/paste the new NPVs from Column D (below Row 45) for the 5 scenario rates into the scenario table for the column. Do this 1 column (scenario) at a time. Center column (scenario) is the base case & thus should be identical in both tables. 3. Bottom Table => Change Yr1 Sales in the base model (cell E16) to the scenario column value & copy/paste the new NPVs from Column D (below Row 45) for the 5 scenario rates into the scenario table for the column. Do this 1 column (scenario) at a time. Again, center column (scenario) is the base case & should be identical in both tables. 4. Remember to reset your Cash Flow Model to the Base Case Values when done populating your scenario tables. 23 23 Depreciation 24 24 Operating CF 25 25 26 26 Time 0 Investments NPV Analysis Grid: NPV vs Discount Rate & Cost of Goods Sold (COGS) Percent Ranges 27 27 Equipment 28 28 ATSV old 29 29 Tax credit 30 30 NWC COGS -> COGS -> CoGSbase % -20% CoGSbase % -10% i.e base %*0.8 i.e base %*0.9 COGS Base % i.e base %*1.0 COGSbase % +10% COGSbase% +20% i.e base%*1.1 i.e base %*1.2 COGS -> 31 31 Cost of Capital 4% 32 32 Terminal Non-OCF: 33 33 ATSV new @ t=6 34 34 NWC 35 35 Net Cash Flow $0 $0 $0 $0 $0 $0 $0 6% 8% 10% 12% 36 36 = Cummulative CF $0 $0 $0 $0 $0 $0 $0 37 37 38 38 Cost of Capital 8% 39 39 NPV 40 40 IRR = NPV Analysis Grid: NPV vs Discount Rate & Year 1 Sales Ranges 41 41 PBP = 42 42 PI= 43 43 44 44 NPV Data Table Rates 45 45 (0-26% by 2 increments) NPV 46 46 47 47 48 48 49 49 50 50 51 51 52 52 53 53 54 54 Enter Notes/Reminders: CapBud Template NPV Profile Use NPVs from table on left to populate grids for different scenarios Sheet3 Accessibility: Good to go + Sales Yr.1 -> Sales Yr.1 -> Sales Yr.1 -20% Sales Yr.1 -10% Sales Yr.1 Base Sales Yr.1 +10% Sales Yr.1 +20% i.e base %*0.8 i.e base%*0.9 i.e base %*1.0 i.e base %*1.1 i.e base %*1.2 $NPV in Cells: Cost of Capital 4% 6% 8% 10% 12% + 72%