(GPC) Guyton Products Company is a high-tech "lab-bench-to-market" development company that takes cutting-edge reseach advances and translates them into consumer products. GPC has recently licensed a nano-fabrication coating technology from a university that promises to significantly increase the efficiency with which solar energy can be harvested and stored as heat.

GPC is considering using this technology in its existing product line. In the "Project S" for "solid", the technology would be used to coat rock and concrete structures to be used as passive heat sinks and sources of energy-efficient residential and commercial buildings.

The cost of required equipment to manufacture the solid coaters in Project S is Rs 8,750. The solid coaters would sell for Rs2.5 per unit, and GPC believes that after Year 1, the sales price will decrease at the rate of 5% every year.The firm believes it could sell 10,000 units every year. The variable costs would amount to Rs1.64 per unit and after Year 1, the variable costs will increase at the inflation rate of 6%.The company's non-variable costs would be Rs 1815 at Year 1 and would increase at 4%.The project S would require net working capital at the beginning of each year in an amount equal to 5% of the year's projected sales; for example, NWC0 = 5%(Sales1).The projects would have a life of 4 years.The estimated market value of the equipment at the end of the project's 4-year life is Rs 433

If the project is undertaken, it must be continued for the entire 4 years.Also, the project's returns is expected to be highly correlated with returns on the firm's other assets.The equipments would be depreciated over a 4-year period, using depreciation rates 33.33%, 44.45%, 14.81% and 7.41% for all the 4 years respectively.The tax rate is 40%.Its cost of capital is 10% for average-risk projects, defined as projects with a coefficient of variation of NPV between 0.8 and 1.2.

a.Develop a spreadsheet model, andestimate the net cash flows for each year. Based on these cash flows, find the project's NPV, Profitability Index, IRR, payback and discounted payback period.Do these indicators suggest the project should be undertaken?

b.Now conduct a sensitivity analysis to determine the sensitivity of NPV to changes in the equipment cost, sales price, number of units sold, variable costs per unit, non variable cost and WACC individually.Set these variables' values at 30% above and below their base-case values. Include a bar graph in your analysis to understand the variable that has the highest impact on NPV. (Perform the sensitivity analysis using 'Data Table' tool in excel)

c. Now conduct an NPV break-even analysis by findingout level of an input that produces an NPV of exactly zero. (Inputs being sales price per unit, variable cost per unit, non variable cost, number of units sold and WACC individually. (NPV break-even analysis can be prformed using 'Goal Seek' tool of excel)

d.Now conduct a scenario analysis.

>> Assume that there is a 25% probability each of best-case condition andworst-case conditions, and a 50% probability of base-case conditions.

>> The number of units sold in year 1 would change by 15%, variable cost per unit by 9%, non variable cost by 10%, tax rate by 25%, sales price by 17% in best and worst case scenarios respectively.

>> % change annually after 1 year, for Project S

*units sold in worst case scenario would be -5% & in best scenario would be 5% as against 0% in base case.

*variable would increase with inflation rate of 6% in all scenarios.

*sales price will decrease by 5%in all scenarios.

e. If the project appears to be more or less risky than an average project, find its risk-adjusted NPV. Low-risk projects are evaluated with a rate of 8%, and high-risk projects at 13%.

f.On the basis of information in the problem, would you recommend that the project be accepted?

g. State all the assumptions very clearly

Solve this on excel, please