>excel sheet due in 1 hour(You might find it helpful to copy and paste problem onto your worksheet. Submit before 9pm DUE TIME!) CellTech currently sells three models of smart phones to the US market. Management is planning to add a new smart phone model to their product line. The project will cost $3000000 in Buildings and Equipment which will be depreciated using MACRS with a class life of 20 years. This project has a 5-year life. At the end of the 5 years management anticipates that the buildings and equipment will be sold for $1000000. The marketing department estimates that the firm will be able to sell 10000 units of the new smart phone at an average price of $200 per unit during the first year. Unit demand is expected to grow at a rate of 20% annually thereafter. Variable operating expenses are expected to average 60% of gross sales and fixed costs (not including depreciation) will be $400000 per year. The companys marginal tax rate is 21% and its WACC is 10%. A. Evaluate the project (find the NPV IRR MIRR Payback Period and Discounted Payback Period). For this you will need to compute initial outlay terminal value and after-tax cash flows (just like the homework exercises organize and format your worksheet the same way we learned in class). B. Perform a sensitivity analysis for (1) Terminal Value of Buildings and Equipment (2) first year phone sales (units) (3) price per unit (4) unit sales growth rate and (5) variable costs as a % of sales using the range of -30% -20% -10% 0% +10% +20% +30%. You will have to create a sensitivity table with NPV values for different percentage changes. You do not have to construct sensitivity diagrams. C. Rank these sensitivity variables based on their impact on the project NPV. Hint: once you get the NPVs for the percentage changes in the above-mentioned variables estimate the absolute value of the slope for each of the sensitivity variables. You can estimate slope by using the slope function where the resulting NPVs are the y-values and the % changes in sensitivity values are the x values. Then use ABS(Slope(…)) so that you get the absolute value of those slopes. Rank the sensitivity variables based on their absolute value of slopes using the Rank function. Highlight the value with the Rank = 1. D. Using scenario manager generate a scenario summary for the following 3 scenarios against NPV. Hint: before running the scenario manager you can define the cells that you are going to work with so the summary output is easier to read. If you do not define the cells the summary will reference the Excel cell rather than your given name and you will have to change them manually. Lastly compute expected NPV value. Using the NPV values from the scenario manager (part C) compute the Expected NPV using the probabilities given. Highlight the expected NPV value. Submit your worksheet via Canvas before the due time. No late submissions will be accepted. Final note: use proper formatting as we learned and label your answers properly so I can spot them easily. Organize and format your worksheet the best you can so it is readable and pleasing to the eyes.
Any citation style (APA, MLA, Chicago/Turabian, Harvard)
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.