Chapter 24 Using Excel P24-37 Using Excel for key performance indicators (KPIs) The US Solar Company has data for the four divisions for the year, and wants the results for the three specified key performance indicators (KPIs). Return on Investment Division Operating Income Average Total Assets Net Sales Profit Margin Ratio Asset Turnover Ratio (ROI) East 1,580,000.00 $ 12,000,750.00 S 25,900,000.00 North 650,000.00 4,875,000.00 5,337,500.00 South 275,050.00 995,090.00 855,770.00 West 2,300,000.00 13,800,000.00 9,200,000.00 Requirements Create an Excel table from the data. Rename the table as Solar_KPI. Select another table style for the table. Use table arithmetic for the formulas (rounded to three decimal places) for Profit Margin Ratio Asset Turnover Ratio C. Return on Investment (ROI) Excel Skills 1. Excel tables a. Create an Excel table b. Change the name of an Excel table C. Change the style of the table d Create a calculated column. 2. ROUND function Excel Hints An Excel table will allow you to create the formula once and have it automatically replicate down the rows of the table. An Excel table has a header row, rows of data, and formula columns. 1. To create a table from the data: Place the cursor anywhere in the data. b. Use CTRL-T to convert data to a table. Confirm the data range for the table. (Alternatively, you can use Insert > Table to create a table.) c. The default table format appears as Division . Operating Incom | Average Total Asset - Net Sales . Profit Margin Rati . Asset Turnover Rati .| Return on Investment (RO . East 1,580.000.00 12.000,750.00 25,900.000.00 North 650,000.00 4,875,000.00 6,337,500.00 South 275.050.00 995,090.00 855,770.00 West .300,000.00 13.800,000.00 | 9,200,000.00 Click anywhere in the table to cause the Table Tools Design Tab to appear.2. Rename the table as Solar_KPI. With the Table Tools Design Tab showing, the table name will be on the left-hand side. You must use contiguous characters for a table name (no spaces). Files Home Table Name: Solar KPI With the Table Tools Design Tab showing, click on the Table Styles drop-down to select another table style. Table Styles is on the right-hand side of the tab. Create formulas rounded to three decimal places. a. In the Profit Margin Ratio cell for the East Division, enter the formula by entering the equals sign (=), then click in the Operating Income cell for East, add a division sign (/), then click in the Net Sales cell for East, and click the Enter key. Operating Income Average Total Assets Net Sales Profit Margin Ratio 1,580,000.00 12,000,750.00 25,900,000,00 =[@ [Operating Income]] 650,000.00 4,875,000.00 6,337,500.00 275,050.00 995,090.00 855,770.00 2,300,000.00 13,800,000,00 9,200,000.00 Operating Income . Average Total Assets . Nut Sales Profit Margin Ratio Asset Turnover 1,580,000.00 12,000,750.00 25,900,000.00 =[@[Operating Income]1/[@[Net Sales]] 650,000.00 1.875,000.00 6,337,500.00 275,050.00 995,090.00 855,770.00 2,300,000.00 13,800,000.00 9.200,000.00 Examine the formula: =[@[Operating Income]]/[@[Net Sales]] The @ symbol is generated automatically, and refers to a row in a table. That is, the Profit Margin Ratio for the East Division is equal to the Operating income for East, divided by the Net Sales for East. Instead of using cell references, the formula is using table references. Notice that when you press the enter key, the formula is replicated down the column. To round the formula to three decimal places, add the ROUND function to the equation. =ROUND([@[Operating Income]]/[@[Net Sales]],3) When you update the formula, the change is automatically replicated down the column.Requirement 1 Create an Excel table from the data. Requirement 2 Rename the table as Solar_KPI. Requirement 3 Select another table style for the table. Requirement 4 Use table arithmetic for the formulas (rounded to three decimal places) for a. Profit Margin Ratio b. Asset Turnover Ratio C. Return on Investment (ROI) Return on Investment Division Operating Income Average Total Assets Net Sales Profit Margin Ratio Asset Turnover Ratio (ROI] East 1,580,000.00 S 12,000,750.00 5 25,900,000.00 North 650,000.00 4,875,000.00 6,337,500.00 South 275,050.00 995,090.00 855,770.00 West 2,300,000.00 13,800,000.00 9,200,000.00