What changes can be made to make the project viable in 3 years.
Use the case answer provided to come up with results of the changes.
Post your excel file with scenarios here.
Remarks
This is a continuation for order no 105604 and it will better if the same person handles this part.
base using sales 4 invent = a Assumptions Required return rate9.5% sale price growth2.0% Raw material cost growth1.0% Raw material price0.940.950.960.970.980.991.001.011.021.03 Overhead growth3.0% Maintenace growth3.0% Labor Cost Growth3.5% SG & A7.8%7.8%7.8%7.8%7.8%7.8%7.8%7.8%7.8%7.8% DSO47.6047.6047.6047.6047.6047.6047.6047.6047.6047.60 DS inventory47.0047.0047.0047.0047.0047.0047.0047.0047.0047.00 Days Payable outstanding34.2034.2034.2034.2034.2034.2034.2034.2034.2034.20 Pro Forma Income Statement 2009201020112012201320142015201620172018Notes Total Capacity (000's)80,00080,00080,00080,00080,00080,00080,00080,00080,00080,000 Capacity Utilization60%65%70%75%80%85%85%85%85%85% Unit Volume48,00052,00056,00060,00064,00068,00068,00068,00068,00068,000 Selling Price Per Unit1.771.811.841.881.921.951.992.032.072.12base is 2% Growth Revenue84,96093,881103,124112,700122,618132,887135,545138,256141,021143,841 Raw Material cost45,12049,36953,69858,10962,60367,18167,85268,53169,21669,908base is .94 Labor Expense18,64020,23322,84225,25527,80830,50831,57632,68133,82535,009 MFG Ovhead3,920 Carol Mannino: Carol Mannino: 3600 in case material 4,0384,1594,2834,4124,5444,6814,8214,9665,115 Mainteance2,2502,3182,3872,4592,5322,6082,6872,7672,8502,936 Selling Gena & admi6,6277,3238,0448,7919,56410,36510,57310,78411,00011,220 EBITDA8,40310,60111,99513,80415,69917,68018,17718,67119,16419,654 Depreciation4,0004,0004,0004,0004,0004,0004,0004,0004,0004,000 EBIT 4,4036,6017,9959,80411,69913,68014,17714,67115,16415,654 Taxes1,7612,6403,1983,9224,6805,4725,6715,8696,0666,262 Net Income2,6423,9614,7975,8827,0198,2088,5068,8039,0989,392 OCF6,6427,9618,7979,88211,01912,20812,50612,80313,09813,392 NWC Calculation Accounts recv11,23412,41313,63514,90116,21317,57117,92218,28018,64619,019 Inventory11,09212,25713,46314,71416,00817,34917,69618,05018,41118,779 Account payable8,0718,9199,79710,70711,64912,62412,87713,13413,39713,665 NWC Calculation14,25415,75117,30218,90920,57322,29622,74123,19623,66024,133 Change in nwc(14,254)(1,497)(1,551)(1,607)(1,664)(1,723)(446)(455)(464)(473) Project cash flows OCF6,6427,9618,7979,88211,01912,20812,50612,80313,09813,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,4647,2468,2769,35510,48512,06012,34812,63418,273 Cum cash(45,000)(52,613)(46,149)(38,903)(30,627)(21,272)(10,786)1,27413,62126,25644,529 NPV$2,0590.89440 IRR10% Payback6.7 MIRR10% Summary NPVIRRPaybackMIRR Base case$4,53111%6.710% sales price flatERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF! Three yearsERROR:#REF!ERROR:#REF!ERROR:#REF!ERROR:#REF! Sales price flat & material raisesERROR:#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 rate9.5% sale price growth2.0% Raw material cost growth1.0% Raw material price0.940.950.960.970.980.991.001.011.021.03 Overhead growth3.0% Maintenace growth3.0% Labor Cost Growth3.5% SG & A7.8%7.8%7.8%7.8%7.8%7.8%7.8%7.8%7.8%7.8% DSO47.6047.6047.6047.6047.6047.6047.6047.6047.6047.60 DS inventory47.0047.0047.0047.0047.0047.0047.0047.0047.0047.00 Days Payable outstanding34.2034.2034.2034.2034.2034.2034.2034.2034.2034.20 Pro Forma Income Statement 2009201020112012201320142015201620172018Notes Total Capacity (000's)80,00080,00080,00080,00080,00080,00080,00080,00080,00080,000 Capacity Utilization60%65%70%75%80%85%85%85%85%85% Unit Volume48,00052,00056,00060,00064,00068,00068,00068,00068,00068,000 Selling Price Per Unit1.771.811.841.881.921.951.992.032.072.12base is 2% Growth Revenue84,960.0093,881103,124112,700122,618132,887135,545138,256141,021143,841 Raw Material cost45,12049,36953,69858,10962,60367,18167,85268,53169,21669,908base is .94 Labor Expense18,64020,23322,84225,25527,80830,50831,57632,68133,82535,009 MFG Ovhead3,920 Carol Mannino: Carol Mannino: 3600 in case material but spreadsheet has this amount.4,0384,1594,2834,4124,5444,6814,8214,9665,115 Maintenance2,2502,3182,3872,4592,5322,6082,6872,7672,8502,936 Selling Gena & admi6,627 Carol Mannino: Carol Mannino: rounded to 7.8%7,3238,0448,7919,56410,36510,57310,78411,00011,220 EBITDA8,40310,60111,99513,80415,69917,68018,17718,67119,16419,654 Depreciation4,0004,0004,0004,0004,0004,0004,0004,0004,0004,000 EBIT 4,4036,6017,9959,80411,69913,68014,17714,67115,16415,654 Taxes1,7612,6403,1983,9224,6805,4725,6715,8696,0666,262 Net Income2,6423,9614,7975,8827,0198,2088,5068,8039,0989,392 OCF6,6427,9618,7979,88211,01912,20812,50612,80313,09813,392 NWC Calculation Accounts recv11,23412,41313,63514,90116,21317,57117,92218,28018,64619,019 Inventoryall costs9,1309,91710,84711,76412,71013,68813,94314,20514,47314,749 Account payable7,2737,9128,6579,39510,15710,94511,15011,36111,57611,798 NWC Calculation13,09014,41815,82517,27018,76620,31420,71521,12421,54321,970 Change in nwc(13,090)(1,328)(1,407)(1,445)(1,496)(1,548)(401)(410)(418)(427) Project cash flows OCF6,6427,9618,7979,88211,01912,20812,50612,80313,09813,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,6337,3908,4389,52410,66012,10512,39312,68020,186 Cum cash(45,000)(51,449)(44,816)(37,426)(28,989)(19,465)(8,805)3,30015,69328,37348,560 NPV$4,5310.72737 IRR11% Payback6.7 MIRR10% Base case nwc with cogs for ap Assumptions Required return rate9.5% sale price growth2.0% Raw material cost growth1.0% Raw material price0.940.950.960.970.980.991.001.011.021.03 Overhead growth3.0% Maintenace growth3.0% Labor Cost Growth3.5% SG & A7.8%7.8%7.8%7.8%7.8%7.8%7.8%7.8%7.8%7.8% DSO47.6047.6047.6047.6047.6047.6047.6047.6047.6047.60 DS inventory47.0047.0047.0047.0047.0047.0047.0047.0047.0047.00 Days Payable outstanding34.2034.2034.2034.2034.2034.2034.2034.2034.2034.20 Pro Forma Income Statement 2009201020112012201320142015201620172018Notes Total Capacity (000's)80,00080,00080,00080,00080,00080,00080,00080,00080,00080,000 Capacity Utilization60%65%70%75%80%85%85%85%85%85% Unit Volume48,00052,00056,00060,00064,00068,00068,00068,00068,00068,000 Selling Price Per Unit1.771.811.841.881.921.951.992.032.072.12base is 2% Growth Revenue84,96093,881103,124112,700122,618132,887135,545138,256141,021143,841 Raw Material cost45,12049,36953,69858,10962,60367,18167,85268,53169,21669,908base is .94 Labor Expense18,64020,23322,84225,25527,80830,50831,57632,68133,82535,009 MFG Ovhead3,920 Carol Mannino: Carol Mannino: 3600 in case material 4,0384,1594,2834,4124,5444,6814,8214,9665,115 Mainteance2,2502,3182,3872,4592,5322,6082,6872,7672,8502,936 Selling Gena & admi6,6277,3238,0448,7919,56410,36510,57310,78411,00011,220 EBITDA8,40310,60111,99513,80415,69917,68018,17718,67119,16419,654 Depreciation4,0004,0004,0004,0004,0004,0004,0004,0004,0004,000 EBIT 4,4036,6017,9959,80411,69913,68014,17714,67115,16415,654 Taxes1,7612,6403,1983,9224,6805,4725,6715,8696,0666,262 Net Income2,6423,9614,7975,8827,0198,2088,5068,8039,0989,392 OCF6,6427,9618,7979,88211,01912,20812,50612,80313,09813,392 NWC Calculation Accounts recv11,23412,41313,63514,90116,21317,57117,92218,28018,64619,019 Inventory9,1309,91710,84711,76412,71013,68813,94314,20514,47314,749 Account payablejust cogs6,6437,9128,6579,39510,15710,94511,15011,36111,57611,798 NWC Calculation13,72014,41815,82517,27018,76620,31420,71521,12421,54321,970 Change in nwc(13,720)(698)(1,407)(1,445)(1,496)(1,548)(401)(410)(418)(427) Project cash flows OCF6,6427,9618,7979,88211,01912,20812,50612,80313,09813,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,2627,3908,4389,52410,66012,10512,39312,68020,186 Carol Mannino: Carol Mannino: case states just a/r 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,30015,69328,37348,560 NPV$4,4810.72737 IRR11.04% Payback6.7 MIRR10.42% base ap with cogs and invetory Assumptions Required return rate9.5% sale price growth2.0% Raw material cost growth1.0% Raw material price0.940.950.960.970.980.991.001.011.021.03 Overhead growth3.0% Maintenace growth3.0% Labor Cost Growth3.5% SG & A7.8%7.8%7.8%7.8%7.8%7.8%7.8%7.8%7.8%7.8% DSO47.6047.6047.6047.6047.6047.6047.6047.6047.6047.60 DS inventory47.0047.0047.0047.0047.0047.0047.0047.0047.0047.00 Days Payable outstanding34.2034.2034.2034.2034.2034.2034.2034.2034.2034.20 Pro Forma Income Statement 2009201020112012201320142015201620172018Notes Total Capacity (000's)80,00080,00080,00080,00080,00080,00080,00080,00080,00080,000 Capacity Utilization60%65%70%75%80%85%85%85%85%85% Unit Volume48,00052,00056,00060,00064,00068,00068,00068,00068,00068,000 Selling Price Per Unit1.771.811.841.881.921.951.992.032.072.12base is 2% Growth Revenue84,96093,881103,124112,700122,618132,887135,545138,256141,021143,841 Raw Material cost45,12049,36953,69858,10962,60367,18167,85268,53169,21669,908base is .94 Labor Expense18,64020,23322,84225,25527,80830,50831,57632,68133,82535,009 MFG Ovhead3,920 Carol Mannino: Carol Mannino: 3600 in case material 4,0384,1594,2834,4124,5444,6814,8214,9665,115 Mainteance2,2502,3182,3872,4592,5322,6082,6872,7672,8502,936 Selling Gena & admi6,6277,3238,0448,7919,56410,36510,57310,78411,00011,220 EBITDA8,40310,60111,99513,80415,69917,68018,17718,67119,16419,654 Depreciation4,0004,0004,0004,0004,0004,0004,0004,0004,0004,000 EBIT 4,4036,6017,9959,80411,69913,68014,17714,67115,16415,654 Taxes1,7612,6403,1983,9224,6805,4725,6715,8696,0666,262 Net Income2,6423,9614,7975,8827,0198,2088,5068,8039,0989,392 OCF6,6427,9618,7979,88211,01912,20812,50612,80313,09813,392 NWC Calculation Accounts recv11,23412,41313,63514,90116,21317,57117,92218,28018,64619,019 Inventory9,1309,91710,84711,76412,71013,68813,94314,20514,47314,749 Account payable6,6437,9128,6579,39510,15710,94511,15011,36111,57611,798 NWC Calculation13,72014,41815,82517,27018,76620,31420,71521,12421,54321,970 Change in nwc(13,720)(698)(1,407)(1,445)(1,496)(1,548)(401)(410)(418)(427) Project cash flows OCF6,6427,9618,7979,88211,01912,20812,50612,80313,09813,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,2627,3908,4389,52410,66012,10512,39312,68034,935 Cum cash(45,000)(52,078)(44,816)(37,426)(28,989)(19,465)(8,805)3,30015,69328,37363,308 NPV$10,4320.72737 IRR12.76% Payback6.7 MIRR11.54% graphs Cathleen SinclairGeneral Health & BeautyWomens Care CompanySkin Care EnterprisesHPL 2007HPL 5 year avg Revenue1346.8446.1397.31247.6680.7590 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/E444.9%19.8%11.1%30.9%14.3%20.7% RevenueCathleen SinclairGeneral Health & BeautyWomens Care CompanySkin Care EnterprisesHPL 2007HPL 5 year avg1346.8446.1397.31247.5999999999999680.7590EBITDA %Cathleen SinclairGeneral Health & BeautyWomens Care CompanySkin Care EnterprisesHPL 2007HPL 5 year avg0.190.139000000000000010.133000000000000010.1250.1080.108EBIT %Cathleen SinclairGeneral Health & BeautyWomens Care CompanySkin Care EnterprisesHPL 2007HPL 5 year avg0.1210.116000000000000010.1240.101000000000000019.9000000000000005E-29.7000000000000003E-2Earnings %Cathleen SinclairGeneral Health & BeautyWomens Care CompanySkin Care EnterprisesHPL 2007HPL 5 year avg1.6E-25.2999999999999999E-20.194000000000000015.1999999999999998E-25.7000000000000002E-25.2999999999999999E-2D/ECathleen SinclairGeneral Health & BeautyWomens Care CompanySkin Care EnterprisesHPL 2007HPL 5 year avg4.44899999999999980.198000000000000010.1110.3090.142999999999999990.20699999999999999