Question

1. undersatnd how to use EXCEL Spreadsheet

(a) Develop proforma Income Statement Using Excel Spreadsheet

(b) Compute Net Project Cashflows, NPV, and IRR

(c) Develop problem-solving and critical thinking skills

and make long-term investment decisions

1) Life Period of the Equipment = 4 years

2) New equipment cost $(200,000)

3) Equipment ship & install cost $(35,000)

4) Related start up cost $(5,000)

5) Inventory increase $25,000

6) Accounts Payable increase $5,000

7) Equip. salvage value before tax $15,000

8) Sales for first year (1) $200,000

9) Sales increase per year 5%

10) Operating cost (60% of Sales) $(120,000

( As a percnt of sales in year 1) -60 percent

11) Depreciation (Straight Line)/YR $(60,000 )

12) Marginal Corporate Tax Rate (T) 21%

13) Cost of Capital (Discount Rate) 10%

ESTIMATING Initial Outlay (Cash Flow, CFo, T= 0)

CF0 CF1 CF2 CF3 CF4

Year 0 1 2 3 4

Investments:

1) Equipment cost

2) Shipping and Install cost

3) Start up expenses

Total Basis Cost (1+2+3)

4) Net Working Capital

Total Initial Outlay

Operations:

Revenue

Operating Cost

Depreciation

EBIT

Taxes

Net Income

Add back Depreciation

Total Operating Cash Flow

  XXXXX   XXXXX   XXXXX   XXXXX Terminal:

1) Change in net WC $- $- $- $20,000

2) Salvage value (after tax)

- Salvage Value Before Tax (1-T) xxxxx/xxxxx Totall

   Project Net Cash Flows $- $- $- $- $

NPV = IRR = Payback=

Q#1 Would you accept the project based on NPV, IRR?

Would you accept the project based on Payback rule if project cut-off

is 3 years?

Q#2 Impact of 2017 Tax Cut Act on Net Income, Cash Flows and

Capital Budgeting (Investment ) Decisions

(a) Estimate NPV, IRR and Payback Period of the project if equipment is fully

depreciated in first year and tax rate equals to 21%. Would you

accept or reject the project?

( b) As a CFO of the firm, which of the above two scenario (a) or (b)

would you choose? Why?

Q#3 How would you explain to your CEO what NPV means?

Q#4 What are advantages and disadvantages of using only Payback method?

Q#5 What are advantages and disadvantages of using NPV versus IRR?

Q#6 Explain the difference between independent projects and mutually exclusive projects.

When you are confronted with Mutually Exclusive Projects and have conflicts

with NPV and IRR results, which criterion would you use (NPV or IRR) and why?

Answer 1

(a)

Proforma Income Statement
Year	1	2	3	4
Sales	$ 200,000	$ 210,000	$ 220,500	$ 231,525
Operating cost	$ 120,000	$ 126,000	$ 132,300	$ 138,915
Depreciation	$    60,000	$    60,000	$    60,000	$    60,000
Taxable Income	$    20,000	$    24,000	$    28,200	$    32,610
Tax @ 21%	$      4,200	$      5,040	$      5,922	$      6,848
EAT	$    15,800	$    18,960	$    22,278	$    25,762

(b)

Sr.No.	Year	CF0	CF1	CF2	CF3	CF4
1	Equipment Cost	$ (200,000)	$             -	$             -	$             -	$             -
2	Ship & Install cost	$   (35,000)	$             -	$             -	$             -	$             -
3	Start up expenses	$      (5,000)	$             -	$             -	$             -	$             -
	Total basi Cost	$ (240,000)	$             -	$             -	$             -	$             -
4	Increase in Working Capital	$   (20,000)	$             -	$             -	$             -	$             -
	Total Initial Outlay	$ (260,000)	$             -	$             -	$             -	$             -
	Operations:
5	Revenue	$               -	$ 200,000	$ 210,000	$ 220,500	$ 231,525
6	operating Cost	$               -	$ 120,000	$ 126,000	$ 132,300	$ 138,915
7	Depreciation	$               -	$    60,000	$    60,000	$    60,000	$    60,000
	EBIT	$               -	$    20,000	$    24,000	$    28,200	$    32,610
8	Taxes @ 21%	$               -	$      4,200	$      5,040	$      5,922	$      6,848
	Net Income	$               -	$    15,800	$    18,960	$    22,278	$    25,762
9	Add: Depreciation	$               -	$    60,000	$    60,000	$    60,000	$    60,000
	Total Operating cashflow	$               -	$    75,800	$    78,960	$    82,278	$    85,762
10	Decrease in Working Capital	$               -	$             -	$             -	$             -	$    20,000
11	Salvage Value After Tax	$               -	$             -	$             -	$             -	$    11,850
	Total Project Net Cash Flow	$ (260,000)	$    75,800	$    78,960	$    82,278	$ 117,612
	DF @ 10%	1.0000	0.9091	0.8265	0.7514	0.6831
	PV	$ (260,000)	$    68,910	$    65,260	$    61,824	$    80,341
	Net Present Value	$      16,335

Q-1

Calculation of IRR
Year	Cash Flow	DF @ 12.67%	PV
0	$ (260,000)	1.0000	$ (260,000)
1	$      75,800	0.8876	$      67,278
2	$      78,960	0.7878	$      62,203
3	$      82,278	0.6992	$      57,529
4	$   117,612	0.6206	$      72,989
Net Present Value	$              (0)

Calculation of Payback
Year	Cash Flow	Cummulative Cash flow
0	$ (260,000)	$                              (260,000)
1	$      75,800	$                              (184,200)
2	$      78,960	$                              (105,240)
3	$      82,278	$                                 (22,962)
4	$   117,612	$                                   94,650

Payback Period = A + B/C

Where,
A is the last period number with a negative cumulative cash flow;
B is the absolute value (i.e. value without negative sign) of cumulative net cash flow at the end of the period A; and
C is the total cash inflow during the period following period A

= 4 + (22,962/117,612)

= 4 + 0.20

= 4.20 Years

As payback years 4.20 and if payback cut off 3 Years so project not acceptable.

Q-2

(a)

Sr.No.	Year	CF0	CF1	CF2	CF3	CF4
1	Equipment Cost	$ (200,000)	$             -	$             -	$             -	$             -
2	Ship & Install cost	$   (35,000)	$             -	$             -	$             -	$             -
3	Start up expenses	$      (5,000)	$             -	$             -	$             -	$             -
	Total basi Cost	$ (240,000)	$             -	$             -	$             -	$             -
4	Increase in Working Capital	$   (20,000)	$             -	$             -	$             -	$             -
	Total Initial Outlay	$ (260,000)	$             -	$             -	$             -	$             -
	Operations:
5	Revenue	$               -	$ 200,000	$ 210,000	$ 220,500	$ 231,525
6	operating Cost	$               -	$ 120,000	$ 126,000	$ 132,300	$ 138,915
7	Depreciation	$               -	$ 240,000	$             -	$             -	$             -
	EBIT	$               -	$    20,000	$    24,000	$    28,200	$    32,610
8	Taxes @ 21%	$               -	$      4,200	$      5,040	$      5,922	$      6,848
	Net Income	$               -	$    15,800	$    18,960	$    22,278	$    25,762
9	Add: Depreciation	$               -	$ 240,000	$             -	$             -	$             -
	Total Operating cashflow	$               -	$ 255,800	$    18,960	$    22,278	$    25,762
10	Decrease in Working Capital	$               -	$             -	$             -	$             -	$    20,000
11	Salvage Value After Tax	$               -	$             -	$             -	$             -	$    11,850
	Total Project Net Cash Flow	$ (260,000)	$ 255,800	$    18,960	$    22,278	$    57,612
	DF @ 10%	1.0000	0.9091	0.8265	0.7514	0.6831
	PV	$ (260,000)	$ 232,548	$    15,670	$    16,740	$    39,355
	Net Present Value	$      44,313

Calculation of IRR
Year	Cash Flow	DF @13.98%	PV
0	$ (260,000)	$                     1	$ (260,000)
1	$   113,600	$               0.88	$      99,663
2	$      66,360	$               0.77	$      51,076
3	$      69,678	$               0.68	$      47,050
4	$   105,012	$               0.59	$      62,210
Net Present Value	$                0

Calculation of Payback
Year	Cash Flow	Cummulative Cash flow
0	$ (260,000)	$                              (260,000)
1	$   113,600	$                              (146,400)
2	$      66,360	$                                 (80,040)
3	$      69,678	$                                 (10,362)
4	$   105,012	$                                   94,650

Payback Period = A + B/C

Where,
A is the last period number with a negative cumulative cash flow;
B is the absolute value (i.e. value without negative sign) of cumulative net cash flow at the end of the period A; and
C is the total cash inflow during the period following period A

= 4 + (10,362/105,012)

= 4 + 0.10

= 4.10 Years

In new tax rule also not accept as payback period is higher than cut off.

(b)

In (a) and (b) select (b) as it have higher NPV & IRR and lessor payback period.

Q-3

Net present value (NPV) is the difference between the present value of cash inflows and the present value of cash outflows over a period of time. NPV is used in capital budgeting and investment planning to analyze the profitability of a projected investment or project.

A positive net present value indicates that the projected earnings generated by a project or investment - in present dollars - exceeds the anticipated costs, also in present dollars. It is assumed that an investment with a positive NPV will be profitable, and an investment with a negative NPV will result in a net loss. This concept is the basis for the Net Present Value Rule, which dictates that only investments with positive NPV values should be considered.

Q-4

Advantages of the Payback Method

The most significant advantage of the payback method is its simplicity. It's an easy way to compare several projects and then to take the project that has the shortest payback time. However, the payback has several practical and theoretical drawbacks.

Disadvantages of the Payback Method

Ignores the time value of money: The most serious disadvantage of the payback method is that it does not consider the time value of money. Cash flows received during the early years of a project get a higher weight than cash flows received in later years. Two projects could have the same payback period, but one project generates more cash flow in the early years, whereas the other project has higher cash flows in the later years. In this instance, the payback method does not provide a clear determination as to which project to select.

Neglects cash flows received after payback period: For some projects, the largest cash flows may not occur until after the payback period has ended. These projects could have higher returns on investment and may be preferable to projects that have shorter payback times.

Ignores a project's profitability: Just because a project has a short payback period does not mean that it is profitable. If the cash flows end at the payback period or are drastically reduced, a project might never return a profit and therefore, it would be an unwise investment.

Does not consider a project's return on investment: Some companies require capital investments to exceed a certain hurdle of rate of return; otherwise the project is declined. The payback method does not consider a project's rate of return.

They payback method is a handy tool to use as an initial evaluation of different projects. It works very well for small projects and for those that have consistent cash flows each year. However, the payback method does not give a complete analysis as to the attractiveness of projects that receive cash flows after the end of the payback period. And it does not consider the profitability of a project nor its return on investment.

Q-5

Although using one discount rate simplifies matters, there are a number of situations that cause problems for IRR. If an analyst is evaluating two projects, both of which share a common discount rate, predictable cash flows, equal risk, and a shorter time horizon, IRR will probably work. The catch is that discount rates usually change substantially over time. For example, think about using the rate of return on a T-bill in the last 20 years as a discount rate. One-year T-bills returned between around 0.1% and 6.4% in the last 20 years, so clearly the discount rate is changing.1

Without modification, IRR does not account for changing discount rates, so it's just not adequate for longer-term projects with discount rates that are expected to vary.

Another type of project for which a basic IRR calculation is ineffective is a project with a mixture of multiple positive and negative cash flows. For example, consider a project for which the marketing department must reinvent the brand every couple of years to stay current in a trendy market.

A single IRR can't be used in this case. Recall that IRR is the discount rate or the interest needed for the project to break even given the initial investment. If market conditions change over the years, this project can have multiple IRRs. In other words, long projects with fluctuating cash flows and additional investments of capital may have multiple distinct IRR values.

Another situation that causes problems for people who prefer the IRR method is when the discount rate of a project is not known. In order for the IRR to be considered a valid way to evaluate a project, it must be compared to a discount rate. If the IRR is above the discount rate, the project is feasible. If it is below, the project is considered not doable. If a discount rate is not known, or cannot be applied to a specific project for whatever reason, the IRR is of limited value. In cases like this, the NPV method is superior. If a project's NPV is above zero, then it's considered to be financially worthwhile.

The advantage to using the NPV method over IRR using the example above is that NPV can handle multiple discount rates without any problems. Each year's cash flow can be discounted separately from the others making NPV the better method.

The NPV can be used to determine whether an investment such as a project, merger or acquisition will add value to a company. Positive net values mean they shareholders will be happy, while negative values are not so beneficial.

Both IRR and NPV can be used to determine how desirable a project will be and whether it will add value to the company. While one uses a percentage, the other is expressed as a dollar figure. While some prefer using IRR as a measure of capital budgeting, it does come with problems because it doesn't take into account changing factors such as different discount rates. In these cases, using the net present value would be more beneficial.

Q-6

Differences

Mutually exclusive projects: Any twoprojects are said to be mutually exclusive, if the acceptance of one project rejects the other's project. This means that the two projects cannot accepted in the same time.

Independent projects: under this project, the acceptance of one project does not affect the cash flow of other project.

Cause of NPV and IRR Conflict

The underlying cause of the NPV and IRR conflict is the nature of cash flows (normal vs non-normal), nature of project (independent vs mutually-exclusive) and size of the project.

in case of mutually-exclusive projects, an NPV and IRR conflict may arise in which one project has a higher NPV but the other has higher IRR. Mutually exclusive projects are projects in which acceptance of one project excludes the others from consideration. The conflict either arises due to relative size of the project or due to the different cash flow distribution of the projects.

Since NPV is an absolute measure, it will rank a project adding more dollar value higher regardless of the initial investment required. IRR is a relative measure, and it will rank projects offering best investment return higher regardless of the total value added.

NPV is the Preferred Technique

Whenever an NPV and IRR conflict arises, always accept the project with higher NPV. It is because IRR inherently assumes that any cash flows can be reinvested at the internal rate of return. This assumption is problematic because there is no guarantee that equally profitable opportunities will be available as soon as cash flows occur. The risk of receiving cash flows and not having good enough opportunities for reinvestment is called reinvestment risk. NPV, on the other hand, does not suffer from such a problematic assumption because it assumes that reinvestment occurs at the cost of capital, which is conservative and realistic.