Question

3. MBATech, Inc., is negotiating with the mayor of Bean City to start a manufacturing plant in an abandoned building. The cash flows for MBAT’s proposed plant are: Year 0 Year 1 Year 2 Year 3 Year 4 - 1,000,000 371,739 371,739 371,739 371,739 The city has agreed to subsidize MBAT. The form and timing of the subsidy have not been determined, and depend on which investment criterion is used by MBAT. In preliminary discussions, MBAT suggested four alternatives: [A] Subsidize the project to bring its IRR to 25%. [B] Subsidize the project to provide a two-year payback. [C] Subsidize the project to provide an NPV of $75,000 when cash flows are discounted at 20%. [D] Subsidize the project to provide an accounting rate of return (ARR) of 40%. This is defined as: 2 This document is authorized for use only by Eric Biltz ([email protected]). Copying or posting is an infringement of copyright. Please contact [email protected] or 800-988-0886 for additional copies. Investment Analysis and Lockheed Tri Star 291-031 ARR = AverageAnnualCash Flow − Investment # of Years Investment ÷ 2 You have been hired by Bean City to recommend a subsidy that minimizes the costs to the city. Subsidy payments need not occur right away; they may be scheduled in later years if appropriate. Please indicate how much of a subsidy you would recommend for each year under each alternative suggested by MBAT. Which of the four subsidy plans would you recommend to the city if the appropriate discount rate is 20%? For this question I need to know hat the cost to the city is for if you Increase IRR to 25%, Give a 2 year payback, NPV of $75,000 (at 20% discount) and ARR of 40$?

Answer 1

A). Increase IRR to 25% (solve using excel Solver):

Year (n)	0	1	2	3	4	IRR
Original cash flow (CF)	(1,000,000)	371,739	371,739	371,739	371,739	18.00%
Cash flow (CF)	(877,880)	371,739	371,739	371,739	371,739	25.00%

Subsidy amount in year 0 = 1,000,000 - 877,880 = 122,120

B). Payback period of 2 years:

-1,000,000 + subsidy = 371,739 + 371,739 (since the project has to break even in two years)

subsidy = 256,522

C). NPV of $75,000 at a discount rate of 20%

Year (n)	0	1	2	3	4
Cash flow (CF)	(1,000,000)	371,739	3,71,739	3,71,739	3,71,739
Discount factor @ 20%	1.000	0.833	0.694	0.579	0.482
PV of CF	(1,000,000)	309,782.50	258,152.08	215,126.74	179,272.28
NPV	(37,666.40)

Current NPV is -37,666.40

To make the NPV 75,000, a subsidy of 75,000 + 37.666.40 = 112,666.40 needs to be given.

D). Make ARR = 40%

ARR = [Annual cash flow - ((investment - subsidy)/number of years)]/(Investment + subsidy)/2

40% = [371,739 - (1,000,000 - s)/4]/(1,000,000 + s)/2

0.2*(1,000,000 + s) = 371,739 - 1,000,000 + s

Solving for s, subsidy amount = 173,913.33

So, to summarize:

	Subsidy amount at t = 0	Subsidy amount spread equally over 4 years at 20% (using PMT function)
Plan A	1,22,120.00	47,173.63
Plan B	2,56,522.00	99,091.66
Plan C	1,12,666.40	43,521.80
Plan D	1,73,913.33	67,180.83

As can be seen from the table, the lowest cost option for the city is plan C.