Calculate NOPAT, Net Operating Working Capital, Total Operating Capital, FCF, ROIC, Asset turnover, Asset/Equity, ROE, Operating costs/Sales, Total liability/Total assets, Times Interest Earned Ratios based on the 2015 financial statements; these will be used for 2016 projections. Fill in missing values for cells D26:D42 in the data sheet (posted on Blackboard). This should fill in the missing values for cells D90:D99. Copy and paste [use Paste Special and select "Values" (1,2,3)] these values to E90:E99 under the "Steady" scenario. Then copy and paste these values from E89:E99 to G89:G99 under the heading "Active is" This should fill out forecast factors in column D (relevant cells in the range D 104:D 140). Use these forecast factors to calculate your projected balance sheet and income statements (Rows I04 to 140). Replicate the calculations done in class on the spreadsheet using these forecast factors. Enter the relevant formulas in cells G/45:Gl60. Formulas already entered in cells DI 45: DI 60 provide a good starting point. Make needed modifications; do not forget to calculate the FCF. Once the formulas are entered, save (copy and paste-use paste special) the VALUES in cells E145:E160. The financial requirements are calculated using best estimates. However, you typically will also consider other contingencies in order to be better prepared. To illustrate this type of analysis, recalculate 2016 funds requirements using the percent of sales approach for all 9 feasible scenarios based on following variations: Calculate AFN if HMS ratios equaled industry averages for the following: Growth rate, operating costs/sales, cash/sales, receivables/sales, inventories/sales, FA/Sales, and Payables and accruals/sales. (If AFN is negative, a special dividend will be paid with the excess funds.) Use the values in the data sheet (posted on Blackboard) in cells F90:F99. Copy these values to G90:G99. Save the values from cells G145:G160 to Fl45:Fl60. a) Calculate 2017 and 2018 financial requirements using the percent of sales approach for a sales increase of 15% retaining all original assumptions. Additional funds will be raised in the form of debt with an interest rate of 10%. The interest expense is based on the debt outstanding at the beginning of the year. b) Construct statements of cash flows for 2016, 2017, and 2018. (Assume that depreciation for 2016 is $50 and that this figure grows at 15% each of the next two years. Note that depreciation is already included in Total Operating Costs and will not require a modification of the Income statement.) Your report should have the following elements: 1. Hatfield's financial ratios for 2015 . 2. A 33 matrix which summarizes the AFN for each of the 9 scenarios you are asked to analyze. Fr this analysis, you will use ratios based on Hatfield's current performance. Provide the details of the AFN calculations (income statement and balance sheet in a well-organized Appendix. 3. AFN in 2016, if Hatfield's ratios matched the financial ratios in the target scenario (cells F90:F99) 4. Projections of 2017 and 2018 income statements, balance sheets, and statements of cash flows using ratios based on Hatfield's current performance. Calculate NOPAT, Net Operating Working Capital, Total Operating Capital, FCF, ROIC, Asset turnover, Asset/Equity, ROE, Operating costs/Sales, Total liability/Total assets, Times Interest Earned Ratios based on the 2015 financial statements; these will be used for 2016 projections. Fill in missing values for cells D26:D42 in the data sheet (posted on Blackboard). This should fill in the missing values for cells D90:D99. Copy and paste [use Paste Special and select "Values" (1,2,3)] these values to E90:E99 under the "Steady" scenario. Then copy and paste these values from E89:E99 to G89:G99 under the heading "Active is" This should fill out forecast factors in column D (relevant cells in the range D 104:D 140). Use these forecast factors to calculate your projected balance sheet and income statements (Rows I04 to 140). Replicate the calculations done in class on the spreadsheet using these forecast factors. Enter the relevant formulas in cells G/45:Gl60. Formulas already entered in cells DI 45: DI 60 provide a good starting point. Make needed modifications; do not forget to calculate the FCF. Once the formulas are entered, save (copy and paste-use paste special) the VALUES in cells E145:E160. The financial requirements are calculated using best estimates. However, you typically will also consider other contingencies in order to be better prepared. To illustrate this type of analysis, recalculate 2016 funds requirements using the percent of sales approach for all 9 feasible scenarios based on following variations: Calculate AFN if HMS ratios equaled industry averages for the following: Growth rate, operating costs/sales, cash/sales, receivables/sales, inventories/sales, FA/Sales, and Payables and accruals/sales. (If AFN is negative, a special dividend will be paid with the excess funds.) Use the values in the data sheet (posted on Blackboard) in cells F90:F99. Copy these values to G90:G99. Save the values from cells G145:G160 to Fl45:Fl60. a) Calculate 2017 and 2018 financial requirements using the percent of sales approach for a sales increase of 15% retaining all original assumptions. Additional funds will be raised in the form of debt with an interest rate of 10%. The interest expense is based on the debt outstanding at the beginning of the year. b) Construct statements of cash flows for 2016, 2017, and 2018. (Assume that depreciation for 2016 is $50 and that this figure grows at 15% each of the next two years. Note that depreciation is already included in Total Operating Costs and will not require a modification of the Income statement.) Your report should have the following elements: 1. Hatfield's financial ratios for 2015 . 2. A 33 matrix which summarizes the AFN for each of the 9 scenarios you are asked to analyze. Fr this analysis, you will use ratios based on Hatfield's current performance. Provide the details of the AFN calculations (income statement and balance sheet in a well-organized Appendix. 3. AFN in 2016, if Hatfield's ratios matched the financial ratios in the target scenario (cells F90:F99) 4. Projections of 2017 and 2018 income statements, balance sheets, and statements of cash flows using ratios based on Hatfield's current performance