***Please show work in Excel (work and cell formulas)***

onch Republic Electronics is a midsized electronics company has spent a further $250.000 for a marketing manufacturer located in Key West, Florida. The com- study to determine the expected sales figures for the pany president is Shelly Couts, who inherited the com- new smartphone. pany. The company originally repaired radios and other Conch Republic can manufacture the new smarthousehold appliances when it was founded more than phone for $210 each in variable costs. Fixed costs for 70 years ago. Over the years, the company has the operation are estimated to run $5.4 million per year expanded, and it is now a reputable manufacturer of var- The estimated sales volumes are 67,000, 108.000, fous specialty electronic items. Jay McCanless, a recent 93,000,86,000, and 55,000 per year for each of the MBA graduate, has been hired by the company in its next five years, respectively. The unit price of the new finance department. smartphone will be $525. The necessary equipment can One of the major revenue-producing items manu- be purchased for $41.5 million and will be depreciated factured by Conch Republic is a smartphone. Conch on a seven-year MACRS schedule. It is believed the Republic currently has one smartphone model on the value of the equipment in five years will be $5.9 million. market, and sales have been excellent. The smartphone_ Net working capital for the smartphones will be 20 is a unique item in that it comes in a variety of tropical percent of sales and will occur with the timing of the colors and is preprogrammed to play Jimmy Buffett cash flows for the year (i.e., there is no initial outlay for music. However, as with any electronic item, technology NWC). Changes in NWC thus will occur first in Year 1 changes rapidy, and the current smartphone has limited with the first year's sales. Conch Republic has a 22 perweatures in comparison with newer models. Conch cent corporate tax rate and a required return of 12 new smartphone that has all the features of the existing answers the following questions: QUESTIONS 1. Whar is the payback period of the project? 6. How sensitive is the NPV to changes in the 2. What is the protitability index of the project? quantity sold? 3. What is the IRR of the project? 7. Should Conch Republic produce the new smartphone? 4. What is the NPV of the project? 5. How sensitive is the NPV to changes in the price 8. Suppose Conch Republic loses sales on other models because of the introduction of the new of the new smartphone? model. How would this affect your analysis