Step 1: Create a data input field. This has been done for you in cells (A20. A25). Step 2: Create an output field. This has been dono for you in cells (B19. E19). Notice these cells pick up values from the cash flow statement and ratio tab. Step 3: Highlight the cells bordered by your inputs (A20, A25) and your output fields (B19. E19). This area is the cells A19. E25. Step 4: From the main menu select "Data" and then "Table". Then Indicate the actual input cell. Step 4: For Excel 2007! Click on the "Data" tab, followed by "Whatif Analysis", and then "Data table". \begin{tabular}{|c|c|c|} \hline Du pont ROE decomposition & 2011 & 2012 \\ \hline Return on equity (\%) & 28.0% & \#DIV/0! \\ \hline Profit margin (%) & 1.4% & 0.0% \\ \hline Asset turnover (x) & 5.3 & \#DIVI0! \\ \hline Asset to equity (x) & 3.8 & \#DIVI0! \\ \hline \end{tabular} \begin{tabular}{|c|c|c|} \hline Profitability Ratios: & & \\ \hline Return on equity (\%) & 28.0% & \#DIV/0! \\ \hline Return on assets (\%) & 7.4% & \#DIVIO! \\ \hline Profit margin (\%) & 1.4% & \\ \hline Gross margin (\%) & 10.0% & \\ \hline \end{tabular} \begin{tabular}{|c|c|} \hline \multicolumn{2}{|l|}{ Tumover-control ratios } \\ \hline Asset turnover (x) & 5.37 \#DIVIO! \\ \hline Fixed-asset turnover (x) & 12.5r \#DIV/O! \\ \hline Accounts receivable turnover (x) & 33.3 \\ \hline Inventory turnover (x) & 15.0r \#DIVIO! \\ \hline Days' sales in cash (days) & 73 \\ \hline Collection period (days) & 11.0 \\ \hline Inventory period & 24.3r \#DIV/0! \\ \hline Payables period (days) & 12.2r \#DIV/0! \\ \hline Cash conversion cycle (days) & 23.1 \#DIV /0 ! \\ \hline \end{tabular} \begin{tabular}{|c|c|} \hline Leverage and liquidity ratios & \\ \hline Asset to equity (x) & 3.8r \#DIV/0! \\ \hline Total liabilities to asset (\%) & 73.7%r \#DIVIO! \\ \hline Total liabilities to equity (\%) & 280.0% " \#DIV/0! \\ \hline Long-term debt to equity (\%) & 200% \#DIV/OI \\ \hline Times interest earned (x) & 3.0 \#DIV/O! \\ \hline Current ratio (x) & 28r \#DIVIO! \\ \hline Acid test (x) & 1.3r \#DIVIo! \\ \hline \end{tabular} tes: Read these notes. Complete the following pro forma financial statements for 2012 , using the % of sales method. Be sure you understand how all yellow and blue fields are calculated. Complete the projections table on the Income Statement tab for different levels of sales growth. You should complete the formulas required to calculate 2012 pro forma figures based on the links and factors provided. Use $ signs to make absolute cell references if appropriate. This should allow you to change cell D6 on the income statement and have the change be reflected through out your pro forma. I have completed factors and links for sales and cash. The blue fields represent the factor and related forecast links for each account. For this simple pro forma we are assuming most accounts are directly linked to sales. In addition assume the following. 1) Sales are forcast to grow at 25%. (First use 10% and compare to the classroom answers) 2) Any external financing requirements will be met by debt. 3) The company's dividend payout ratio is 40%. 4) The company pays 10% interest on long term debt at yearend. Be sure to set iterations to 100 to solve circular reference. Office button Excel Options Formulas Check iterations and set equal to 100 Step 1: Create a data input field. This has been done for you in cells (A20. A25). Step 2: Create an output field. This has been dono for you in cells (B19. E19). Notice these cells pick up values from the cash flow statement and ratio tab. Step 3: Highlight the cells bordered by your inputs (A20, A25) and your output fields (B19. E19). This area is the cells A19. E25. Step 4: From the main menu select "Data" and then "Table". Then Indicate the actual input cell. Step 4: For Excel 2007! Click on the "Data" tab, followed by "Whatif Analysis", and then "Data table". \begin{tabular}{|c|c|c|} \hline Du pont ROE decomposition & 2011 & 2012 \\ \hline Return on equity (\%) & 28.0% & \#DIV/0! \\ \hline Profit margin (%) & 1.4% & 0.0% \\ \hline Asset turnover (x) & 5.3 & \#DIVI0! \\ \hline Asset to equity (x) & 3.8 & \#DIVI0! \\ \hline \end{tabular} \begin{tabular}{|c|c|c|} \hline Profitability Ratios: & & \\ \hline Return on equity (\%) & 28.0% & \#DIV/0! \\ \hline Return on assets (\%) & 7.4% & \#DIVIO! \\ \hline Profit margin (\%) & 1.4% & \\ \hline Gross margin (\%) & 10.0% & \\ \hline \end{tabular} \begin{tabular}{|c|c|} \hline \multicolumn{2}{|l|}{ Tumover-control ratios } \\ \hline Asset turnover (x) & 5.37 \#DIVIO! \\ \hline Fixed-asset turnover (x) & 12.5r \#DIV/O! \\ \hline Accounts receivable turnover (x) & 33.3 \\ \hline Inventory turnover (x) & 15.0r \#DIVIO! \\ \hline Days' sales in cash (days) & 73 \\ \hline Collection period (days) & 11.0 \\ \hline Inventory period & 24.3r \#DIV/0! \\ \hline Payables period (days) & 12.2r \#DIV/0! \\ \hline Cash conversion cycle (days) & 23.1 \#DIV /0 ! \\ \hline \end{tabular} \begin{tabular}{|c|c|} \hline Leverage and liquidity ratios & \\ \hline Asset to equity (x) & 3.8r \#DIV/0! \\ \hline Total liabilities to asset (\%) & 73.7%r \#DIVIO! \\ \hline Total liabilities to equity (\%) & 280.0% " \#DIV/0! \\ \hline Long-term debt to equity (\%) & 200% \#DIV/OI \\ \hline Times interest earned (x) & 3.0 \#DIV/O! \\ \hline Current ratio (x) & 28r \#DIVIO! \\ \hline Acid test (x) & 1.3r \#DIVIo! \\ \hline \end{tabular} tes: Read these notes. Complete the following pro forma financial statements for 2012 , using the % of sales method. Be sure you understand how all yellow and blue fields are calculated. Complete the projections table on the Income Statement tab for different levels of sales growth. You should complete the formulas required to calculate 2012 pro forma figures based on the links and factors provided. Use $ signs to make absolute cell references if appropriate. This should allow you to change cell D6 on the income statement and have the change be reflected through out your pro forma. I have completed factors and links for sales and cash. The blue fields represent the factor and related forecast links for each account. For this simple pro forma we are assuming most accounts are directly linked to sales. In addition assume the following. 1) Sales are forcast to grow at 25%. (First use 10% and compare to the classroom answers) 2) Any external financing requirements will be met by debt. 3) The company's dividend payout ratio is 40%. 4) The company pays 10% interest on long term debt at yearend. Be sure to set iterations to 100 to solve circular reference. Office button Excel Options Formulas Check iterations and set equal to 100