Answered step by step
Verified Expert Solution
Question
1 Approved Answer
What are the Excel Formulas for Question C, QuestionD and E for the following attachment? I am having trouble understanding how the numbers came about?
What are the Excel Formulas for Question C, QuestionD and E for the following attachment? I am having trouble understanding how the numbers came about?
Question C =$713.061 - What is the Formula?
Question D =$1,061.032 - What is the Formula?
Question E =$1,096.539 - What is the Formula?
Student Chapter: Problem: Rachel Matties 8 23 Valuation of Stocks and Corporations Value Drivers in the Free Cash Flow Valuation Mo Start with the partial model in Ch08 Build a Model.xlsx on the textbook's Web site. Traver-Dunlap Corpor 15% weighted average cost of capital (WACC). Its most recent sales were $980 million and its total net op capital is $870 million. The following shows estimates of the forecasted growth rates, operating profitabi and capital requirement ratios for the next three years. All of these ratios are expected to remain constan third year. Use this information to answer the following questions. Estimated Data for Traver-Dunlap Corporation Forecast 1 20% 12% 80% 35% Annual sales growth rate Operating profitability (NOPAT/Sales) Capital requirement (OpCap/Sales) Tax rate 2 6% 10% 80% 35% 3 6% 10% 80% 35% a. Use the data to forecast sales, net operating profit after taxes (NOPAT), total net operating capital (OpC cash flow (FCF), growth rate in FCF, and return on invested capital (ROIC) for the next three years. What growth rate for Year 3 and how does it compare with the growth rate in sales? What is the ROIC for Year does it compare with the 15% WACC? Sales Net operating profit after taxes Total net operating capital FCF = NOPAT - Investment in OpCap Growth in FCF ROIC = NOPAT/OpCap Current 0 $980 $870 Forecast Year 1 2 $1,176.00 $1,246.56 $141.12 $124.66 $940.80 $997.25 $70.32 $68.21 -3% 15.00% 12.50% 3 $1,321.35 $132.14 $1,057.08 $72.30 6.0% 12.50% the FCF rate in year 3 is equal to the growth in sales, the year 3 ROIC is 12.5%, and is less than the WACC. b. What is the value of operations at Year 3, Vop,3? What is the current value of operations, Vop,0? How doe operations at Year 0 compare with the total net operating working capital at Year 3, and what might expla relationship? Free cash flow at beginning of the constant growth phase (FCF3) = Weighted average cost of capital (WACC) = Constant growth rate (gL) = HV3 = Vop, 3 = Present value of HV = Present value of free cash flows = Total value of operations at Year 0, Vop, 0 = $72.300 15.00% 6% $851.539 $559.901 $160.262 $720.162 The value of operations at year 0 is larger than the total net operating capital at year 3. The ROIC and WACC/gL rates might explain this. The value of operations at year 0 is larger than the total net operating capital at year 3. The ROIC and WACC/gL rates might explain this. c. Suppose the growth rates for Years 2, 3, and thereafter can be increased to 7%. What is the new value operations? Did it go up or down? Why did it change in this manner? Sales growth rates after Year 1 = Total value of operations at Year 0, Vop, 0 = 7% $713.061 The value decreased this may be because TNOC decreases. d. Return the growth rates to the original values. Now suppose that the capital requirement ratio can be d 60% for all three years and thereafter. What is the new value of operations? Did it go up or down relative original base case? Why did it change in this manner? Capital requirement ratios = Total value of operations at Year 0, Vop, 0 = 60% $1,061.032 The new value of operations is larger than the originial. This is because a lower capital requirement has a postive effect on value of operations. e. Leave the capital requirement ratios at 60% for all three years and thereafter, but increase the sales gr Years 2, 3, and thereafter to 7%. What is the new value of operations? Did it go up or down relative to the scenarios? Why did it change in this manner? Sales growth rates after Year 1 = Capital requirement ratios = Total value of operations at Year 0, Vop, 0 = 7% 60% $1,096.539 The value is larger than the first scenario, but smaller than the second. The FCF's and captial requirement ratio each led to this. 1/1/2015 Corporations Cash Flow Valuation Model site. Traver-Dunlap Corporation's has a 0 million and its total net operating h rates, operating profitability ratios, xpected to remain constant after the l net operating capital (OpCap), free the next three years. What is the FCF What is the ROIC for Year 3 and how operations, Vop,0? How does the value of ear 3, and what might explain this 7%. What is the new value of Hint: Create a scenario and copy the new scenario's output as a value. requirement ratio can be decreased to d it go up or down relative to the Hint: Create a scenario and copy the new scenario's output as a value. r, but increase the sales growth rates for o up or down relative to the other Hint: Create a scenario and copy the new scenario's output as a valueStep by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started