FIN3310: INTERMEDIATE FINANCIAL ANALYSIS SPREADSHEET ASSIGNMENT: Chapter 8 1. Retrieve the file HWChapter8ValueDrivers. Do not modify the fonts, format, cell addresses, or organizational design displayed in the worksheet file. Values in boldface in the Inputs section display changes from scenario #1 in Chapter 8. 2. Part A reports information regarding Scenario #4 in Chapter 8. Refer to Cells A10:B16 and A20:B24 and apply this information to solve for the values in Cells C20:G24 and B27:B29. Assume there are 50 shares of common stock. Lastly, in Cell A32 display the exact equation used to solve for the value in Cell B27 1 Value Driver Scenarios, Chapter8 Scenario #4 Better Capital Utilization 3 Part A 4 9 Inputs 10 Sales growth in 1 st year 11 |Sales growth in 2nd year 12 Sales growth in 3rd year 13 Long-term sales growth (gl.) 14 Operating profitability (OP) 15 Capital requirement (CR) 16 Cost of capital (WACC 17 18 Scenario #4 19 20 Net sales 21 NOPAT 22 Total net operating capital 23 FCF=NOPAT-Capital 24 ROIC=NOPAT / Capital 25 26 Results 27 Value of operations (Vop) 28 Intrinsic stock price 29 Return on capital (ROIC 30 31 Calculation of Vop, Scenario 4: 32 10.00% 8.00% 7.00% 5.00% 6.00% 52.00% 10.97% Actual Year 1 Year 2 Year 3 Year 4 Year 5 5,000.0 300.0 3,050.0 -260.0 9.84% Scenario #4 FIN3310: INTERMEDIATE FINANCIAL ANALYSIS SPREADSHEET ASSIGNMENT: Chapter 8 1. Retrieve the file HWChapter8ValueDrivers. Do not modify the fonts, format, cell addresses, or organizational design displayed in the worksheet file. Values in boldface in the Inputs section display changes from scenario #1 in Chapter 8. 2. Part A reports information regarding Scenario #4 in Chapter 8. Refer to Cells A10:B16 and A20:B24 and apply this information to solve for the values in Cells C20:G24 and B27:B29. Assume there are 50 shares of common stock. Lastly, in Cell A32 display the exact equation used to solve for the value in Cell B27 1 Value Driver Scenarios, Chapter8 Scenario #4 Better Capital Utilization 3 Part A 4 9 Inputs 10 Sales growth in 1 st year 11 |Sales growth in 2nd year 12 Sales growth in 3rd year 13 Long-term sales growth (gl.) 14 Operating profitability (OP) 15 Capital requirement (CR) 16 Cost of capital (WACC 17 18 Scenario #4 19 20 Net sales 21 NOPAT 22 Total net operating capital 23 FCF=NOPAT-Capital 24 ROIC=NOPAT / Capital 25 26 Results 27 Value of operations (Vop) 28 Intrinsic stock price 29 Return on capital (ROIC 30 31 Calculation of Vop, Scenario 4: 32 10.00% 8.00% 7.00% 5.00% 6.00% 52.00% 10.97% Actual Year 1 Year 2 Year 3 Year 4 Year 5 5,000.0 300.0 3,050.0 -260.0 9.84% Scenario #4