Question

onch Republic Electronics is a midsized electronics manufacturer located in Key West, Florida. The company president is Shelly Couts, who inherited the company. The company originally repaired radios and other household appliances when it was founded more than 70 years ago. Over the years, the company has expanded, and it is now a reputable manufacturer of various specialty electronic items. Jay McCanless, a recent MBA graduate, has been hired by the company in its finance department.

One of the major revenue-producing items manufactured by Conch Republic is a smartphone. Conch Republic currently has one smartphone model on the market and sales have been excellent. The smartphone is a unique item in that it comes in a variety of tropical colors and is preprogrammed to play Jimmy Buffett music. However, as with any electronic item, technology changes rapidly, and the current smartphone has limited features in comparison with newer models. Conch Republic spent $750,000 to develop a prototype for a new smartphone that has all the features of the existing one but adds new features such as wifi tethering. The company has spent a further $200,000 for a marketing study to determine the expected sales figures for the new smartphone.

Conch Republic can manufacture the new smartphone for $205 each in variable costs. Fixed costs for the operation are estimated to run $5.1 million per year. The estimated sales volume is 64,000, 106,000, 87,000, 78,000, and 54,000 per year for the next five years, respectively. The unit price of the new smartphone will be $485. The necessary equipment can be purchased for $34.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 $5.5 million.

Net working capital for the smartphones 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 35 percent corporate tax rate and a required return of 12 percent.

Shelly has asked Jay to prepare a report that answers the following questions:

1. What is the payback period of the project?

2. What is the profitability index of the project?

3. What is the IRR of the project?

4. What is the NPV of the project?

5. How sensitive is the NPV to changes in the price of the new smartphone?

6. How sensitive is the NPV to changes in the quantity sold?

7. Should Conch Republic produce the new smartphone?

8. Suppose Conch Republic loses sales on other models because of the introduction of the new model. How would this affect your analysis?

PLEASE ANSWER ALL QUESTIONS USING EXCEL. THANK YOU!

Answer 1

CONCH REPUBLIC ELECTRONICS:	0	1	2	3	4	5
Sales in units of new smart phone		64000	106000	87000	78000	54000
Sales revenue ($485)		$   3,10,40,000	$   5,14,10,000	$ 4,21,95,000	$ 3,78,30,000	$ 2,61,90,000
Variable cost ($205)		$   1,31,20,000	$   2,17,30,000	$ 1,78,35,000	$ 1,59,90,000	$ 1,10,70,000
Fixed costs (other than depreciation)		$      50,10,000	$       50,10,000	$      50,10,000	$      50,10,000	$      50,10,000
Depreciation (7 Year MACRS) %		14.29	24.49	17.49	12.49	8.93
Depreciation expense		$      49,30,050	$       84,49,050	$      60,34,050	$      43,09,050	$      30,80,850	$   2,68,03,050
EBIT		$      79,79,950	$   1,62,20,950	$ 1,33,15,950	$ 1,25,20,950	$      70,29,150
Tax at 35%		$      27,92,983	$       56,77,333	$      46,60,583	$      43,82,333	$      24,60,203
NOPAT		$      51,86,968	$   1,05,43,618	$      86,55,368	$      81,38,618	$      45,68,948
Add: Depreciation		$      49,30,050	$       84,49,050	$      60,34,050	$      43,09,050	$      30,80,850
OCF		$   1,01,17,018	$   1,89,92,668	$ 1,46,89,418	$ 1,24,47,668	$      76,49,798
Capital expenditure	$      3,45,00,000
Change in NWC		$      62,08,000	$       40,74,000	$    -18,43,000	$      -8,73,000	$    -23,28,000
Release of NWC						$      52,38,000
After tax salvage value = [5500000+(7696950-5500000)*35%]						$      62,68,933
After tax annual cash flows	$     -3,45,00,000	$      39,09,018	$   1,49,18,668	$ 1,65,32,418	$ 1,33,20,668	$ 2,14,84,730	$   3,56,65,500
PAYBACK PERIOD:
Cumulative after tax cash flows	$     -3,45,00,000	$ -3,05,90,983	$ -1,56,72,315	$        8,60,103	$ 1,41,80,770	$ 3,56,65,500
Payback period = 2+15672315/16532418 =	2.95	Years
PVIF at 12% [PVIF = 1/1.12^n]	1	0.89286	0.79719	0.71178	0.63552	0.56743
PV at 12%		$      34,90,194	$   1,18,93,070	$ 1,17,67,448	$      84,65,525	$ 1,21,91,013	$   4,78,07,251
PI = PV of cash inflows/Initial investment = 47807251/34500000 =	1.39
IRR:
PVIF at 23% [PVIF = 1/1.23^n]	1	0.81301	0.66098	0.53738	0.43690	0.35520
PV at 23%	$     -3,45,00,000	$      31,78,063	$       98,60,974	$      88,84,255	$      58,19,766	$      76,31,402	$         8,74,461
PVIF at 24%	1	0.80645	0.65036	0.52449	0.42297	0.34111
PV at 24%	$     -3,45,00,000	$      31,52,433	$       97,02,567	$      86,71,042	$      56,34,291	$      73,28,608	$           -11,058
IRR = 23%+1%*874461/(874461+11058) =	23.99%
NPV:
= PV of cash inflows-Initial investment = 47807251-34500000 =	$      1,33,07,251
Sensitivity to changes in price of new smart phone:
Increase in price by 1$
Sales in units of new smart phone		64000	106000	87000	78000	54000
Sales revenue ($486)		$   3,11,04,000	$   5,15,16,000	$ 4,22,82,000	$ 3,79,08,000	$ 2,62,44,000
Variable cost ($205)		$   1,31,20,000	$   2,17,30,000	$ 1,78,35,000	$ 1,59,90,000	$ 1,10,70,000
Fixed costs (other than depreciation)		$      50,10,000	$       50,10,000	$      50,10,000	$      50,10,000	$      50,10,000
Depreciation (7 Year MACRS) %		14.29	24.49	17.49	12.49	8.93
Depreciation expense		$      49,30,050	$       84,49,050	$      60,34,050	$      43,09,050	$      30,80,850	$   2,68,03,050
EBIT		$      80,43,950	$   1,63,26,950	$ 1,34,02,950	$ 1,25,98,950	$      70,83,150
Tax at 35%		$      28,15,383	$       57,14,433	$      46,91,033	$      44,09,633	$      24,79,103
NOPAT		$      52,28,568	$   1,06,12,518	$      87,11,918	$      81,89,318	$      46,04,048
Add: Depreciation		$      49,30,050	$       84,49,050	$      60,34,050	$      43,09,050	$      30,80,850
OCF		$   1,01,58,618	$   1,90,61,568	$ 1,47,45,968	$ 1,24,98,368	$      76,84,898
Capital expenditure	$      3,45,00,000
Change in NWC		$      62,20,800	$       40,82,400	$    -18,46,800	$      -8,74,800	$    -23,32,800
Release of NWC						$      52,48,800
After tax salvage value = [5500000+(7696950-5500000)*35%]						$      62,68,933
After tax annual cash flows	$     -3,45,00,000	$      39,37,818	$   1,49,79,168	$ 1,65,92,768	$ 1,33,73,168	$ 2,15,35,430
PVIF at 12% [PVIF = 1/1.12^n]	1	0.89286	0.79719	0.71178	0.63552	0.56743
PV at 12%	$     -3,45,00,000	$      35,15,908	$   1,19,41,301	$ 1,18,10,404	$      84,98,890	$ 1,22,19,781	$   1,34,86,284
NPV	$      1,34,86,284
For 1$ increase in price, the NPV changes by 13486284-13307251 =			$         1,79,033
Sensitivity to changes in quantity of new smart phone:
Increase in quantity by 1 unit
Sales in units of new smart phone		64001	106001	87001	78001	54001
Sales revenue ($485)		$   3,10,40,485	$   5,14,10,485	$ 4,21,95,485	$ 3,78,30,485	$ 2,61,90,485
Variable cost ($205)		$   1,31,20,205	$   2,17,30,205	$ 1,78,35,205	$ 1,59,90,205	$ 1,10,70,205
Fixed costs (other than depreciation)		$      50,10,000	$       50,10,000	$      50,10,000	$      50,10,000	$      50,10,000
Depreciation (7 Year MACRS) %		14.29	24.49	17.49	12.49	8.93
Depreciation expense		$      49,30,050	$       84,49,050	$      60,34,050	$      43,09,050	$      30,80,850	$   2,68,03,050
EBIT		$      79,80,230	$   1,62,21,230	$ 1,33,16,230	$ 1,25,21,230	$      70,29,430
Tax at 35%		$      27,93,081	$       56,77,431	$      46,60,681	$      43,82,431	$      24,60,301
NOPAT		$      51,87,150	$   1,05,43,800	$      86,55,550	$      81,38,800	$      45,69,130
Add: Depreciation		$      49,30,050	$       84,49,050	$      60,34,050	$      43,09,050	$      30,80,850
OCF		$   1,01,17,200	$   1,89,92,850	$ 1,46,89,600	$ 1,24,47,850	$      76,49,980
Capital expenditure	$      3,45,00,000
Change in NWC		$      62,08,097	$       40,74,000	$    -18,43,000	$      -8,73,000	$    -23,28,000
Release of NWC						$      52,38,097
After tax salvage value = [5500000+(7696950-5500000)*35%]						$      62,68,933
After tax annual cash flows	$     -3,45,00,000	$      39,09,103	$   1,49,18,850	$ 1,65,32,600	$ 1,33,20,850	$ 2,14,85,009
PVIF at 12% [PVIF = 1/1.12^n]	1	0.89286	0.79719	0.71178	0.63552	0.56743
PV at 12%	$     -3,45,00,000	$      34,90,270	$   1,18,93,215	$ 1,17,67,578	$      84,65,641	$ 1,21,91,171	$   1,33,07,875
NPV	$      1,33,07,875
For 1 unit increase in quantity, the NPV changes by 13307875-13307251 =			$                   624