I need to make a sensitivity analysis for the variable overhead costs. I am not sure how to make the table in excel or how to format it.

Read-only This workbook was opened in read-only mode A B C D E F G 1 Estimated Net Profits for Yucky Goo (Millions of Dollars) H I J K L M IN Year 2021 W 2022 2023 2024 Years 2025 2026 2027 1 2 2028: 3 4 4 5 Nominal Disco 12% Cases Sold (Millions) 6 15.00 7 20.00 25.00 Sales 15.00 12.00 Inflation Rate 3% 12.00 $45.00 $60.00 12.00 12.00 Tax Rate 6 COGS $75.00 $45.00 $22.50 $36.00 $30.00 $36.00 27% :Depreciation $37.50 $36.00 $22.50 $36.00 $18.00 $8.00 $18.00 $8.00 $8.00 $18.00 $8.00 $18.00 8 Lease Payments $8.00 $4.00 $8.00 $4.00 $4.00 $8.00 $4.00 $8.00 9 Advertising $4.00 $6.00 $4.00 $4.00 $4.00 10 Overhead $2.00 $2.00 $4.00 $5.00 $2.00 $5.00 $2.00 $5.00 $2.00 Operating Profit $5.00 $5.00 $2.00 11 $5.00 $(0.50) $9.00 $5.00 $18.50 $5.00 Taxes $3.50 $(0.13) $2.39 $(1.00) $4.90 $(1.00) $(1.00) $(1.00) 13 Net Profit $0.93 $(0.37) $(0.27) $6.62 $(0.27) $13.60 $2.57 $(0.27) $(0.27) 14 Profit Erosion $1.00 $2.00 $(0.74) $1.00 $(0.74) $(0.74) 15 Adjusted Net Profit $- $(0.74) $(1.37) $- $4.62 $- $12.60 $2.57 $- 16 PV $(1.22) $(0.74) $(0.74) $3.68 $(0.74) $8.97 $(0.74) $1.63 $(0.42) $(0.37) $(0.33) $(0.30) 17 NPV 11.64 18 19 20 21 22 23Assignment: Prepare an analysis of this capital budgeting problem, in which you compute the Net Present Value of Yucky Goo and decide if Horrible should introduce Yucky Goo. In the course of working through this case, some decisions need to be made that reasonable people might disagree about. You may make the simplifying assumption that cash flows occur once per year at the end of each year. Specifically, your assignment should include the following: 1) A summary describing your approach, your results, and a recommendation. Explain your reasoning including quantitative and any qualitative factors. 2) Many of the values given in the case are uncertain. Use sensitivity analysis to provide estimates of NPV over a range of possible values. (These should include the discount rate, sales, lifetime of the project, and anything else that you think may be important.) 3) Brief explanations of how you handled each of the following issues and how they affected your analysis: i) The $6 million spent on development and test marketing ii) Capital expenditures (i.e. purchase of machinery) iii) Depreciation iv ) Lease payments v) Overhead vi) Profit erosion vii) Taxes vil) InflationHorrible Toys Horrible is an Ontario-based manufacturer of children's toys, and over the years it has pioneered a large number of products that are sold internationally. Some of these turned out to be fads, while others have become permanent fixtures of our popular culture. In both situations, Horrible' management has found ways to turn them into very lucrative opportunities for the company. Competition in the toy market is cut-throat, but by responding quickly and forcefully to competitive threats, Horrible has managed to do more cutting than bleeding. Their aggressiveness in the product markets has always been guided and disciplined by careful analysis. Says one senior executive, "Our big advantage over other companies is that we actually take toys seriously". Recently a new threat has emerged. A competitor has introduced a product known as Muck. It is a slimy miracle substance that will slide off of flat surfaces. When thrown by the handful at older people it sticks to their skin or clothing without staining or otherwise causing permanent physical damage. Muck appears to the management of Horrible as a serious threat in part because it will erode sales of their own products, Ack and Silly Dough. Further, to the extent that this type of product will generate new sales, they are anxious to get in on the action. "This is, in many ways, the perfect toy," claims one of the company's analysts, "It brings out the worst in small children with relatively benign consequences for adults." Accordingly, the firm has spent $6 million over the past year developing and test marketing a new product, to be called Yucky Goo. Yucky Goo will have several advantages over the competitive product. It can be sold in a variety of fluorescent colours, as opposed to the mud brown of Muck. It can also be scented in foul ways, so that adults will find it even more distasteful and children will therefore find it more amusing. The company plans to sell Yucky Goo in Rotten-Egg Yellow, Oil-Spill Purple, Greasy Green, and Sewage Brown. Importantly, Horrible hopes that the introduction of Yucky Goo will reinforce their reputation as a leading innovator in toy technology. Based on extensive marketing and production analyses, the company has estimated the sales and costs associated with the product over its expected life. These are provided in the table below. The table is only intended as guidance - you should adjust any numbers that you think should be adjusted for the purpose of your recommendation. Horrible plans to launch the product with a media blitz that will cost $6 million in the first year, and $4 million in the second. Once the product is established, continuing advertising and promotional expenses will run about $2 million a year. To mass-produce the product, Horrible would have to purchase new equipment immediately at a cost of $32 million. The new equipment would have an expected life of four years, after which it would have to be replaced. It has no salvage value. Assume it is depreciated for tax purposes straight-line over the four years. Marketing Yucky Goo would also require warehouse and trucking facilities. Rather than purchasing new facilities, the company would lease them at a rate of $4 million per year. This rate would be contractually fixed for eight years. 3The introduction of Yucky Goo will create a unique problem for the company. To a moderate extent, it will draw sales away from the company's existing products Ack and Silly Dough. Estimates of the profit erosion are shown in the appended table. Because competitors have already introduced similar products, however, Horrible' financial analysts are uncertain how to treat this profit erosion. As a matter of policy, Horrible allocates a portion of the company's general and administrative overhead costs to each product line. The addition in allocated overhead to the Inane Toys Division, which will be in charge of Yucky Goo, will be $5 million per year, even though the new products would only create $3 million per year in incremental overhead expenses. Estimates of the after-tax profits for the new product line are provided in the table below. The analysis is restricted to 8 years because management feels that this reflects the likely product life for Yucky Goo. Given the risks involved, management feels that a 12% nominal discount rate should be used. Inflation is expected to be 3% annually. Estimated Net Profits For Yucky Goo (All figures in millions of dollars) annually from 2021 2022 2023 2024 2025 to 2028 Cases Sold 15.00 20.00 25.00 15.00 12.00 Sales 45.00 60.00 75.00 45.00 36.00 Cost of Goods Sold" 22.50 30.00 37.50 22.50 18.00 Depreciation 8.00 8.00 8.00 8.00 8.00 Lease payments 4.00 4.00 4.00 4.00 4.00 Advertising 6.00 4.00 2.00 2.00 2.00 Overhead 5.00 5.00 5.00 5.00 5.00 Operating Profit -0.50 9.00 18.50 3.50 -1.00 Taxes -0.13 2.39 4.90 0.93 -0.27 Net Profit -0.37 6.62 13.60 2.57 -0.74 Profit Erosion 1.00 2.00 1.00 0.00 0.00 Adjusted Net Profit -1.37 4.62 12.60 2.57 -0.74 "Cost of Goods Sold is 50% of Sales. 'Depreciation is computed using the straight-line method, a four year life, zero salvage value, and based on the cost of the new equipment. Depreciation is always calculated in nominal terms. Horrible pays a combined federal and provincial marginal tax rate of 26.5%. It is now late 2020 you are asked to provide an analysis and a recommendation on whether to introduce Yucky Goo. If approved, operations will begin immediately. 4