Question

Excel and Writing Case Assignment Description: ABC Corporation has a machine that requires repairs or should be replaced. ABC has evaluated the two options and calculated the cash flows resulting from each option as follows:

Option A: Repair the Machine

Year Cash Flow

0 -50,000

1 15,500

2 20,100

3 18,900

4 17,100

5 13,700

Year Cash Flow

Option B: Buy a new Machine

Cash Flow

0 -400,000

1 51,300

2 155,000

3 127,800

4 126,900

5 125,100

You have recently been hired by ABC Corporation and your first assignment is to help them decide which of these two options should be pursued. You would like to apply Capital Budgeting and Time Value of Money concepts you have learnt to analyze the problem and present your recommendation to your boss, Ms. Jane Austen. Conduct the analysis by calculating the following: Payback Period Net Present Value (NPV) Internal Rate of Return (IRR) Modified Internal Rate of Return MIRR Profitability Index (PI) Calculate and graph the NPV profile for both options Calculate cross-over rate if there is one The company has a Weighted Average Cost of Capital (WACC) of 8%.

For this analysis, your boss asked you to conduct a sensitivity analysis by calculating NPV at three different discount rates: 8% (the current WACC), 10% and 14%. You must use the built-in financial functions and logical functions in Excel spreadsheet. The spreadsheet should be a model such that if the cash-flows are changed, the answer and recommendation should change automatically in the spreadsheet.

You must pay special attention to calculating Payback and Discounted Payback measures and it should change as inputs are changed. Please use =IF() function to display your decision. Your Excel must also have a properly labeled graph for NPV Profile for two projects.

Please post with excel solutions - Edit: Make an excel spread sheet showing the function / calculations with formula so they automatically calculate.

You must use the built-in financial functions and logical functions in Excel spreadsheet. The spreadsheet should be a model such that if the cash-flows are changed, the answer and recommendation should change automatically in the spreadsheet. You must pay special attention to calculating Payback and Discounted Payback measures and it should change as inputs are changed. Please use =IF() function to display your decision. Your Excel must also have a properly labeled graph for NPV Profile for two projects.

Answer 1

Option A: Repair the Machine				WACC=8%			Option B: Buy a new Machine				WACC=8%
Year	Cash Flow	Cumulative cash flow	PV F at 8%	PV at 8%	Cumulative disounted cash flow		Year	Cash Flow	Cumulative cash flow	PV F at 8%	PV at 8%	Cumulative disounted cash flow
1	2	3	4=1/1.08^n	5=2*4	6		1	2	3	4=1/1.08^n	5=2*4	6
0	-50000	-50000	1	-50000	-50000		0	-400,000	-400000	1	-400000	-400000
1	15500	-34500	0.92593	14351.9	-35648.15		1	51,300	-348700	0.92593	47500	-352500.00
2	20100	-14400	0.85734	17232.5	-18415.64		2	155,000	-193700	0.85734	132888	-219612.48
3	18900	4500	0.79383	15003.4	-3412.21		3	127,800	-65900	0.79383	101452	-118160.72
4	17100	21600	0.73503	12569	9156.80		4	126,900	61000	0.73503	93275.3	-24885.43
5	13700	35300	0.68058	9323.99	18480.79		5	125,100	186100	0.68058	85141	60255.52
			NPV=	18480.8						NPV=	60255.5
			PI =	1.37						PI =	1.15
Ordinary P/B		2+(14400/18900)=					Ordinary P/B		3+(65900/126900)=
		2.76	Years						3.52	Yrs.
Discounted Payback				3+(3412.21/12569.01)=			Discounted Payback				4+(24885.43/85140.96)=
				Years	3.27						Years	4.29
IRR(of Col.2)(Excel fn.)			21%				IRR(of Col.2)(Excel fn.)			13%
MIRRof Col.2) (Excel fn.)			15%				MIRR(of Col.2) (Excel fn.)			11%

Option A: Repair the Machine				WACC=10%			Option B: Buy a new Machine				WACC=10%
Year	Cash Flow	Cumulative cash flow	PV F at 10%	PV at 10%	Cumulative disounted cash flow		Year	Cash Flow	Cumulative cash flow	PV F at 10%	PV at 10%	Cumulative disounted cash flow
1	2	3	4=1/1.10^n	5=2*4	6		1	2	3	4=1/1.10^n	5=2*4	6
0	-50000	-50000	1	-50000	-50000		0	-400,000	-400000	1	-400000	-400000
1	15500	-34500	0.90909	14090.9	-35909.09		1	51,300	-348700	0.90909	46636.4	-353363.64
2	20100	-14400	0.82645	16611.6	-19297.52		2	155,000	-193700	0.82645	128099	-225264.46
3	18900	4500	0.75131	14199.8	-5097.67		3	127,800	-65900	0.75131	96018	-129246.43
4	17100	21600	0.68301	11679.5	6581.86		4	126,900	61000	0.68301	86674.4	-42572.02
5	13700	35300	0.62092	8506.62	15088.48		5	125,100	186100	0.62092	77677.3	35105.23
			NPV=	15088.5						NPV=	35105.2
			PI =	1.30						PI =	1.09
Ordinary P/B		2+(14400/18900)=					Ordinary P/B		3+(65900/126900)=
		2.76	Years						3.52
Discounted Payback				3+(5097.67/11679.53)=			Discounted Payback				4+(42572.02/77677.26)=
				Years	3.44						Years	4.55
IRR(of Col.2)(Excel fn.)			21%				IRR(of Col.2)(Excel fn.)			13%
MIRR(of Col.2) (Excel fn.)			16%				MIRR(of Col.2) (Excel fn.)			12%

Option A: Repair the Machine				WACC=14%			Option B: Buy a new Machine				WACC=14%
Year	Cash Flow	Cumulative cash flow	PV F at 14%	PV at 14%	Cumulative disounted cash flow		Year	Cash Flow	Cumulative cash flow	PV F at 14%	PV at 14%	Cumulative disounted cash flow
1	2	3	4=1/1.10^n	5=2*4	6		1	2	3	4=1/1.10^n	5=2*4	6
0	-50000	-50000	1	-50000	-50000		0	-400,000	-400000	1	-400000	-400000
1	15500	-34500	0.87719	13596.5	-36403.51		1	51,300	-348700	0.87719	45000	-355000.00
2	20100	-14400	0.76947	15466.3	-20937.21		2	155,000	-193700	0.76947	119267	-235732.53
3	18900	4500	0.67497	12757	-8180.25		3	127,800	-65900	0.67497	86261.4	-149471.17
4	17100	21600	0.59208	10124.6	1944.32		4	126,900	61000	0.59208	75135	-74336.19
5	13700	35300	0.51937	7115.35	9059.67		5	125,100	186100	0.51937	64973.02	-9363.17
			NPV=	9059.67						NPV=	-9363.17
			PI =	1.18						PI =	0.98
Ordinary P/B		2+(14400/18900)=					Ordinary P/B		3+(65900/126900)=
		2.76	Years						3.52	Years
Discounted Payback				3+(8180.25/10124.57)=			Discounted Payback			Does not payback within 5 yrs.
				Years	3.81
IRR(of Col.2)(Excel fn.)			21%				IRR(of Col.2)(Excel fn.)			13%
MIRR(of Col.2) (Excel fn.)			18%				MIRR(of Col.2) (Excel fn.)			13%

Summary
	Option A: Repair the Machine				Option B: Buy a new Machine
WACC	8%	10%	14%		8%	10%	14%
Payback(in Yrs.)	2.76	2.76	2.76		3.52	3.52	3.52
Disc.P/B(in Yrs.)	3.27	3.44	3.81		4.29	4.55	0
NPV	18480.79	15088.48	9059.67		60255.52	35105.23	-9363.17
PI	1.37	1.30	1.18		1.15	1.09	0.98
IRR	21%	21%	21%		13%	13%	13%
MIRR	15%	16%	18%		11%	12%	13%
At 8% & 10% WACC Buying a new m/c is recommended for its greater NPV
At 14% WACC, Repairing the existing m/c is recommended for its greater NPV.