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)

QUESTIONS:

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 arithmetic for the formulas( rounded to three decimal places) for:

a. Profit Margin Ratio

b. Asset Turnover Ratio

c. Return on Investment (ROI)

Picture right below shows how to complete this and please show cell references please:)

The picture below just shows you how to create the table for requirement 1 :)

Respesialty Accounting and Performance Evaluation Blog Bir key performance indications The US Solar Company has con for the four difor the year and waters of the most Ratan REN Operating se ARTA True Marginatie Ass Turnen E TRR arth So South 11 335 Like the blat dels on the ENTERANSWERS ALWAYS Silves and forest wine appropria civilupport you will be war Requirt Chatroulable from the Renate Solu h. Scooter table style Bear the table the table wife for the formed to redeemul place for of Margine 1. Asscomover Ratio Return immesen (HO) Ed Ski Bato Channel Chanthawaran 6. Change the style of the table d Crui calculadom 2 Uite the function ROUND to mount your store della to in As Excel table will allow you to create the formance and have anonically replies browse the table Antables bases and fomnule.com Telefone del Mae un anywhere in the di CTRL-T to cover de to be Confirm the data as the table (Altya Tohlo c The default format panas Click anywhere on the table to cause the Table Tools Desa Topo Kenanie telesola KPL With Table Top Table billeder for at spel Hie Home was the Table Tesle Design Touring click on the Table Styles drop-down seles normesyle Table Salon the right-hand side Chef decimal plus In the Prolit Manis Ratiocell feels East Destec ma wyciemne de Operating med Elis devem then click on the Net Sales call for at and they Erammet On Sall The symbol grated matically and retail That the Holi Margaret the Disqualibet perting income for Fast, divided by the Net Sales Sant Stadig lieferences, the missible de Narar all = tamil paal pross the uter les, ts as 1s Ta on the formulate three decimal de IOUND quatic -ROUNDE Operating In Sie Men you do the other type Create an Excel table from the data Rename the table as Sala KPL b Select Medium 7 table style for the table. Use table arithmetic for the formulas (rounded to three decimal places) for a. Profit Margin Ratio h. Asset Tumover Ratio c. Return on investment (ROI) Always we cell references and formulas where appropriate to receive full credit. If you copy/paste from the Instruction tab you will be marked wrong Division Profit Margin Ratio Asset Turnover Ratio Return on investment (ROI) North South West Operating Income 1.680,000 560,000 264.050 2.260,000 Average Total Assets 12.700.250 4.100.000 1.015.290 13.240.0001 Net Sales 26,700,000 5,700,000 M69.10 9,360,000 HINTS To insert and edit Excel table refer to Excel Help section on the Instructions tab Use the function-ROUND) to calculate profit marginatase turnover ratio and return on investment Responibility Accounting and Performance Evaluation Using Excel for key performance Indicators (KPI) 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 (HOU Division Operating income Average Total Assets Net Sales Profit Martin Statio Asset Turnover Ratio 7 East S 1.650,000.00 12,700,250.00 $ 26,700,000.00 North 560,000.00 4,300,000.00 5,700,000.00 South 264.050.00 1,015,290.00 369,870.00 10 West 2,250,000.00 13.340,000.00 3,360,000.00 1 Use the blue shadedress on the ENTERANSWELS tab for inputs 12 ALWAYS e cell references and formulas where appropriate to receive full credit. If you copy/paste from the instruction tab you will be marked wrong 56 5 Requirements 16 1 Create an Excel table from the data 17 Rename the table as Solar KPI 11 b Select another table style for the table. 19 2 Use table arithmetic for the formules rounded to three decimal places) for 20 Profit Marin Ratio 21 Asset Turnover Ratio 22 Return on investment (ROI) 34 35. Si 1 Excel tables 22 Create an Excel table 1. Change the name of an Excel table Change the style of the table. 00 d. Create a calculated column. 31 2. Use the Excel function ROUND o round your answer to the decimal places Create an Excel table from the data Rename the table as Solar KPL . Select Mesum table style for the table 2 Une table arithmetic for the formulas onded to three decimal places for 3. Pro Mangi Ratio b. Asset Turnoverato Return on investment (RON (Always use cell references and formules where appropriate to receive full credit. If you copy/paste from the instruction tab you will be marked wront) loivision 3 Profit Margin Ratio Asset Turnover Ratio Return on investment RON North South West Operating Income 650.000,00 500.000.00 MA050.00 2.000.00 Average Total Assets 13.200.250.00 4,100,000.00 1.01.250.00 11,20,000.00 Net Sales 26.700,000.00 5,700,000.00 859,800 9.300,000.00 HINTS To and edit Excel table refer to Excel pection on the instructions tab 2. Use the functie BOUND to calculate profit margintate setturnover ratio, and return on investment A 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: a. 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 Taat North 1.100.000 1200 3000 DO 0.00 2,100,000.00 Click anywhere in the table to cause the Table Tools Design Tab to appear. 2 Rename the table as Solar_KPL. 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) Fie Home Table Name Solok 1 > 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 then click in the Operating Income cell for fast, add a division sign in then click in the Net Sales cell for East, and click the Enter key Operating come Average Total Assets Not Sales Pront Marcin Rate 1.580.000,00 12.000 750.00 25 900,000.00 Operating incomall 650 000.00 4,875,000.00 5,337,500.00 275,050.00 995,000.00 555,770.00 2.300,000.00 13,800,000.00 9,200,000.00 7 De 000.00 1300 10.00 20020000 0.00 GA000.00 1171000 95.000 115.770.00 2100,000.00 11.100.000.00 9.200.000.00 Lamine the formula telOperating income pret Salell The symbolis generated automatically and refers to a row in a table. That, the Profit Marin Ratio for the East Division as equal to the Operating income for Fast, divided by the tales for East. Instead of using cell references, the formula istable references notice that when you press the enter key, the forma replicated down the column. To round the formula to three decimal pace, add the ROUND function to the equation -ROUND Operating comeNet Sala When you update the formula, the changes automatically replicated down the column 1 2 2