Question

You currently own and operate a bar in Champaign called the Illini Tap (the bar from the financial statement section of the course). Up until now you have only served beer and hard alcohol to your customers (no food), and all liquor served has been purchased through a distributor. Business has been good, but you have just bought and moved into a new building and are looking to further expand the bar’s business.  

The new building you are now located in has more space than the bar’s previous location and the basement of the new building is currently unfinished. You have come up with two different investment possibilities:

Investment Alternative 1: Microbrewery

Renovate the basement into a microbrewery and begin brewing your own beer for sale at the bar.

Investment Alternative 2: Kitchen

Renovate the basement into a kitchen and begin to serve a full menu of food at the bar.

Information:

You have estimated that both projects will require an initial investment of $100,000 for renovations and new equipment purchases. You have found a local bank that will lend 70% of the initial investment, financed over 5 years at 6% per year. You can assume that the entire initial investment will be depreciated using the straight-line method over a 10-year period.

You expect both investments to generate additional revenues of $50,000 and additional expenses of $32,000 in the first year. Both the revenues and expenses directly related to the new investments are expected to grow at a rate of 2% each year.

Effects on other sales:

If you decide to go with the microbrewery and start selling your own beer, you expect sales of other beverages to decline by $3000 in the first year. This loss in sales is expected to grow at a rate of 1% each year.

If you decide on the kitchen investment and start serving food, you expect beverage sales to increase by $3000 in the first year. This gain in beverage sales is expected to grow at a rate of 1% each year.

Other information you might need:

Illini Tap – Financial Information
D/A	0.70
Interest rate on debt (rdebt)	6%
Required rate of return on equity (requity)	40%
Marginal Tax Rate	25%

Using the above information, answer the following questions and perform the analysis outlined below.

1. What is the Illini Tap’s weighted-average-cost-of-capital (WACC)? (10 pts.)

2. Calculate the NATCF series, and find the NPV of each investment using the WACC as the discount rate. (50 pts.)

3. Find the IRR of each investment. (10 pts.)

4. Find the MIRR of each investment using the WACC as the discount rate. (20 pts.)

5. Using the NPV as your decision criteria, which investment would you choose as the owner of the Illini Tap? What if you made the decision based on IRR or MIRR? (10 pts.)

6. Suppose that the microbrewery did NOT cause the decline in other sales and that serving food did NOT cause an increase in other sales, but everything else outlined in the information above is the same. Basically, ignore the information in the effects on other sales section.

How would the NPV, IRR, and MIRR for the two investments compare? Note: You should not necessarily have to do any additional calculations for this question. You should be able to say something about the NPV, IRR, and MIRR for the two investments given the other information.  

Answer 1

Note: As HOMEWORKLIB's answering policy, in case of multiple subparts question, only the first 4 parts are required to answer. Hence, the solution is given only for part 1 to 4

The solution to part 1)

Calculation of Cost of Debt (K_d)

= Interest * (1-tax)

= [ 6% *(1-25%)

= 4.50%

Cost of Equity (K_e) = 40%

D/A of 70% would mean that the total asset has finance 70% by debt

Hence,

The weighted average cost of capital (WACC) of the company will be calculated as:

	Cost OF Capital	Target Weight	WACC
Long Term Debt	4.50%	70%	3.15%
Equity	40.00%	30%	12.00%
TOTAL		100%	15.15%

Hence, the Illini Tap’s weighted-average-cost-of-capital (WACC) is 15.15%

The Solution to part 2)

Since the bank is ready to finance 70% of the cost, the tax saving on interest will be:

Calculation of the NATCF series, and NPV of Investment alternative 1 Microbrewery using WACC as the discount rate

Year	0	1	2	3	4	5	6	7	8	9	10
Additional Revenues		$        50,000.00	$        51,000.00	$        52,020.00	$        53,060.40	$   54,121.61	$   55,204.04	$   56,308.12	$   57,434.28	$   58,582.97	$   59,754.63
Additional Expenses		$      (32,000.00)	$      (32,640.00)	$      (33,292.80)	$      (33,958.66)	$ (34,637.83)	$ (35,330.59)	$ (36,037.20)	$ (36,757.94)	$ (37,493.10)	$ (38,242.96)
Loss of sales of other beverages		$        (3,000.00)	$        (3,030.00)	$        (3,060.30)	$        (3,090.90)	$   (3,121.81)	$   (3,153.03)	$   (3,184.56)	$   (3,216.41)	$   (3,248.57)	$   (3,281.06)
Depreciation		$      (10,000.00)	$      (10,000.00)	$      (10,000.00)	$      (10,000.00)	$ (10,000.00)	$ (10,000.00)	$ (10,000.00)	$ (10,000.00)	$ (10,000.00)	$ (10,000.00)
Tax Saving on Interest		$          1,050.00	$              840.00	$              630.00	$              420.00	$         210.00	$                   -	$                   -	$                   -	$                   -	$                   -
Profit Before tax		$          6,050.00	$          6,170.00	$          6,296.90	$          6,430.84	$      6,571.97	$      6,720.42	$      7,086.36	$      7,459.94	$      7,841.30	$      8,230.61
Less: Taxes		$        (1,512.50)	$        (1,542.50)	$        (1,574.23)	$        (1,607.71)	$   (1,642.99)	$   (1,680.11)	$   (1,771.59)	$   (1,864.98)	$   (1,960.32)	$   (2,057.65)
Profit After Tax (PAT)		$          4,537.50	$          4,627.50	$          4,722.68	$          4,823.13	$      4,928.98	$      5,040.32	$      5,314.77	$      5,594.95	$      5,880.97	$      6,172.96
Add: Depreciation		$        10,000.00	$        10,000.00	$        10,000.00	$        10,000.00	$   10,000.00	$   10,000.00	$   10,000.00	$   10,000.00	$   10,000.00	$   10,000.00
NATCF		$        14,537.50	$        14,627.50	$        14,722.68	$        14,823.13	$   14,928.98	$   15,040.32	$   15,314.77	$   15,594.95	$   15,880.97	$   16,172.96
Initial Invesment	$            (100,000.00)
WACC	15.15%
NPV	($21,959.24)

Calculation of the NATCF series, and NPV of Investment alternative 2 Kitchen using WACC as the discount rate

Year	0	1	2	3	4	5	6	7	8	9	10
Additional Revenues		$   50,000.00	$   51,000.00	$   52,020.00	$   53,060.40	$   54,121.61	$   55,204.04	$   56,308.12	$   57,434.28	$   58,582.97	$   59,754.63
Additional Expenses		$ (32,000.00)	$ (32,640.00)	$ (33,292.80)	$ (33,958.66)	$ (34,637.83)	$ (35,330.59)	$ (36,037.20)	$ (36,757.94)	$ (37,493.10)	$ (38,242.96)
Gain on sale of beverage		$      3,000.00	$      3,030.00	$      3,060.30	$      3,090.90	$      3,121.81	$      3,153.03	$      3,184.56	$      3,216.41	$      3,248.57	$      3,281.06
Depreciation		$ (10,000.00)	$ (10,000.00)	$ (10,000.00)	$ (10,000.00)	$ (10,000.00)	$ (10,000.00)	$ (10,000.00)	$ (10,000.00)	$ (10,000.00)	$ (10,000.00)
Tax Saving on Interest		$      1,050.00	$         840.00	$         630.00	$         420.00	$         210.00	$                   -	$                   -	$                   -	$                   -	$                   -
Profit Before tax		$   12,050.00	$   12,230.00	$   12,417.50	$   12,612.65	$   12,815.59	$   13,026.48	$   13,455.48	$   13,892.75	$   14,338.44	$   14,792.72
Less: Taxes		$   (3,012.50)	$   (3,057.50)	$   (3,104.38)	$   (3,153.16)	$   (3,203.90)	$   (3,256.62)	$   (3,363.87)	$   (3,473.19)	$   (3,584.61)	$   (3,698.18)
Profit After Tax (PAT)		$      9,037.50	$      9,172.50	$      9,313.13	$      9,459.49	$      9,611.69	$      9,769.86	$   10,091.61	$   10,419.56	$   10,753.83	$   11,094.54
Add: Depreciation		$   10,000.00	$   10,000.00	$   10,000.00	$   10,000.00	$   10,000.00	$   10,000.00	$   10,000.00	$   10,000.00	$   10,000.00	$   10,000.00
NATCF		$   19,037.50	$   19,172.50	$   19,313.13	$   19,459.49	$   19,611.69	$   19,769.86	$   20,091.61	$   20,419.56	$   20,753.83	$   21,094.54
Initial Investment	$ (100,000.00)
WACC	15.15%
NPV	($1,784.56)

The solution to part 3)

Calculation of IRR in investment alternative 1 Microbrewery

Year	0	1	2	3	4	5	6	7	8	9	10
NATCF	$            (100,000.00)	$        14,537.50	$        14,627.50	$        14,722.68	$        14,823.13	$   14,928.98	$   15,040.32	$   15,314.77	$   15,594.95	$   15,880.97	$   16,172.96
WACC	15.15%
IRR	=IRR(values,[guess])
IRR	8.22%

The IRR of the investment alternative 1 Microbrewery is 8.22%

Calculation of IRR in investment alternative 2 Kitchen

Year	0	1	2	3	4	5	6	7	8	9	10
NATCF	$ (100,000.00)	$   19,037.50	$   19,172.50	$   19,313.13	$   19,459.49	$   19,611.69	$   19,769.86	$   20,091.61	$   20,419.56	$   20,753.83	$   21,094.54
WACC	15.15%
IRR	=IRR(values,guess)
IRR	14.62%

The IRR of the investment alternative 2 Kitchen is 14.62%

The Solution to part 4)

Calculation of MIRR in investment alternative 1 Microbrewery

Year	0	1	2	3	4	5	6	7	8	9	10
NATCF	$            (100,000.00)	$        14,537.50	$        14,627.50	$        14,722.68	$        14,823.13	$   14,928.98	$   15,040.32	$   15,314.77	$   15,594.95	$   15,880.97	$   16,172.96
WACC	15.15%
MIRR	=MIRR(values,finance_rate,reinvest_rate)
MIRR	11.84%

MIRR =MIRR(values,finance_rate,reinvest_rate)

where finance rate is the required rate of return that is WACC

and reinvest rate is the rate at which cash flows are reinvested which in our case is WACC

Calculation of MIRR in investment alternative 2 Kitchen

Year	0	1	2	3	4	5	6	7	8	9	10
NATCF	$ (100,000.00)	$   19,037.50	$   19,172.50	$   19,313.13	$   19,459.49	$   19,611.69	$   19,769.86	$   20,091.61	$   20,419.56	$   20,753.83	$   21,094.54
WACC	15.15%
MIRR	=MIRR(values,finance_rate,reinvest_rate)
MIRR	14.91%