Hello. I need to Use the NPV numbers from the table on the left to populate grids for different scenarios in excel. The following picture has the numbers I am using, please use them to make one or both of the graphs in the second picture and explain by formulas and such how you completed them so I better understand please. Thank you. If you need any other info just let me know. I think you only need the two pics but including a third picture with extra data just in case. Just need the NPV Analysis Grid: NPV vs Discount Rate & Cost of Goods Sold (CoGS) Percent Ranges grid completed and the NPV Analysis Grid: NPV vs Discount Rate & Year 1 Sales Ranges grid completed. please help and explain how filled them out thanks.
AutoSave . Off) H Team Capital Budget Exercise Search Ben B X File Home Insert Page Layout Formulas Data Review Help Share Comments & Cut Calibri 11 ~ A" A = = [ Copy ap Wrap Text General AutoSum Paste Fill AY O 4 Format Painter BIUV EEE E Merge & Center $ ~ % 9 70 20 Conditional Format as Cell Insert Delete Format Clear Sort & Find & Ideas Formatting * Table ~ Styles ~ Filter ~ Select Clipboard Font Alignment Number Styles Cells Editing Ideas F54 X v fx Use NPVs from table on left to populate grids for different scenarios L M N O P Q R S T U 23 Complete the grids below to report 5x5 Grids of NPV vs input variable changes noted 24 25 26 NPV Analysis Grid: NPV vs Discount Rate & Cost of Goods Sold (CoGS) Percent Ranges 27 28 COGS -> CoGSbase%-20% CoGSbase%-10% COGS Base % CoGSbase%+10% CoGSbase%+20% 29 COGS -> 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 30 COGS -> 36% 40.50% 45% 49.50% 54% 31 Cost of Capital 4% 32 6% 33 8% match on left 34 10% 35 12% 36 37 38 CapBud Template Sheet3 + Ready + 175% Type here to search W 6:31 PM 10/13/2020 EAutoSave . Off) H Team Capital Budget Exercise Search Ben B X File Home Insert Page Layout Formulas Data Review Help Share Comments & Cut Calibri 11 ~ A" A =E [ Copy de Wrap Text General AutoSum Fill AY O 4 Paste Format Painter BIUV EEE E Merge & Center $ ~ % 9 70 20 Conditional Format as Cell Insert Delete Format Clear Sort & Find & Ideas Formatting > Table Styles Filter ~ Select Clipboard Font Alignment Number Styles Cells Editing Ideas F54 X v fx Use NPVs from table on left to populate grids for different scenarios L M N O P Q R S T U 38 39 40 NPV Analysis Grid: NPV vs Discount Rate & Year 1 Sales Ranges 41 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 Sales Yr.1 42 Sales Yr.1 -> 20% -10% Base +10% +20% 43 $NPV in Cells: 800,000 900,000 1,000,000 1,100,000 1,200,000 44 Cost of Capital 4% 45 6% 46 8% 47 10% 48 12% 49 50 51 Screenshot saved The screenshot was added to your 52 OneDrive. CapBud Template Sheet3 + OneDrive Ready Type here to search X W 6:31 PM 10/13/2020 EAutoSave . Off) H Team Capital Budget Exercise Search Ben B X File Home Insert Page Layout Formulas Data Review Help Share Comments & Cut Calibri 11 ~ A A = ay Wrap Text Number AutoSum 4 Paste [ Copy AY O BIUV E EE EE E Merge & Center v $ ~ % 9 00 20 Conditional Format as Cell Insert Delete Format Fill Sort & Find & Ideas Format Painter Formatting > Table Styles Clear Filter ~ Select Clipboard Font Alignment Number Styles Cells Editing Ideas 126 X v fx A B C D E F G H 1 J K L M N O P Q 14 14 Operating Life CFs 15 15 Time 0 1 2 3 5 6 16 16 Sales $1,000,000 $1,150,000 $1,322,500 $1,388,625 $1,458,056 $1,530,959 17 17 - COGS 450,000 517,500 595,125 624,881 656,125 688,932 18 18 - SG&A expenses 100,000 115,000 132,250 138,863 145,806 153,096 19 19 - Depreciation 283,333 283,333 283,333 283,33 283,333 283,333 20 20 = EBIT 166,667 234,167 311,792 341,548 372,792 405,598 21 21 -Taxes (40%) 66,667 93,667 124,717 136,619 149,117 162,239 22 22 = Net Income 100,000 140,500 187,075 204,929 223,675 243,359 NPV Scenario / Risk Analysis: 23 23 + Depreciation 283,333 283,333 283,333 283,333 283,333 283,333 Complete the grids below to report 5x5 Grids of NPV vs input variab 24 24 = Operating CF 383,333 423,833 470,408 488,262 507,009 526,692 25 25 26 26 Time 0 Investments NPV Analysis Grid: NPV vs Discount Rate & Cost of Goods Sold (CoGS) Percent 27 27 Equipment 2,000,000 28 28 ATSV old 305,000 COGS -> CoGSbase%-20% CoGSbase%-10% COGS Base % 29 29 Tax credit 100,000 COGS -> i.e base%*0.8 i.e base%*0.9 i.e base%*1.0 30 30 NWC 200,000 COGS -> 36% 40.50% 15% 31 31 Cost of Capital 4% 32 32 Terminal Non-OCF: 6% 33 33 ATSV new @ t=6 260,000 8% match on left 34 34 NWC 200,000 10% 35 35 = Net Cash Flow $1,795,000.00 $383,333.33 $423,833.33 $470,408.33 $488,262.08 $507,008.52 $986,692.28 12% 36 36 = Cummulative CF $1,795,000.00 -$1,411,666.67 -$987,833.33 -$517,425.00 -$29,162.92 $477,845.60 $1,464,537.88 37 37 4.05751958 38 38 Cost of Capital 8% 39 39 NPV $622,464.54 40 40 IRR = 17.23% NPV Analysis Grid: NPV vs Discount Rate & Year 1 Sales Ranges 41 41 PBP = 4.06 CapBud Template Sheet3 + 1 Ready + 102% Type here to search X W 6:32 PM 10/13/2020AutoSave . Off) H Team Capital Budget Exercise Search Ben B X File Home Insert Page Layout Formulas Data Review Help Share Comments & Cut Calibri 11 ~ A" A =E ap Wrap Text General AutoSum [A Copy BIUV $ ~ % 9 70 20 Conditional Format as Cell Insert Delete Format Fill AY O 4 Paste EEE E Merge & Center Clear Sort & Find & Ideas Format Painter Formatting > Table Styles Filter ~ Select Clipboard Font Alignment Number Styles Cells Editing Ideas F54 X V Use NPVs from table on left to populate grids for different scenarios A B C D E F G H 44 44 NPV Data Table 45 45 Rates NPV 46 46 0% $1,464,537.88 47 47 2% $1,217,915.08 48 48 4% $997,528.54 49 49 6% $800,003.97 50 50 8% $622,464.54 51 51 10% $462,449.12 52 52 12% $317,845.25 Notes / Reminders: 53 53 14% $186,833.94 NPV Profile 54 54 16% $67,844.05 Use NPVs from table on left to populate grids for different scenarios 55 55 18% -$40,485.52 56 56 20% -$139,337.31 57 58 59 CapBud Template Sheet3 + Ready - + 175% Type here to search x @ w 6:23 PM 10/13/2020 E