PLEASE ANSWER QUESTION 3 - Instruction below question

3. Analysis (Required 1'5 Points) a. Calculate the expected initiaf cost of development for all three options using weighted values. (25 points) i. Include your calculations (Le, a screenshot of the part of the Excel spreadsheet that shows your work or copy and right clickfpaste - picture) in your submission. ii. Explain (for DB'v'ac) what these values are and how they should be useful to the company in terms of evaluating the three options. DATA 21 DO HW Major Data Analysis Page 5 of 6 b. Calculate the NPV for all three options using the data provided in the background section and using the expected initial cost of development (as calculated in Se) for each of the three options. (25 points). i. Use a discount rate of10% and calculate the NP'llr inclusive of the investment year (Year El) and the ve years that follow a ve-year time-horizon. ii. Include the calculations (Le, a screenshot of the Excel spreadsheet showing your work or copy and right clickfpaste - picture) in your submission. iii. Explain for DB'v'ac what these values tell us and how they might be useful in comparing the three possible choices. c. Conduct a sensitivity analysis showing what the NPV for the product with the highest NPV only as calculated in 3b. In the sensitivity analysis, provide three different calculations of NF'V: (1) based on the expectations noted in 3b above (i.e., reiterate the calculation from 3b), (2) based on sales growth that is half as great as that predicted by the company and used in 3b above. and (3) based on -5% (negative 5%) annual sales growth after the rst year. (25 points). i. Include the calculations (e.g., from Excel) in your submission. ii. Clearly report the results of your calculations (i.e., do not make the reader interpret the spreadsheet calculations tell the reader what the calculations mean). iii. Explain for DB'v'ac what the difference is between (1), (2), and (3) and why it is useful to consider all three possible outcomes. DATA ANALYSIS AND BUSINESS CASE PURPOSE In this exercise. students will learn to build a basic business case by using data analysis techniques and interpreting and presenting their results. Techniques include weighted values, NPV calculation and comparison, and sensitivity analysis. SUBMISSION REQUIREMENTS Respond to the items specied in the Assignment section ot this document. For this assignment, you will submit one document via Canvas (Answer Sheet HW Major Data Analysis). The answer sheet document provides an outline with appropriate headings, however, please feel free to make changes to the formatting and adjust the length of each section as you see t. 1. Include your responses/answers in the Answer Sheet document (download from the Assignment lnstnrctiohs: Answer Sheet HW Major Data Analysis). Do NOT use this instruction document to respond. 2. Your submitted Answer Sheet document must include your name at the top. Failure to include your name at the top at your document will result in a ve-point deduction on your grade. 3. Complete this assignment on your own: doing otherwise represents a breach of academic integrity. What you submit must represent your own thinking and your own work. Do not look at answers from other students, and do not share your answers with others. Students submitting something other than their own work will receive a 0 on this assignment and be reported to the university for the academic integrity violation. 4. Submit your completed Answer Sheet via Canvas as a MS Word document. 5. Submit your working excel le(s) used for data analysis. BACKGROUND DBVac is considenng adding an additional productto their lineup. The compaan Research and Development (RD) team has come up with three different concepts for new vacuum cleaners and assigned them the codenames Fin, Snorkel, and Facemask. Each of the three proposed products has a number of different characteristics (described below) that distinguish them from one another. However, the company only has the means to bring one of these three products to market in the near future. As such, they would like you to evaluate the three products using a few different analyses, then write up a business case recommending which of the three new products to bring to market. The business case should explain the reasons behind your recommendation and detail expectations the company should have for the new product (e.g.. how much they should expect to prot from its release). Page ">0\"; ZOOM + Page of 6 - ZOOM + In assessing DBVac's situation, please carefully read the content in this background section as your response is very dependent on the content herein. You will need to come back to this background section to find data required to perform the analyses required in the assignment. PRODUCT CANDIDATES DBVac is considering developing and bringing to market one of three possible products, code-named Fin, Snorkel, and Facemask. Fin would be a very low-cost model, Snorkel would be a mid-range model with some innovative features, and Facemask would be a top-of-the-line model built on a drone platform. In trying to understand what the initial (pre-launch) development cost would be for these potential new products, the company thinks it would be worthwhile to consider the pre-launch development cost for its four most recent new product development projects: FarmBunny, RapidBunny, BunnyKing, and the disastrous CozyCony. The company has analyzed facets of each of the three potential new projects to get a sense for how similar each of the three is to the four previous new project development projects. This was done under the assumption that, if the development process for the new product is exactly like, for instance, the FarmBunny development process, then the initial cost of development should be the same. The initial cost of development for these previous new product development projects were as follows: FarmBunny: $2.7 million RapidBunny: $4.2 million BunnyKing: $4.6 million . CozyCony: $13.7 million Product 1 - Fin Vacuum Fin would be DBVac's lowest cost entry into the vacuum cleaner market, providing no advanced features but still being a vacuum cleaner, technically speaking. R&D would consist of aggressive down-costing measures to ensure the affordability of product manufacturing. In considering Fin's initial R&D cost, DBVac has provided the following estimates of the project's similarity to past new product development projects. 30% probability of being similar to the FarmBunny initial R&D cost. 38% probability of being similar to the RapidBunny initial R&D cost. 5% probability of being similar to the BunnyKing initial R&D cost. . 27% probability of being similar to the CozyCony initial R&D cost. No additional revenue or cost is expected to be accrued during the initial year (i.e., the R&D year) beyond the initial R&D cost. After this initial R&D year, sales net of unit cost are expected to total $2.5 million in the first year, then increase by 19% each year thereafter. The company expects to incur a cost of $800,000 to train service, support, and repair personnel in handling the product during the first year after introduction, then $250,000 per year each yearPage 3 of 6 - ZOOM + thereafter. Additionally, because this is a high-volume product, DBVac expects to need to spend $1.2 million the third year after the initial R&D year to expand their current service and repair facilities to accommodate service claims Because the product is significantly down-costed, the company expects a high failure rate. As such, the company foresees warranty and repair costs that total 27% of sales net of unit cost annually starting the first year after development. On the other hand, because the product is made primarily of recycled materials, DBVac expects to be able to recoup 4% of the total annual warranty cost each year by selling the recyclable materials to recycling companies. Product 2 - Snorkel Vacuum Snorkel is foreseen as a vacuum cleaner that would fit in the middle of the DBVac lineup in terms of price, settling in somewhere between the company's current MadHare and JackRabbit models. Using many of the same technologies already available in other models, such as the patented turbo button found on both the MadHare and JackRabbit models, Snorkel would also include two new key technologies: (a) reverse-thrust, which would allow the user to jettison collected debris through the hose directly into a waste receptacle (e.g., a garbage can), and (b) the carpet de-tangler, which would sense when a long carpet strand is getting lodged within the vacuum's roller, warn the user, then eject the strand. In considering Snorkel's initial R&D cost, DBVac provides the following estimates of the project's similarity to past new product development projects 9% probability of being similar to the FarmBunny initial R&D Cost. 48% probability of being similar to the RapidBunny initial R&D cost. . 29% probability of being similar to the BunnyKing initial R&D cost. 14% probability of being similar to the CozyCony initial R&D cost. No additional revenue or cost is expected to be accrued during the initial year (i.e., the R&D year) beyond the initial R&D cost. After this initial R&D year, sales net of unit cost are expected to total $3.0 million in the first year, then increase by 12% each year thereafter. Further, based on the level of innovation involved in the development of the product, the company is 33% confident that it will qualify for an $800,000 research innovation grant from the federal government, which would be paid out the second year after development. The company expects to incur a cost of $950,000 to train service, support, and repair personnel in handling the product during the first year after introduction, then $450,000 per year for each year thereafter. Further, to service what is expected to be an unusual technology, the company will need to spend $750,000 to improve its current service and repair facilities the first year after the R&D year. Additionally, DBVac expects to need to spend $400,000 in the fourth year to expand their service and repair facilities Given the unique nature of the product, the company expects warranty and repair costs that total 20% of sales net of unit cost annually starting the first year after development.Page of 6 - ZOOM + Product 3 - Facemask Vacuum Facemask would be the company's most technically advanced product - it would be the company's "flagship" the kind of awe-inspiring product that would be valuable not only in terms of its own sales, but in its ability to bring new customers to consider the entire DBVac family of vacuum cleaners. In addition to the always-on, dirt-anticipating TruSentience capabilities already found in the company's BunnyKing model, Facemask would work in three dimensions. Incorporating military-grade drone technology, Facemask would be able to fly through the household, cleaning window coverings and crown moldings as needed and could even capture dust and dirt mid-air before it has a chance to land on things In considering Facemask's initial R&D cost, DBVac provides the following estimates of the project's similarity to past new product development projects. . 2% probability of being similar to the FarmBunny initial R&D Cost. 39% probability of being similar to the RapidBunny initial R&D cost. . 45% probability of being similar to the BunnyKing initial R&D cost. 14% probability of being similar to the CozyCony initial R&D cost. No additional revenue or cost is expected to be accrued during the initial year (i.e., the R&D year) beyond the initial R&D cost. After this initial R&D year, sales net of unit cost are expected to total $1.9 million in the first year, then increase by 20% each year thereafter. Further, based on the level of innovation involved in the development of the product, the company is 100% confident that it will qualify for an $800,000 research innovation grant from the federal government, which would be paid out the second year after development. The company expects to incur a cost of $500,000 to train service, support, and repair personnel in handling the product during the first year after introduction, then $240,000 per year for each year thereafter. Finally, the company expects warranty and repair costs that total 16% of sales net of unit cost annually starting the first year after development