Question

Jack wants to go into partnership with you by opening a franchise and asked you for help in performing an analysis.
The opportunity involves a coffee shop franchise. Which will require a $650,000 buy in. Typical annual operating costs will be $200,000 (cash) and that the forecasted revenues will be $310,000 per year. The Franchisor demands a payment of 12% of Revenues for trademark and business model. You would like to earn at least 8% on this investment and will need to borrow the entire buy-in amount at an interest rate cost of 4%. You plan to conduct your analysis over a 15 year period and will not consider taxes at this time.

Required
1. Find the NPV and IRR of this investment, given the information above.

2. You are skeptical of jack's revenue forecast of $310,000 per year. He did take Math in school and achieved a grade of 95%, however he never attended any classes and was only able to achieve this remarkable grade by plagiarizing from a classmate. You believe that a more realistic revenue forecast will be lower. Conduct a sensitivity analysis by finding the NPV and IRR (similar to Part 1 above) of this investment using $280,000 and $260,000 as the revenue forecast.

3. You believe that you can negotiate a lower payment to the franchisor and also think that if revenues are lower than $310,000 costs will decrease by $20,000. Repeat the same analysis as you did in Part 2 above (using $280,000 and $260,000 forecasted revenue) with annual operating costs of $180,000 and Franchise fee of 9% of revenues

4. Discuss how the sensitivity analysis will affect your decision to buy the franchise. Why don’t you have to recalculate the IRR if you change the desired (discount) interest rate?

Answer 1

1.) NPV (net present value) and IRR (internal rate of return)

Capital requirement = $ 650,000 which is borrowed at 4% interest. So interest cost is $ 26,000 per year

Annual operating cost $ 200,000

Forecasted revenue is $ 310,000

Payment to franchisor 12% of revenue = $ 37,200 per year

Discount rate is = 4%

So cash flow to the Jack annually = Revenue - operating cost - interest cost - payment to franchisor

$ 310,000 - $ 200,000 - $ 26,000 - $ 37,200 = $ 46,800

So to Calculate NPV

NPV =

C = Cash flow per year

r= discounting rate

t= time

n= period

NPV calculates the discount value of all future cash flows and then after miuns initial amount. Here I have used excel to calculate.

Below the image calculates the NPV

NPV is -$ 124,672.57

I have used excel function to find NPV. In excel there function called NPV in which you need to fill details and NPV will calculated.

Here the function image is given.

In rate arguments write discounting rate and in value1 select the cells of cash flow.

IRR

Internal rate of return means it is a rate at which all discount cash flow equal to initial investment.

Initial Investment =

In IRR we find the discounting rate.

So for Jack IRR is calculated as:

Here 1% is IRR for the business

I have used IRR function from excel

In values just select the cells of cash flow.

So NPV and IRR for the business is -$ 124,672.57 and 1% respectively.

2.) Now if revenue is $ 280,000 or $ 260,000 then what is NPV and IRR

Now annual cash flow to Jack will $ revenue miuns the cost which is given above.

So for revenue $ 280,000 - ( $ 200,000 - $ 26,000 - $ 33,600) = $ 20,400

And for revenue $ 260,000 - ($ 200,000 - $ 26,000 - $ 31,200) = $ 2,800

So If you calculate NPV and IRR for both scenario by above discuss method

Calculated values	Original Forecaste revenue ( $ 310,000)	2nd Revenue ($ 280,000)	3rd Revenue ($ 260,000
NPV	($ 124,672.57)	($ 402,632.25)	($ 595,065.88)
IRR	1%	-8%	Very Negative

*The calculation method is same in all scenario

3.) Now lets Operating cost is $ 180,000 and payment ot franchisor at 9% of revenue.

So cash flow to Jack for revenue $ 280,0000 - $ 180,000 - $ 26,000 - $ 25,200 = $ 48800

And for revenue $ 260,000 - $ 180,000 - $ 26,000 - $ 23,400 = $ 30,600

Calculated values	Original Forecaste revenue ( $ 310,000)	2nd Revenue ($ 280,000)	3rd Revenue ($ 260,000
NPV	$ 188,566.52	($ 103,291.05)	($ 297,862.83)
IRR	8%	2%	-4%

4.) By sensitivity analysis if Jack will look earlier ( i.e original forecaste) then he will not go for this business because NPV is in negative and IRR is at 1%. And after that by sensitivty analysis Jack a%lso reduce revenue forecaste and look at the various scenario., and in this analysis he finds that at $ 310,000 revenue and at oparting cost $ 180,000 and 9% payment to franchisor gives him it require rate of return (i.e NPV $ 188,566.52 and IRR is at 8%).

IRR is not require to recalculate because by IRR we find rate at which initial investment and all cash flow become equal so that's why we not require to recalculate IRR because chaging in discounting rate.

Note = All figures in bracket is in negative.