WA ? 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 g Wrap Text v General PSCg+u3+Pd... VTj/8gyn7 AOT... TIX v AutoSum A [ Copy Fill Paste Format B IUV MVAv = = Merge & Center v $ ~ % 9 08 528 Conditional Format x1ZBNmjytHh... Normal 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 F40 Jx B D E F G H M N Format Shape X 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 > Shadow Division Operating Income Average Total Assets Net Sales Profit Margin Ratio Asset Turnover Ratio (ROI) > Reflection 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 > Glow South 275,050.0 995,090.00 855,770.00 West 2,300,000.00 13,800,000.00 ,200,000.00 > Soft Edges Use the blue shaded areas on the ENTERANSWERS tab for inputs. >3-D Format 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. > 3-D Rotation Requirements Possible points Create an Excel table from the data. a. Rename the table as Solar_KPI. b. Select another table style for the table. Use table arithmetic for the formulas (rounded to three decimal places) for 12 a. Profit Margin Ratio b. Asset Turnover Ratio C. Return on Investment (ROI) Excel Skills Excel tables . 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 Use the Excel function ROUND to round your answers to three decimal places 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. 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.) The default table format appears as Division Operating Incom |Average Total Asset Net Sales . Profit Margin Ratio 1,580,000.00 12,000,750.00 25,900,000.00 North 650,000:00 4,875,000.00 6 275,050.00 995,090.00 855,770.00 West 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 2 47 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). 49 File Home Instructions ENTERANSWERS Ready 100%WA ? 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 g Wrap Text v General PSCg+u3+Pd... VTj/8gyn7 AOT... TIX v AutoSum A Paste [ Copy Fill Format B IUV MVAv = = Merge & Center v $ ~ % 9 08 598 Conditional Format x1ZBNmjytHh... Normal 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 F40 Jx B F G H M N Format Shape X Place the cursor anywhere in the data. 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 a > Shadow Division |Operating Incom | Average Total Asset . Net Sales | Profit Margin Ratio 12,000,750.00 25,900,000.00 rover Rati North 650,000.00 4,875,000.00 6,337,500.00 >Reflection South 275,050.00 855,770.00 West 2,300,000.00 3,800,000.00 9,200,000.00 > Glow Click anywhere in the table to cause the Table Tools Design Tab to appear > Soft Edges 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). >3-D Format File Home > 3-D Rotation 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. 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 e Total Assets Net Sales Profit Margin Ratio 1,580,000.00 12,000,750.0 25,900,000.00 =[@[Operating Income]] 650,000.00 4,875,000.00 5,337,500.00 275,050.0 995,090.00 855,770.00 2,300,000.00 13,800,000.00 9,200,000.00 Operating Incon Average Total Assets Net Sales Profit Margin Ratio |Asset Turnover 1,580,000.00 12,000,750.00 25,900,000.00 =[@[Operating Income]]/[@[Net Sales]] 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 xamine 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 Saving & Submitting Solution Save file to desktop. a. Create folder on desktop, and label COMPLETED EXCEL PROJECTS 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 Instructions ENTERANSWERS Ready 100%GI Home Insert Design Layout References Mailings r3 Miller-Nobles (7e139) - Using Excel cthchM7 v2_lnstructions Review View 0 Subscription Required to Edit and Save Start your free one month trial of Microsoft 366 or sign into activate an existing subscription. Page 1 of1 214 words [13 English {United States) Grader , Instructions Accounting 2019 Proyect Miller-Nobles (Te/139) - Using Excel ChM9/ChM7 v2 Project Description: Miller-Nobles (7e/13e) - Using Excel ChM9/ChM7 ISteps to Perform: Step Instructions P732253 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 excel activity window, and progress to Step 3. Follow on screen instmctions. (To review score, navigate to Results in the MM and click on Review link.) Total Points + Activate 184%