CapBud Assign Oshaw Final

xlsx

School

Bellevue University *

*We aren’t endorsed by this school

Course

655

Subject

Finance

Date

Feb 20, 2024

Type

xlsx

Pages

1

Uploaded by BarristerCrown6225

Report
2 Yellow highlighted cells are cells for inputs. Team should verify all other calculations & formats 3 C D E F G H I J K L M N O P Q R S T 4 Inputs 5 ATSV old @ t=0 $ 305,000.00 ATSV formula = Names of all members who contributed to overall project: Include an NPV Profile 6 Equipment $ (2,000,000.00) 1 Orin Shaw Note: Only 1 NPV Profile for base case 7 Tax Credit $ 100,000.00 2 8 Depreciaton per year $ 300,000.00 3 9 Sales period 1 $ 1,000,000.00 growth: g yrs 2-3 = 15% g yrs 4-6 = 5% 4 10 CoGS %of sales 45% 5 11 SG&A exp. %of sales 10% 6 12 ATSV new @ t=6 $ 260,000.00 13 14 Operating Life CFs 15 Time 0 1 2 3 4 5 6 16 Sales $ 1,000,000.00 $1,150,000 $1,322,500 $1,388,625 $1,458,056 $1,530,959 17 - COGS $ 450,000.00 517,500 595,125 624,881 656,125 688,932 18 - SG&A expenses $ 100,000.00 115,000 132,250 138,863 145,806 153,096 19 - Depreciation $ 300,000.00 $ 300,000.00 $ 300,000.00 $ 300,000.00 $ 300,000.00 $ 300,000.00 20 = EBIT $ 150,000.00 $ 217,500.00 $ 295,125.00 $ 324,881.25 $ 356,125.31 $ 388,931.58 21 -Taxes (40%) $ 60,000.00 $ 87,000.00 $ 118,050.00 $ 129,952.50 $ 142,450.13 $ 155,572.63 22 = Net Income $ 90,000.00 $ 130,500.00 $ 177,075.00 $ 194,928.75 $ 213,675.19 $ 233,358.95 23 + Depreciation $ 300,000.00 $ 300,000.00 $ 300,000.00 $ 300,000.00 $ 300,000.00 $ 300,000.00 24 = Operating CF $ 390,000.00 $ 430,500.00 $ 477,075.00 $ 494,928.75 $ 513,675.19 $ 533,358.95 25 26 Time 0 Investments NPV Analysis Grid: NPV vs Discount Rate & Cost of Goods Sold (CoGS) Percent Ranges 27 Equipment -2,000,000 28 ATSV old 305,000 CoGS -> CoGSbase% -20% CoGSbase% -10% CoGS Base % CoGSbase% +10% CoGSbase% +20% 29 Tax credit 100,000 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 NWC -200,000 CoGS -> 36% 40.50% 45% 49.50% 54% 31 Cost of Capital 4% $1,399,451 $1,215,964 $1,032,476 $848,989 $665,501 32 Terminal Non-OCF: 6% $1,175,471 $1,004,129 $832,786 $661,444 $490,101 33 ATSV new @ t=6 260,000 8% $974,016 $813,650 $653,284 $492,918 $332,552 34 NWC 200,000 10% $792,323 $641,904 $491,484 $341,065 $190,645 35 = Net Cash Flow -$1,795,000 $390,000 $430,500 $477,075 $494,929 $513,675 $993,359 12% $628,022 $486,638 $345,255 $203,871 $62,488 36 = Cummulative CF -$1,795,000 -$1,405,000 -$974,500 -$497,425 -$2,496 $511,179 $1,504,538 37 38 Cost of Capital 8% 39 NPV $653,284 40 IRR = 17.7% NPV Analysis Grid: NPV vs Discount Rate & Year 1 Sales Ranges 41 PBP = 4.005 42 PI = 54% Sales Yr.1 -> 43 Sales Yr.1 -> 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 44 NPV Data Table $NPV in Cells: $800,000.00 $900,000.00 $ 1,000,000.00 $ 1,100,000.00 $ 1,200,000.00 45 NPV Cost of Capital 4% $665,501.12 $848,989 $1,032,476 $1,215,964 $1,399,451 46 0% $1,504,537.88 6% $490,101.06 $661,444 $832,786 $1,004,129 $1,175,471 47 2% $1,255,257.95 8% $332,551.70 $492,918 $653,284 $813,650 $974,016 48 4% $1,032,476.12 10% $190,645.21 $341,065 $491,484 $641,904 $792,323 49 6% $832,786.13 12% $62,487.55 $203,871 $345,255 $486,638 $628,022 50 8% $653,283.73 51 10% $491,484.19 52 12% $345,254.63 Notes / Reminders: 53 14% $212,758.39 NPV Profile 54 16% $92,408.96 Use NPVs from table on left to populate grids for different scenarios 55 18% ($17,168.17) 56 20% ($117,167.24) Sales Yr.1 -20% Sales Yr.1 -10% Sales Yr.1 Base Sales Yr.1 +10% Sales Yr.1 +20% Rates (0-26% by 2 increments) Some Tips for NPV Scenario / Risk Analysis = NPV of Full Project under different "What if?" Assumptions for CoGS% (Top Table) & SalesYr1 (Bott 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 C 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 fro 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 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 (bel the scenario table for the column. Do this 1 column (scenario) at a time. Again, center column (scenario) is the base case & should be identica 4. Remember to reset your Cash Flow Model to the Base Case Values when done populating your scenario tables. NPV Profile
Discover more documents: Sign up today!
Unlock a world of knowledge! Explore tailored content for a richer learning experience. Here's what you'll get:
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help