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...

1 answer below ยป

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
Answered Same DayMay 31, 2022

Answer To: What changes can be made to make the project viable in 3 years. Use the case answer provided to come...

Rochak answered on Jun 01 2022
91 Votes
Base case
    Assumptions
    Required return rate        9.5%
    sale price growth        21.1%
    Raw material cost growth        1.0%
    Raw material price        0.79    0.80    0.80    0.81    0.82    0.83    0.84    0.84    0.85    0.86
    Overhead growth        0.7%
    Maintenace growth        1.7%
    Labor Cost Growth        3.5%
    SG & A        4.1%    4.1%    4.1%    4.1%    4.1%    4.1%    4.1%    4.1%    4.1%    4.1%
    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    2.14    2.59    3.14    3.80    4.60    5.57    6.75    8.17    9.89    base is 2% Growth
    Revenue        84,960.00    111,425    145,269    188,426    243,318    312,974    378,890    458,689    555,295    672,246
    Raw Material cost        37,790    41,348    44,974    48,668    52,432    56,266    56,829    57,397    57,971    58,551    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
but spreadsheet has this amount.    3,947    3,975    4,003    4,031    4,059    4,087    4,116    4,144    4,173
    Maintenance        2,250    2,288    2,326    2,365    2,405    2,445    2,486    2,528    2,570    2,613
    Selling Gena & admi        3,495
Carol Mannino: Carol Mannino:
rounded to 7.8%    4,584    5,977    7,752    10,010    12,876    15,588    18,871    22,846    27,657
    EBITDA        18,865    39,024    65,175    100,383    146,632    206,819    268,324    343,096    433,938    544,243
    Depreciation        4,000    4,000    4,000    4,000    4,000    4,000    4,000    4,000    4,000    4,000
    EBIT         14,865    35,024    61,175    96,383    142,632    202,819    264,324    339,096    429,938    540,243
    Taxes        5,946    14,010    24,470    38,553    57,053    81,128    105,730    135,638    171,975    216,097
    Net Income        8,919    21,014    36,705    57,830    85,579    121,692    158,594    203,458    257,963    324,146
    OCF        12,919    25,014    40,705    61,830    89,579    125,692    162,594    207,458    261,963    328,146
    NWC Calculation
    Accounts recv        11,234    14,733    19,208    24,914    32,172    41,382    50,098    60,649    73,422    88,886
    Inventory    all costs    8,173    8,854    9,676    10,482    11,316    12,178    12,400    12,628    12,861    13,101
    Account payable        6,279    6,878    7,609    8,364    9,185    10,085    10,504    10,981    11,529    12,160
    NWC Calculation        13,127    16,709    21,275    27,032    34,303    43,475    51,994    62,295    74,755    89,826
    Change in nwc        (13,127)    (3,581)    (4,567)    (5,757)    (7,270)    (9,173)    (8,518)    (10,301)    (12,459)    (15,072)
    Project cash flows
    OCF        12,919    25,014    40,705    61,830    89,579    125,692    162,594    207,458    261,963    328,146
    NWC change        (13,127)    (3,581)    (4,567)    (5,757)    (7,270)    (9,173)    (8,518)    (10,301)    (12,459)    61,654
    Spending    (45,000)
    Total project cash flow    (45,000)    (208)    21,433    36,138    56,073    82,309    116,519    154,076    197,156    249,503    389,800
    Cum cash    (45,000)    (45,208)    (23,775)    12,363    68,436    150,745    267,264    421,339    618,496    867,999    1,257,799
    NPV    $210                            1.73462
    IRR    10%
    Payback    6.7
    MIRR    10%
Changes Made:
1. Sales Price Growth: Sales price growth is increased from 2% to 21.1% and this will lead to increase in sales
2. Raw Material Price: The raw material price is decreased from 0.94 to 0.79 which will decreases the overall expense cost
4. Overhead Growth: The overhead growth is reduced to 0.7%, this will lead to decrease in the total expense and therefore the company will generate more profits
5. Maintenance Growth: Maintenance Growth is reduced to 1.7% from 3% as this will again save the profits and the company will generate more cash
6. SG&A: Selling, General and Administrative (SG&A) is decreased from 7.8% to 4.1% becasue this impacts the profitability of a company big time
Overall if we see the major thihg which will make the project viable in three years will be the increase in Sales Price Growth and decrease in Raw Material Price
Summary
        NPV    IRR    Payback    MIRR
    Base case    $4,531    11%    6.7    10%
    sales price flat    $26,819    -5%    0    2%
    Three years    $210    10%    6.7    10%
    Sales price flat & material raises    $210    10%    2.66    10%
    Raise material %    $210    11%    2.62    11%
    IRR not a factor beyond base case
    Payback nasty always
    very sensitive to sales price need to work that some more
base using sales 4 invent = a
    Assumptions
    Required return rate        9.50%
    sale price growth        24.69%
    Raw material cost growth        0.50%
    Raw material price        0.77    0.77    0.78    0.78    0.79    0.79    0.79    0.80    0.80    0.81
    Overhead growth        1.77%
    Maintenace growth        2.29%
    Labor Cost Growth        3.50%
    SG & A        6.00%    6.00%    6.00%    6.00%    6.00%    6.00%    6.00%    6.00%    6.00%    6.00%
    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...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions ยป

Submit New Assignment

Copy and Paste Your Assignment Here