Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

image text in transcribedimage text in transcribed

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.

image text in transcribedimage text in transcribedimage text in transcribedimage text in transcribed
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

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

Personal Finance

Authors: Jeff Madura

7th Edition

0134989961, 978-0134989969

More Books

Students also viewed these Finance questions