Part I Lee Company is considering the purchase of equipment that would allow the company to add a new product to its line. The equipment is expected to cost $800,000 (paid in full at the beginning of the first year) with a 5 year useful life and a $200,000 salvage value. It will be depreciated on a straight-line basis. The company anticipates the following after-tax net incomes from the new product: The company has established the following required rates of return for investments based on their cost: Required: Set up an Excel spreadsheet that calculates the net present value of this equipment. To do this, you will first need to compute depreciation expense for the year You will then need to compute the after-tax net cash inflows for each year. Remember that depreciation expense is a tax deductible, non-cash expense. Also, assume that the company will receive in cash (from a sale transaction) the estimated salvage value of the equipment at the end of its useful life. Using the after-tax net cash inflows, you will be able to compute the net present value of the equipment. I will test your spreadsheet by changing one or more of the numerical parameters of the problem. Your spreadsheet should automatically re-compute the net present value for any parameter change. This means that you should establish a section of the worksheet where all the parameters are first entered. You can then build your spreadsheet using cell references from this section. For example, the parameter section of your worksheet might appear as: Cost Salvage Value Useful Life Interest Rate Year 1 net income Year 2 net income Year 3 net income Year 4 net income Required: Set up an Excel spreadsheet that calculates the net present value of this equipment. To do this, you will first need to compute depreciation expense for the year You will then need to compute the after-tax net cash inflows for each year. Remember that depreciation expense is a tax deductible, non-cash expense. Also, assume that the company will receive in cash (from a sale transaction) the estimated salvage value of the equipment at the end of its useful life. Using the after-tax net cash inflows, you will be able to compute the net present value of the equipment. I will test your spreadsheet by changing one or more of the numerical parameters of the problem. Your spreadsheet should automatically re-compute the net present value for any parameter change. This means that you should establish a section of the worksheet where all the parameters are first entered. You can then build your spreadsheet using cell references from this section. For ex ple, the parameter section of your worksheet might appear as: Cost Salvage Value Useful Life Interest Rate Year 1 net income Year 2 net income Year 3 net income Year 4 net income Year 5 net income Your spreadsheet must automatically indicate whether the project should be accepted or rejected. Part II Assume the same facts as before, except now the machine is anticipated to last 7 years and produce a $20,000 after-tax net income in year 6 and a $10,000 after-tax net income in year 7 . Also, the company now uses the double declining balance depreciation method. Using the same approach as above with only the aforementioned modifications, set up an Excel spreadsheet that calculates the equipment's net present value
