Question

Your company is undertaking a new project. A building was purchased 10 years ago for $750,000 depreciated straight line to $150,000 (the land value) over 30 years. It is now worth $500,000 (including $150,000 land).

The project requires improvements to the building of $100,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 $600,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. (at least 2 decimals)

IX. What is payback in whole years?

X. What is Average Accounting Return? (correct format with at least 2 decimals)

XI. Show how (algebraic format), IRR would be solved (do not solve).

Answer 1

1. Total depreciation per year = Cost of Improvement - Depreciated Value / Life of asset = (100000 - 0) / 4 = $ 25,000 (Since existing building is fully depreciated, its depreciation is ignored)

2. Projected annual income statements.

Year Cash Revenues Cash Expenses EBDT (Revenue - Expenses) Depreciation EBT taxes@40% EAT
1 325000 80000 245000 25000 220000 88000 132000
2 350000 100000 250000 25000 225000 90000 135000
3 375000 120000 255000 25000 230000 92000 138000
4 400000 140000 260000 25000 235000 94000 141000
3. Initial cost(CFO) = Cost of Improvemnt = $ 100,000

4. Annual operating cash flows

Cash Flow = EAT + Depreciation

EAT Add Depreciation Cash Inflow
132000 25000 157000
135000 25000 160000
138000 25000 163000

Any doubt comment below i will explain or resolve until you got....
PLEASE.....UPVOTE....ITS REALLY HELPS ME....THANK YOU....SOOO MUCH....
Please comment if any querry i will resolve as soon as possible