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

Report
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
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
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
2016 2017 2018 Column1 Err:509 Err:509 Err:509 Err:509 Err:509 Err:509 -7 -7 -7 -2 -2 -2 -30 -25 -15 -39 -34 -24 Err:509 Err:509 Err:509 0.6 0.6 0.6 56.65 34 0.6 Err:509 Err:509 Err:509 #NAME? #NAME? 22.2525 Err:509 Err:509 Err:509 Err:509 Terminal Value
Building depreciation (mil Initial Cash Flows Column1 8 Investments Amount (millions) year 1 Design tech and manufacturing specs ($30.00) year 2 cost of equipment ($8.00) year 3 working captial ($15.00) accumulated dep Initial outflow ($53.00) OCF yr 2 Rev Variable cost fixed cost 14.59 52.5 19.95 9.3 OCF yr 3 Rev Variable cost fixed cost 22.4284 72.45 27.531 9.245 OCF yr 4 Rev Variable cost fixed cost 29.4023 92.575 35.1785 9.406 Column1 Column2 Column3 Sales Revenue year 2013 2014 cost of shoe 90 90 market value 350 350 new balance market shares projections 15% 18% units sold 0.583333333333333 0.805 net revenue 52.5 72.45 Column1 Column2 Column3 Fixed Costs 2013 2014 selling, general and admin expenses 6.3 7.245 advertising and promotional 3 2 Total fixed costs 9.3 9.245 Column1 Column2 Column3 Net working Captial 2012 2013 Net Revnue 52.5 Variable costs 19.95 NWC Accounts recievable + Inventory balance Accounts Payable NWC Column1 Column2 Terminal Value $16.39 Year 2012 2013 Cash Flow ($53.00) 14.59
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
Discount rate (10%) 14% NPV $6.99 IRR 21.23% EAS ($3.38) Payback Amount Column1 Initial ($53.00) Year 1 14.59 -38.41 Year 2 22.4284 -15.9816 Year 3 15.9816 0 partial payback years 0.35 Full payback years 3 Total payback 3.35
percentage after- tax 2.32 1.6 1.392 2.56 1.52 5.68 (1-T) (dep*tax) 0.6 0.64 (1-T) (dep*tax) 0.6 1.024 (1-T) (dep*tax) 0.6 0.608 Column4 2015 90 350 20% 1.02861111111111 92.575 Column4 2015 7.406 2 9.406 Column4 Column5 year 2012 2014 2015 sales revenue 52.5 72.45 92.575 VC 19.95 27.531 35.1785 FC 9.3 selling and general costs 6.3 25 -10 advertising and promotion costs 3 15 total fixed costs 9.3 subtotal taxes (40%) 0.6 depreciation added back 0.64 earnings -30 2014 2015 changes in NWC 22.4284 $45.79 CF from operations 14.59
Initial -53
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
2013 2014 Column1 72.45 92.575 27.531 35.1785 9.245 9.406 7.245 -7 2 2 9.245 9.406 109.226 137.1595 0.6 0.6 1.024 0.608 66.5596 82.9037 0 0 22.4284 29.4023
$16.39 Terminal Value