Nhdc Solution.xls 4k383t
This document was ed by and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this report form. Report 3b7i
Overview 3e4r5l
& View Nhdc Solution.xls as PDF for free.
More details w3441
- Words: 1,304
- Pages: 5
New Heritage Doll Company: Capital Budgeting Teaching Note NPV Analysis for Match My Doll Clothing Line Extension TN Exhibit 1 2010 Revenue
2011 4,500
2012
2013
2014
2015
2016
2017
2018
2019
2020
9,082
9,808
10,593
11,440
12,355
13,344
14,411
6,860
8,409
52.4%
22.6%
8.0%
8.0%
8.0%
8.0%
8.0%
8.0%
8.0%
575
575
587
598
610
622
635
648
660
674
2,035
3,404
4,291
4,669
5,078
5,521
6,000
6,519
7,079
7,685
0.4522
0.4962
0.5103
0.5141
0.5178
0.5212
0.5245
0.5276
0.5305
0.5332
152
152
152
152
164
178
192
207
224
242
0
2,762
4,131
5,029
5,419
5,853
6,321
6,827
7,373
7,963
8,600
Selling, General & istrative
1,250
1,155
1,735
2,102
2,270
2,452
2,648
2,860
3,089
3,336
3,603
Total Operating Expenses
1,250
3,917
5,866
7,132
7,690
8,305
8,969
9,687
10,462
11,299
12,203
583
994
1,277
1,392
1,503
1,623
1,753
1,893
2,045
2,209
Operating Profit/Sales
0.130
0.145
0.152
0.153
0.153
0.153
0.153
0.153
0.153
0.153
SG&A/Sales
0.257
0.253
0.250
0.250
0.250
0.250
0.250
0.250
0.250
0.250
Revenue Growth
NA
Production Costs Fixed Production Expense (excl depreciation) Variable Production Costs Depreciation Total Production Costs
Operating Profit
(1,250)
Working Capital Assumptions: Minimum Cash Balance as % of Sales
NA
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
Days Sales Outstanding
NA
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
Inventory Turnover (prod. cost/ending inv.)
NA
7.7x
8.3x
12.7x
12.7x
12.7x
12.7x
12.7x
12.7x
12.7x
12.7x
0.0x
30.8x
30.9x
31.0x
31.0x
31.0x
31.0x
31.0x
31.0x
31.0x
31.0x
952
152
152
334
361
389
421
454
491
530
Days Payable Outstanding (based on tot. op. exp.) Capital Expenditures
1,470
Growth in capex Net Working Capital s Cash s Receivable Inventory s Payable Net Working Capital DNWC
2010
800 800
-35.2% -84.0% 0.2116 0.022187 2011 2012 135 206 729 1112 360 500 317 484 907 1334 107 427
0.0% 119.3% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 0.0181 0.036756 0.036756 0.036752 0.036757 0.036754 0.036759 0.036756 2013 2014 2015 2016 2017 2018 2019 2020 252 272 294 318 343 371 400 432 1363 1472 1590 1717 1855 2003 2163 2336 396 427 461 498 538 581 627 677 593 640 692 747 807 871 941 1016 1418 1531 1653 1786 1929 2083 2250 2429 84 113 122 132 143 154 167 180
NWC/Sales
0.202
0.195
0.169
0.169
0.169
0.169
0.169
0.169
0.169
0.169
2011 350 152 (107) (952) (557)
2012 596 152 (427) (152) 169
2013 766 152 (84) (152) 682
2014 835 152 (113) (334) 541
2015 902 164 (122) (361) 583
2016 974 178 (132) (389) 630
2017 1,052 192 (143) (421) 680
2018 1,136 207 (154) (454) 735
2019 1,227 224 (167) (491) 793
2020 1,325 242 (180) (530) 857
NPV Analysis Free Cash Flows EBIT(1-t) plus depreciation less DNWC less capital expenditures Free Cash Flow Terminal value
2010 (750) 0 (800) (1,470) (3,020) 3.00%
16,345
Initial Outlays Net working capital Net property, plant & equipment Discount factor Present value Net Present Value NPV without Terminal Value
(800) (1470)
8.40% $ $
1.0000 (3,020) 7,150 (146)
0.9225 (514)
0.8510 144
0.7851 536
0.7242 392
0.6681 390
0.6163 388
0.5686 387
0.5245 385
0.4839 384
0.4464 7679
2010 (3,020) 7.6%
2011 (557)
2012 169
2013 682
2014 541
2015 583
2016 630
2017 680
2018 735
2019 793
2020 17,202
2010 (3,020) (3,020)
2011 (557) (3,577)
2012 169 (3,408)
2013 682 (2,726)
2014 541 (2,185)
2015 583 (1,602)
2016 630 (972)
2017 680 (291)
2018 2019 735 793 443 7.40 years
2020 17,202
IRR Analysis Cash Flows IRR
Payback Analysis Cash flows Cumulative cash flow Payback period 5-year Cumulative EBITDA
$ 6,522
Profitability Index NPV/Initial Investment
1.35
New Heritage Doll Company: Capital Budgeting Teaching Note TN Exhibit 2 NPV Analyss for Design Your Own Doll 2010 Revenue
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
0
6,000
14,360
20,222
21,435
22,721
24,084
25,529
27,061
28,685
Revenue Growth
NA
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
2,250
7,651
11,427
12,182
12,983
13,833
14,736
15,694
16,712
Production Costs Fixed Production Expense (excl depreciation)
0
Additional development costs (IT personnel)
435
Variable Production Costs
0
Depreciation
0
310
310
310
436
462
490
520
551
584
0
435
4,210
9,644
13,454
14,369
15,231
16,145
17,113
18,140
19,229
Selling, General & istrative
1,201
0
1,240
2,922
4,044
4,287
4,544
4,817
5,106
5,412
5,737
Total Operating Expenses
1,201
435
5,450
12,566
17,498
18,656
19,775
20,962
22,219
23,553
24,966
(1,201)
(435)
550
1,794
2,724
2,779
2,946
3,123
3,310
3,508
3,719
Operating Profit/Sales
0.092
0.125
0.135
0.130
0.130
0.130
0.130
0.130
0.130
SG&A/Sales
0.207
0.203
0.200
0.200
0.200
0.200
0.200
0.200
0.200
Total Production Costs
Operating Profit
Working Capital Assumptions: Minimum Cash Balance as % of Sales
NA
NA
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
Days Sales Outstanding
NA
NA
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
Inventory Turnover (prod. cost/ending inv.)
NA
NA
12.2x
12.3x
12.6x
12.7x
12.7x
12.7x
12.7x
12.7x
12.7x
Days Payable Outstanding (based on tot. op. exp.)
NA
NA
33.7x
33.8x
33.9x
33.9x
33.9x
33.9x
33.9x
33.9x
33.9x
0
310
310
826
875
928
983
Capital Expenditures
4,610
Growth in capex Net Working Capital s Cash
2010
2,192
1,043
608%
-62%
6%
6%
6%
6%
6%
0.052
0.022
0.108
0.039
0.039
0.039
0.039
0.039
0.039
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
0
180
431
607
643
682
723
766
812
861
2328
3278
3475
3683
3904
4139
4387
4650 1512
s Receivable
973
Inventory
346
786
1065
1130
1197
1269
1345
1426
s Payable
474
1135
1598
1694
1796
1904
2018
2139
Net Working Capital DNWC NWC/Sales
1,105
0%
1,000 1,000
3,352
3,553
3,766
3,992
4,232
4,486
2267
1,024
2,410
4,755
24
1,386
942
202
213
226
240
254
269
0.171
0.168
0.166
0.166
0.166
0.166
0.166
0.166
0.166
NPV Analysis 2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
(721)
(261)
330
1,077
1,634
1,667
1,767
1,874
1,986
2,105
2,231
310
310
310
436
462
490
520
551
584
Free Cash Flows EBIT(1-t) plus depreciation
0
less DNWC
(1,000)
less capital expenditures
(4,610)
Free Cash Flow
(5,331)
Terminal value
3.00%
Discount factor
9.00%
0 (1,261)
(24)
(1,386)
(942)
(202)
(213)
(226)
(240)
(254)
(269)
(310)
(310)
(2,192)
(826)
(875)
(928)
(983)
(1,043)
(1,105)
306
(309)
(1,190)
1,359
1,441
1,076
1,141
1,210
1,283
24,737
Present value
1.000
0.9174
0.8417
(5,331)
(1,157)
2010 (5,331) -0.5%
2010 (5,331) (5,331)
Net Present Value
$
7,058
NPV without Terminal Value
$
(3,391)
0.7722
0.7084
0.6499
0.5963
0.5470
0.5019
0.4604
258
(239)
2011 (1,261)
2012 306
2011 (1,261) (6,592)
2012 306 (6,286)
0.4224
(843)
700
681
662
644
626
11,058
2013 (309)
2014 (1,190)
2015 1,076
2016 1,141
2017 1,210
2018 1,283
2019 1,359
2020 26,178
2013 (309) (6,595)
2014 (1,190) (7,784)
2015 1,076 (6,708)
2016 1,141 (5,566)
2017 1,210 (4,357)
2018 1,283 (3,074)
2019 1,359 (1,715)
IRR Analysis Cash Flows IRR
Payback Analysis Cash flows Cumulative cash flow Payback period 5-year Cumulative EBITDA
$
Profitability Index NPV/Initial Investment
1.32
8,778
2020 26,178 24,464 >10 years
New Heritage Doll Company: Capital Budgeting Teaching Note TN Exhibit 3 NPV Sensitivity Analyses NPV of Match My Doll Clothing Line Extension
Discount rate 7.70% 8.40% 9.00%
$ $ $
0% 5,289 $ 4,408 $ 3,766 $
TV growth rate 1% 2% 6,141 $ 7,292 $ 5,075 $ 5,950 $ 4,314 $ 5,019 $
3% 8,932 7,150 5,958
NPV if TV=BV of NWC + PPE $ 3,011 $ 2,687 $ 2,425
3% 11,957 9,016 7,058
NPV if TV=BV of NWC + PPE $ 3,567 $ 2,980 $ 2,507
NPV of Design Your Own Doll
Discount rate 7.70% 8.40% 9.00%
$ $ $
0% 5,830 $ 4,404 $ 3,372 $
TV growth rate 1% 2% 7,263 $ 9,198 $ 5,526 $ 6,998 $ 4,294 $ 5,479 $
2011 4,500
2012
2013
2014
2015
2016
2017
2018
2019
2020
9,082
9,808
10,593
11,440
12,355
13,344
14,411
6,860
8,409
52.4%
22.6%
8.0%
8.0%
8.0%
8.0%
8.0%
8.0%
8.0%
575
575
587
598
610
622
635
648
660
674
2,035
3,404
4,291
4,669
5,078
5,521
6,000
6,519
7,079
7,685
0.4522
0.4962
0.5103
0.5141
0.5178
0.5212
0.5245
0.5276
0.5305
0.5332
152
152
152
152
164
178
192
207
224
242
0
2,762
4,131
5,029
5,419
5,853
6,321
6,827
7,373
7,963
8,600
Selling, General & istrative
1,250
1,155
1,735
2,102
2,270
2,452
2,648
2,860
3,089
3,336
3,603
Total Operating Expenses
1,250
3,917
5,866
7,132
7,690
8,305
8,969
9,687
10,462
11,299
12,203
583
994
1,277
1,392
1,503
1,623
1,753
1,893
2,045
2,209
Operating Profit/Sales
0.130
0.145
0.152
0.153
0.153
0.153
0.153
0.153
0.153
0.153
SG&A/Sales
0.257
0.253
0.250
0.250
0.250
0.250
0.250
0.250
0.250
0.250
Revenue Growth
NA
Production Costs Fixed Production Expense (excl depreciation) Variable Production Costs Depreciation Total Production Costs
Operating Profit
(1,250)
Working Capital Assumptions: Minimum Cash Balance as % of Sales
NA
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
Days Sales Outstanding
NA
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
Inventory Turnover (prod. cost/ending inv.)
NA
7.7x
8.3x
12.7x
12.7x
12.7x
12.7x
12.7x
12.7x
12.7x
12.7x
0.0x
30.8x
30.9x
31.0x
31.0x
31.0x
31.0x
31.0x
31.0x
31.0x
31.0x
952
152
152
334
361
389
421
454
491
530
Days Payable Outstanding (based on tot. op. exp.) Capital Expenditures
1,470
Growth in capex Net Working Capital s Cash s Receivable Inventory s Payable Net Working Capital DNWC
2010
800 800
-35.2% -84.0% 0.2116 0.022187 2011 2012 135 206 729 1112 360 500 317 484 907 1334 107 427
0.0% 119.3% 8.0% 8.0% 8.0% 8.0% 8.0% 8.0% 0.0181 0.036756 0.036756 0.036752 0.036757 0.036754 0.036759 0.036756 2013 2014 2015 2016 2017 2018 2019 2020 252 272 294 318 343 371 400 432 1363 1472 1590 1717 1855 2003 2163 2336 396 427 461 498 538 581 627 677 593 640 692 747 807 871 941 1016 1418 1531 1653 1786 1929 2083 2250 2429 84 113 122 132 143 154 167 180
NWC/Sales
0.202
0.195
0.169
0.169
0.169
0.169
0.169
0.169
0.169
0.169
2011 350 152 (107) (952) (557)
2012 596 152 (427) (152) 169
2013 766 152 (84) (152) 682
2014 835 152 (113) (334) 541
2015 902 164 (122) (361) 583
2016 974 178 (132) (389) 630
2017 1,052 192 (143) (421) 680
2018 1,136 207 (154) (454) 735
2019 1,227 224 (167) (491) 793
2020 1,325 242 (180) (530) 857
NPV Analysis Free Cash Flows EBIT(1-t) plus depreciation less DNWC less capital expenditures Free Cash Flow Terminal value
2010 (750) 0 (800) (1,470) (3,020) 3.00%
16,345
Initial Outlays Net working capital Net property, plant & equipment Discount factor Present value Net Present Value NPV without Terminal Value
(800) (1470)
8.40% $ $
1.0000 (3,020) 7,150 (146)
0.9225 (514)
0.8510 144
0.7851 536
0.7242 392
0.6681 390
0.6163 388
0.5686 387
0.5245 385
0.4839 384
0.4464 7679
2010 (3,020) 7.6%
2011 (557)
2012 169
2013 682
2014 541
2015 583
2016 630
2017 680
2018 735
2019 793
2020 17,202
2010 (3,020) (3,020)
2011 (557) (3,577)
2012 169 (3,408)
2013 682 (2,726)
2014 541 (2,185)
2015 583 (1,602)
2016 630 (972)
2017 680 (291)
2018 2019 735 793 443 7.40 years
2020 17,202
IRR Analysis Cash Flows IRR
Payback Analysis Cash flows Cumulative cash flow Payback period 5-year Cumulative EBITDA
$ 6,522
Profitability Index NPV/Initial Investment
1.35
New Heritage Doll Company: Capital Budgeting Teaching Note TN Exhibit 2 NPV Analyss for Design Your Own Doll 2010 Revenue
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
0
6,000
14,360
20,222
21,435
22,721
24,084
25,529
27,061
28,685
Revenue Growth
NA
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
2,250
7,651
11,427
12,182
12,983
13,833
14,736
15,694
16,712
Production Costs Fixed Production Expense (excl depreciation)
0
Additional development costs (IT personnel)
435
Variable Production Costs
0
Depreciation
0
310
310
310
436
462
490
520
551
584
0
435
4,210
9,644
13,454
14,369
15,231
16,145
17,113
18,140
19,229
Selling, General & istrative
1,201
0
1,240
2,922
4,044
4,287
4,544
4,817
5,106
5,412
5,737
Total Operating Expenses
1,201
435
5,450
12,566
17,498
18,656
19,775
20,962
22,219
23,553
24,966
(1,201)
(435)
550
1,794
2,724
2,779
2,946
3,123
3,310
3,508
3,719
Operating Profit/Sales
0.092
0.125
0.135
0.130
0.130
0.130
0.130
0.130
0.130
SG&A/Sales
0.207
0.203
0.200
0.200
0.200
0.200
0.200
0.200
0.200
Total Production Costs
Operating Profit
Working Capital Assumptions: Minimum Cash Balance as % of Sales
NA
NA
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
3.0%
Days Sales Outstanding
NA
NA
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
59.2x
Inventory Turnover (prod. cost/ending inv.)
NA
NA
12.2x
12.3x
12.6x
12.7x
12.7x
12.7x
12.7x
12.7x
12.7x
Days Payable Outstanding (based on tot. op. exp.)
NA
NA
33.7x
33.8x
33.9x
33.9x
33.9x
33.9x
33.9x
33.9x
33.9x
0
310
310
826
875
928
983
Capital Expenditures
4,610
Growth in capex Net Working Capital s Cash
2010
2,192
1,043
608%
-62%
6%
6%
6%
6%
6%
0.052
0.022
0.108
0.039
0.039
0.039
0.039
0.039
0.039
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
0
180
431
607
643
682
723
766
812
861
2328
3278
3475
3683
3904
4139
4387
4650 1512
s Receivable
973
Inventory
346
786
1065
1130
1197
1269
1345
1426
s Payable
474
1135
1598
1694
1796
1904
2018
2139
Net Working Capital DNWC NWC/Sales
1,105
0%
1,000 1,000
3,352
3,553
3,766
3,992
4,232
4,486
2267
1,024
2,410
4,755
24
1,386
942
202
213
226
240
254
269
0.171
0.168
0.166
0.166
0.166
0.166
0.166
0.166
0.166
NPV Analysis 2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
(721)
(261)
330
1,077
1,634
1,667
1,767
1,874
1,986
2,105
2,231
310
310
310
436
462
490
520
551
584
Free Cash Flows EBIT(1-t) plus depreciation
0
less DNWC
(1,000)
less capital expenditures
(4,610)
Free Cash Flow
(5,331)
Terminal value
3.00%
Discount factor
9.00%
0 (1,261)
(24)
(1,386)
(942)
(202)
(213)
(226)
(240)
(254)
(269)
(310)
(310)
(2,192)
(826)
(875)
(928)
(983)
(1,043)
(1,105)
306
(309)
(1,190)
1,359
1,441
1,076
1,141
1,210
1,283
24,737
Present value
1.000
0.9174
0.8417
(5,331)
(1,157)
2010 (5,331) -0.5%
2010 (5,331) (5,331)
Net Present Value
$
7,058
NPV without Terminal Value
$
(3,391)
0.7722
0.7084
0.6499
0.5963
0.5470
0.5019
0.4604
258
(239)
2011 (1,261)
2012 306
2011 (1,261) (6,592)
2012 306 (6,286)
0.4224
(843)
700
681
662
644
626
11,058
2013 (309)
2014 (1,190)
2015 1,076
2016 1,141
2017 1,210
2018 1,283
2019 1,359
2020 26,178
2013 (309) (6,595)
2014 (1,190) (7,784)
2015 1,076 (6,708)
2016 1,141 (5,566)
2017 1,210 (4,357)
2018 1,283 (3,074)
2019 1,359 (1,715)
IRR Analysis Cash Flows IRR
Payback Analysis Cash flows Cumulative cash flow Payback period 5-year Cumulative EBITDA
$
Profitability Index NPV/Initial Investment
1.32
8,778
2020 26,178 24,464 >10 years
New Heritage Doll Company: Capital Budgeting Teaching Note TN Exhibit 3 NPV Sensitivity Analyses NPV of Match My Doll Clothing Line Extension
Discount rate 7.70% 8.40% 9.00%
$ $ $
0% 5,289 $ 4,408 $ 3,766 $
TV growth rate 1% 2% 6,141 $ 7,292 $ 5,075 $ 5,950 $ 4,314 $ 5,019 $
3% 8,932 7,150 5,958
NPV if TV=BV of NWC + PPE $ 3,011 $ 2,687 $ 2,425
3% 11,957 9,016 7,058
NPV if TV=BV of NWC + PPE $ 3,567 $ 2,980 $ 2,507
NPV of Design Your Own Doll
Discount rate 7.70% 8.40% 9.00%
$ $ $
0% 5,830 $ 4,404 $ 3,372 $
TV growth rate 1% 2% 7,263 $ 9,198 $ 5,526 $ 6,998 $ 4,294 $ 5,479 $
Related Documents 3m3m1z
Nhdc Solution.xls 4k383t
December 2019 15More Documents from "Shivam Goyal" 6s3m5w
Nhdc Solution.xls 4k383t
December 2019 15
Shiksha - Vedanga 6p3g4l
November 2021 0
Surdas Ke Pad 644w45
January 2022 0
Chemical Formulas All Worksheets 5o649
November 2019 71
Alvars - Vaishnava Poet Saints 4t2nu
December 2021 0