1. 

Extra Credit Assignment (Total Extra Credit Points =9) Instructions: Create a professionally formatted Excel file to complete Sections 1 through 9 below. Each section must be placed on a different sheet in the file. The file will be presented to your boss. All linkages to relevant cells must be shown if calculations are required in the file (no hard coding). Section 1( Extra Credit Points = 1) Diaz Manufacturing Company, a fictional Houston-based provider of petroleum and industrial equipment and services worldwide. Diaz Manufacturing was formed in 2013 as a spin-off of several divisions of Cooper Industries. The firm specializes in the design and manufacturing of systems used in petroleum production and has two divisions: (1) Diaz Energy Services, which sells oil and gas compression equipment, and (2) Diaz Manufacturing, which makes valves and related parts for energy production. In 2017 Diaz Manufacturing's sales increased to $1.56 billion, an increase of 12.8 percent from the previous year. A letter to stockholders in the 2017 annual report stated that management did not expect earnings in 2018 to exceed the 2017 earnings. The reason for caution was that Diaz's earnings are very susceptible to changes in the political and economic environment in the world's energy-producing regions, and in 2017 the environment in the Middle East was highly unstable. Management reassured investors, however, that Diaz had the financial strength and the management team needed to weather any economic adversity. Arrange in Excel the following accounts and their relevant accounts to create a properly labeled and formatted comparative balance sheet for Diaz Manufacturing. Note all values are in millions. Section 4( Extra Credit Points = 1) (a) Using the general format below, create a statement of cash flows for Diaz manufacturing. Diaz Manufacturing Statement of Cash Flows For the Fiscal Year Ending December 31, 2017 (\$ millions) Operating Activities Long-term Investing Activities Financing Activities Cash Reconciliation Accounts to be arranged accordingly into the statement above. Some items need to calculate or obtained from a previous Diaz Manufacturing financial statement: Cash and securities at end of year Increase in inventories Property, equipment, and other assets Net income Depreciation and amortization Increase in goodwill and other assets Increase in notes payable Net increase/decrease in cash and marketable securities Increase in accounts payable Increase in accounts receivable Increase in accrued income taxes Net cash provided/used in investing activities Purchase of treasury stock Cash and securities at beginning of year Increase in long-term debt Net cash provided/used by operating activities Decrease in other current assets Net cash provided/used by financing activities Section 5( Extra Credit Points = 1) (a) Using an organization chart inserted into Excel, illustrate the interrelationships between the following statements for Diaz Manufacturing: Income Statement 12/31/17 Balance Sheet 12/31/2016 Statement of Cash Flows 12/31/2017 Balance Sheet 12/31/2017 Statement of Retained Earnings 12/31/2017 (b) Show all the steps to calculate the Free Cash Flow to the firm for 2017. In some texts this is also referred to as the cash flow to investors (CFI) Section 2( Extra Credit Points =0.5) (a) Arrange in Excel the following accounts and their relevant amounts to create a properly labeled and formatted comparative income statement for Diaz Manufacturing, including the following sub-total titles were appropriate: "Earnings before interest, taxes, depreciation and amortization", "Earnings before interest and taxes", "Earnings before taxes". Note all values are in millions. Section 3( Extra Credit Points =0.5) Use the information given so far for Diaz Manufacturing to create a Statement of Retained Earnings for the fiscal year ending December 31, 2017 (\$ millions) on a new Excel Sheet. (a) Create a common size balance sheet for Diaz manufacturing 2017 and 2016 (one statement), using total assets as the base. Write a brief interpretation of your analysis (inserted in a text box). The accounts listed for 2015, are the only accounts to be displayed in the common size statements prepared. (b) Create a common size income statement for Diaz manufacturing 2017 and 2016 (one statement), using net sales as the base. Write a brief interpretation of your analysis (inserted in a text box). The accounts listed for 2015, are the only accounts to be displayed in the common size statements prepared. Create a table and find the following ratios for Diaz Manufacturing in 2017 (keep list in order given). Apart from the values for each ratio, the table should also contain a brief interpretation of each ratio. Current ratio Quick Ratio Inventory turnover Day's sales in inventory Accounts receivable turnover Day's sales outstanding Total asset turnover Fixed asset turnover Total debt ratio Debt-to-equity ratio Equity multiplier Times interest earned Cash coverage Gross profit margin Operating profit margin Net profit margin EBIT return on assets Return on assets Return on equity Price-Earnings ratio Earnings per share Market-to-book ratio Recreate, the exact same diagram below, but fill in each box with the appropriate value to show the relations in the DuPont system of Analysis for Diaz Manufacturing in 2017 (\$ millions). Values could be dollar amounts, percentages, or decimals. One must NOT copy and paste the picture to answer this section. It can be created in MS Word, Windows Paint or other software and carried over as a .jpeg file to the MS Excel sheet. Section 9 -(Extra Credit Points =2) (a) Recreate the table below and fill in the relevant ratio values for Diaz manufacturing in 2017, calculated from a previous section. (b) Calculate the differences between the ratios of Diaz Manufacturing to its peer group (competitor) and display in an additional column to the right. (c) Write a brief report describing the differences found between the ratios of Diaz and its peer group and identify areas that require further analysis. (d) From your experience completing parts 1 through 9, write a brief explanation as to why there are limitations to financial analysis. Extra Credit Assignment (Total Extra Credit Points =9) Instructions: Create a professionally formatted Excel file to complete Sections 1 through 9 below. Each section must be placed on a different sheet in the file. The file will be presented to your boss. All linkages to relevant cells must be shown if calculations are required in the file (no hard coding). Section 1( Extra Credit Points = 1) Diaz Manufacturing Company, a fictional Houston-based provider of petroleum and industrial equipment and services worldwide. Diaz Manufacturing was formed in 2013 as a spin-off of several divisions of Cooper Industries. The firm specializes in the design and manufacturing of systems used in petroleum production and has two divisions: (1) Diaz Energy Services, which sells oil and gas compression equipment, and (2) Diaz Manufacturing, which makes valves and related parts for energy production. In 2017 Diaz Manufacturing's sales increased to $1.56 billion, an increase of 12.8 percent from the previous year. A letter to stockholders in the 2017 annual report stated that management did not expect earnings in 2018 to exceed the 2017 earnings. The reason for caution was that Diaz's earnings are very susceptible to changes in the political and economic environment in the world's energy-producing regions, and in 2017 the environment in the Middle East was highly unstable. Management reassured investors, however, that Diaz had the financial strength and the management team needed to weather any economic adversity. Arrange in Excel the following accounts and their relevant accounts to create a properly labeled and formatted comparative balance sheet for Diaz Manufacturing. Note all values are in millions. Section 4( Extra Credit Points = 1) (a) Using the general format below, create a statement of cash flows for Diaz manufacturing. Diaz Manufacturing Statement of Cash Flows For the Fiscal Year Ending December 31, 2017 (\$ millions) Operating Activities Long-term Investing Activities Financing Activities Cash Reconciliation Accounts to be arranged accordingly into the statement above. Some items need to calculate or obtained from a previous Diaz Manufacturing financial statement: Cash and securities at end of year Increase in inventories Property, equipment, and other assets Net income Depreciation and amortization Increase in goodwill and other assets Increase in notes payable Net increase/decrease in cash and marketable securities Increase in accounts payable Increase in accounts receivable Increase in accrued income taxes Net cash provided/used in investing activities Purchase of treasury stock Cash and securities at beginning of year Increase in long-term debt Net cash provided/used by operating activities Decrease in other current assets Net cash provided/used by financing activities Section 5( Extra Credit Points = 1) (a) Using an organization chart inserted into Excel, illustrate the interrelationships between the following statements for Diaz Manufacturing: Income Statement 12/31/17 Balance Sheet 12/31/2016 Statement of Cash Flows 12/31/2017 Balance Sheet 12/31/2017 Statement of Retained Earnings 12/31/2017 (b) Show all the steps to calculate the Free Cash Flow to the firm for 2017. In some texts this is also referred to as the cash flow to investors (CFI) Section 2( Extra Credit Points =0.5) (a) Arrange in Excel the following accounts and their relevant amounts to create a properly labeled and formatted comparative income statement for Diaz Manufacturing, including the following sub-total titles were appropriate: "Earnings before interest, taxes, depreciation and amortization", "Earnings before interest and taxes", "Earnings before taxes". Note all values are in millions. Section 3( Extra Credit Points =0.5) Use the information given so far for Diaz Manufacturing to create a Statement of Retained Earnings for the fiscal year ending December 31, 2017 (\$ millions) on a new Excel Sheet. (a) Create a common size balance sheet for Diaz manufacturing 2017 and 2016 (one statement), using total assets as the base. Write a brief interpretation of your analysis (inserted in a text box). The accounts listed for 2015, are the only accounts to be displayed in the common size statements prepared. (b) Create a common size income statement for Diaz manufacturing 2017 and 2016 (one statement), using net sales as the base. Write a brief interpretation of your analysis (inserted in a text box). The accounts listed for 2015, are the only accounts to be displayed in the common size statements prepared. Create a table and find the following ratios for Diaz Manufacturing in 2017 (keep list in order given). Apart from the values for each ratio, the table should also contain a brief interpretation of each ratio. Current ratio Quick Ratio Inventory turnover Day's sales in inventory Accounts receivable turnover Day's sales outstanding Total asset turnover Fixed asset turnover Total debt ratio Debt-to-equity ratio Equity multiplier Times interest earned Cash coverage Gross profit margin Operating profit margin Net profit margin EBIT return on assets Return on assets Return on equity Price-Earnings ratio Earnings per share Market-to-book ratio Recreate, the exact same diagram below, but fill in each box with the appropriate value to show the relations in the DuPont system of Analysis for Diaz Manufacturing in 2017 (\$ millions). Values could be dollar amounts, percentages, or decimals. One must NOT copy and paste the picture to answer this section. It can be created in MS Word, Windows Paint or other software and carried over as a .jpeg file to the MS Excel sheet. Section 9 -(Extra Credit Points =2) (a) Recreate the table below and fill in the relevant ratio values for Diaz manufacturing in 2017, calculated from a previous section. (b) Calculate the differences between the ratios of Diaz Manufacturing to its peer group (competitor) and display in an additional column to the right. (c) Write a brief report describing the differences found between the ratios of Diaz and its peer group and identify areas that require further analysis. (d) From your experience completing parts 1 through 9, write a brief explanation as to why there are limitations to financial analysis