Requesting formulas and answers to Net Working Capital, NPV, IRR, for 2011-2020. Tax Rate is 40%. Can you detail what is missing? This was the

Requesting formulas and answers to Net Working Capital, NPV, IRR, for 2011-2020. Tax Rate is 40%. Can you detail what is missing? This was the only information provided.

image text in transcribed

B L M N C D E G H Selected Operating Projections for Design Your Own Doll ($ in thousands) 1 2010 Nosno 2011 $0 2012 2013 2014 2015 2016 2017 2018 2019 2020 $6,000 $14,360 $20,222 $21,435 $22,721 $24,084 $25,529 $27,061 $28,685 139.3% 40.8% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0% 1,650 1,683 1,717 1,751 1,786 1,822 1,858 1,895 1.933 0 435 0 0 435 0 $435 2,250 7,651 11,427 12,182 12,983 13.833 14,736 15,694 16,712 310 310 310 436 462 490 520 551 584 4,210 9,644 13,454 14,369 15,231 16,145 17,114 18,140 19,229 1,240 2.922 4,044 4,287 4,544 4,817 5,106 5412 5,737 $5,450 $12,566 $17,498 $18,656 $19,775 $20,962 $22,220 $23,552 $24,966 1,201 $1,201 ($1,201) 4 Revenue Revenue Growth 6 Production Costs 7 Foxed Production Expense ledc. Depreciation) 8 Additional development costs (IT) 9 Variable Production Costs 10 Depreciation 11 Total Production Costs 12 Selling, General, & Administrative 13 Total Operating Expense 14 15 Operating Profit 16 Operating Profit/Sales 17 SG&A/Sales 18 19 Working Capital Assumptions: 20 Minimum Cash Balance as % of Sales 21 Days Sales Outstanding 22 Inventory Turnover (prod. Cost/ending inv.] 23 Days Payable Outstanding (based on tot. op. exp.) 24 25 Capital Expenditure 26 Growth in Capex 27 ($435) $550 0.092 0.207 $1,794 0.125 0.203 $2,724 0.135 0.200 $2,779 0.130 0.200 $2,946 0.130 0.200 $3,122 0.130 0.200 $3,309 0.130 0.200 $3,509 0.130 0.200 $3,719 0.130 0.200 3.0% NA NA NA NA NA NA NA NA 3.0% 59.2x 12 2x 33.7x 3.0% 592x 12.3x 33.8% 3.0% 59.2x 12.6% 33.9% 3.0% 59.2x 12.7% 33.9% 3.0% 59.2x 12.7x 33.9x 59.2x 12.7x 33.9% 3.0% 592x 12.7x 33.9x 3.0% 59.2x 12.7x 33.9x 3.0% 59.2x 12.7x 33.9x $4,610 $0 $310 $310 $2,192 0.00% 607.10% $826 -62.32% $875 5.93% $928 6.06% $983 5.93% $1,043 6.10% $1,105 5.94% Home Insert Draw Page Layout Formulas Data Review View = Calibri (Body) 11 A- A = Number 1 | Wi" Conditional Formattin Format as Table Cell Styles Paste BIU A M% $ % > 2.0 .00 100 0 % D35 fx 1000 B C D E F G H 1 K 1 M N 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 1,000 1,000 o - 1,000 0.000 0 0 0.000 0 0 0.000 0 0 0.000 0 0 0.000 0 0 0.000 0 0 0.000 0 0 0.000 0 0 0.000 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 28 MILESTONE 1 (hint: start by calculating Net Working Capital 29 30 Step 1: Net Working Capital 31 Cash 32 Accounts Receivable 33 Inventory 34 Accounts Payable 35 Networking Capital 36 ANWC 37 NWC/Sales 38 39 Step 2: NPV and IRR 40 NPV Analysis 41 Free Cash Flows 42 EBIT (1-1) 43 plus depreciation 44 less ANWC 45 less capital expenditures 46 Free Cash Flows 47 Terminal Value 3.00% 48 Discount Factor 9.00% 49 Present Value with TV in 2020 So Present Value without TV in 2020 51 Net Present Value 52 NPV without Terminal Value 53 54 ARR analysis 55. Cash Flows 56 IRR 57 0 0 0 0 0 0 0 0 0 0 0 o 1.0000 0 DO OO OO 0 0 OO 0 0 0 0 OO 0 0 0 0 0 $0 $0 2012 2013 2016 2019 2010 0 MNUMI 2011 0 2014 0 2015 0 2017 0 2018 0 2020 0 0 0 0 0 B L M N C D E G H Selected Operating Projections for Design Your Own Doll ($ in thousands) 1 2010 Nosno 2011 $0 2012 2013 2014 2015 2016 2017 2018 2019 2020 $6,000 $14,360 $20,222 $21,435 $22,721 $24,084 $25,529 $27,061 $28,685 139.3% 40.8% 6.0% 6.0% 6.0% 6.0% 6.0% 6.0% 1,650 1,683 1,717 1,751 1,786 1,822 1,858 1,895 1.933 0 435 0 0 435 0 $435 2,250 7,651 11,427 12,182 12,983 13.833 14,736 15,694 16,712 310 310 310 436 462 490 520 551 584 4,210 9,644 13,454 14,369 15,231 16,145 17,114 18,140 19,229 1,240 2.922 4,044 4,287 4,544 4,817 5,106 5412 5,737 $5,450 $12,566 $17,498 $18,656 $19,775 $20,962 $22,220 $23,552 $24,966 1,201 $1,201 ($1,201) 4 Revenue Revenue Growth 6 Production Costs 7 Foxed Production Expense ledc. Depreciation) 8 Additional development costs (IT) 9 Variable Production Costs 10 Depreciation 11 Total Production Costs 12 Selling, General, & Administrative 13 Total Operating Expense 14 15 Operating Profit 16 Operating Profit/Sales 17 SG&A/Sales 18 19 Working Capital Assumptions: 20 Minimum Cash Balance as % of Sales 21 Days Sales Outstanding 22 Inventory Turnover (prod. Cost/ending inv.] 23 Days Payable Outstanding (based on tot. op. exp.) 24 25 Capital Expenditure 26 Growth in Capex 27 ($435) $550 0.092 0.207 $1,794 0.125 0.203 $2,724 0.135 0.200 $2,779 0.130 0.200 $2,946 0.130 0.200 $3,122 0.130 0.200 $3,309 0.130 0.200 $3,509 0.130 0.200 $3,719 0.130 0.200 3.0% NA NA NA NA NA NA NA NA 3.0% 59.2x 12 2x 33.7x 3.0% 592x 12.3x 33.8% 3.0% 59.2x 12.6% 33.9% 3.0% 59.2x 12.7% 33.9% 3.0% 59.2x 12.7x 33.9x 59.2x 12.7x 33.9% 3.0% 592x 12.7x 33.9x 3.0% 59.2x 12.7x 33.9x 3.0% 59.2x 12.7x 33.9x $4,610 $0 $310 $310 $2,192 0.00% 607.10% $826 -62.32% $875 5.93% $928 6.06% $983 5.93% $1,043 6.10% $1,105 5.94% Home Insert Draw Page Layout Formulas Data Review View = Calibri (Body) 11 A- A = Number 1 | Wi" Conditional Formattin Format as Table Cell Styles Paste BIU A M% $ % > 2.0 .00 100 0 % D35 fx 1000 B C D E F G H 1 K 1 M N 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 1,000 1,000 o - 1,000 0.000 0 0 0.000 0 0 0.000 0 0 0.000 0 0 0.000 0 0 0.000 0 0 0.000 0 0 0.000 0 0 0.000 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 28 MILESTONE 1 (hint: start by calculating Net Working Capital 29 30 Step 1: Net Working Capital 31 Cash 32 Accounts Receivable 33 Inventory 34 Accounts Payable 35 Networking Capital 36 ANWC 37 NWC/Sales 38 39 Step 2: NPV and IRR 40 NPV Analysis 41 Free Cash Flows 42 EBIT (1-1) 43 plus depreciation 44 less ANWC 45 less capital expenditures 46 Free Cash Flows 47 Terminal Value 3.00% 48 Discount Factor 9.00% 49 Present Value with TV in 2020 So Present Value without TV in 2020 51 Net Present Value 52 NPV without Terminal Value 53 54 ARR analysis 55. Cash Flows 56 IRR 57 0 0 0 0 0 0 0 0 0 0 0 o 1.0000 0 DO OO OO 0 0 OO 0 0 0 0 OO 0 0 0 0 0 $0 $0 2012 2013 2016 2019 2010 0 MNUMI 2011 0 2014 0 2015 0 2017 0 2018 0 2020 0 0 0 0 0