W3 Excel Exercise - Chp 8 + 9 solution

.xlsx

School

Humber College *

*We aren’t endorsed by this school

Course

CSAC3510

Subject

Finance

Date

Feb 20, 2024

Type

xlsx

Pages

Uploaded by EarlSardinePerson1031

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:

3. NET PRESENT VALUE (NPV) Net Present value is just the sum of discounted cash flows and the initial investment. Discounted cash flow -$ 1,000,000 $ 284,091 $ 351,240 $ 319,309 $ 213,442 $ 170,753 NPV $ 338,834 A positive NPV indicates that the present value of the investment is $338,834. A positive NPV indicates that it is worth doing an investment. 4. PROFITABILITY INDEX (PI) The Profitability Index just takes the sum of discounted cash flows divided by the initial investment Sum of discounted cash flows (Years 1 to 5) $ 1,338,834 Initial Investment (Year 0) $ 1,000,000 Make sure this number is POSITIVE Profitability Index 1.34 A profitability index over 1 indicates you should accept an investment. A profitability index below 1 indicates you should reject an investment. A profitability index at 0 means you are indifferent to accepting or rejecting. 5. INTERNAL RATE OF RETURN (IRR) The IRR determines the discount rate that produces a NPV of zero. The most effective way to determine IRR is through spreadsheet computation. We will use the IRR formula. After-tax cash flows (NOT discounted): After-tax Cash Flow -$ 1,000,000 $ 312,500 $ 425,000 $ 425,000 $ 312,500 $ 275,000 IRR 22.88% So, if our discount rate was 22.88% instead of 10%, then our NPV would be 0. If the IRR is higher than your discount rate, that means you should accept the investment decision. If the IRR is lower than your discount rate, you should reject the investment decision.

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

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Recommended textbooks for you

EBK CONTEMPORARY FINANCIAL MANAGEMENT

Finance

ISBN:9781337514835

Author:MOYER

Publisher:CENGAGE LEARNING - CONSIGNMENT

Cornerstones of Cost Management (Cornerstones Ser...

Accounting

ISBN:9781305970663

Author:Don R. Hansen, Maryanne M. Mowen

Publisher:Cengage Learning

Corporate Fin Focused Approach

Finance

ISBN:9781285660516

Author:EHRHARDT

Publisher:Cengage

Principles of Accounting Volume 2

Accounting

ISBN:9781947172609

Author:OpenStax

Publisher:OpenStax College

Financial Management: Theory & Practice

Finance

ISBN:9781337909730

Author:Brigham

Publisher:Cengage

Survey of Accounting (Accounting I)

Accounting

ISBN:9781305961883

Author:Carl Warren

Publisher:Cengage Learning

SEE MORE TEXTBOOKS

Recommended textbooks for you

EBK CONTEMPORARY FINANCIAL MANAGEMENT
Finance
ISBN:9781337514835
Author:MOYER
Publisher:CENGAGE LEARNING - CONSIGNMENT
Cornerstones of Cost Management (Cornerstones Ser...
Accounting
ISBN:9781305970663
Author:Don R. Hansen, Maryanne M. Mowen
Publisher:Cengage Learning
Corporate Fin Focused Approach
Finance
ISBN:9781285660516
Author:EHRHARDT
Publisher:Cengage
Principles of Accounting Volume 2
Accounting
ISBN:9781947172609
Author:OpenStax
Publisher:OpenStax College
Financial Management: Theory & Practice
Finance
ISBN:9781337909730
Author:Brigham
Publisher:Cengage
Survey of Accounting (Accounting I)
Accounting
ISBN:9781305961883
Author:Carl Warren
Publisher:Cengage Learning