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.]
[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. Required: Click here to download the Excel workbook, "ExcelAnalytics Returnonlnvestment Template" which you will use to complete the equirements in Parts 1 through 9b of this exercise. 1. Refer to the "Requirement 1 Financials" tab within your Excel workbook. For this year, create formulas within column D that calculate he average balance for each balance sheet account. a. What is the average balance for the accounts receivable, inventory, and retained earnings accounts? b. What is this year's average total liabilities and stockholders' equity? Save your work in Excel, then enter your answers to questions 1a and 1b in the tabs below. What is the average balance for the accounts receivable, inventory, and retained earnings accounts? Save your work in Excel, then enter your answers to questions 1a and 1b in the tabs below. What is this year's average total liabilities and stockholders' equity? A1 Edman Company \begin{tabular}{|c|c|c|c|c|c|c|c|} \hline & A & & B & C & D & E & F \\ \hline 1 & \multicolumn{5}{|c|}{ Edman Company } & & Blue cells can be modified \\ \hline 2 & \multicolumn{5}{|c|}{ Balance Sheets } & & \\ \hline 3 & Balanced Sheet: & & \begin{tabular}{l} Beginning \\ Balance \end{tabular} & \begin{tabular}{l} Ending \\ Balance \end{tabular} & \begin{tabular}{l} Average \\ Balance \end{tabular} & & \\ \hline 4 & Assets & & & & & & \\ \hline 5 & Cash & $ & 62,800 & 150,000 & & & \\ \hline 6 & Accounts receivable & & 160,000 & 180,000 & & & \\ \hline 7 & Inventory & & 230,000 & 240,000 & & & \\ \hline 8 & Property, plant \& equipment (net) & & 833,000 & 793,000 & & & \\ \hline 9 & Other assets & & 37,000 & 37,000 & & & \\ \hline 10 & Total assets & $ & 1,322,800 & 1,400,000 & $ & & \\ \hline \multicolumn{8}{|l|}{11} \\ \hline 12 & Liabilities \& stockholders' equity & & & & & & \\ \hline 13 & Accounts payable & $ & 70,000 & 80,000 & & & \\ \hline 14 & Bonds payable & & 550,000 & 550,000 & & & \\ \hline 15 & Common stock & & 410,000 & 410,000 & & & \\ \hline 16 & Retained earnings & & 292,800 & 360,000 & & & \\ \hline 17 & Total liabilities \& stocholders' equity & $ & 1,322,800 & $1,400,000 & $ & & \\ \hline \multicolumn{8}{|l|}{18} \\ \hline 19 & \multicolumn{7}{|c|}{ Edman Company } \\ \hline 20 & \multicolumn{3}{|c|}{ Income Statement } & & & & \\ \hline 21 & Sales & $ & 2,500,000 & & & & \\ \hline 22 & Variable expenses: & & & & & & \\ \hline 23 & Cost of goods sold & & 1,600,000 & & & & \\ \hline 24 & Variable selling expense & & 240,000 & & & & \\ \hline 25 & Total variable expenses & & 1,840,000 & & & & \\ \hline 26 & Contribution margin & & 660,000 & & & & \\ \hline 27 & Fixed expenses: & & & & & & \\ \hline 28 & Fixed selling expense & & 220,000 & & & & \\ \hline 29 & Fixed administrative expense & & 300,000 & & & & \\ \hline 30 & Total fixed expenses & & 520,000 & & & & \\ \hline 31 & Net operating income & & 140,000 & & & & \\ \hline 32 & Interest expense (8\%) & & - & & & & \\ \hline 33 & Net income before tax & & 140,000 & & & & \\ \hline 34 & Tax expense (30\%) & & 42,000 & & & & \\ \hline 35 & Net income & $ & 98,000 & & & & \\ \hline \end{tabular} Requirement 1 Financials Requirement 2 DuPont Diagram Requirement 3 Financials Requirement 4 DuPont Diagram Requirem
