PLEASE SHOW THE STEPS IN EXCEL for K and L ONLY (STEP-BY-STEP) (Proof of work)

Your employer also is considering the acquisition of Hatfield Medical Supplies. You have gathered the following data regarding Hatfield, with all dollars reported in millions: (1) most recent sales of $2,000; (2) most recent total net operating capital, OpCap 5 $1,120; (3) most recent operating profitability ratio, OP 5 NOPAT/Sales 5 4.5%; and (4) most recent capital requirement ratio, CR 5 OpCap/Sales 5 56%. You estimate that the growth rate in sales from Year 0 to Year 1 will be 10%, from Year 1 to Year 2 will be 8%, from Year 2 to Year 3 will be 5%, and from Year 3 to Year 4 will be 5%. You also estimate that the long-term growth rate beyond Year 4 will be 5%. Assume the operating profitability and capital requirement ratios will not change. Use this information to forecast Hatfields sales, net operating profit after taxes (NOPAT), OpCap, free cash flow, and return on invested capital (ROIC) for Years 1through 4. Also estimate the annual growth in free cash flow for Years 2 through 4. The weighted average cost of capital (WACC) is 9%. How does the ROIC in Year 4 compare with the WACC?

j. What is the horizon value at Year 4? What is the total net operating capital at Year 4? Which is larger, and what can explain the difference? What is the value of operations at Year 0? How does the

k. What are value drivers? What happens to the ROIC and current value of operations if expected growth increases by 1 percentage point relative to the original growth rates (including the long-term growth rate)? What can explain this? Hint: Use Scenario Manager.

l. Assume growth rates are at their original levels. What happens to the ROIC and current value of operations if the operating profitability ratio increases to 5.5%? Now assume growth rates and operating profitability ratios are at their original levels. What happens to the ROIC and current value of operations if the capital requirement ratio decreases to 51%? Assume growth rates are at their original levels. What is the impact of simultaneous improvements in operating profitability and capital requirements? What is the impact of simultaneous improvements in the growth rates, operating profitability, and capital requirements? Hint: Use Scenario Manager.