Please write a report on which project should Harris recommend or neither.In your report,you need to include quantitativeanalysis, including NPV, IRR, Payback and sensitivity analysis.You also need to include qualitative analysis including risk factors, long term impact of the project on the overall business, growth potentials, etc.
In Module7Lecture.xlsx, I have finished cash flows, NPV, IRR and Payback under three different scenarios.Please finish cash flow forecasting on the second project and calculating NPV, IRR and Payback for three scenarios as well. A sensitivity analysis parameters have been created for the first project. Please use the same parameters to construct sensitivity analysis for both projects.
Please write the report using the following template named Module7Template.docx and submit it on Blackboard. You can attach an Excel sheet as an appendix.
Exhibit 1 MMDC New Heritage Doll Company: Capital Budgeting Exhibit 1Selected Operating Projections for Match My Doll Clothing Line Expansion 20102011201220132014201520162017201820192020 Revenue4,5006,8608,4099,0829,80810,59311,44012,35513,34414,411 Revenue Growth52.4%22.6%8.0%8.0%8.0%8.0%8.0%8.0%8.0% Production Costs Fixed Production Expense (excl depreciation)575575587598610622635648660674 Variable Production Costs2,0353,4044,2914,6695,0785,5216,0006,5197,0797,685 Depreciation152152152152164178192207224242 Total Production Costs02,7624,1315,0295,4195,8536,3216,8277,3737,9638,600 Selling, General & Administrative1,2501,1551,7352,1022,2702,4522,6482,8603,0893,3363,603 Total Operating Expenses1,2503,9175,8667,1327,6908,3058,9699,68710,46211,29912,203 Operating Profit(1,250)5839941,2771,3921,5031,6231,7531,8932,0452,209 Working Capital Assumptions: Minimum Cash Balance as % of Sales3.0%3.0%3.0%3.0%3.0%3.0%3.0%3.0%3.0%3.0% Days Sales Outstanding59.2x59.2x59.2x59.2x59.2x59.2x59.2x59.2x59.2x59.2x Inventory Turnover (prod. cost/ending inv.)7.7x8.3x12.7x12.7x12.7x12.7x12.7x12.7x12.7x12.7x Days Payable Outstanding (based on tot. op. exp.)30.8x30.9x31.0x31.0x31.0x31.0x31.0x31.0x31.0x31.0x Capital Expenditures1,470952152152334361389421454491530 Net Working Capital (NWC) Cash135206252272294318343371400432 Account Receivable7291,1121,3631,4721,5901,7171,8552,0032,1632,336 Inventory360500396427461498538581627677 Account Payable3174845936406927478078719411016 Net Working Capital8009071,3341,4181,5311,6531,7861,9292,0832,2502,429 Change in NWC80010742784113122132143154167180(2,429) Scenario I: No Continuation Value: Assuming the project will be terminated. There will be no salvage value and net working capital will be recovered in 2021 Free Cash Flows Operating Cash Flows: EBIT(1-t) + Depreciation(750)502749919987106611521244134314511567 Cash Flow from NWC: - change in NWC(800)(107)(427)(84)(113)(122)(132)(143)(154)(167)(180)2429 Cash Flow from CapExp: - CapEx(1,470)(952)(152)(152)(334)(361)(389)(421)(454)(491)(530) Total Cash Flow(3020)(557)1696825415836306807357938572429 Cumulative cash flow(3020)(3577)(3408)(2726)(2185)(1602)(972)(291)443 Discount Rate 8.40% NPV$854 IRR12% Payback7.40 Scenario II: Assuming the project will last fore ever and free cash flow will grow at 3% per year and NWC will stay at 2,429 after 2020 Free Cash Flows Operating Cash Flows: EBIT(1-t) + Depreciation-750502749919987106611521244134314511567 Cash Flow from NWC: - change in NWC(800)(107)(427)(84)(113)(122)(132)(143)(154)(167)(180) Cash Flow from CapExp: - CapEx(1,470)(952)(152)(152)(334)(361)(389)(421)(454)(491)(530) Total Cash Flow(3020)(557)169682541583630680735793857 Continuation value16345.3321597854 Total cash flow plus continuation value(3020)(557)16968254158363068073579317202 Discount Rate 8.40% NPV7150.0724011938 IRR24% Payback7.40 Scenario III: Assuming the project will last fore ever and NWC will stay at 2,429 after 2020. The continuation value is calculated using EV/EBITDA Free Cash Flows Operating Cash Flows: EBIT(1-t) + Depreciation-750502749919987106611521244134314511567 Cash Flow from NWC: - change in NWC(800)(107)(427)(84)(113)(122)(132)(143)(154)(167)(180) Cash Flow from CapExp: - CapEx(1,470)(952)(152)(152)(334)(361)(389)(421)(454)(491)(530) Total Cash Flow(3020)(557)169682541583630680735793857 Continuation value32609.5 Total cash flow plus continuation value(3020)(557)16968254158363068073579333466 Discount Rate 8.40% Industrial EV/EBITDA average Under Recreation: EV/EBITDA 13.31 NPV$14,410 IRR31% Payback7.4 Sensitivity Analysis Based on Scenario 3 as The Base Case WorstBaseBest Parameter Sales20% lower8.40%20% higher Discount Rate9.00%8.40%7.70% Variable Production Cost20% higher8.40%20% lower NPV WorstBaseBest Parameter Sales14,410 Discount rate14,410 Variable Production cost14,410 http://pages.stern.nyu.edu/~adamodar/New_Home_Page/datafile/vebitda.html Exhibit 2 DYOD New Heritage Doll Company: Capital Budgeting Exhibit 2Selected Operating Projections for Design Your Own Doll 20102011201220132014201520162017201820192020 Revenue06,00014,36020,22221,43522,72124,08425,52927,06128,685 Revenue Growth139.3%40.8%6.0%6.0%6.0%6.0%6.0%6.0% Production Costs Fixed Production Expense (excl depreciation)01,6501,6831,7171,7511,7861,8221,8581,8951,933 Variable Production Costs02,2507,65111,42712,18212,98313,83314,73615,69416,712 Depreciation0310310310436462490520551584 Total Production Costs04,2109,64413,45414,36915,23116,14517,11318,14019,229 Selling, General & Administrative1,20101,2402,9224,0444,2874,5444,8175,1065,4125,737 Total Operating Expenses1,20105,45012,56617,49818,65619,77520,96222,21923,55324,966 Operating Profit(1,201)05501,7942,7242,7792,9463,1233,3103,5093,719 Working Capital Assumptions: Minimum Cash Balance as % of Sales3.0%3.0%3.0%3.0%3.0%3.0%3.0%3.0%3.0% Days Sales Outstanding59.2x59.2x59.2x59.2x59.2x59.2x59.2x59.2x59.2x Inventory Turnover (prod. cost/ending inv.)12.2x12.3x12.6x12.7x12.7x12.7x12.7x12.7x12.7x Days Payable Outstanding (based on tot. op. exp.)33.7x33.8x33.9x33.9x33.9x33.9x33.9x33.9x33.9x Capital Expenditures4,61003103102,1928268759289831,0431,105 New Heritage Doll Company Executive Summary: (Explain what the situation is and what is at stake) Assumptions: Please list assumptions you make for each case and explain the rationale behind your assumptions. Quantitative Analysis: Please use a table to show NPV, IRR and Payback of each projects. Ask some what if questions (Sensitivity Analysis): When we make financial decisions, we always face many uncertainties. You want to evaluate such uncertainties as much as you can. Risk factors and other considerations: For Example: What might go wrong? What are the long term impacts of each project on the overall business, growth potentials? What are the risks? Are the forecasts too optimistic? Conclusion: What should Harris recommend?