A firms 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 6 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 this firm 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 5-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 6 percent per year over the 6 year life of the project.

This firms 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. I am assuming that the project has the same risk as the firm's current average project, and hence I am using the corporate cost of capital, 15 percent, for this project. I need to calculate the NINV, NPV, IRR, and payback period for the appliance control computer project. I need to create Best Case and Worst Case scenarios based on the Sales and variable cost numbers only. I am assuming the Best and Worst are +/- 25% for sales, and best/worst variable cost is 60% and 75%. I only need one best and one worst case scenario, combine the sales/vc best and worst case.

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, as thats the part that I am having trouble understanding, is how all the data gets put together.