Instructions Points Possible tep 0 1 Download and open the excel spreadsheet, Miller-Nobles_6e-12e- Using Excel Ch24_Start.xlsx. This spreadsheet includes 2 tabs, you will enter all answers on the second tab. ENTERANSWERS (All answers should be entered in the blue highlighted cells.) 3 2. The US Solar Company has data for the four divisions for the year, and wants the results for Dhe three specified key performance indicators (KPIs). Create an Excel table from the data. (3 pts) a. Rename the table as Solar KPI b. Select Medium 7 table style for the table. 12 3 Use table arithmetic for the formulas (rounded to three decimal places) for the following: (12 pts) a. Profit Margin Ratio b. Asset Turnover Ratio c. Return on Investment (ROI) (Always use cell references and formulas where appropriate to receive full credit.) 0 4 Once completed, save the project to the desktop. File name can be anything, but DO NOT include spaces. Return to auto-graded excel activity window, and progress to Step 3. Follow on screen instructions. (To review score, navigate to Results in the MyLab and click on Review link.) D Focus utoSave Off Bounougou Omgba_Miller-Nobles_6e-12e-Using Excel_Ch24_Start - Protected View - le Home Insert Draw Page Layout Formulas Data Review View Help roland thierry bounougou omgba a Share Acrobat PROTECTED VIEW Be careful - files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing E F G H H A B D Responsibility Accounting and Performance Evaluation 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 (ROI) Division Operating Income Average Total Assets Net Sales Profit Margin Ratio Asset Turnover Ratio East S 1,580,000.00 S 12,000,750.00 S 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 Use the blue shaded areas on the ENTERANSWERS tab for inputs. ALWAYS use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instruction tab you will be marked wrong. Possible points 3 Requirements 1 Create an Excel table from the data. a. Rename the table as Solar_KPI. b. Select another table style for the table. 2 Use table arithmetic for the formulas (rounded to three decimal places) for a. Profit Margin Ratio b. Asset Turnover Ratio G. Return on Investment (ROI) 12 Instructions ENTERANSWERS dy 1 3:12 Type here to search * W 72% cs 1/16 B South West C 275,050.00 2,300,000.00 E D 995,090.00 855,770.00 13 800,000.00 9,200,000.00 F G H 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). File Home Table Name: Solar KPI a 3 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. 4 Create formulas rounded to three decimal places. 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 Incomell 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 1,580,000.00 650,000.00 275.050.00 Instructions Average Total Assets - 12,000,750.00 4,875,000.00 995 090.00 ENTERANSWERS Net Sales Profit Margin Ratlo Asset Turnover 25,900,000.00 =@Operating Incomell/ tNet Salesll 6,337,500.00 855.770.00 10 Type here to search 72% A 3:13 AM 4/16/2021 E 26 C 7 3 B D 2012 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]l/@[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]l/ @[Net Sales]],3) When you update the formula, the change is automatically replicated down the column 3 Saving & Submitting Solution 1 Save file to desktop. a: Create folder on desktop, and label COMPLETED EXCEL PROJECTS b. Save your solution in the folder you just created; add-solution-date to end of file name 2 Upload and submit your file to be graded. a. Navigate back to the activity window - screen where you downloaded the initial spreadsheet b. Click Choose button under step 3; locate the file you just saved and click Open c. Click Upload button under step 3 d. Click Submit button under step 4 Viewing Results Instructions ENTERANSWERS G H B D E a. Navigate back to the activity window - screen where you downloaded the initial spreadsheet b. Click Choose button under step 3: locate the file you just saved and click Open C. Click Upload button under step 3 d. Click Submit button under step 4 ng Results 1 Click on Results tab in My Accountinglab 2 Click on the Assignment you were working on 3 Click on Project link; this will bring up your Score Card 4 Within Score Card window, click on Live Comments Report (lower right) to download spreadsheet with feedback OTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing 2 D E F G H B 1 Create an Excel table from the data. a. Rename the table as Solar KPI. b. Select Medium 7 table style for the table. 2 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) (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instruction tab you will be marked wrong.) Profit Margin Ratio Asset Turnover Ratio Return on Investment $ Division East North South West Operating Income Average Total Assets 1,580,000.00 $ 12,000 750.00 S 650,000.00 4,875,000.00 275,050.00 995,090.00 2,300,000.00 13,800,000.00 Net Sales 25,900,000,00 6,337,500.00 855,770.00 9,200,000.00 HINTS o insert and edit Excel table refer to Excel Help section on the Instructions tab. Instructions ENTERANSWERS Type here to search 1 3:1 4/15 W 72% AutoSave On Bounougou Omgba_Miller-Nobles_6e-12-Using Excel_Ch24 Start - Protected View roland thierry bounougou omgba Share ile Home Insert Draw Page Layout Formulas Data Review View Help Acrobat PROTECTED VIEW Be careful_files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View Enable Editing Ix 2 A H B C. D E F G 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) (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instruction tab you will be marked wrong.) Profit Margin Ratio Asset Turnover Ratio Return on Investment (ROI) $ Division East North South West Operating Income Average Total Assets 1,580,000.00 $ 12,000,750.00 S 650,000.00 4,875,000.00 275,050.00 995,090.00 2,300,000.00 13,800,000.00 Net Sales 25,900,000.00 6,337,500.00 855,770.00 9,200,000.00 HINTS 1. To insert and edit Excel table refer to Excel Help section on the Instructions tab. 2. Use the function=ROUND() to calculate profit margin ratio, asset turnover ratio, and return on investment. Instructions Points Possible tep 0 1 Download and open the excel spreadsheet, Miller-Nobles_6e-12e- Using Excel Ch24_Start.xlsx. This spreadsheet includes 2 tabs, you will enter all answers on the second tab. ENTERANSWERS (All answers should be entered in the blue highlighted cells.) 3 2. The US Solar Company has data for the four divisions for the year, and wants the results for Dhe three specified key performance indicators (KPIs). Create an Excel table from the data. (3 pts) a. Rename the table as Solar KPI b. Select Medium 7 table style for the table. 12 3 Use table arithmetic for the formulas (rounded to three decimal places) for the following: (12 pts) a. Profit Margin Ratio b. Asset Turnover Ratio c. Return on Investment (ROI) (Always use cell references and formulas where appropriate to receive full credit.) 0 4 Once completed, save the project to the desktop. File name can be anything, but DO NOT include spaces. Return to auto-graded excel activity window, and progress to Step 3. Follow on screen instructions. (To review score, navigate to Results in the MyLab and click on Review link.) D Focus utoSave Off Bounougou Omgba_Miller-Nobles_6e-12e-Using Excel_Ch24_Start - Protected View - le Home Insert Draw Page Layout Formulas Data Review View Help roland thierry bounougou omgba a Share Acrobat PROTECTED VIEW Be careful - files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View Enable Editing E F G H H A B D Responsibility Accounting and Performance Evaluation 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 (ROI) Division Operating Income Average Total Assets Net Sales Profit Margin Ratio Asset Turnover Ratio East S 1,580,000.00 S 12,000,750.00 S 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 Use the blue shaded areas on the ENTERANSWERS tab for inputs. ALWAYS use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instruction tab you will be marked wrong. Possible points 3 Requirements 1 Create an Excel table from the data. a. Rename the table as Solar_KPI. b. Select another table style for the table. 2 Use table arithmetic for the formulas (rounded to three decimal places) for a. Profit Margin Ratio b. Asset Turnover Ratio G. Return on Investment (ROI) 12 Instructions ENTERANSWERS dy 1 3:12 Type here to search * W 72% cs 1/16 B South West C 275,050.00 2,300,000.00 E D 995,090.00 855,770.00 13 800,000.00 9,200,000.00 F G H 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). File Home Table Name: Solar KPI a 3 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. 4 Create formulas rounded to three decimal places. 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 Incomell 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 1,580,000.00 650,000.00 275.050.00 Instructions Average Total Assets - 12,000,750.00 4,875,000.00 995 090.00 ENTERANSWERS Net Sales Profit Margin Ratlo Asset Turnover 25,900,000.00 =@Operating Incomell/ tNet Salesll 6,337,500.00 855.770.00 10 Type here to search 72% A 3:13 AM 4/16/2021 E 26 C 7 3 B D 2012 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]l/@[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]l/ @[Net Sales]],3) When you update the formula, the change is automatically replicated down the column 3 Saving & Submitting Solution 1 Save file to desktop. a: Create folder on desktop, and label COMPLETED EXCEL PROJECTS b. Save your solution in the folder you just created; add-solution-date to end of file name 2 Upload and submit your file to be graded. a. Navigate back to the activity window - screen where you downloaded the initial spreadsheet b. Click Choose button under step 3; locate the file you just saved and click Open c. Click Upload button under step 3 d. Click Submit button under step 4 Viewing Results Instructions ENTERANSWERS G H B D E a. Navigate back to the activity window - screen where you downloaded the initial spreadsheet b. Click Choose button under step 3: locate the file you just saved and click Open C. Click Upload button under step 3 d. Click Submit button under step 4 ng Results 1 Click on Results tab in My Accountinglab 2 Click on the Assignment you were working on 3 Click on Project link; this will bring up your Score Card 4 Within Score Card window, click on Live Comments Report (lower right) to download spreadsheet with feedback OTECTED VIEW Be careful-files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing 2 D E F G H B 1 Create an Excel table from the data. a. Rename the table as Solar KPI. b. Select Medium 7 table style for the table. 2 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) (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instruction tab you will be marked wrong.) Profit Margin Ratio Asset Turnover Ratio Return on Investment $ Division East North South West Operating Income Average Total Assets 1,580,000.00 $ 12,000 750.00 S 650,000.00 4,875,000.00 275,050.00 995,090.00 2,300,000.00 13,800,000.00 Net Sales 25,900,000,00 6,337,500.00 855,770.00 9,200,000.00 HINTS o insert and edit Excel table refer to Excel Help section on the Instructions tab. Instructions ENTERANSWERS Type here to search 1 3:1 4/15 W 72% AutoSave On Bounougou Omgba_Miller-Nobles_6e-12-Using Excel_Ch24 Start - Protected View roland thierry bounougou omgba Share ile Home Insert Draw Page Layout Formulas Data Review View Help Acrobat PROTECTED VIEW Be careful_files from the Internet can contain viruses. Unless you need to edit it's safer to stay in Protected View Enable Editing Ix 2 A H B C. D E F G 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) (Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instruction tab you will be marked wrong.) Profit Margin Ratio Asset Turnover Ratio Return on Investment (ROI) $ Division East North South West Operating Income Average Total Assets 1,580,000.00 $ 12,000,750.00 S 650,000.00 4,875,000.00 275,050.00 995,090.00 2,300,000.00 13,800,000.00 Net Sales 25,900,000.00 6,337,500.00 855,770.00 9,200,000.00 HINTS 1. To insert and edit Excel table refer to Excel Help section on the Instructions tab. 2. Use the function=ROUND() to calculate profit margin ratio, asset turnover ratio, and return on investment