***Excel Spreadsheet***

Conch Republic Electronics is a midsized electronics manufacturer located in Key West, Florida. The company president is Shelley Couts, who inherited the company. Over the years, the company expanded into manufacturing and is now a reputable manufacturer of various electronic items. One of the major revenue-producing items manufactured by Conch Republic is a smart phone. Conch Republic currently has one smart phone model on the market, and sales have been excellent. Conch republic spent $750,000 to develop a prototype for a new smart phone that has all the features of their existing smart phones. The company has spent a further $200,000 for a marketing study to determine the expected sales figures for the new smart phone.

Conch republic can manufacture the new smart phones for $220 each in variable costs. Fixed costs for the operation are estimated to run $6.4 million per year. The estimated sales volume is 155,000, 165,000, 125,000, 95,000, and 75,000 per year for the next five years, respectively. The unit price of the new smart phone will be $535. The necessary equipment can be purchased for $43.5 million and will be depreciated on a seven-year MACRS schedule. It is believed the value of the equipment in five years will be $6.5 million.

As previously stated, Conch Republic currently manufactures a smartphone, production of the existing model is expected to be terminated in two years. If Conch Republic does not introduce the new smartphone, sales will be 95,000 units and 65,000 units for the next two years respectively. The price of the existing smart phone is $385 per unit; with variable cost of $145 and fixed costs of $4.3 million per year. If Conch Republic does introduce the new smartphone, sales of the existing smart phone will fall by 30,000 per unit, and the price of the existing units will have to be lowered to $215 each. Net working capital for the smart phones will be 20 percent of sales and will occur with the timing of the cash flows for the year (i.e., there is no initial outlay for NWC). Changes in NWC will thus first occur in Year 1 with the first year's sales. Conch republic has a 21 percent corporate tax rate and a required rate of 12 percent.

1) How sensitive is the NPV to changes in the price of the new smartphone?

2)How sensitive is the NPV to changes in the quantity sold of the new Smartphone?

Sales in units of new smart phone 125000 155000 165000 95000 75000 Sales revenue ($535) Variable cost ($220) Fixed cost (except depreciation) 88275000 66875000 40125000 82925000 50825000 20900000 36300000 27500000 16500000 34100000 6400000 6400000 6400000 6400000 6400000 14.29 24.49 17.49 12.49 8.93 Depreciation Depreciation expense 6216150 10653150 7608150 5433150 3884550 34921850 18091850 36208850 25366850 13340450 EBIT Contribution lost on existing smart phone Sales in units without new phone 95000 65000 Contribution margin @ $240 22800000 15600000 Sales in units with new phone 65000 35000 Contribution margin @70 4550000 2450000 Loss in contribution with new phone 18250000 13150000 17958850 25366850 INCREMENTAL EBIT 21771850 18091850 13340450 4572088.5 2801494.5 3771358.5 3799288.5 x @ 0.21 5327038.5 NOPAT 14187491.5 17199761.5 20039811.5 14292561.5 10538955.5 |(+) Depreciation 6216150 10653150 7608150 5433150 3884550 OCF 20403641.5 27852911.5 27647961.5 19725711.5 14423505.5 Capital expenditure 43,500,000 Change in NWC 1,658,500 1070000 -4280000 -3210000 -2140000 89025000 Release on NWC After tax salvage value 7173019 After tax annual cash flows -43,500,000 3818642 26782912 31927962 22935712 31761524 2.40398592 Years 1) PAYBACK PERIOD 1 0.89285714 0.79719388 0.71178025 0.63551808 0.56742686 2) PVIF @ 12% PV 3409501 21351173 22725692 14576059 18022342 PI 1.84102913 3) IRR 1 0.73529412 0.54065744 0.39754223 0.292310470.21493417 PVIF @ 36% 2807825 12881450 PV -43500000 14587642 6854440 7031166 PVIF @ 37% 1 0.72992701 0.53279344 0.38890032 0.28386885 0.20720354 PV -43500000 2787330 6510734 6581100 14269759 12416794 IRR 41.4927674 NPV 36,584,767 Sales in units of new smart phone 125000 155000 165000 95000 75000 Sales revenue ($535) Variable cost ($220) Fixed cost (except depreciation) 88275000 66875000 40125000 82925000 50825000 20900000 36300000 27500000 16500000 34100000 6400000 6400000 6400000 6400000 6400000 14.29 24.49 17.49 12.49 8.93 Depreciation Depreciation expense 6216150 10653150 7608150 5433150 3884550 34921850 18091850 36208850 25366850 13340450 EBIT Contribution lost on existing smart phone Sales in units without new phone 95000 65000 Contribution margin @ $240 22800000 15600000 Sales in units with new phone 65000 35000 Contribution margin @70 4550000 2450000 Loss in contribution with new phone 18250000 13150000 17958850 25366850 INCREMENTAL EBIT 21771850 18091850 13340450 4572088.5 2801494.5 3771358.5 3799288.5 x @ 0.21 5327038.5 NOPAT 14187491.5 17199761.5 20039811.5 14292561.5 10538955.5 |(+) Depreciation 6216150 10653150 7608150 5433150 3884550 OCF 20403641.5 27852911.5 27647961.5 19725711.5 14423505.5 Capital expenditure 43,500,000 Change in NWC 1,658,500 1070000 -4280000 -3210000 -2140000 89025000 Release on NWC After tax salvage value 7173019 After tax annual cash flows -43,500,000 3818642 26782912 31927962 22935712 31761524 2.40398592 Years 1) PAYBACK PERIOD 1 0.89285714 0.79719388 0.71178025 0.63551808 0.56742686 2) PVIF @ 12% PV 3409501 21351173 22725692 14576059 18022342 PI 1.84102913 3) IRR 1 0.73529412 0.54065744 0.39754223 0.292310470.21493417 PVIF @ 36% 2807825 12881450 PV -43500000 14587642 6854440 7031166 PVIF @ 37% 1 0.72992701 0.53279344 0.38890032 0.28386885 0.20720354 PV -43500000 2787330 6510734 6581100 14269759 12416794 IRR 41.4927674 NPV 36,584,767