Excel Analytics 11-1 (Static) Return on Investment (ROI) [LO11-1] [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. Ending Balance: Assets Cash Accounts receivable! Inventory Property, plant & equipment (net) Other assets Total assets Liabilities & Stockholders' Equity Accounts payable Bonds payable Common stock Retained earnings Total liabilities & stockholders' equity Beginning Balance $ 62,800 $ 150,000 160,000 180,000 230,000 240,000 833,000 793,000 37,000 37,000 $1,322,800 $1,400,000 $ 70,000 550,000 $ 80,000 550,000 410,000 410,000 292,800 360,000 $1,322,800 $1,400,000 0 Sales This Year $2,500,000 Variable expenses: Cost of goods sold Variable selling expense Total variable expenses 1,600,000 240,000 1,840,000 Contribution margin 660,000 Fixed expenses: Fixed selling expenses. 220,000 Fixed administrative expenses 300,000 Total fixed expenses 520,000 Net operating income 140,000 Interest expense (8) 44,000 Net income before tax 96,000 ces Tax expense (30%) Net income 28,800 67,200 Click here to download the Excel template. which you will use to answer the questions that follow. Click here for a a brief tutorial on Charts in Excel. Excel Analytics 11-1 (Static) Part 2 2. Refer to the "Requirement 2 DuPont Diagram" tab within your template. 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 alternatives will be independently evaluated in subsequent requirements. Complete this question by entering your answers in the tabs below. Req 2A Req 28 Which choice shows the formulas used to compute the margin and turnover? Turnover Margin -H5 H10 H19 H25 H19 H25 -H5*H10 H5/H10 -H19/H25 H19/H25 H5/H10 Excel Analytics 11-1 (Static) Part 2 2. Refer to the "Requirement 2 DuPont Diagram" tab within your template. 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 alternatives will be independently evaluated in subsequent requirements. Complete this question by entering your answers in the tabs below. Req 2A 628 What is this year's margin, turnover, and return on investment (ROI)? (Round your answers to 2 decimal places.) Margin Tumover Return on investment (ROI) Charts: Requirement 2 DuPont Diagram Requirement 3 Financials Charts are a way to easily visualize your data sets in order to present findings or to gain further insights. There are a wide variety of chart types that you can utilize in Microsoft Excel. The most common types used are bar/column charts to show comparisons of values and line charts to show trends. Below is an example of how to set up a chart: 1. The first step is to select the data you would like to chart out. In general, you want to format your data in a way that you have column headers like days and then row headers such as your cost or company. See the below as an example: 2. 3. 2. 3. Day 1 Day 2 Day 3 Cost A Cost B 55 65 50 88 80 85 Highlight the data yodoshat to create a chart: 65 5. Day 1 Day 2 Day 3 6. Cost A 55 3. Go to the "Inse Cost B 88 Cost C 888 65 60 382 50 80 85 70 65 4. Click on "Reco 888 a. irt" window Within this window you have two tabs: "Recommended Charts" and "All Charts" 1. The "Recommended Charts" tab will give you several charts that are recommended for the specific data set you have chosen 2. The "All Charts" tab will let you dictate the type of chart you want. You will see that on this tab, the left-hand side shows all the different chart types and as you click on them you will see the right-hand pane generate the different subtypes of charts available for that group. b. On the "All Charts" tab, choose the "Line" option from the left-hand pane 1. On the right-hand side look through the various options. If we want to show a trend line with the data points marked, we would choose the option titled "Line with Markers". Chose this option and click "OK" 2. The below chart will generate 3. 4. 3. 4. 5. 6. Chart Title 100 7. 90 8. 80 70 9. 60 50 10. 40 30 11. 20 10 5. You will see a. Doubl b. C. h as the following: Day 1 Day 2 Day 3 Cost A Cost Cost C ment 3 Financials You can right click on eitner the x (nere vay 1, Day 2,...) or y (nere U-100) axes and click "Format Axis" pane will generate giving you a plethora of options to modify the axes Another set of easy tools is to click on the chart and within the "Chart Design" ribbon click on "Add Chart Element" within the "Chart Layouts" section 1. Within this section you can add titles to the axes, add other labels, create legends, etc. A 1 Month 2 January B Selling Price $ 41,178,364 3 February $ 17,651,444 4 March $ 20,099,321 5 April $ 6 May $ 7 June 555 1,245,889 $ 38,252,793 840,532 8 October $ 31,010,733 9 November $ 39,013,975 10 December $ 26,411,168, 11 12 13 14 15 16 17 18 19 20 DAY C D 114 630 13 2451 941 22 1818 1-10 402 ito M N