This the question for my assignment. I already calculate the NPV and IRR using spreadsheet. Also calculated the sensitivity analysis but i'm confuse it is correct or not can you please check. I'm having problem with the graph as well. Can you please answer this and also can you do scenario analysis in both case by using best and worst case please.

Base Case analysis fo option 1 Base Case analysis for option 2 Best Case Base Case (Topic 7 Workshop) Income tax rate 25% Discount rate 8% Revenues Revenues with 4% increasement by 2nd year 200,000,000 208,000,00 216,320,000 224,972,800 233,971,712 Expected increased sales by 2% 204,000,000 212,160,000 220,646,400 229,472,256 238,651,146.24 Costs (increased by 3% after year 1) Reduced Cost by 15% 22,000.000 22,660,000 23,339.800 18,700,000 19,261,000 24,039,994 19,838,830 20.433,994.9 24,761,193.82 21.047,014.747 Fixed cost (excluding depreciation) 0 2 3 Fixed Cost per year 2,000.000 2,000.000 2.000.000 2,000.000 2,000,000 Net income Revenues Saving 4,000,000 4,160,000 .300,000 4,326,400 ,399,000 4,499,456 3.500,970 4,679,434.24 Fixed Cost 3,714,179.073 -Depreciation expense 2,000,000 2,000,000 Taxable income 4000.000 2,000,000 ,605,999.1 4.000.000 2,000,000 1,300,000 ,559,000 4.000.000 2,000,000 -Taxes 1,827,370 4,000.009 4.000.000 After-tax income $25.000 $89.750 2, 105,455.1 2,393.613.313 75.000 456.842.5 169.250 526.363.775 .370.527.5 598.403.328 $79.091.325 .795.209.985 Annual Net Cash Flow Estimate 0 1 2 3 Investment in fixed assets Net income $20,000,000 $0 975,000 0.00 1,169,250 0.00 0.00 0.00 Add back depreciation SO 4.000.000 4.000,000 1,370,527.5 1,579,091.325 4.000.000 1,795,209.985 Net cash flows -$20,000,000 4,975,000 5,169,250 5,370,527.5 4,000.000 4.000.000 5,579,091.325 5,795,209.985 NPV IRR $1,346,499.27 10.45% Sensitivity analysis NPV % deviation from base case WACC NPV $1,346,499.27 Units sold yr1 NPV -30% 7.0% -15% 8.5% $1,936.672.74 $1,346,499.27 875 NWC% yr1 rev 0% $1,060,736.38 $1,346,499.27 $1,346,499.27 1062 8.4% 15% 10.0% $238,755.40 $1,346,499.27 $1,346,499.27 1250 11.5% -$533,495.72 $1,346,499.27 10.2% $1,346,499.27 1438 12.0% $1,346,499.27 30% 13.0% $1,259,850.19 $1,346,499.27 13.8% 1625 $1,346,499.27 $1,346,499.27 15.6% $1,346,499.27 Above we use an Excel "Data Table" to find NPV with different values of a variable holding other things constant. For example, after inputting the values for WACC in cells B38:B42 and the formula =B31 for NPV in cell C37, select the range B37:C42 Then choose from the menu Data. What-If Analysis, Data Table and enter B5 (which is the input for WACC) as the Column input This produces the sensitivity analysis for WACC shown above. If you want to examine changes in a different variable you need to change the data table column input to the input cell for that variable, cell An alternative (but more time consuming) method is to change each variable to its new value. copy the resulting NPV and paste it into the relevant Sensitivity Analysis lysis is static. If other variables are changed, it will not update. 4,000,000 3,000,000 2,000.000 1,000.000 30% -1,000,000 -15% 0% 15% 30% -2.000,000 -3,000,000 4.000,000 NWC% yr1 rev Units sold yr1 WACC3:18 . .1 4G X Option 2.xIsx Income tax rate Discount rate or WACC 25% Forecast Growth 1 8% Forecast Growth 2 4% 2% Plant and euipment cost $1,400.000.00 Revenue Forcast sales revenue with 4% growth Forcast sales revenue with plus 2% growth 0.4(2%/5) $200,000.000.00 $208,000,000.00 $216,320,000.00 $224,972,800.00 $204,000.000.00 $212,160,000.00 233,971,712.00 $220,646,400.00 $229,472,256.00 $238,651,146.24 Fixed cost data (excluding depreciation) Selling adminisrative $1,000.000.00 $1,000.000.00 $1,000,000.00 $1,000,000.00 $1,000,000.00 Net income Revenues for recycle method Selling adminisrative and general expenses $4,000.000.00 $4,160,000.00 $4,326,400.00 $4,499,456.00 $4,679.434.24 Taxable income $1,000.000.0 $3.000,000 $1,000,000.00 $3,160,00 $1,000,000.00 1,000,000.00 $1,000,000.00 Taxes After-tax income $750.000 3,326,400 $3,499,456 $790.000 $3.679,434 $2.250.000 $2.370.000 $831.600 $2.494.800 $874,864 $2.624.592 $919.859 $2.759.576 Annual Net Cash Flow Estimates Investment in fixed assets Net income So SO SO $2.250,000 SO Net cash flows sol 2,250.000 $2,370,000 SO $2,370.000 $2.494,800 SO 2,494,800 $2.624,592 $2.624.592 $2,759.576 2,759.576 NPV IRR $9.902,953.32 Sensitivity analysis NPV % deviation from base case NPV NPV WACC $9,902,953.32 -20% Fixed cost -10% 6.4% $10.350,751.15 $9,902,953.32Plant & equipment cost $9,902,953.32 7.2%% 8.0% $10.122,978.15 $800,000.00 $10,041,842.21 0% $9,902,953.32 $900.000.00 $1,120,000.00 $9,972,397.77 $9,902,953.32 1,000,000.00 $9,902,953.32 $1,260.000.00 $1.400,000.00 $9,902,953.32 10% $9,902,953.32 20% Sensitivity Analysis $9.902,953.32 $9,902,953.32 5,000,000 4,000,000 3,000.000 2,000,000 1,000,000 -20.00% -10.00% 0.00% 10.00% 20.00% Plant & equipment cost] Fixed cost WACC Unit sold is the steepest line in the graph = the NPV is most sensitive for the estimates of WACC3:05 .all 4G O ...thos.s3-ap-southeast-2.amazonaws.com Assessment 3: Business Case Studies 2 ACCO0716 S1 2019 ACCO0716 Finance Session 1, 2019 Assessment 3: Business Case Studies 2 Due date: 19 May 2019, 11PM 1 OT This assignment has a 25% weighting in your overall mark for this unit and focuses on content from Weeks 6, 7 and 8. The assignment will be marked out of 25 and marks will be allocated as indicated in the rubric on page 4. Your total assignment submission will consist of a word document and a spreadsheet. The assignment is based on the case information below. While the company and financial data in the case are fictitious', the context is not. Many companies face similar investment decisions as well as challenges and opportunities to run more environmentally and socially responsible businesses. DuoLever Limited operates in the personal care (e.g. skin and hair care products) industry. All its products are sold in plastic packaging and a significant proportion in multi-layer sachets (or pouches). Managers at Duolever are acutely aware of the increase in world production of plastic and the environmental impact of plastic waste ending up in landfills, rivers and oceans. For example, it is estimated that 8 million metric tons entered the ocean in 2010 and this annual amount is predicted to more than double by 2025, accumulating as show in the following graph': 300 250 E $ 200 Plastic marine debris, cumulative( 150 100 50 2010 2015 2020 2025 Year To help develop a closed-loop system related to the company's products, Duolever has invested around $50 million in soft plastic recycling research, development and pilot testing. The outcome is a new and efficient method for recycling sachet waste. In fact, their recycling method is more energy efficient than producing virgin sachet plastic, reducing energy usage by 83%. The output plastic is of UniLever and its research and development in the area of multi-layer sachet recycling provides the inspiration for this case but all facts related to the financial analysis are fictitious. 2 While not necessary for attempting this case study, you will better understand the plastic packaging in this case context if you go to https:/www.plasticpackagingfacts.org/blog/multi-layer-pouch-packaging-a-sustainable-story-animated/ and watch the video on multilayer plastic pouches. Although many improvements to this packaging have been made, as pointed out in the video, there remains much to do in reducing the impacts of waste pouches on the environment. Estimates from Jambeck, J.R., Andrady, A., Geyer, R., Narayan, R., Perryman, M., Siegler, T., Wilcox, C., Lavender Law. K. (2015) Plastic waste inputs from land into the ocean, Science, 347, p. 768-771 and graph reproduced from p. 770. If you are interested, see see https:/jambeck.engr.uga.edu/landplasticinput for further details and an infographic. 1 Assessment 3: Business Case Studies 2 ACCO0716 51 2019 such high quality it can be used in food grade packaging applications. Currently, no other recycling method in the market can achieve this. The company now faces a decision: should it (1) add production of recycled sachet plastic to the company's portfolio of businesses or (2) license use of the patented method? The CEO has asked you to undertake a financial analysis of the options and present your recommendations in a short memo. Option 1: The recycling production option requires an upfront investment in plant and equipment of $20 million, which will be depreciated to a zero book value on a straight-line basis over 5 years. The plant will provide sufficient capacity to meet the company's forecast plastic packaging needs over the period of its life. After this, it is expected that the plant will have no salvage value and will be updated using new and better technology. Financing for the plant and equipment will be via a new 5 year debt issue, resulting in interest costs of $1.4 million payable at the end of each year. Producing recycled plastic has several financial benefits for the company. First, sales revenue of the company's existing products, which will be packaged in the recycled plastic, is predicted to increase due to consumer demand for environmentally responsible products. Excluding this benefit, the company's forecast sales revenue for the coming year is $200 million and this is expected to grow by Q 84 ...3:06 .. I 46 C) i ...thos.$3-apsoutheast2.3mazonawscom due to consumer demand ror environmentally responsroie products. excluding Ilnis oenerit, the company's forecast sales revenue for the coming year is $200 million and this is expected to grow by 4% each year after that. The benet of recycled packaging is expected to increase these sales forecasts 2% during the 5 year lite of the project. The second benet is that the cost of plastic packaging for the company's existing products will decrease. The recycled plasticwill be cheaper than buying virgin plastic due to lower energy costs and avoiding a supplier margin,The reduced energy costs will shave 15% off total variable packaging costs, currently (without recycling) estimated at $12 million for the coming year and expected to grow by 3% per year after that, Avoiding a supplier margin will reduce total variable packaging costs by 10%. However, the benets of avoidingthe current supplier margin will be offset by the need to pay a new partner, Clean World Ltd. who will set up a plastic waste collection system to supply sufcient raw material for the recycling plant. Apart from these changes. it is expected that variable costs and net working capital will be equivalent to existing forecasts. However, an additional $2 million annually in selling. administrative and general expenses directly related to the project (excluding depreciation) will be incurred. Option 1: Option 2 involves licensing use of the patented recycling method to another company, Clean World Ltd, which has shown intere in taking on the entire project, not just supply of raw material. initial negotiations between DuoLever and Clean World have reached some agreement on what the terms of the arrangement would involve. Clean World would produce recycled plastic using DuoLevei's method for the hearts years and all output during that time would be supplied exclusively to Duolever tor the same cost as DuoLevei's existing virgin plastic supply forecasts. This means that Clean World would capture the energy savings associated with the new recycling method, along with a supplier margin. The benefits for DuoLever would be no initial outlay for plant and equipment and locked in packaging materials supply costs for the next 5 years, DuoLever would also retain the ability to market the environmentally responsible characteristics of its recycled packaging and so retain the expected Assessment3: 3U$in53 Case Studiesl ACCGOTIE 51 2019 additional sales revenue benefits of Option 1. Annual selling, administrative and general expenses would be just $1 million annually under Option 2, as no additional production administration would be required. other information: DuoLever has an 8% weighted average cost of capital and is subject to a 15% tax rate on its income. Required: Prepare (1) a spreadsheet financial analysis of the proposed options and (2) a memo to DuoLevers CEO that briey explains and iusties your chosen methods, inputs and any assumptions made, summarises yourndings. and presents your recommendations on the proposed options. Ensure you not only address base case cash flows but also analyse potential uncertainty. Recommendations should address the decision to be made, along With any further follow up or other matters the company should consider prior to making a nal decision. Instructions: Submit your spreadsheet separately in the provided spreadsheet link in the M52 section oi the unit site. By submitting the spreadsheet, you are confirming that it is entirely your own work. Save the spreadsheet with your details in the file name using the following format (failure to do so could result in your spreadsheet not being considered in marking): Student !D_Fuli_name_ACC00716A3 For example: 13579246_Jennifer_Harrlson_ACC00716A3 The memo will be submitted as a word document via a Turnitin assignment link in the BCSZ section of the unit site and include your name, student ID, unit code (ACC00716), assessment number (A3) and word count at the beginning of the document. The remainder of the document should be set up as a formal memo and include an appendix with a screen shotis) of your base case gures from the spreadsheet. Within thememo body, you may provide tables and figures that are discussed in the text and assist decision makers understand your methods, findings and their implications for decision making, The word document submission must not exceed 1,000 words (excluding the screen shot appendix and reference list). This is an individual assasment exercise. The unit teaching team is very experienced at marking such assessments and recognising the differences between individual and "group" work, as well as data, facts, statements and ideas of others that have not been appropriately acknowledged. To avoid any potential for academic misoond uct investigation, ensure that every aspect of your work is your own and that you acknowledge all sources you have directly drawn upon in your submitted work. Quotations should be shown as such. We are not fussy about referencing style, just that you reference when needed