Need assistance (New Heritage Doll Company) determining Net Working Capital, NPV, and IRR for year 2010-2020. Tax Rate is 40%. Please include formulas and solutions. Began NWC for 2011, but am stuck.

H67 X fx Calc Here A 1 J K L M N 0 B D E F G H Selected Operating Projections for Design Your Own Doll ($ in thousands) 1 2010 2012 2017 2011 $4,500 $6,860 52.4% 2013 $8,409 22.6% 2014 $9,082 8.0% 2015 $9,808 8.0% 2016 $10,593 8.0% $11,440 8.0% 2018 $12,355 8.0% 2019 2020 $13,344 $14,411 8.0% 8.0% 635 575 2,035 152 2,762 1,155 $3,917 575 3,404 152 4,131 1,735 $5,866 587 4,291 152 5,030 2,102 $7,132 598 4,669 152 5,419 2,270 $7,689 610 5,078 164 5,852 2,452 $8,304 622 5,521 178 6,321 2,648 $8,969 6,000 192 6,827 2,860 $9,687 648 6,519 207 7,374 3,089 $10,463 660 7,079 224 7,963 3,336 $11,299 674 7,685 242 8,601 3,603 $12,204 1,250 $1,250 $ 2 2 3 4 Revenue 5 Revenue Growth 6 Production Costs 7 Fixed Production Expense (exic. Depreciation) 8 Variable Production Costs 9 Depreciation 10 Total Production Costs 11 Selling General, & Administrative 12 Total Operating Expense 13 14 Operating Profit 15 Operating Profit/Sales 16 SG&A/Sales A 17 18 Working Capital Assumptions: 19 Minimum Cash Balance as % of Sales 20 Days Sales Outstanding 21 Inventory Turnover (prod. Cost/ending inv.) 22 Days Payable Outstanding (based on tot. op. exp.) 23 24 Capital Expenditure 25 Growth in Capex 26 ($1,250) $583 0.130 0.257 $994 0.145 0.253 $1,277 0.152 0.250 $1,393 0.153 0.250 $1,504 0.153 0.250 $1,624 0.153 0.250 $1,753 0.153 0.250 $1,892 $ 0.153 0.250 $2,045 0.153 0.250 $2,207 0.153 0.250 3.0% NA NA 3.0% 59.2x 3.0% 59.2x 59.2x NA NA NA NA 3.0% 59.2x 12.2x 33.7x 3.0% 59.2x 12.3x 33.8x 3.0% 59.2x 12.6x 33.9x 3.0% 59.2x 12.7x 33.9x 3.0% 59.2x 12.7x 33.9x 3.0% 59.2x 12.7x 33.9x 12.7x NA NA 12.7x 33.9x 12.7x 33.9x 33.9x $1,470 $530 $952 -35.24% $152 -84.03% $152 $334 0.00% 119.74% $361 8.08% $389 7.76% $421 8.23% $454 7.84% $491 8.15% 7.94% 2010 2012 2013 2014 2015 2016 2017 2018 2019 2020 2011 135 730 359 331 893 893 0 0 0 0 0 -893 0.000 0 0.000 0 0 0.000 0 0.000 0 0 0.000 0198 0 0.000 0 0 0.000 0 0.000 0 0 0.000 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 893 0 O 0 0 0 0 0 0 27 MILESTONE (hint: start by calculating Net Working Capital) 28 29 Step 1: Net Working Capital 30 Cash 31 Accounts Receivable 32 Inventory 33 Accounts Payable 34 Net working Capital 35 ANWC 36 NWC/Sales 37 38 Step 2: NPV and IRR 39 NPV Analysis 40 Free Cash Flows 41 EBIT (1-1) 42 plus depreciation 43 less ANWC 44 less capital expenditures 45 Free Cash Flows 46 Terminal Value 3.00% 47 48 Intial Outlays 49 Net Working Capital 50 Net Property, plant & equipment 51 52 Discount Factor 8.40% 53 Present Value with TV in 2020 54 Present Value without TV in 2020 55 Net Present Value 56 NPV without Terminal Value S7 58 IRR analysis 59 Cash Flows 60 IRR 0 0 0 0 0 1.0000 0 0 $o $o 0 0 0 0 OO : OO 0 0 OO 0 0 Oo OO 0 0 0 0 0 0 0 2020 2010 0 UNUM! 2011 0 2012 0 2013 0 2014 0 2015 0 2016 0 2017 0 2018 0 2019 0 0 H67 X fx Calc Here A 1 J K L M N 0 B D E F G H Selected Operating Projections for Design Your Own Doll ($ in thousands) 1 2010 2012 2017 2011 $4,500 $6,860 52.4% 2013 $8,409 22.6% 2014 $9,082 8.0% 2015 $9,808 8.0% 2016 $10,593 8.0% $11,440 8.0% 2018 $12,355 8.0% 2019 2020 $13,344 $14,411 8.0% 8.0% 635 575 2,035 152 2,762 1,155 $3,917 575 3,404 152 4,131 1,735 $5,866 587 4,291 152 5,030 2,102 $7,132 598 4,669 152 5,419 2,270 $7,689 610 5,078 164 5,852 2,452 $8,304 622 5,521 178 6,321 2,648 $8,969 6,000 192 6,827 2,860 $9,687 648 6,519 207 7,374 3,089 $10,463 660 7,079 224 7,963 3,336 $11,299 674 7,685 242 8,601 3,603 $12,204 1,250 $1,250 $ 2 2 3 4 Revenue 5 Revenue Growth 6 Production Costs 7 Fixed Production Expense (exic. Depreciation) 8 Variable Production Costs 9 Depreciation 10 Total Production Costs 11 Selling General, & Administrative 12 Total Operating Expense 13 14 Operating Profit 15 Operating Profit/Sales 16 SG&A/Sales A 17 18 Working Capital Assumptions: 19 Minimum Cash Balance as % of Sales 20 Days Sales Outstanding 21 Inventory Turnover (prod. Cost/ending inv.) 22 Days Payable Outstanding (based on tot. op. exp.) 23 24 Capital Expenditure 25 Growth in Capex 26 ($1,250) $583 0.130 0.257 $994 0.145 0.253 $1,277 0.152 0.250 $1,393 0.153 0.250 $1,504 0.153 0.250 $1,624 0.153 0.250 $1,753 0.153 0.250 $1,892 $ 0.153 0.250 $2,045 0.153 0.250 $2,207 0.153 0.250 3.0% NA NA 3.0% 59.2x 3.0% 59.2x 59.2x NA NA NA NA 3.0% 59.2x 12.2x 33.7x 3.0% 59.2x 12.3x 33.8x 3.0% 59.2x 12.6x 33.9x 3.0% 59.2x 12.7x 33.9x 3.0% 59.2x 12.7x 33.9x 3.0% 59.2x 12.7x 33.9x 12.7x NA NA 12.7x 33.9x 12.7x 33.9x 33.9x $1,470 $530 $952 -35.24% $152 -84.03% $152 $334 0.00% 119.74% $361 8.08% $389 7.76% $421 8.23% $454 7.84% $491 8.15% 7.94% 2010 2012 2013 2014 2015 2016 2017 2018 2019 2020 2011 135 730 359 331 893 893 0 0 0 0 0 -893 0.000 0 0.000 0 0 0.000 0 0.000 0 0 0.000 0198 0 0.000 0 0 0.000 0 0.000 0 0 0.000 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 893 0 O 0 0 0 0 0 0 27 MILESTONE (hint: start by calculating Net Working Capital) 28 29 Step 1: Net Working Capital 30 Cash 31 Accounts Receivable 32 Inventory 33 Accounts Payable 34 Net working Capital 35 ANWC 36 NWC/Sales 37 38 Step 2: NPV and IRR 39 NPV Analysis 40 Free Cash Flows 41 EBIT (1-1) 42 plus depreciation 43 less ANWC 44 less capital expenditures 45 Free Cash Flows 46 Terminal Value 3.00% 47 48 Intial Outlays 49 Net Working Capital 50 Net Property, plant & equipment 51 52 Discount Factor 8.40% 53 Present Value with TV in 2020 54 Present Value without TV in 2020 55 Net Present Value 56 NPV without Terminal Value S7 58 IRR analysis 59 Cash Flows 60 IRR 0 0 0 0 0 1.0000 0 0 $o $o 0 0 0 0 OO : OO 0 0 OO 0 0 Oo OO 0 0 0 0 0 0 0 2020 2010 0 UNUM! 2011 0 2012 0 2013 0 2014 0 2015 0 2016 0 2017 0 2018 0 2019 0 0