with excel formula

with excel formula pleas

You have been hired as a consultant for Pristine Urban-Tech Zither, hc. (PUTZ), manufacturers of fine zithers. The market for zithers is growing quickly. The company bought some land three years ago for $2.1 million in anticipation of using it as a toxic waste dump site but has recently hired another company to handle all toxic materials. Based on a recent appraisal, the company believes it could sell the land for $2.3 million on an attertax basis. n four years, the land could be sold for $2.4 million after taxes. The company also hired a marketing firm to analyee the zither market, at a cost of $125,000. An excerpt ofthe marketing report is as follows: The zither industry will have a rapid expansion in the next four years. With the brand name recognition that PUTZ brings to bear, we feel that the company will be able to sell 3.600,4,300,5200, and 3 900 units each year for the next four years, respectively. Again, capitalizing on the name recognition of PUTZ we eel that a premium price of $750 can be charged for each zither. Because zithers appear to be a fad, we feel at the end of the four-year period, sales should be discontinued. PUTZ believes that fixed costs for the project will be $415,000 per year, and variable costs are 15 percent of sales. The equipment necessary for production will cost $3.5 million and will be depreciated according to a three-year MACRS schedule. At the end ofthe project, the equipment can be scrapped for $350,000. Net working capital of $125 000 will be required immediately. PUTZhas a 22 percent tax rate, and the required retum on the project is 13 percent. What is the NPVofthe project? Original cost of land Current land value Land value in 4 years Marketing study $ $ $ $ 2,100 000 2,300 DOO 2,400 DOO 125 000 Year 1 3 600 Year 2 4,300 Year 3 5,200 Year 4 3,900 $ $ Uhits sold Sales price Fixed costs Variable costs Equipment costs Pretax salage value Net working capital Tax rate Required return $ $ $ 750 415 000 15% 3,500 DOO 350 DOO 125 DOO 22% 13% MACRS pero ntages Year 1 33.33% Year 2 44.45% Year 3 14.81% Year 4 7.41% Complete the following analysis. Do not hard code values in your calculations. Enter a "0" for any cost that should not be included. You must use the built-in Excel functionto calculate the NPV. Aftertax cash low Pretax salvage value Taxes Aftertax salage value Year o Year 1 Year 2 Year 3 Year 4 Revenues Fixed costs Variable costs Depreciation Taxes Net income Equipment Land Net working capital O CF Total cash 10w NPV