AH ? " C ... Hamade_Miller-Nobles_7e-13e-Using_Excel_ChM9-ChM7_Start Home Insert Page Layout Formulas Data Review View Picture Format Share & Cut Calibri (Body) A ag Wrap Text v General WfQd +FwlXZ3 ... YQ4Oui14 kxZ+... AutoSum [ Copy Fill Paste Uvv MVA LEE Merge & Center v v % 9 |408 98 Conditional Format 26DjimHhQ70... Normal Insert Delete Format Clear v Sout & Find & Format Formatting as Table Filter Select x Subscription Required to Edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription. Activate Picture 4 fx B C D E F G H 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). 5 Return on Investment Division Operating Income Average Total Assets Net Sales Profit Margin Ratio Asset Turnover Ratio ROI East $ ,580,000.00 $ 12,000,750.00 $ 25,900,000.00 8 North $50,000.00 4,875,000.00 6,337,500.00 South 275,050.00 95,090.00 855,770.00 10 West 2,300,000.00 13,800,000.00 9,200,000.00 11 Use the blue shaded areas on the ENTERANSWERS tab for inputs. 12 Always use cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instructions tab you will be marked wrong. 13 14 15 Requirements Possible points 16 1 Create an Excel table from the data. 3 17 a. Rename the table as Solar_KPI. 18 b. Select another table style for the table. 19 2 Use table arithmetic for the formulas (rounded to three decimal places) for 12 20 a. Profit Margin Ratio 21 b. Asset Turnover Ratio 22 C. Return on Investment (ROI) 23 24 25 Excel Skills 26 1 Excel tables 27 a. Create an Excel table. 28 b. Change the name of an Excel table. 29 C. Change the style of the table. 30 d. Create a calculated column. 31 2 Use the Excel function ROUND to round your answers to three decimal places. 32 Instructions ENTERANSWERS Ready 156%AH ? " C ... Hamade_Miller-Nobles_7e-13e-Using_Excel_ChM9-ChM7_Start Home Insert Page Layout Formulas Data Review View Picture Format Share Cut Calibri (Body) A ag Wrap Text v General WfQd +FWIXZ3.. YQ4Oui14 kxZ+... AutoSum [ Copy Fill Paste Uvv Merge & Center v v % 9 | 408 598 Conditional Format 26DjimHhQ70... Normal Insert Delete Format Clear v Sout & Find & Format Formatting as Table Filter Select x Subscription Required to Edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription. Activate Picture 4 fx A B C D E F G H 33 34 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. 35 36 1 To create a table from the data: 37 a. Place the cursor anywhere in the data. 38 b. Use CTRL-T to convert data to a table. Confirm the data range for the table. 39 (Alternatively, you can use Insert > Table to create a table.) 40 C. The default table format appears as 41 42 on Investment (ROY Division |Operating Incom . Average Total Asset. Net Sales .|Profit Margin Rati.|Asset Turnover Rati|Return on Investment (RO 43 East 1,580,000.00 12,000,750.00 25,900,000.00 North 650,000.0 4,875,000.00 6,337,500.00 44 South 275,050.00 995,090.00 855,770.00 45 West ,300,000.00 3,800,000.00 9,200,000.00 46 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). 47 48 File 49 Home 50 51 Table Name: 52 Solar_KPI 53 54 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. - W 55 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 56 a. Sales cell for East, and click the Enter key. 57 Operating Income Average Total Assets Net Sales Y Profit Margin Ratio 58 1,580,000.00 12,000,750.00 25,900,000.00 =[@ [Operating Income]] 59 650,000.00 4,875,000.00 6,337,500.00 60 275,050.00 995,090.00 855,770.00 61 2,300,000.00 13,800,000.00 9,200,000.00 62 Instructions ENTERANSWERS Ready 156%AH ? " C ... Hamade_Miller-Nobles_7e-13e-Using_Excel_ChM9-ChM7_Start Home Insert Page Layout Formulas Data Review View Picture Format Share & Cut Calibri (Body) A ag Wrap Text v General WfQd +FWIXZ3.. YQ4Oui14 kxZ+... AutoSum [ Copy Fill Paste Format Uvv Merge & Center v v % 9 | 68 98 Conditional Format 26DjimHhQ70... Normal Insert Delete Format Find & Formatting as Table Clear v Sout & Filter Select x Subscription Required to Edit and Save Start your free one month trial of Microsoft 365 or sign in to activate an existing subscription. Activate Picture 4 fx A B C D E F G H 62 63 Operating Income Average Total Assets Net Sales Profit Margin Ratio Asset Turnover 64 1,580,000.00 12,000,750.00 25,900,000.00 =[@[Operating Income]]/[@ [Net Sales]] 65 650,000.00 4,875,000.00 6,337,500.00 66 275,050.00 995,090.00 855,770.00 67 Examine the fe,320,000.00 13,800,000.00 9,200,000.00 68 =[@[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 69 East. Instead of using cell references, the formula is using table references. 70 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 71 To round the formula to three decimal places, add the ROUND function to the equation. 72 =ROUND([@[Operating Income]]/[@[Net Sales]],3) 73 When you update the formula, the change is automatically replicated down the column. 74 75 76 Saving & Submitting Solution 77 1 Save file to desktop. 78 a. Create folder on desktop, and label COMPLETED EXCEL PROJECTS 79 2 Upload and submit your file to be graded. 80 Navigate back to the activity window - screen where you downloaded the initial spreadsheet 81 b. Click Choose button under step 3; locate the file you just saved and click Open 82 Click Upload button under step 3 83 d. Click Submit button under step 4 84 85 86 Viewing Results 87 1 Click on Results tab in MyAccountingLab 88 2 Click on the Assignment you were working on 89 3 Click on Project link; this will bring up your Score Summary 90 4 Within Score Summary window, click on Download (upper right) to download the spreadsheet with feedback 91 92 93 Instructions ENTERANSWERS Ready 156%GI l3] r3 Miller-Nobles (7e13e) - Using Excel ChMSChM7 v2_lnstructions (2) Home Insert Design Layout References Mailings Review View 0 Subscription Required to Edit and Save Start your free one month trial of Microsott 366 or sign in to activate an existing subscription. Activate Graderr Instruchons Accountmg 2019 Preject Miller-Nobles (7el13e) - Using Excel ChM9/ChM7 v2 Project Description: Miller-Nobles (7e/13e) - Using Excel ChM9/ChM7 :Steps to Perform: step Instructions P732233 Download and open the excel spreadsheet. Miller-Nobles_7e-13e-Using_ExceI_ChM9- 0 ChM7_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.) The US Solar Company has data for the four divisions for the year, and wants the results for the three specied key performance indicators (KPIs). Create an Excel table from the data (3 ptslw a. Rename the table as W. b, Select Medium 7 table style for the table. Use lable arithmetic for the formulas (rounded to three decimal places) for the following: (12 P's) a. Prot 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 Instructions tab you will be marked wrong.) Once completed. save the project to the desktop, File name can be anything. but DO NOT include spaces Return to auto-graded exoel activity window, and progress to Step 3' Follow on screen instmctions. (To review soore, navigate to Results in the MM and click on Review link.) Total Points Page'Iofl 214words III:X EnglishIUnItedStates) . + 184%