Question

You run The Empourium, a local brewery with an attached bar. Sales have slowed down and you feel like you need to do something to jazz things up. You’ve saved up $300,000 in free cash, and you demand an annualized return of 7% on any investment you make. Here are some things you could do with that money. You can buy a set of three new fermentation tanks. Up-front cost (including purchase price, setup, and inspection) is $50,000. The tanks will let you increase the variety of beers you produce. You’d sell about $25,000 more beer per year, but the additional ingredients and labor will run you an extra $18,000 every year. The tanks will last about 10 years before needing to be scrapped. You can enter into a concessions contract with a local sports stadium. At a $60,000 up-front cost, you’d have Empourium beer being sold at very high prices during all major sporting events, concerts, etc. This would net you about $80,000 in extra revenue per year, but the beer would cost about $51,000 per year to make, and you’d also have an extra $10,000 in annual transportation costs. The contract would be good for five years. You could start selling food at your restaurant. Your state has some wacky liquor laws, though, and selling food means you’d have a one-time licensing fee of $250,000. After all is said and done, you’ll be earning an extra $80,000 per year for the foreseeable future (let’s say 10 years). You could try rebranding. Hire some marketing and communications folks to do a slick logo redesign, change the beer’s packaging, etc. A local firm has quoted you a price of $200,000 to do this, and they figure this new rebranding would last for 6 years. Here are your questions: 1. Keeping in mind that you’ve only got $300,000 to work with, which combination of the first three choices will give you the highest total NPV? (It could just be one, more than one, or even none at all, as long as your initial outlay doesn’t exceed $300,000.) 2. Are there any among the first three choices that you should refuse regardless of how much money you have? (Do any have a negative net present value, in other words?) 3. (This one’s a challenge.) Consider the fourth option. What’s the lowest your extra annual earnings would have to be in order to justify spending $200,000 on the rebranding effort?

Answer 1

1)    To get highest NPV- select only option 3 at initial cost of 250000, giving an NPV of $ 311,886.52

first lets determine the profitability index and NPV of each of the alternatives

Option 1

Initial Cost                                               -50000              

Net Cash inflow per year (PMT)           7000           (25000-18000)

Period of inflow (n)                                   10               

Discount rate ( i)                                      7%  

Option 2

Initial Cost                                               -60000              

Net Cash inflow per year (PMT)           19,000       (80000-51000-10000)

Period of inflow (n)                                   5                

Discount rate ( i)                                      7%  

Option 3

Initial Cost                                               -60000              

Net Cash inflow per year (PMT)           19,000       (80000-51000-10000)

Period of inflow (n)                                   5                

Discount rate ( i)                                      7%  

For above parameter- lets find the PV of Future Cash inflows by using PV function.

Lets compute profitability index P I    =   PV of future inflows/ Initial Cost

To this PV we will add the initial cost to get the NPV        

from the above, option 3 has the highest profitabiity index, hence it is preferred first.

Total capital available 300,000.   for undertaking Option 3, outlay is 250,000. So balance available for other options will be is 50,000. (300000-250000). with 50k we can chose only option 1 ( since option 2 initial cost is 60k). But option 1 has a negtive NPV, and hence it is not preferred.

Hence to get highest NPV- select only option 3 at initial cost of 250000, giving an NPV of $ 311,886.52

excel workings

2) option 1 gives a negative NPV of -834.93. as mentioned above.Hence it must not be considered whatsoever

3) lowest extra annual earnings of option 4 must be $107,391.64   per annum

out of the first 3 option, we have already selected the 3rd option with an NPV of 311,886.52. so the 4th option to be able to competer with 3rd option must have a minimum NPV of 311,886.52

hence,                   NPV of 4th option      = 311,886.52

           PV of Cash inflows   - Initial Cost =311,886.52

PV of Cash inflows   - 200000      =311,886.52

   PV of Cash inflows = 511,886.52

so we solve for the yearly cash inflow for 6 years, where PV =511,886.52 at discount rate of 7%

PV of Cash inflows          PV        = 511,886.52   

Net Cash inflow per year (PMT)    = ?      

Period of inflow (n)                                   6 years               

Discount rate ( i)                                      7%  

lowest extra annual earnings of option 4 must be $107,391.64   per annum

excel formula