You have been hired by Dynamic Company, Inc. to help them evaluate two projects and help them select the best one. Project A represents an expansion of an existing product line. Project B involves an entirely new product line. They have provided you with these basic assumptions:

• The weighted average cost of capital is 10.0%
• Expected inflation by year is given in the spreadsheet. That is already built into the variable expenses that are a percent of sales, but not the fixed expenses.
• Both projects will last for six years and the equipment will last until the end of the project.
• They use straight-line depreciation for both book and tax depreciation with no salvage value.
• Dynamic Company will provide information about initial sales expected and annual increases or decreases in sales.
• You will also be provided with select information about expense to allow you to forecast those expenses.
• All fixed expenses except for depreciation will increase at the rate of inflation each year.
• There are not working capital changes.
• The companys average tax rate is 25%.

Additional Instructions:

• Labor rates and selling prices per unit should always be rounded to dollars and cents.
• Revenue and individual expenses should be rounded to the nearest whole dollar each year.
• If the depreciation expense does not total the exact amount adjust it in year 6.
• All percentages should be rounded to the nearest tenth of a percent.
• Show all of your calculations even if you need to provide schedules for the calculations.
• Model your cash flow calculations format after the example I provided to you in class.

Required:

1. Use the template provided for the project. Add your last name followed by the first initial of your name to the end of the file name. If it not properly formatted your grade will be reduced by ten points.
2. Calculate the Net Present Value for each project
3. Calculate the Internal Rate of Return for each project.
4. Calculate the Modified Internal Rate of Return for each project.
5. Calculate the Payback Period for each project.
6. Calculate the Discounted Payback Period for each project.
7. Provide a table for each project showing the Net Present Value and Internal Rate of Return for each of each of the following conditions and list them in the Summary provided:
   1. With the stated assumptions
   2. With sales 10% higher
   3. With sales 10% lower
8. Determine which project you would recommend and justify your answer completely.
9. Relate which project you feel is more risky with be sure to justify your answer completely.

C7 fx . B D E F. G 1 J K L M 1 Summary of Calculations: 2 Keep the formatting of cells on this sheet intact and link the cells to your spreadsheet calculations. 3 4 Summary 5 Project A Project B 6 7 Net present value These two cells will highlight green if your net present value is exactly the right ar 8 Internal rate of return Round to nearest tenth of a percent. 9 Modified internal rate of return Round to nearest tenth of a percent. 10 Payback Round to the nearest tenth of a year. 11 Discounted payback Round to the nearest tenth of a year. 12 Total cash flow These two cells will highlight green if your total net cash flow is is exactly the right 13 14 Project A Project B 15 NPV IRR NPV IRR 16 Sales - 10% less 17 Targeted sales 18 Sales - 10% more 19 Summary Project A Project B Ready 5:45 PM O Type here to search 7/25/2021 + EO 140% 1 O TT 15 6 * 85F D9 fr 19.6% B C D E F G H 1 J K L M . N Year 3 0 1 2. 4 5 6 $ 200,000 0.0% 3.1% 3.1% 3.1% 3.1% 3.1% 5,000 19.6% 11.0% 5.0% -5.0% -1.0% $ 49.00 . 1 Project A: 2 3 4 5 Equipment cost 6 Inflation rate 7 8 Unit sales 9 Projected % change in unit sales 10 Selling price per unit 11 Projected % change in selling prices 12 Direct materials as % of sales 13 Direct labor hours per unit produced 14 Direct labor rate per hour with benefits 15 Increase in direct labor rate from prior year 16 Variable overhead as a % of sales 17 Fixed overhead excluding depreciation 18 Variable selling and administrative exp as % of sales 19 Fixed selling and administrative expense 20 Weighted average cost of capital 21 22 Calculations: 23 24 25 Summary Project A Project B + 40.1% 0.75 15.05 2.1% 39.6% 0.749 3.0% 39.2% 0.748 3.0% 38.9% 0.747 1.0% 38.9% 0.746 1.0% 38.9% 0.746 $ 3.0% 5.0% 3.0% 5.0% 3.0% 5.0% 3.0% 5.5% 3.0% 5.4% $ 5.0% 20,000 2.9% 10,100 10% 3.0% 3.0% 3.0% 3.1% 3.2% $ O Ready EO 110% Type here to search O TT 6 85F 5:45 PM 7/25/2021 D20 fic 2.9% B E F G H I J K L M N Year mo 0 1 2 3 4 5 6 $ 249,000 0.0% 3.1% 3.1% 3.1% 3.1% 3.1% 3,000 40.0% 35.0% 30.0% 20.0% 20.0% $ 44.95 1 Project B: 2 3 Assumptions: 4 5 6 7 Equipment cost 8 Inflation rate 9 10 Unit sales 11 Projected % change in unit sales 12 Selling price per unit 13 Projected % change in selling prices 14 Direct materials as % of sales 15 Direct labor hours per unit produced 16 Direct labor rate per hour with benefits 17 Increase in direct labor rate from prior year 18 Variable overhead as a % of sales 19 Fixed overhead excluding depreciation 20 Variable selling and administrative exp as % of sales 21 Fixed selling and administrative expense 22 Weighted average cost of capital 23 24 Calculations: 25 Summary Project A Project B (+ Ready EO 34.9% 0.5 15.05 2.1% 34.4% 0.499 3.0% 34.0% 0.498 3.0% 33.6% 0.497 1.0% 33.2% 0.496 1.0% 33.2% 0.496 $ 3.1% 4.0% 3.1% 4.0% 3.1% 4.0% 3.1% 4.0% 3.1% 4.0% 4.1% 20,000 $ 2.9% 2.9% 2.9% 3.0% 3.1% 3.2% $ 10,100 10% + 110% 1 O Type here to search DI 6 ? 85F 1) 5:45 PM 7/25/2021