Excel Analytics 11-01 (Static) Return on Investment (ROI) [LO11-1]
To complete this activity, you will need to have Excel installed on your computer. This exercise requires you to work in Excel and answer questions in Connect. You will read a brief scenario and then download an Excel file that you will need to complete the requirements in Parts 1 through 9b of this exercise.
Some of the requirements include brief video tutorials on using Excel functions. After viewing the tutorials, you will then use what you learned to work directly in Excel to answer the required questions in Connect.
[The following information applies to the questions displayed below.]
Edman Company is a merchandiser that has provided the following balance sheet and income statement for this year. \begin{tabular}{|c|c|c|c|} \hline & & \begin{tabular}{c} Beginning \\ Balance \end{tabular} & \begin{tabular}{l} Ending \\ Balance \end{tabular} \\ \hline \multicolumn{4}{|l|}{ Assets } \\ \hline Cash & & $62,800 & $150,000 \\ \hline Accounts receivable & & 160,000 & 180,000 \\ \hline Inventory & & 230,000 & 240,000 \\ \hline Property, plant \& equipment (net) & & 833,000 & 793,000 \\ \hline Other assets & & 37,000 & 37,000 \\ \hline Total assets & & $1,322,800 & $1,400,000 \\ \hline \multicolumn{4}{|l|}{ Liabilities \& Stockholders' Equity } \\ \hline Accounts payable & & $70,000 & $80,000 \\ \hline Bonds payable & & 550,000 & 550,000 \\ \hline Common stock & & 410,000 & 410,000 \\ \hline Retained earnings & & 292,800 & 360,000 \\ \hline \multicolumn{2}{|l|}{ Total liabilities \& stockholders' equity } & $1,322,800 & $1,400,000 \\ \hline & This Year & & \\ \hline Sales & $2,500,000 & & \\ \hline \multicolumn{4}{|l|}{ Variable expenses: } \\ \hline Cost of goods sold & 1,600,000 & & \\ \hline Variable selling expense & 240,000 & & \\ \hline Total variable expenses & 1,840,000 & & \\ \hline Contribution margin & 660,000 & & \\ \hline \multicolumn{4}{|l|}{ Fixed expenses: } \\ \hline Fixed selling expenses & 220,000 & & \\ \hline Fixed administrative expenses & 300,000 & & \\ \hline Total fixed expenses & 520,000 & & \\ \hline Net operating income & 140,000 & & \\ \hline Interest expense (8%) & 44,000 & & \\ \hline Net income before tax & 96,000 & & \\ \hline Tax expense (30\%) & 28,800 & & \\ \hline Net income & $67,200 & & \\ \hline \end{tabular} 2. Refer to the "Requirement 2 DuPont Diagram" tab within your Excel workbook. For this year, complete the diagram by using appropriate formulas and reference cells. (In some instances your formulas and reference cells will refer to the Requirement 1 Financials tab.) a. Which choice shows the formulas used to compute the margin and turnover? b. What is this year's margin, turnover, and return on investment (ROI)? For next year, the company is interested in pursuing one of three proposed courses of action. Each of these three alternatives wi independently evaluated in subsequent requirements. Save your work in Excel, then enter your answers to questions 2a and 2b in the tabs below. Which choice shows the formulas used to compute the margin and turnover? Save your work in Excel, then enter your answers to questions 2a and 2b in the tabs below. What is this year's margin, turnover, and return on investment (ROI)? Note: Round your answers to 2 decimal places. A1 A B C D F G H I J E K L M N 2 3 4 5 6 7 8 9 10 11 12 Sales Cost of goods sold \begin{tabular}{|c|c|c|} \hline \multirow[t]{2}{*}{ Net operating income } & \multirow[b]{2}{*}{ Margin } & \\ \hline & & \\ \hline \multicolumn{3}{|l|}{} \\ \hline \multirow[t]{2}{*}{ Sales } & & \\ \hline & x & Return on investment \\ \hline \end{tabular} \begin{tabular}{|l|} \hline \end{tabular} 13 14 15 16 17 18 Sales Cash 19 20 21 Accounts receivable 22 23 24 25 26 27 28 29 30 31 32 \begin{tabular}{c} \hline Current assets \\ + \end{tabular} , Requirement 1 Financials Requirement 2 DuPont Diagram Requirement 3 Financials Requirement 4 DuPont Diagram Requirement 5 Financials Requir
