You are the chief financial officer (CFO) of Palermo Products. You have been asked to analyze investment in new equipment to manufacture a new product. You will be asked to recommend whether or not the firm should make the investment based on your analysis. The equipment will be used in production during the first quarter. The firms industrial engineers estimate the required capital investment in equipment will most probably be $550,000, with a minimum of $450,000 and a maximum of $600,000. They also estimate that the variable cost of producing the product will be between $6.00/unit and $7.50/unit, with any value in that range equally likely. MACRS will be used to depreciate the book value of the equipment. According to the regulations of the Internal Revenue Service, the equipment has a depreciable life of seven years. The company expects to put the equipment into operation during the first quarter of the year of its investment in the equipment. Although the depreciable life of the equipment is seven years according to the IRS, the company expects that the useful life of the equipment and the market for the product is five years. At the end of five years, the firm expects to be able to sell the equipment for 10% of its cost. If the equipments selling price is less than its book value at the end of five years, there will be a capital loss on which the firm will realize a tax benefit. The firms marketing division has forecast the total, industry-wide market for the type of product to be 150,000 units each year for the next five years. The standard forecast error for the total, industry-wide demand is 15,000 units each year. Palermos share of the total market will depend on the products selling price. The marketing division estimates that at a selling price of $18.00/unit, the companys share of the market would be 25%. They also estimate that increasing the selling price above $18.00/unit will reduce the market share according to the relation where MS = market share (percent) and SP = selling price ($/unit). Thus, at a selling price of $19/unit, the predicted market share would be 24%; at a selling price of $20/unit, the predicted market share would be 21%, and so forth. (Use the formula given to calculate market share as a function of selling price. There is no need to derive an alternate formula.) Non-production costs will be 35% of sales receipts. Base your analysis on the 5-year useful life of the equipment. Use a discount rate of 14.00%, based on the firms weighted average cost of capital and the risk of the investment. Use a reinvestment rate of 12.5%. Use a tax rate of 40% for normal income and 30% for long-term capital gains. These rates are a composite of federal, state, and local tax rates. a. Create a spreadsheet model to determine the net present value, internal rate of return, modified internal rate of return, and years to break even under the most probable conditions. Your spreadsheet should include all of the data values neatly organized and properly labeled at the top. Program the analysis for the most probable set of conditions immediately below the data value section. Use Solver to determine the optimum selling price for the most probable conditions. b. Using the optimum selling price for the most probable conditions, apply Excels Scenario Manager to determine the net present value, internal rate of return, modified internal rate of return, and years to break even under the best-on-best and worst-on-worst scenarios. For the best-on-best and worst-on-worst scenarios, set the total markets at two standard forecast errors above and below the predicted total markets. Copy the results from the Scenario Manager to a separate worksheet and edit the copy to make an effective presentation of the results. c. Using the most-probable values for the total market, capital investment, and unit or variable cost, create a one-variable input table to determine the effect of selling price on the values of market share, NPV, IRR, and MIRR at the end of five years and the years to break even. Use selling prices from $18/unit to $21/unit in increments of $0.50/unit. d. Use the results from part c to create a chart showing the effect of selling price on the net present value. Note on the chart that the results are for the most probable values of 150,000 units/year for the total market, $550,000 for the capital investment, and $6.75/unit for the variable cost. Format the chart to make an effective management presentation. Show the curve as a smooth curve without a point.