W3 Excel Exercise - Chp 8 + 9 solution

STRATEGIC INVESTMENT DECISIONS Example: You are considered investing in a new machine to produce a new type of product at your firm. The machine will cost $1M and will have a useful life of 5 years. Straight-line depreciation is allowed for accounting and tax purposes. The machine is expected to generate cash flows (before-tax) of $350K in year 1, $500K in years 2 and 3, $350K in year 4, and $300K in year 5. Your company has a tax rate of 25%. Your company's WACC (return) is 10%. How can you evaluate this investment decision? There are many different tools we can use to evaluate this decisions. But first, let's organize the cash flows for this investment: Year Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Investment -$ 1,000,000 Cash flows $ 350,000 $ 500,000 $ 500,000 $ 350,000 $ 300,000 Depreciation -$ 200,000 -$ 200,000 -$ 200,000 -$ 200,000 -$ 200,000 Taxable Profit $ 150,000 $ 300,000 $ 300,000 $ 150,000 $ 100,000 Tax (25%) -$ 37,500 -$ 75,000 -$ 75,000 -$ 37,500 -$ 25,000 After-tax Profit -$ 1,000,000 $ 112,500 $ 225,000 $ 225,000 $ 112,500 $ 75,000 Add: Depreciation $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 200,000 After-tax Cash Flow -$ 1,000,000 $ 312,500 $ 425,000 $ 425,000 $ 312,500 $ 275,000 TOOLS: 1. PAYBACK PERIOD In this method, we look at how many years it takes to get our money back, without taking the time value of money into consideration (more on that later). Payback Method Year 0 -$ 1,000,000 In Year 0, we spend $1M on the machine. Year 1 -$ 687,500 In year 1, we get $312,500 of our money back (After-tax), but we are still $687.5K in the hole. Year 2 -$ 262,500 In year 2, we get $425K back, but still need to recover $262.5K at the end of the year. Year 3 $ 162,500 By the end of year 3, we have gotten all of our money back, and now start getting additional return. Year 4 $ 475,000 Year 5 $ 750,000 So, using the payback method, we could say that somewhere between Year 2 and Year 3 we would get our money back.

2. DISCOUNTED PAYBACK METHOD The payback method's main advantage is its very simple application. However, it's main flaw is that it does not take into account the time value of money. This investment has a lifespan of five years - there are borrowing costs (the cost of capital) associated with this project. The company needs to earn at least its cost of capital (10%) over the life of the asset. If I have $1M, and in the first year I want 10% return, then I want to earn $100,000 or 10% x $1,000,000. My end of year balance is $1.1M In the second year, I want to earn 10% return again, but my new base is now $1.1M, so I want to have a return of $110,000 or 10% x $1.1M. This is equal to (1+10%)^2 X $1M. This is the effect of compounding. Compounding = (1+discount rate)^number of periods With these principles, we can discount our cash flows back to the present: Year 0 1 2 3 4 5 After-tax Cash Flow -$ 1,000,000 $ 312,500 $ 425,000 $ 425,000 $ 312,500 $ 275,000 Discount rate factor 1.00 1.10 1.21 1.33 1.46 1.61 Discounted cash flow -$ 1,000,000 $ 284,091 $ 351,240 $ 319,309 $ 213,442 $ 170,753 In year 1, we take $312,500 and discount it by 1.10 and get $284,091. What this means is that in today's dollars, $312.5K in one year is worth $284.091K to us because today we are expected to earn 10% on the money we invest. For year 2, we have to take into account two years of earning 10% in return (compounding), so $425K is discounted by (1+10%)^2. Now, we can use the discounted payback method: DISCOUNTED PAYBACK METHOD Year 0 -$ 1,000,000 In Year 0, we spend $1M on the machine. Year 1 -$ 715,909 In year 1, we get $284,091 of our money back (After-tax and discounted), but we are still $715.9K in the hole. Year 2 -$ 364,669 In year 2, we get $251.24K back, but still need to recover $364.669K at the end of the year. Year 3 -$ 45,361 By the end of year 3, we still need to get $-45.361K back from our investment. Year 4 $ 168,081 By year 4, we have made our money back on a discounted, after-tax basis. Year 5 $ 338,834 So, using the discounted payback method, we could say that somewhere between Year 3 and Year 4 we would get our money back. We also need to take compounding into account:

PUTTING IT ALL TOGETHER: Year Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Investment -$ 1,000,000 Cash flows $ 350,000 $ 500,000 $ 500,000 $ 350,000 $ 300,000 Depreciation -$ 200,000 -$ 200,000 -$ 200,000 -$ 200,000 -$ 200,000 Taxable Profit $ 150,000 $ 300,000 $ 300,000 $ 150,000 $ 100,000 Tax (25%) -$ 37,500 -$ 75,000 -$ 75,000 -$ 37,500 -$ 25,000 After-tax Profit $ 112,500 $ 225,000 $ 225,000 $ 112,500 $ 75,000 Add: Depreciation $ 200,000 $ 200,000 $ 200,000 $ 200,000 $ 200,000 After-tax Cash Flow -$ 1,000,000 $ 312,500 $ 425,000 $ 425,000 $ 312,500 $ 275,000 Discount factor (10%) 1.00 1.10 1.21 1.33 1.46 1.61 Discounted CF -$ 1,000,000 $ 284,091 $ 351,240 $ 319,309 $ 213,442 $ 170,753 Payback Period Between Year 2 and Year 3 Discounted Payback Between Year 3 and Year 4 NPV $ 338,834 PI 1.34 IRR 22.88%

Capital Cost Allowance "Depreciation for tax purposes, not necessarily the same as depreciation under IFRS." Half Year Rule CRA's requirement to figure CCA on only 50% of an asset's installed cost for its first year of use. Purchase January 2017 vs. December 2017 Example Question Example Question Purchased equipment at the beginning of 2013 for $50,000 plus installation costs of $5,000. Expected to have 15-year economic life with salvage value of $5,000. The equipment belongs in class 8 with a rate of 20% per year. Assume straight-line depreciation for accounting purposes. Tax rate is 40%. Equipment will be the only Class 8 asset used by the company; after fifteen years the asset pool will be terminated upon the sale of equipment. Calculate the book value or tax base of the equipment for CCA and Accounting purposes after five years. Ignore disposal of the asset. Tax purposes Value of Equipment $ 55,000 CCA Rate 20% 2013 2014 2015 2016 2017 Beginning Value $ 55,000 $ 49,500 $ 39,600 $ 31,680 $ 25,344 Half Year Amount $ 27,500 CCA Amount $ 5,500 $ 9,900 $ 7,920 $ 6,336 $ 5,069 Ending Value (UCC) $ 49,500 $ 39,600 $ 31,680 $ 25,344 $ 20,275 Accounting Purposes Value of Equipment $ 55,000 Salvage Value $ 5,000 Economic life 15 Depreciation $ 3,333 2013 2014 2015 2016 2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027 Beginning Value $ 55,000 $ 51,667 $ 48,333 $ 45,000 $ 41,667 $ 38,333 $ 35,000 $ 31,667 $ 28,333 $ 25,000 $ 21,667 $ 18,333 $ 15,000 $ 11,667 $ 8,333 Depreciation $ 3,333 $ 3,333 $ 3,333 $ 3,333 $ 3,333 $ 3,333 $ 3,333 $ 3,333 $ 3,333 $ 3,333 $ 3,333 $ 3,333 $ 3,333 $ 3,333 $ 3,333 Ending Value $ 51,667 $ 48,333 $ 45,000 $ 41,667 $ 38,333 $ 35,000 $ 31,667 $ 28,333 $ 25,000 $ 21,667 $ 18,333 $ 15,000 $ 11,667 $ 8,333 $ 5,000

Year Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Cash flows $ 350,000 $ 500,000 $ 500,000 $ 350,000 $ 300,000 CCA -$ 150,000 -$ 255,000 -$ 178,500 -$ 124,950 -$ 87,465 Taxable Profit $ 200,000 $ 245,000 $ 321,500 $ 225,050 $ 212,535 Tax (25%) -$ 50,000 -$ 61,250 -$ 80,375 -$ 56,263 -$ 53,134 After-tax Profit $ 150,000 $ 183,750 $ 241,125 $ 168,788 $ 159,401 Add: CCA $ 150,000 $ 255,000 $ 178,500 $ 124,950 $ 87,465 After-tax Cash Flow $ 300,000 $ 438,750 $ 419,625 $ 293,738 $ 246,866 Investment -$ 1,000,000 Tax Shield at Year 5 $ 38,266 Total Inflows -$ 1,000,000 $ 300,000 $ 438,750 $ 419,625 $ 293,738 $ 285,132 Discount Factor 1.00 1.10 1.21 1.33 1.46 1.61 Present Value -$ 1,000,000 $ 272,727 $ 362,603 $ 315,270 $ 200,627 $ 177,045 NPV $ 328,272 NPV $328,272 IRR 22.47% PI 1.33 Method 2 - Tax shield calculated at Year 1 Year Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Cash flows $ 350,000 $ 500,000 $ 500,000 $ 350,000 $ 300,000 Tax (25%) -$ 87,500 -$ 125,000 -$ 125,000 -$ 87,500 -$ 75,000 After-tax Profit $ 262,500 $ 375,000 $ 375,000 $ 262,500 $ 225,000 Discount Factor 1.10 1.21 1.33 1.46 1.61 Present Value $ 238,636.36 $ 309,917.36 $ 281,743.05 $ 179,291.03 $ 139,707.30 Tax Shield $ 178,977 NPV Calculation Investment -$ 1,000,000 PV of Tax Shield $ 178,977 DCF $ 238,636.36 $ 309,917.36 $ 281,743.05 $ 179,291.03 $ 139,707.30 -$ 821,023 $ 238,636 $ 309,917 $ 281,743 $ 179,291 $ 139,707 NPV $ 328,272 Note that for method 2, the IRR formula will not work because the discount rate is part of the tax shield formula. We would use excel solver or goal seek to find the IRR of 22.47%. This is the present value of future tax shield from end of Year 5 onwards.

You are considered investing in a new machine to produce a new type of product at your firm. The machine will cost $1M and will have a useful life of 5 years. The CCA rate is 30% for this asset class. The machine is expected to generate cash flows (before-tax) of $350K in year 1, $500K in years 2 and 3, $350K in y Your company has a tax rate of 25%. Assume there is no salvage value for CCA purposes Your company's WACC (return) is 10%. How can you evaluate this investment decision? Years 0 1 2 3 4 5 Cash flows - 1,000,000 350,000 500,000 500,000 350,000 300,000 CCA @30% 105,000 150,000 150,000 105,000 90,000 UCC 245,000 350,000 350,000 245,000 210,000 Less: Taxes @25% 61,250 87,500 87,500 61,250 52,500 Taxable Income 183,750 262,500 262,500 183,750 157,500 Add back CCA 105,000 150,000 150,000 105,000 90,000 Cash flows - 1,000,000 288,750 412,500 412,500 288,750 247,500 1 0.9091 0.8264 0.7513 0.6830 0.6209 PV of cash flows- 1,000,000 262,500 340,909 309,917 197,220 153,678 NPV 264,225 IRR 9.282% PI 1.26 Chances Prob Expected Return HH 0.25 40 TH 0.25 10 HT 0.25 10 TT 0.25 -20 Total E® 10 10 STD DEV 21.213203435597 Scenario Probability Auto Stock Gold Stock Recession 0.33 -8.00% ## Normal 0.33 5.00% 3.00% Boom 0.33 # ## E ( R) 5.00% 1.00% STD DEV 10.61% 16.39% Correlation -0.9962709627734 Covariance 0.0000 PVF@10%

For First Year with Salvage Value For First Year, no Salvage Value For later years, no Salvage Value (𝑖𝑛𝑣𝑒𝑠𝑡𝑚𝑒𝑛𝑡 ×𝐶𝐶𝐴 𝑟𝑎𝑡𝑒 × 𝑇𝑎𝑥 𝑟𝑎𝑡𝑒)/(𝐶𝐶𝐴 𝑟𝑎𝑡𝑒+𝑑𝑖𝑠𝑐𝑜𝑢𝑛𝑡 𝑟𝑎𝑡𝑒) ×(1+ℎ𝑎𝑙𝑓 𝑜𝑓 𝑡ℎ𝑒 𝑑𝑖𝑠𝑐𝑜𝑢𝑛𝑡 𝑟𝑎𝑡𝑒)/(1+𝑑𝑖𝑠𝑐𝑜𝑢𝑛𝑡 𝑟𝑎𝑡𝑒) 𝑟𝑎𝑡𝑒 × 𝑇𝑎𝑥 𝑟𝑎𝑡𝑒)/(𝐶𝐶𝐴 𝑟𝑎𝑡𝑒+𝑑𝑖𝑠𝑐𝑜𝑢𝑛𝑡 𝑟𝑎𝑡𝑒) ×(1+ℎ𝑎𝑙𝑓 𝑜𝑓 𝑡ℎ𝑒 𝑑𝑖𝑠𝑐𝑜𝑢𝑛𝑡 𝑟𝑎𝑡𝑒)/(1+𝑑𝑖𝑠𝑐𝑜𝑢𝑛𝑡 𝑟𝑎𝑡𝑒) − (𝑆𝑎𝑙𝑣𝑎𝑔𝑒 𝑣𝑎𝑙𝑢𝑒 ×𝐶𝐶𝐴 𝑟𝑎𝑡𝑒 × 𝑇𝑎𝑥 𝑟𝑎𝑡𝑒)/((𝐶𝐶𝐴 𝑟𝑎𝑡𝑒+𝑑𝑖𝑠𝑐𝑜𝑢𝑛𝑡 𝑟𝑎𝑡𝑒 𝐶𝐶 ×𝐶𝐶𝐴 𝑟𝑎𝑡𝑒 × 𝑇𝑎𝑥 𝑟𝑎𝑡𝑒)/(𝐶𝐶𝐴 𝑟𝑎𝑡𝑒+𝑑𝑖𝑠𝑐𝑜𝑢𝑛𝑡 𝑟𝑎𝑡𝑒))/((1+𝑑𝑖𝑠𝑐𝑜𝑢𝑛𝑡 𝑟𝑎𝑡𝑒)^𝑛 )

𝑒)(1+𝑑𝑖𝑠𝑐𝑜𝑢𝑛𝑡 𝑟𝑎𝑡𝑒)^𝑛 )

NET PRESENT VALUE T 40% CCA 20% R 10% Sales Growth 20% Marketing Costs 10% Initial Investment $ 1,000 Old Asset Value 50 COGS 30% YR 0 YR 1 YR 2 YR 3 YR 4 YR 5 Revenue $ 300 $ 360 $ 432 $ 518 $ 622 COGS $ (90) $ (108) $ (130) $ (156) $ (187) Gross Profit $ 210 $ 252 $ 302 $ 363 $ 435 Marketing $ (30) $ (36) $ (43) $ (52) $ (62) EBT $ 180 $ 216 $ 259 $ 311 $ 373 Tax $ (72) $ (86) $ (104) $ (124) $ (149) After-tax CF $ 108 $ 130 $ 156 $ 187 $ 224 Initial Investment $ (1,000) Old Asset Value $ 50 Tax Shield $ 255 Total ATCF $ (695) $ 108 $ 130 $ 156 $ 187 $ 224 Discount Factor 1.00 1.10 1.21 1.33 1.46 1.61 PV of CF $ (695.45) $ 98.18 $ 107.11 $ 116.84 $ 127.47 $ 139.05 PV $ (106.80)