Question

1.     (1 % ) Estimate the NPV, ROI, and payback period for the XYZ project using the information below. Your results should look similar to Figures 2-4 through 2-6 on pp. 46-47. Use the “business case financials” template. Type your name on the spreadsheet, and type any comments/answers directly on the spreadsheet. Be sure to change the formatting to display one decimal place for the discount rate and ROI and properly note the year of payback (not Year X).

a.     Estimated costs for the XYZ project are $250,000 in year 0 and $50,000 each in years 1, 2, and 3. Estimated benefits are $0 in year 0 and $175,000 each year in years 1, 2 and 3. Show the results (entire spreadsheet showing NPV, ROI, and year in which payback occurs) for a discount rate of 5.0%. Scoring: correct NPV, ROI, and payback year, 2 pts each, correct number and other formatting, 2 pts) (8 points)

b.     Would you recommend doing this project based on the NPV, ROI, and payback? Why or why not? Be sure to type your response directly on the first tab of the worksheet. (2 pts)

2.     (0.5 %) You want to estimate how many years it will take to pay back the money you have spent earning a bachelor’s degree. Hint Table and Figure similar to Figure 2-7 on page 49.

a.     Assume your costs were $40,000 per year for four years (years 1, 2, 3, and 4) by paying for college and not working as many hours, and your after-graduation benefits were $15,000 per year for the next 30 years by getting a better job. Ignoring the time value of money, taxes, etc., in what year will you pay back for your college? (Hint: Find the year where the cumulative cash flow is equal to zero.)

b.     Be sure to type your response directly on the second tab of the worksheet with your results and how you calculated them.

3.     (0.5 %) Prepare a weighted scoring model (table and chart similar to Figure 2-8 on p. 51) based on the following data, using the template with that name. Your company is trying to decide which of five building locations to build on. Be sure to type your response directly on the third tab of the worksheet. Make sure you add DATA LABELS to your charts.

a.     The main criteria and their weights are as follows: Cost 35%; Location 20%, Taxes 10%, Local Governmental Laws 25%, and Local Labor Rates 10%.

b.     The ratings for Location 1 are 75, 85, 50, 40, and 80. The ratings for Location 2 are 90, 60, 70, 55, and 30. The ratings for Location 3 are 60, 50, 70, 80, and 90. The ratings for Location 4 are all 65. The ratings for Location 5 are 70, 40, 90, 65, and 90.

c.     Find the weighted score for each project (3 points) and describe which one you would recommend and why (1 point) on the spreadsheet. Note: Check/adjust the formulas to make sure they are correct. (4 points)

Answer 1

	1a)	Present value(PV) of cash flow=(cashflow)/((1+i)^N)
			i=discount rate=5%=0.05
			N=Year of cash flow
			Yearwise cashflow and PV of net cash flows are given below:
				N	A	B	C	D=A+B+C	E	F=D/(1.05^N)
				Year	Initial cost	Annual benefit	Annual cost	Net Cash flow	Cumulative Cash flow	PV of Net Cash flow
				0	($250,000)			($250,000)	($250,000)	-250000
				1		$175,000	($50,000)	$125,000	($125,000)	119047.619
				2		$175,000	($50,000)	$125,000	$0	113378.6848
				3		$175,000	($50,000)	$125,000	$125,000	107979.6998
									Total	90406.00367
					Net Present Value(NPV)			$ 90,406.00
					Return on Investment(ROI)			0.50	(125000/250000)
					ROI (Percentage)			50%
					Payback Period			2 years
	1b)	Yes, we will recommend doing this project. The PV is positive , ROI is high at 50% and payback period only 2 years
	2a)		Year	Cash flow	Cumulative cash flow
			1	($40,000)	($40,000)
			2	($40,000)	($80,000)
			3	($40,000)	($120,000)
			4	($40,000)	($160,000)
			5	$15,000	($145,000)
			6	$15,000	($130,000)
			7	$15,000	($115,000)
			8	$15,000	($100,000)
			9	$15,000	($85,000)
			10	$15,000	($70,000)
			11	$15,000	($55,000)
			12	$15,000	($40,000)
			13	$15,000	($25,000)
			14	$15,000	($10,000)
			15	$15,000	$5,000
			16	$15,000	$20,000
			17	$15,000	$35,000
			PAYBACK In the year 15
			In the year 15 Cumulative cash flow turn positive from negative
				Cost	Location	Taxes	Local Governmental laws	Local labor rates	Total
		A	Weight	0.35	0.20	0.10	0.25	0.10	1.00
		B	Rating forLocation1	75.00	85	50	40	80
		C=A*B	Weighted Score Location 1	26.25	17.00	5.00	10.00	8.00	66.25
		D	Rating forLocation2	90.00	60	70	55	30
		E=A*D	Weighted Score Location 2	31.50	12.00	7.00	13.75	3.00	67.25
		F	Rating forLocation3	60.00	50	70	80	90
		G=A*F	Weighted Score Location 3	21.00	10.00	7.00	20.00	9.00	67.00
		H	Rating forLocation4	65.00	65	65	65	65
		I=A*H	Weighted Score Location 4	22.75	13.00	6.50	16.25	6.50	65.00
		J	Rating forLocation5	70.00	40	90	65	90
		K=A*J	Weighted Score Location 5	24.50	8.00	9.00	16.25	9.00	66.75
									-
		Location	Weighted Score
		1	66.25
		2	67.25
		3	67.00
		4	65.00
		5	66.75
			Recommendation:	Location 2	(Highest Weighted score)