EXCEL HELP NPV IRR CF's WACC HELP

*More information at the bottom!!*

GPS TRANSMITTER PROJECTIONS

HMT would not have to construct a new factory to produce the GPS transmitter, as production would be outsourced over the projects life to a reliable supplier in Israel. Sales were forecasted at 5,000 units in the first year and then expected to grow at 10 per cent per year for the next four years before levelling off for the remaining five years. The proposed list price for the transmitter was $500, although that was believed to be too low by some members of NPRC. The Israeli contractor quoted a cost of $350, which included packaging and transportation to HMTs warehouse facility in Toronto. Additional net working capital of $200,000 would be required, which would be liquidated at the end of the project. Incremental selling and administration costs were estimated to be $750,000 per year, and corporate overhead was expected to rise by $250,000. The patent and software could be purchased from Hu for $2,500,000 ($1,500,000 for the patent and $1,000,000 for the software). In addition, $50,000 was estimated to be required for software maintenance each year for the life of the product. The capital cost allowance rate for patents was 25 per cent, and software could be written off immediately for tax purposes.

SURVEILLANCE AIRCRAFT PROJECTIONS

HMT would have had to construct a new factory to produce the surveillance aircraft. The factory could produce up to 1,000 units per year over the 10-year life of the product. Land costing up to $250,000 would be required along with building and equipment costing of $750,000 and $1,500,000, respectively. At the end of 10 years, the land could be sold for approximately $450,000, while the building and equipment were estimated to be worth $300,000 and $150,000. The building was subject to a capital cost allowance rate of four per cent, while the equipment was subject to a 20 per cent capital cost allowance rate. Only half of all taxable capital gains or losses on land were subject to tax. In addition to land, building and equipment, $350,000 in net working capital would have to be invested, which would be liquidated at the end of the project.

Sales were estimated to be only 250 units in the first year, but then expected to grow to 500 in the second year and 1,000 units by the third year before stabilizing. The list price for a surveillance airplane was $60,000, and the estimated cost of manufacturing an aircraft was $54,000, which included direct materials, direct labour and manufacturing overhead. Incremental selling and administration costs of $2,500,000 per year would also be incurred along with $750,000 in incremental corporate overhead.

COST OF CAPITAL CALCULATIONS

Rogers realized that small errors in estimating the different costs of capital for each of the products would have a dramatic influence on the NPV calculated, so he devoted considerable time to researching these new industries. For the GPS transmitter, Rogers carefully researched other microcomputer component manufacturers such as hard drive, microchip and liquid crystal display (LCD) producers. It was thought that this industry was substantially riskier than that of surveillance aircraft, as sales were dependent on consumer and business demand, which varied considerably with the business cycle. Demand for surveillance aircraft was largely dependent on government purchasing, which was usually more stable. Five public companies in the industry with similar debt ratios were identified (see Exhibit 1).

As HMT had limited experience in the microcomputer components industry, it decided to add three per cent to the cost of capital to allow for additional project risk. The industry for surveillance aircraft was much less developed as the technology was relatively new, having its roots in the beginnings of the war on terrorism after the bombing of the World Trade Centre on September 11, 2001. Sky Hawk Ltd. was the only public company in the industry and had been in existence for five years (see Exhibit 2). Sky Hawk recently issued 10-year bonds to fund a major capital expansion. The bonds had a coupon rate of 6.77 per cent and traded at $98.56. The 20-year Government of Canada spot rate from the yield curve was four per cent. The market risk premium was estimated at five per cent.

Due to its size and strong financial position, HMT had little difficulty raising new capital, which was reflected in its issuance costs. The cost of issuing new equity was five per cent, and the cost of raising debt was two per cent. It was company policy to include issuance costs in the cost of capital, but it generated all new equity capital using retained earnings, due to their low issuance costs and in order to avoid disrupting the balance of control at the board level. HMT had a marginal tax rate of approximately 30 per cent and a long-term debt to total capital target of 40 per cent.

What I am looking for and this to be in EXCEL is : Calculation of CF's, WACC, NPV, IRR. If you include Initial Investment and terminal that would be great.

UPDATE : Just uploaded it to IMGUR for more information! Right there -------------------------> http://imgur.com/a/nUoWp