Question

Regis International Corporation (RIC), a Denver based technology company, has been applying its expertise in microprocessor technology to develop a small computer specifically designed to control home appliances. Once programmed, the computer would automatically control the heating and air-conditioning system, hot water heater, and even small appliances such as coffee makers. By increasing the energy efficiency of a home, the appliance control computer can save on costs hence pay for itself in a few years. This effort has now reached the stage where a decision on whether or not to go forward with production must be made.RIC’s marketing department plans to target sales of the appliance control computer to the owners of larger homes - the computer is cost effective only on homes with 2,000 or more square feet of heated/air-conditioned space. The marketing vice-president forecasts sales in 2018 to be $40 million and to increase by 7 percent per year. The engineering department has estimated that the firm would need a new manufacturing plant; this plant could be built and made ready for production in 1 year, once the “go” decision is made. The plant would require a 25-acre site, and RIC currently has an option to purchase a suitable tract of land for $1.2 million; the land option must be exercised on December 31, 2016. Building construction would begin in early 2017 and would continue through the end of 2017. The building, which would fall into the MACRS 39-year class (ignore any half-year convention), would cost an estimated $8 million, payable on December 31, 2017.The necessary manufacturing equipment would be installed late in 2017 and would be paid for on December 31, 2017. The equipment, which would fall into the MACRS 7-year class, would have a cost of $6.5 million, including transportation, plus another $500,000 for installation.The project would also require an initial investment in net working capital equal to 12 percent of the estimated sales in the first year. The initial working capital investment would be made on December 31, 2017, and on December 31 of each following year, net working capital would be increased by an amount equal to 12 percent of any sales increase expected during the coming year. The project’s estimated economic life is 6 years. At that time, the land is expected to have a market value of $1.7 million, the building a value of $1.0 million, and the equipment a value of $2 million. The production department has estimated the variable manufacturing costs would total 65 percent of dollar sales, and that fixed overhead costs, excluding depreciation, would be $8 million for the first year of operation. Fixed overhead costs, other than depreciation, are projected to increase with inflation which is expected to average 7 percent per year over the 6 year life of the project.RIC’s marginal tax rate (federal and state) is 40 percent; its weighted average cost of capital is 15%; and the company’s policy, for capital budgeting purposes, is to assume that operating cash flows occur at the end of each year. Since the plant would begin operations on January 1, 2018, the first operating cash flows would thus occur on December 31, 2018. The capital gains tax rate is the same as the ordinary income tax rate.As one of the company’s financial analysts, you have been assigned the task of supervising the capital budgeting analysis. For now, you may assume that the project has the same risk as the firm’s current average project, and hence you may use the corporate cost of capital, 15 percent, for this project. Calculate the NINV, NPV, IRR, and payback period for the appliance control computer project. Create Best Case and Worst Case scenarios based on the Sales number only. Assume the Best and Worst are +/- 25% for Revenues. Also, conduct a best/worst case scenario with operating expenses being 60% and 70% of sales. This will give you a base case, and 4 risk analysis scenarios.In addition to showing the values for the NINV, FCFs, NPV, IRR, and payback period, please provide a copy of your spreadsheet that shows your calculated values for the initial investment, the cash flows, and the salvage value.

Answer 1

BASE Case--Yr. ending Dec 31	2016	2017	2018	2019	2020	2021	2022	2023
Project life		0	1	2	3	4	5	6
1.Cost of land(1200000*1.07)		-1284000
2.Building construction cost		-8000000
3.Equipment(6500000+500000)		-7000000
Net working capital Reqd.
a.Sales projected			40000000	42800000	45796000	49001720	52431840	56102069
b.NWC (12%*Sales)		4800000	5136000	5495520	5880206.4	6291820.8	6732248.3	0
Beg. W/c		0	4800000	5136000	5495520	5880206.4	6291820.8	6732248
Ending W/c		4800000	5136000	5495520	5880206.4	6291820.8	6732248.3	0
4.Net Change in W/c		-4800000	-336000	-359520	-384686.4	-411614.4	-440427.5	6732248
After-tax Salvage:
Land(as per wkgs.)								1533600
Building								3311040
Equipment								1574920
5. Total after-tax salvage								6419560
5a.Initial Investment & CAPEX Cash flows		-21084000	-336000	-359520	-384686.4	-411614.4	-440427.5	13151808
Operating Cash flows:
6.Sales projected			40000000	42800000	45796000	49001720	52431840	56102069
7.Variable mfg.costs(6*65%)			-26000000	-27820000	-29767400	-31851118	-34080696	-36466345
8. Fixed OH			-8000000	-8560000	-9159200	-9800344	-10486368	-11220414
9. Depn.Bldgs(See wkgs.)			-196800	-205120	-205120	-205120	-205120	-205120
10.Depn.eqpt. (see wkgs.)			-1000300	-1714300	-1224300	-874300	-625100	-624400
11. EBIT(6+7+8+9+10)			4802900	4500580	5439980	6270838	7034556	7585790
12. Tax at 40%(11*40%)			-1921160	-1800232	-2175992	-2508335	-2813822	-3034316
13.EAT(11+12)			2881740	2700348	3263988	3762503	4220734	4551474
14. Add back depn.(9+10)			1197100	1919420	1429420	1079420	830220	829520
15.Operating cash flow(13+14)			4078840	4619768	4693408	4841923	5050954	5380994
16. Net annual cash flows(5a+15)		-21084000	3742840	4260248	4308722	4430308	4610526	18532803
17. PV F at 15%(1/(1+0.15)^n		1	0.86957	0.75614	0.65752	0.57175	0.49718	0.43233
18.PV at 15%(16*17)		-21084000	3254643	3221360	2833054	2533043	2292246	8012242
19. NPV(sum of Row 18)		1062589
20. IRR(of row 16)		16.44%
21. Pay back period:
16. Net annual cash flows(5a+15)		-21084000	3742840	4260248	4308722	4430308	4610526	18532803
22.Cumulative cash flows		-21084000	-17341160	-13080912	-8772190	-4341882	268644	18801447
23. Payback period=
4+(4341882/4610526)=
4.94
Years

Workings:

Land
Cost (at end yr.0/beg yr.1)	1284000
Salvage	1700000
Gain on salvage	416000
Tax on gain(416000*40%)	166400
After-tax salvage(1700000-166400)	1533600
Building--MACRS 39 year class
	Depn.%	$ depn(%*Cost)	Carrying value
Cost			8000000
Depn.Yr.1	2.46%	196800	7803200
Depn.Yrs 2-6	2.564%	1025600	6777600
Salvage			1000000
Loss on salvage(6777600-1000000)			5777600
Tax saved on loss(5777600*40%)			2311040
After-tax salvage(1000000+2311040)			3311040
Equipment--Macrs 7-year class
	Depn.%	$ depn(%*Cost)	Carrying value
Cost			7000000
Year ---1	14.29%	1000300	5999700
2	24.49%	1714300	4285400
3	17.49%	1224300	3061100
4	12.49%	874300	2186800
5	8.93%	625100	1561700
6	8.92%	624400	937300
Salvage			2000000
Gain on salvage(2000000-937300)			1062700
Tax on gain(1062700*40%)			425080
After-tax salvage(2000000+425080)			1574920

Summary
		+25%sales	-25% sales	Opg. Costs 60%	Opg. Costs 70%
	Base Case	Best Case	Worst case	Best Case	Worst case
NPV	1062589	9491217	-7366040	6330482	-4205304
IRR	16.44%	26.72%	3.75%	23.43%	9.16%
P/Back (in yrs.)	4.94	3.51	5.73	3.83	5.39