ile Edit View Go Tools Window Help Project 3 capital budgeting sp 2022-1.doc (page 1 of 2) Qs Finance 410: Project 3 Spring 2022 Instructions: You must use Excel to solve all problems, and email to me on or before 4/27/22 by 11:59 pm. The spreadsheet should be set up similar to the one on pages 534 and 535 of your textbook. 1. (20 points) Predator Inc. (PI) is considering a short term project with a few special buyers. The project is expected to last 3 years and then be terminated as this a short term investment. Fixed costs per year including rental of the building, insurance, etc., will cost $525,000 per year. The equipment for the facility will cost $560,000 and shipping and installation costs for the equipment are $75,000. Depreciation is MACRS scale as a 3 year asset (page 586 textbook). It is estimated the equipment can be sold for $160,000 at the end of the 3rd year. In order to operate the facility the company will have to train employees at an after-tax cost of $25,000 and incur additional net operating working capital equal to 0.4% of the upcoming years sales revenue. All net working capital will be liquidated at the end of the project and the company spent $50,000 last year on specialized technology research. In addition, The company's marginal tax rate is 25%, and they expect the following units sold, etc.: Year units sold Sales price per unit Costs (expenses) per unit 1 140,000 18.75 12.60 For the next two years (to year 3), the company expects units sold to rise by 6% per year, sales price per unit to rise by 5% per year due to inflation, and variable costs per unit to rise by 4.50% per year. In addition, it is expected that for each year, the sale of this product will be complementary to an additional product they already sell, thus bringing an additional revenue to the alternate product of 0.5% of total sales revenue of this project. The company's WACC is 15%. A) (65 points) Set up the spreadsheet for the calculations from year 0 to year 3. If the company's required rate of return is 15%, what is the NPV and IRR of this project? This is considered the base case for part C below. B) (20 points) The CFO expressed concern the base case information might be too optimistic or pessimistic. She wants to know how the NPV will be affected under the following scenarios. What is the NPV under the following cases? (Post each of these analyses on separate sheets in the same spreadsheet file). Best case Worst Case Unit Sales rise by 8.0% per year rise by only 1% per year Sales price per unit rise by 6% per year fall by 1% per year Costs per unit rise by 4% per year rise by 5% per year C) (15 points) If there is a 10% probability of the best case, 55% probability of the base case, and 35% probability of the worst case scenario, what is the expected NPV, standard deviation, and coefficient of variation (CV) of this project? If the typical CV for projects is between 1.0 and 2.0, is this riskier than the average project? Do you think this project should be accepted? MacBoo ile Edit View Go Tools Window Help Project 3 capital budgeting sp 2022-1.doc (page 1 of 2) Qs Finance 410: Project 3 Spring 2022 Instructions: You must use Excel to solve all problems, and email to me on or before 4/27/22 by 11:59 pm. The spreadsheet should be set up similar to the one on pages 534 and 535 of your textbook. 1. (20 points) Predator Inc. (PI) is considering a short term project with a few special buyers. The project is expected to last 3 years and then be terminated as this a short term investment. Fixed costs per year including rental of the building, insurance, etc., will cost $525,000 per year. The equipment for the facility will cost $560,000 and shipping and installation costs for the equipment are $75,000. Depreciation is MACRS scale as a 3 year asset (page 586 textbook). It is estimated the equipment can be sold for $160,000 at the end of the 3rd year. In order to operate the facility the company will have to train employees at an after-tax cost of $25,000 and incur additional net operating working capital equal to 0.4% of the upcoming years sales revenue. All net working capital will be liquidated at the end of the project and the company spent $50,000 last year on specialized technology research. In addition, The company's marginal tax rate is 25%, and they expect the following units sold, etc.: Year units sold Sales price per unit Costs (expenses) per unit 1 140,000 18.75 12.60 For the next two years (to year 3), the company expects units sold to rise by 6% per year, sales price per unit to rise by 5% per year due to inflation, and variable costs per unit to rise by 4.50% per year. In addition, it is expected that for each year, the sale of this product will be complementary to an additional product they already sell, thus bringing an additional revenue to the alternate product of 0.5% of total sales revenue of this project. The company's WACC is 15%. A) (65 points) Set up the spreadsheet for the calculations from year 0 to year 3. If the company's required rate of return is 15%, what is the NPV and IRR of this project? This is considered the base case for part C below. B) (20 points) The CFO expressed concern the base case information might be too optimistic or pessimistic. She wants to know how the NPV will be affected under the following scenarios. What is the NPV under the following cases? (Post each of these analyses on separate sheets in the same spreadsheet file). Best case Worst Case Unit Sales rise by 8.0% per year rise by only 1% per year Sales price per unit rise by 6% per year fall by 1% per year Costs per unit rise by 4% per year rise by 5% per year C) (15 points) If there is a 10% probability of the best case, 55% probability of the base case, and 35% probability of the worst case scenario, what is the expected NPV, standard deviation, and coefficient of variation (CV) of this project? If the typical CV for projects is between 1.0 and 2.0, is this riskier than the average project? Do you think this project should be accepted? MacBoo