Question

Your company is undertaking a new project. A building was purchased 10 years ago for $500,000 depreciated straight line to $50,000 (the land value) over 30 years. It is now worth $700,000 (including $50,000 land). The project requires improvements to the building of $60,000. The improvements are depreciated straight line to zero over the life of the project. The project will generate revenues of $325,000, $350,000, $375,000 and $400,000 for years 1-4, respectively. Annual cash operating expenses are $80,000, $100,000, $120,000 and $140,000, respectively. The project will last 4 years, at which time the building will be sold for $800,000. Taxes are 40% and rate of return is 10%. Using Excel, prepare a spreadsheet and upload: I. What is the total depreciation per year? II. Show projected annual income statements. III. What is the initial cost (CF0)? IV. What are annual operating Cash Flows? V. What is the ending Cash Flow from the sales of the assets? VI. What is the total annual Cash Flows? VII. What is NPV? Show work VIII. What is Profitability Index? Show work. IX. What is payback in whole years? X. What is Average Accounting Return? XI. Show how (algebraic format),IRR would be solved (do not solve).

Answer 1

Required I

Annual Depreciation on Building = ($500,000 - $50,000) / 30 = $15,000

Annual Depreciation on improvements over the life of the project = $60,000 / 4 = $15,000

Total Annual Deprciation = $15,000 + $15,000 = $30,000

Required II

Required III

Calculation of Profit from sale of asset if sold at Year 0 = $700,000 - ($500,000 - $15,000 * 10) = $350,000

Tax on profit from sale of asset = $350,000 * 40% = $140,000

Net cash inflow = $350,000 - $140,000 = $210,000

Initial Cost = Cost of Impreovement + Cash Flow from sale of asset

= $60,000 + $210,000

= $270,000

Required IV

Required V

Calculation of Profit from sale of asset = $800,000 - ($500,000 - $15,000 * 14) = $510,000

Tax on profit from sale of asset = $510,000 * 40% = $204,000

Net cash inflow = $510,000 - $204,000 = $306,000

Required VI

Total Annual Cash Flows

Required VII

Required VIII

Profitability Index = Total cash inflow / Total Cash Outflow

= $726,145 / $270,000

= 2.70

Required IX

Payback Period

Payback period ( in whole years) = 2 years

Required X

Accounting rate of return = Average Annual Income / Innitial Investment

= {($129,000+ $132,000+$135,000+$444,000)/4} / $270,000

= $210,000 / $270,000

= 77.78%

Required XI

Let IRR be r

Thus,

Cash Inflow = Cash outflow

$159,000 / (1+r%) + $162,000 / (1+r%)² + $165,000 / (1+r%)³ + $474,000 / (1+r%)⁴ = $270,000