Question

2. A company has the following mutually exlcusive projects to choose from: Year Cash Flow A Cash Flow B -55.000 -24,000 12.800 11,700 23.100 11.100 27.700 12,600 46,400 5,900 The required return is 14%. a. Using payback, which project will you choose? b. Using NPV, which project will you choose? c. Using IRR, which project will you choose? d. Using profitability index, which project will you choose? e. Based on all the previous answers, which project will you choose?

Answer 1

Project A
Year	cash flow	cumulative cash flow
0	-55000
1	12800	12800
2	23100	35900
3	27700	19100	amount to be recovered
4	46400
pay back period = year before final year of recovery+(amount to be recovered/cash flow of final year of recovery)	2+(19100/27700)	2.69
Project A
Year	cash flow	present value factor @ 14% =1/(1+r)^n r= 14%	present value of cash flow = cash flow*PVF at 14%
0	-55000	1	-55000
1	12800	0.877192982	11228.0702
2	23100	0.769467528	17774.6999
3	27700	0.674971516	18696.711
4	46400	0.592080277	27472.5249
NPV = sum of present value of cash flow			20172.006
PI =1+(npv/initial investment)	1+(20172.00/55000)		1.37
IRR=Using IRR function in MS Excel		IRR(H4649:H4653)	27.65%
Project B
Year	cash flow	cumulative cash flow
0	-24000
1	11700	11700
2	11100	22800
3	12600	1200	amount to be recovered
4	5900
pay back period = year before final year of recovery+(amount to be recovered/cash flow of final year of recovery)	2+(1200/12600)	2.10
Project A
Year	cash flow	present value factor @ 14% =1/(1+r)^n r= 14%	present value of cash flow = cash flow*PVF at 14%
0	-24000	1	-24000
1	11700	0.877192982	10263.1579
2	11100	0.769467528	8541.08957
3	12600	0.674971516	8504.6411
4	5900	0.592080277	3493.27364
NPV = sum of present value of cash flow			6802.1622
PI =1+(npv/initial investment)	1+(6802.16/24000)		1.28
IRR=Using IRR function in MS Excel		IRR(H4669:H4673)	28.32%
project	A	B	Final selection
pay back period = year before final year of recovery+(amount to be recovered/cash flow of final year of recovery)	2.69	2.10	project B
NPV = sum of present value of cash flow	20172.00595	6802.162201	Project A
IRR=Using IRR function in MS Excel	27.65%	28.32%	project B
PI =1+(npv/initial investment)	1.37	1.28	Project A
I will choose project A on the basis of NPV as it is considered the best method of capital budgeting as projects are mutually exclusive			= Con New Microsoft Office Excel Worksheet - Microsoft Excel U D - Autosum " AY A Ea Copy 2 Clear Sort & Filter Editing Find & Select P Q R Home Insert Page Layout Formulas Data Review View Cut Calibri - 11 - A A Dr Wrap Text Percentage Paste Format Painter BI U A E E EF Merge & Center - $ % , 0.00 Conditional Format Cell Insert Delete Format Formatting as Table Styles Clipboard Font Alignment Number Styles Cells J4656 for EIRR(H4649:H4653) EF N 4644 46400 4645 pay back period = year b 2+(19100/27700) 2.69 4646 4647 Project A 4648 Year cash flow present value factor present value of cash flow = cash flow*PVF at 14% 4649 -55000 1 -55000 4650 12800 0.877192982 11228.0702 4651 23100 0.769467528 17774.6999 4652 27700 0.674971516 18696.711 4653 46400 0.592080277 27472.5249 4654 NPV = sum of present value of cash flow 20172.006 4655 PL=1+npv/initial invest1+20172.00/55000) 1.37 4656 IRR=Using IRR function in MS Excel IRR(H4649:H4653) 27.65%|| 4657 4658 Project B 4659 Year cash flow cumulative cash flow -24000 11700 11700 4662 11100 22800 4663 12600 1200 amount to be recovered 5900 4665 pay back period = year b2+(1200/12600) 2.10 4666 4667 Project A 4668 Year cash flow present value factor present value of cash flow = cash flow*PVF at 14% Sheet22 Sheet23 Sheet24 Sheet25 Sheet26 Sheet27 Sheet28 Sheet29 4660 4661 4664 Salat dastination and prace ENTED or chooc. Dacte FAMT 100% A = Con New Microsoft Office Excel Worksheet - Microsoft Excel Review View Percentage Dr EF U Wrap Text Merge & Center - D - Autosum " AY Eg Copy A Home Insert Page Layout Formulas Data Cut Calibri - 11 - A A Paste Format Painter BI U A E Clipboard Font J4676 for EIRR(H4669:H4673) F E $ % , 0.00 Insert Delete Format Conditional Format Formatting as Table Styles Cell Styles 2 Clear Sort & Filter Editing Find & Select Alignment Number Cells Η N P Q R 4663 1200 amount to be recovered 12600 5900 pay back period = year b|2+(1200/12600) 2.10 4664 4665 4666 4667 4668 4669 4670 4671 4672 4673 Project A Year cash flow present value factor present value of cash flow = cash flow*PVF at 14% - 24000 - 24000 11700 0.877192982 10263.1579 11100 0.769467528 8541.08957 12600 0.674971516 8504.6411 5900 0.592080277 3493.27364 NPV = sum of present value of cash flow 6802.1622 PL=1+npv/initial invest 1+6802.16/24000) 1.28 IRR=Using IRR function in MS Excel IRR(H4669:H4673) 28.32% 4674 B 4675 4676 4677 4678 4679 4680 4681 4682 4683 4684 4685 4686 project pay back period = year b NPV = sum of present va IRR=Using IRR function i PL=1+npv/initial invest 2.69 20172.00595 27.65% 1.37 Final selection 2.10 project B 6802.162201 Project A 28.32% project B 1.28 Project A I will choose project A on the basis of NPV as it is considered the best method of capital budgeting as projects are mutually exclusive