FIN 420 final (the right one)

.xlsx

School

Southern New Hampshire University *

*We aren’t endorsed by this school

Course

420

Subject

Finance

Date

Jan 9, 2024

Type

xlsx

Pages

11

Uploaded by CoachWrenMaster1187

Initial Cash Flows Column1 Investments Amount (millions) Cost of the factory + installation ($150.00) cost of equipment ($20.00) working captial ($10.00) Initial outflow ($180.00) OCF yr 1 Rev Variable cost fixed cost (1-T) 9.4975 103 56.65 34 0.6 OCF yr 2 Rev Variable cost fixed cost (1-T) 29.695 169 92.95 24 0.6 OCF yr 3 Rev Variable cost fixed cost (1-T) 37.27 161 88.55 19 0.6 OCF yr 4 Rev Variable cost fixed cost (1-T) 41.8175 276 151.8 40 0.6 OCF yr 5 Rev Variable cost fixed cost (1-T) 46.3675 207 113.85 34 0.6 OCF yr 6 Rev Variable cost fixed cost (1-T) 38.6775 103.5 56.925 24 0.6 Column1 Column2 Column3 Column4 Net working Captial 2012 2013 2014 Net Revnue 103 169 Variable costs 56.65 92.95 NWC Accounts recievable (8%) #NAME? #NAME? Inventory balance 15 14.1625 #NAME? Accounts Payable 5 11.33 #NAME? NWC 10 11.0725 #NAME? Change in net working capital -1.0725 #NAME? Column1 Column2 Terminal Value $146.62 Year 2012 2013 2014 Cash Flow -180 9.4975 29.695 Discount rate (11%) 11% NPV $12.04 IRR 12.82% EAS ($3.00) Payback Amount Column1 Initial -180
Year 1 9.4975 -170.5025 Year 2 29.695 -140.8075 Year 3 37.27 -103.5375 year 4 41.8175 -61.72 Year 5 46.3675 -15.3525 Year 6 3.1 0 partial payback years 0.02 Full payback years 5 Total payback 5.02
Building depreciation (millions) percentage Column 150 BV year 1 3.9 year 2 7.5 year 3 7.05 year 4 6.75 year 5 6.45 (dep*tax) year 6 6 3.16 accumulated dep 37.65 (dep*tax) Equipment depreciation (millions) percentage 5.56 20 year 1 4 (dep*tax) year 2 6.4 4.34 year 3 3.8 year 4 2.4 (dep*tax) year 5 2.2 3.66 year 6 1.2 accumulated dep 20 (dep*tax) after tax 3.46 0 1.8 (dep*tax) 2.88 Column5 Column6 Column7 Column8 2015 2016 2017 2018 161 276 207 103.5 88.55 151.8 113.85 56.925 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? 22.2525 0 #NAME? #NAME? #NAME? 22.2525 2015 2016 2017 2018 37.27 41.8175 46.3675 $146.62
n1 after tax 112.35 bv 106.14 after tax value Column1 Column2 Annual Depreciation (milion) year 1 7.9 year 2 13.9 year 3 10.85 year 4 9.15 year 5 8.65 year 6 7.2 accumulated annual dep 57.65 year 2012 2013 2014 2015 sales revenue Err:509 Err:509 Err:509 VC -56.65 Err:509 Err:509 FC selling and general costs -7 -7 -7 endorsements -2 -2 -2 advertising and promotion costs -25 -15 -10 total fixed costs -34 -24 -19 subtotal 12.35 Err:509 Err:509 taxes (40%) 0.6 0.6 0.6 depreciation added back 3.16 9.4975 103 earnings -150 10.57 Err:509 Err:509 changes in NWC -1.0725 #NAME? #NAME? CF from operations 9.4975 Err:509 Err:509 Initial -180
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help