Question

Question 1

Using a range of WACC of 8% to 10% and an EBITDA multiple of 9 to 11 times to calculate the terminal value,

please establish a valuation range (EV) for ARAG. In Excel, you will need to use the NPV function (=NPV) and the

data table of the What-If Analysis to answer this question (see instructions in the template).

Questions (SECTION 1)
Question 1		Year 1	Year 2	Year 3	Year 4	Year 5
UFCF		11.0	12.0	13.0	14.0	15.0
EBITDA (year 5)						21.0
Multiple						10
Terminal value						210.0
Total Cash Flows		11.0	12.0	13.0	14.0	225.0
WACC	9%
NPV	186.4
Sensitivity Analysis - Data table	186.4	8%	9%	10%
	9	179.9	172.7	165.9
	10	194.2	186.4	179.0
	11	208.5	200.0	192.0
Minimum	165.9
Maximum	208.5

Answer 1

? - . Sign in X GS FILE HOME INSERT PAGE LAYOUT % Cut Calibri 11 LE Copy Paste B IU - Format Painter Clipboard FORMULAS DATA Á A === A E = = REVIEW Open Cheegg WACC - Excel VIEW DEVELOPER ACROBAT & Wrap Text Number Merge & Center, $,% , 2.09 P D X AutoSum" AY H Conditional Format as Formatting Table Styles Cell Styles Insert Delete Format - Cells Fill Clear Sort & Find & Filter - Select Editing Font Alignment Number L M N O P Q R S E17 X fac {=TABLE(C11,H7)} B C D E F G H I J K 2 Particulars Year 1 Year 2 Year 3 Year 4 Year 5 Total 4 Unlevered Free Cash Flow (UFCF) 11 12 13 14 15 65 6 EBITDA 21 7 Multiplier 8 Terminal Value 210 (EBITDA*Multiplier) (Terminal Value + Year 5 Cash 10 Total cash flow - 12 13 14 225 Flow = 210+15 = 225) 11 WACC 996 12 NPV $186.4 13 Formula Used for NPV NPV(C14,C13,D13, E13,F13,613) 14 WACC 15 Sensitivity Analysis - Data Table $186.4 8% 9% 10% Formula Used 16 9 179.9 172.7 165.9 Min 165.9 MIN(D16:F18) 17 10 194.21 186.4! 179.0 Max 208.5 MAX(D16:F18) 18 11 208.5 200.0 192.0 20 To get Table Value Through What If Analysis use following step 21 1. Find NPV through NPV formula in cell - C12 22 2. Give Reference of C12 cell to C15 cell 23 3. Select entire matrix from cell C15 to F18 24 4. Go to Data Tab--->What if Analyis-->Data Table 5. In Data table Use Raw Reference as c11 (Because we have put WACC in a raw) and Column reference as H7 (Because we put 25 Multiplier Range in Column) and press enter. You will get the result Sheet1 Sheet2 READY Multiplier @ U - + 93% 11:46 AM A O 9 30/11/2019 0