base using sales 4 invent = a
Assumptions
Required return rate 9.5%
sale price growth 2.0%
Raw material cost growth 1.0%
Raw material price 0.94 0.95 0.96 0.97 0.98 0.99 1.00 1.01 1.02 1.03
Overhead growth 3.0%
Maintenace growth 3.0%
Labor Cost Growth 3.5%
SG & A 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8%
DSO 47.60 47.60 47.60 47.60 47.60 47.60 47.60 47.60 47.60 47.60
DS inventory 47.00 47.00 47.00 47.00 47.00 47.00 47.00 47.00 47.00 47.00
Days Payable outstanding 34.20 34.20 34.20 34.20 34.20 34.20 34.20 34.20 34.20 34.20
Pro Forma Income Statement
2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 Notes
Total Capacity (000's) 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000
Capacity Utilization 60% 65% 70% 75% 80% 85% 85% 85% 85% 85%
Unit Volume 48,000 52,000 56,000 60,000 64,000 68,000 68,000 68,000 68,000 68,000
Selling Price Per Unit 1.77 1.81 1.84 1.88 1.92 1.95 1.99 2.03 2.07 2.12 base is 2% Growth
Revenue 84,960 93,881 103,124 112,700 122,618 132,887 135,545 138,256 141,021 143,841
Raw Material cost 45,120 49,369 53,698 58,109 62,603 67,181 67,852 68,531 69,216 69,908 base is .94
Labor Expense 18,640 20,233 22,842 25,255 27,808 30,508 31,576 32,681 33,825 35,009
MFG Ovhead 3,920
Carol Mannino: Carol Mannino:
3600 in case material
4,038 4,159 4,283 4,412 4,544 4,681 4,821 4,966 5,115
Mainteance 2,250 2,318 2,387 2,459 2,532 2,608 2,687 2,767 2,850 2,936
Selling Gena & admi 6,627 7,323 8,044 8,791 9,564 10,365 10,573 10,784 11,000 11,220
EBITDA 8,403 10,601 11,995 13,804 15,699 17,680 18,177 18,671 19,164 19,654
Depreciation 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000
EBIT 4,403 6,601 7,995 9,804 11,699 13,680 14,177 14,671 15,164 15,654
Taxes 1,761 2,640 3,198 3,922 4,680 5,472 5,671 5,869 6,066 6,262
Net Income 2,642 3,961 4,797 5,882 7,019 8,208 8,506 8,803 9,098 9,392
OCF 6,642 7,961 8,797 9,882 11,019 12,208 12,506 12,803 13,098 13,392
NWC Calculation
Accounts recv 11,234 12,413 13,635 14,901 16,213 17,571 17,922 18,280 18,646 19,019
Inventory 11,092 12,257 13,463 14,714 16,008 17,349 17,696 18,050 18,411 18,779
Account payable 8,071 8,919 9,797 10,707 11,649 12,624 12,877 13,134 13,397 13,665
NWC Calculation 14,254 15,751 17,302 18,909 20,573 22,296 22,741 23,196 23,660 24,133
Change in nwc (14,254) (1,497) (1,551) (1,607) (1,664) (1,723) (446) (455) (464) (473)
Project cash flows
OCF 6,642 7,961 8,797 9,882 11,019 12,208 12,506 12,803 13,098 13,392
NWC change (14,254) (1,497) (1,551) (1,607) (1,664) (1,723) (446) (455) (464) 4,881
Spending (45,000)
Total project cash flow (45,000) (7,613) 6,464 7,246 8,276 9,355 10,485 12,060 12,348 12,634 18,273
Cum cash (45,000) (52,613) (46,149) (38,903) (30,627) (21,272) (10,786) 1,274 13,621 26,256 44,529
NPV $2,059 0.89440
IRR 10%
Payback 6.7
MIRR 10%
Summary
NPV IRR Payback MIRR
Base case $4,531 11% 6.7 10%
sales price flat ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
Three years ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
Sales price flat & material raises ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
Raise material % ERROR:#REF! ERROR:#REF! ERROR:#REF! ERROR:#REF!
IRR not a factor beyond base case
Payback nasty always
very sensitive to sales price need to work that some more
Base case
Assumptions
Required return rate 9.5%
sale price growth 2.0%
Raw material cost growth 1.0%
Raw material price 0.94 0.95 0.96 0.97 0.98 0.99 1.00 1.01 1.02 1.03
Overhead growth 3.0%
Maintenace growth 3.0%
Labor Cost Growth 3.5%
SG & A 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8%
DSO 47.60 47.60 47.60 47.60 47.60 47.60 47.60 47.60 47.60 47.60
DS inventory 47.00 47.00 47.00 47.00 47.00 47.00 47.00 47.00 47.00 47.00
Days Payable outstanding 34.20 34.20 34.20 34.20 34.20 34.20 34.20 34.20 34.20 34.20
Pro Forma Income Statement
2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 Notes
Total Capacity (000's) 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000
Capacity Utilization 60% 65% 70% 75% 80% 85% 85% 85% 85% 85%
Unit Volume 48,000 52,000 56,000 60,000 64,000 68,000 68,000 68,000 68,000 68,000
Selling Price Per Unit 1.77 1.81 1.84 1.88 1.92 1.95 1.99 2.03 2.07 2.12 base is 2% Growth
Revenue 84,960.00 93,881 103,124 112,700 122,618 132,887 135,545 138,256 141,021 143,841
Raw Material cost 45,120 49,369 53,698 58,109 62,603 67,181 67,852 68,531 69,216 69,908 base is .94
Labor Expense 18,640 20,233 22,842 25,255 27,808 30,508 31,576 32,681 33,825 35,009
MFG Ovhead 3,920
Carol Mannino: Carol Mannino:
3600 in case material
ut spreadsheet has this amount. 4,038 4,159 4,283 4,412 4,544 4,681 4,821 4,966 5,115
Maintenance 2,250 2,318 2,387 2,459 2,532 2,608 2,687 2,767 2,850 2,936
Selling Gena & admi 6,627
Carol Mannino: Carol Mannino:
ounded to 7.8% 7,323 8,044 8,791 9,564 10,365 10,573 10,784 11,000 11,220
EBITDA 8,403 10,601 11,995 13,804 15,699 17,680 18,177 18,671 19,164 19,654
Depreciation 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000
EBIT 4,403 6,601 7,995 9,804 11,699 13,680 14,177 14,671 15,164 15,654
Taxes 1,761 2,640 3,198 3,922 4,680 5,472 5,671 5,869 6,066 6,262
Net Income 2,642 3,961 4,797 5,882 7,019 8,208 8,506 8,803 9,098 9,392
OCF 6,642 7,961 8,797 9,882 11,019 12,208 12,506 12,803 13,098 13,392
NWC Calculation
Accounts recv 11,234 12,413 13,635 14,901 16,213 17,571 17,922 18,280 18,646 19,019
Inventory all costs 9,130 9,917 10,847 11,764 12,710 13,688 13,943 14,205 14,473 14,749
Account payable 7,273 7,912 8,657 9,395 10,157 10,945 11,150 11,361 11,576 11,798
NWC Calculation 13,090 14,418 15,825 17,270 18,766 20,314 20,715 21,124 21,543 21,970
Change in nwc (13,090) (1,328) (1,407) (1,445) (1,496) (1,548) (401) (410) (418) (427)
Project cash flows
OCF 6,642 7,961 8,797 9,882 11,019 12,208 12,506 12,803 13,098 13,392
NWC change (13,090) (1,328) (1,407) (1,445) (1,496) (1,548) (401) (410) (418) 6,794
Spending (45,000)
Total project cash flow (45,000) (6,449) 6,633 7,390 8,438 9,524 10,660 12,105 12,393 12,680 20,186
Cum cash (45,000) (51,449) (44,816) (37,426) (28,989) (19,465) (8,805) 3,300 15,693 28,373 48,560
NPV $4,531 0.72737
IRR 11%
Payback 6.7
MIRR 10%
Base case nwc with cogs for ap
Assumptions
Required return rate 9.5%
sale price growth 2.0%
Raw material cost growth 1.0%
Raw material price 0.94 0.95 0.96 0.97 0.98 0.99 1.00 1.01 1.02 1.03
Overhead growth 3.0%
Maintenace growth 3.0%
Labor Cost Growth 3.5%
SG & A 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8%
DSO 47.60 47.60 47.60 47.60 47.60 47.60 47.60 47.60 47.60 47.60
DS inventory 47.00 47.00 47.00 47.00 47.00 47.00 47.00 47.00 47.00 47.00
Days Payable outstanding 34.20 34.20 34.20 34.20 34.20 34.20 34.20 34.20 34.20 34.20
Pro Forma Income Statement
2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 Notes
Total Capacity (000's) 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000
Capacity Utilization 60% 65% 70% 75% 80% 85% 85% 85% 85% 85%
Unit Volume 48,000 52,000 56,000 60,000 64,000 68,000 68,000 68,000 68,000 68,000
Selling Price Per Unit 1.77 1.81 1.84 1.88 1.92 1.95 1.99 2.03 2.07 2.12 base is 2% Growth
Revenue 84,960 93,881 103,124 112,700 122,618 132,887 135,545 138,256 141,021 143,841
Raw Material cost 45,120 49,369 53,698 58,109 62,603 67,181 67,852 68,531 69,216 69,908 base is .94
Labor Expense 18,640 20,233 22,842 25,255 27,808 30,508 31,576 32,681 33,825 35,009
MFG Ovhead 3,920
Carol Mannino: Carol Mannino:
3600 in case material
4,038 4,159 4,283 4,412 4,544 4,681 4,821 4,966 5,115
Mainteance 2,250 2,318 2,387 2,459 2,532 2,608 2,687 2,767 2,850 2,936
Selling Gena & admi 6,627 7,323 8,044 8,791 9,564 10,365 10,573 10,784 11,000 11,220
EBITDA 8,403 10,601 11,995 13,804 15,699 17,680 18,177 18,671 19,164 19,654
Depreciation 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000
EBIT 4,403 6,601 7,995 9,804 11,699 13,680 14,177 14,671 15,164 15,654
Taxes 1,761 2,640 3,198 3,922 4,680 5,472 5,671 5,869 6,066 6,262
Net Income 2,642 3,961 4,797 5,882 7,019 8,208 8,506 8,803 9,098 9,392
OCF 6,642 7,961 8,797 9,882 11,019 12,208 12,506 12,803 13,098 13,392
NWC Calculation
Accounts recv 11,234 12,413 13,635 14,901 16,213 17,571 17,922 18,280 18,646 19,019
Inventory 9,130 9,917 10,847 11,764 12,710 13,688 13,943 14,205 14,473 14,749
Account payable just cogs 6,643 7,912 8,657 9,395 10,157 10,945 11,150 11,361 11,576 11,798
NWC Calculation 13,720 14,418 15,825 17,270 18,766 20,314 20,715 21,124 21,543 21,970
Change in nwc (13,720) (698) (1,407) (1,445) (1,496) (1,548) (401) (410) (418) (427)
Project cash flows
OCF 6,642 7,961 8,797 9,882 11,019 12,208 12,506 12,803 13,098 13,392
NWC change (13,720) (698) (1,407) (1,445) (1,496) (1,548) (401) (410) (418) 6,794
Spending (45,000)
Total project cash flow (45,000) (7,078) 7,262 7,390 8,438 9,524 10,660 12,105 12,393 12,680 20,186
Carol Mannino: Carol Mannino:
case states just a
and a/p are returned
Carol Mannino: Carol Mannino:
3600 in case material
Cum cash (45,000) (52,078) (44,816) (37,426) (28,989) (19,465) (8,805) 3,300 15,693 28,373 48,560
NPV $4,481 0.72737
IRR 11.04%
Payback 6.7
MIRR 10.42%
ase ap with cogs and invetory
Assumptions
Required return rate 9.5%
sale price growth 2.0%
Raw material cost growth 1.0%
Raw material price 0.94 0.95 0.96 0.97 0.98 0.99 1.00 1.01 1.02 1.03
Overhead growth 3.0%
Maintenace growth 3.0%
Labor Cost Growth 3.5%
SG & A 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8% 7.8%
DSO 47.60 47.60 47.60 47.60 47.60 47.60 47.60 47.60 47.60 47.60
DS inventory 47.00 47.00 47.00 47.00 47.00 47.00 47.00 47.00 47.00 47.00
Days Payable outstanding 34.20 34.20 34.20 34.20 34.20 34.20 34.20 34.20 34.20 34.20
Pro Forma Income Statement
2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 Notes
Total Capacity (000's) 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000 80,000
Capacity Utilization 60% 65% 70% 75% 80% 85% 85% 85% 85% 85%
Unit Volume 48,000 52,000 56,000 60,000 64,000 68,000 68,000 68,000 68,000 68,000
Selling Price Per Unit 1.77 1.81 1.84 1.88 1.92 1.95 1.99 2.03 2.07 2.12 base is 2% Growth
Revenue 84,960 93,881 103,124 112,700 122,618 132,887 135,545 138,256 141,021 143,841
Raw Material cost 45,120 49,369 53,698 58,109 62,603 67,181 67,852 68,531 69,216 69,908 base is .94
Labor Expense 18,640 20,233 22,842 25,255 27,808 30,508 31,576 32,681 33,825 35,009
MFG Ovhead 3,920
Carol Mannino: Carol Mannino:
3600 in case material
4,038 4,159 4,283 4,412 4,544 4,681 4,821 4,966 5,115
Mainteance 2,250 2,318 2,387 2,459 2,532 2,608 2,687 2,767 2,850 2,936
Selling Gena & admi 6,627 7,323 8,044 8,791 9,564 10,365 10,573 10,784 11,000 11,220
EBITDA 8,403 10,601 11,995 13,804 15,699 17,680 18,177 18,671 19,164 19,654
Depreciation 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000 4,000
EBIT 4,403 6,601 7,995 9,804 11,699 13,680 14,177 14,671 15,164 15,654
Taxes 1,761 2,640 3,198 3,922 4,680 5,472 5,671 5,869 6,066 6,262
Net Income 2,642 3,961 4,797 5,882 7,019 8,208 8,506 8,803 9,098 9,392
OCF 6,642 7,961 8,797 9,882 11,019 12,208 12,506 12,803 13,098 13,392
NWC Calculation
Accounts recv 11,234 12,413 13,635 14,901 16,213 17,571 17,922 18,280 18,646 19,019
Inventory 9,130 9,917 10,847 11,764 12,710 13,688 13,943 14,205 14,473 14,749
Account payable 6,643 7,912 8,657 9,395 10,157 10,945 11,150 11,361 11,576 11,798
NWC Calculation 13,720 14,418 15,825 17,270 18,766 20,314 20,715 21,124 21,543 21,970
Change in nwc (13,720) (698) (1,407) (1,445) (1,496) (1,548) (401) (410) (418) (427)
Project cash flows
OCF 6,642 7,961 8,797 9,882 11,019 12,208 12,506 12,803 13,098 13,392
NWC change (13,720) (698) (1,407) (1,445) (1,496) (1,548) (401) (410) (418) 21,543
Carol Mannino: Carol Mannino:
with return of inventory
Carol Mannino: Carol Mannino:
3600 in case material
Spending (45,000)
Total project cash flow (45,000) (7,078) 7,262 7,390 8,438 9,524 10,660 12,105 12,393 12,680 34,935
Cum cash (45,000) (52,078) (44,816) (37,426) (28,989) (19,465) (8,805) 3,300 15,693 28,373 63,308
NPV $10,432 0.72737
IRR 12.76%
Payback 6.7
MIRR 11.54%
graphs
Cathleen Sinclair General Health & Beauty Womens Care Company Skin Care Enterprises HPL 2007 HPL 5 year avg
Revenue 1346.8 446.1 397.3 1247.6 680.7 590
EBITDA % 19.0% 13.9% 13.3% 12.5% 10.8% 10.8%
EBIT % 12.1% 11.6% 12.4% 10.1% 9.9% 9.7%
Earnings % 1.6% 5.3% 19.4% 5.2% 5.7% 5.3%
D/E 444.9% 19.8% 11.1% 30.9% 14.3% 20.7%
Revenue Cathleen Sinclair General Health & Beauty Womens Care Company Skin Care Enterprises HPL 2007 HPL 5 year avg 1346.8 446.1 397.3 XXXXXXXXXX 680.7 590 EBITDA % Cathleen Sinclair General Health & Beauty Womens Care Company Skin Care Enterprises HPL 2007 HPL 5 year avg 0.19 XXXXXXXXXX XXXXXXXXXX 0.125 0.108 0.108 EBIT % Cathleen Sinclair General Health & Beauty Womens Care Company Skin Care Enterprises HPL 2007 HPL 5 year avg 0.121 XXXXXXXXXX 0.124 XXXXXXXXXX 9.9000000000000005E-2 9.7000000000000003E-2 Earnings % Cathleen Sinclair General Health & Beauty Womens Care Company Skin Care Enterprises HPL 2007 HPL 5 year avg 1.6E-2 5.2999999999999999E-2 XXXXXXXXXX 5.1999999999999998E-2 5.7000000000000002E-2 5.2999999999999999E-2 D/E Cathleen Sinclair General Health & Beauty Womens Care Company Skin Care Enterprises HPL 2007 HPL 5 year avg XXXXXXXXXX XXXXXXXXXX 0.111 0.309 XXXXXXXXXX XXXXXXXXXX