\"The Horace Newtech Company is considering the introduction of anew product. Generally, the company's products have a life of about5 years, after which they are deleted from the range of productsthat the company sells. The new product requires the purchase ofnew equipment costing $9,500,000, including freight andinstallation charges. The useful life of the equipment is 5 years,with an estimated resale of equipment of $2,750,000 at the end ofthat period. The equipment will be fully depreciated to zero valueusing the straight line (prime cost) method.
The new product will be manufactured in a factory already ownedby the company. This factory is currently being rented to anothercompany under a lease agreement that has 5 years to run andprovides for an annual rental of $190,000. Under the leaseagreement, the Horace Newtech Company can cancel the lease byimmediately (year 0) paying the lessee compensation equal to 1year's rental payment.
It is expected that the product will involve the company inmarket research expenditures that will amount to $750,000 duringthe first year the product is on the market. Additions to currentassets (net working capital) will require $310,000 at thecommencement of the project and are assumed to be fully recoverableat the end of the fifth year.
The new product is expected to generate sales revenue asfollows:
Year 1: $3,250,000
Year 2: $3,750,000
Year 3: $3,500,000
Year 4: $3,250,000
Year 5: $1,750,000
It is assumed that all cash flows are received at the end ofeach year. The corporate tax rate is 25%.
Horace Newtech Company has an equity beta of 0.5. Its capitalstructure consists of equal amounts of equity and debt. The riskfree rate is 5%. The debt has a pre-tax yield of 9% and theexpected rate of return on the market index is 18%.
Using the inputs suggested in the proposed investment, design anExcel financial model that can help to evaluate the project. Themodel should enable Horace Newtech Company to consider the projectand forecast the net cash flows. 5 marks will be allocated for thepresentation and clarity of your model (e.g., if there are clearheadings, clear arrangement for input cells, appropriate use ofcolors, have some degree of flexibility, generate warning messagesfor wrong user inputs, etc.).
Answer the following questions using your Excel financialmodel:
a. What is Horace Newtech Company ’s weighted averagecost of capital (WACC)?
b. Estimate the free cash flows of the project.
c. What is the net present value (NPV) and internalrate of return (IRR) of the project? Should Horace Newtech Companyundertake the project based on NPV? How about IRR? Do both NPV andIRR lead to the same decision?
d. Draw a line graph to illustrate the sensitivity ofthe NPV to changes in WACC? The graph should have a chart title, aswell as x- and y-axis labels. Briefly explain the relationshipbetween WACC and NPV.\"
(Please help me )
