n \"wow Home a Hamade_MiIIer-Nobles_7e-13e-Using_Excel_Cth-chMLstart Insert PageLayout Formulas Data Review View - Cut Calibri (Body) v 11 v A A 7 gL Wrap Text v General v E 3E c Ev v [3 Copy v .54 > v ' T Mer seem\" v 11 gig Conditional Format $ Format w 9 U\" 'v Formatting as Table EVEVEV LAutoSuvaYV/(DV El FIII v l'a .tf Insert Delete Format \\ sorta smug. / Clear v Filter Select 0 Subscription Required to Edit and Save Start your free one month trial of Microsoft 366 or sign in to activate an existing subscription. Activate czz ' Return on Investment (ROI) A B C D E F G H | J 2 Using Excel for key performance indicators (KPIs) 3 4 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 6 Division Operating Income Average Total Assets Net Sales Prot Margin Ratio Asset Turnover Ratio (ROI) 7 East 5 1,580,000.00 12,000,750.00 S 25,900,000.00 8 North 650,000.00 4,875,000.00 6,337,500.00 9 South 275,050.00 995,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. Ifyou 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 3. Create an Excel table. 28 b. Change the name ofan 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 youranswers to three decimal places. Instructions ENTERANSWERS + + 158% AH ? C ... Hamade_Miller-Nobles_7e-13e-Using_Excel_ChM9-ChM7_Start Home Insert Page Layout Formulas Data Review View Share & Cut Calibri (Body) 11 AA 29 Wrap Text v General j21ERO1JFkriG... jpillHVpoqTr... _ AutoSum [ Copy Fill Paste Format B I U v MVA Merge & Center v $ ~ % " Conditional Format IYKF1iLQ6y5D... obEXCHIrOG8... Insert Delete Format Sort & Find & Formatting as Table Clear v 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 A Excel Hints An Excel table will allow you to create the formula once an ler row, rows of data, and formula columns. To create a table from the data: . Use CTRL-T to convert data to a table. Confirm the data range for the table able to create a table.) c. The default table format appears as Be TOTAL ASSETS 650,000.00 4.875,000.00 6,387,500.00 2,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. 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 Table Name : Solar_ KPI create fomentstools 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. 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 a. Sales cell for East, and click the Enter key. Operating Income Average Total Assets 1,580,000.00 Net Sales Profit Margin Ratio 25,900,000.00 =[@[Operating Income]] 650,000.00 4,875,000.00 275,050.00 995,090.00 855,770.00 ,300,000.00 13,800,000.00 9,200,000.00 $80,000.00 12 090.750.00 Profit Margin Ratio |Asset Turnov 650,000.00 4,875,000.00 6,337 500.00 El@ [Operating Incomell/[@[Net Sales]] 2,300,000.00 ' 13 ,800, 000.00 ' 9.200, 000: 00 xamine the formula: Operating Incomell/[@[Net Sales ]] bol is generated automatically, and refers to a row in a table. That is, the Profit Margin Ratio for the East Division is equ 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. ROUND(@[Operating Incomell/[@[Net Sales]],3) When you update the formula, the change is automatically replicated down the column. Saving & Submitting Solution 1 Save file to desktop. a. Create folder on desktop, and label COMPLETED EXCEL PROJECTS 2 Upload and submit your file to be graded. a. Navigate back to the activity wi k 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 d . Click Submit button under step 4 lewing Results click on movies who who is you were working on Click on Project link; this will bring up your Score Summary 4 Within Score Summary window, click on Download (upper right) to download the spreadsheet with feedback Instructions ENTERANSWERS + Ready 66%GI l3] D Miller-Nobles (7e139) - Using Excel cthchM7 v2_lnstructions Home Insert Design Layout References Mailings Review View 0 Subscription Required to Edit and Save Start your free one month trial of Microsoft 366 or sign in to activate an existing subscription. Activate Grader , instructions Accounting 2019 Project Miller-Nobles (7el13e) - Using Excel ChM9/ChM7 v2 Project Description: Miller-Nobles (7el13e) - Using Excel ChMQ/ChM7 +'Steps to Perform: Step Instructions Pfizls'ifle Download and open the excel spreadsheet, Miller~NobIesi7e-1le-UsingiExceLChMQ- O 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 M a. Rename the table as W. b. Select Medium 7 table style for the table. Use table arithmetic for the formulas (rounded to three decimal places) for the following: (12 pm) 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 excel activity window, and progress to Step 3. Follow on screen instructions. (To review score. navigate to Results in the Wu and click on Review link.) Total Points Page'lofl 214words III:X EnglishlUmtedStates) . + 184%