50.1K

Verified Solution

Link Copied!

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). \begin{tabular}{|c|c|c|c|c|c|c|c|} \hline Division & Net Sales & Variable Costs & Fixed Costs & Average Total Assets & Profit Margin Ratio & Asset Turnover Ratio & \begin{tabular}{l} Return on Investment \\ (ROI) \end{tabular} \\ \hline East & 27,070,000.00 & 15,441,300.00 & 10,294,200.00 & 12,270,250.00 & & & \\ \hline North & 5,770,000.00 & 3,162,600.00 & 2,108,400.00 & 4,230,000.00 & & & \\ \hline South & 897,870.00 & 410,016.60 & 273,344.40 & 1,251,290.00 & & & \\ \hline West & 9,730,000.00 & 4,616,300.00 & 2,944,200.00 & 13,620,000.00 & & & \\ \hline \end{tabular} 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. Requirements 1. Create an Excel table from the data. 2. Rename the table as Solar_KPI. 3. Select another table style for the table. 4. Calculate Operating Income in the table. 5. 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) Excel Skills 1 Excel tables a. 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. Requirement 1 Create an Excel table from the data. a. Rename the table as Solar_KPI. b. Select Medium 7 table style for the table. c. Calculate Operating Income in the table. Requirement 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.) \begin{tabular}{|c|c|c|c|c|c|c|} \hline Division & Operating Income & Average Total Assets & Net Sales & Profit Margin Ratio & Asset Turnover Ratio & Return on Investment (ROI) \\ \hline \multicolumn{7}{|l|}{ East } \\ \hline \multicolumn{7}{|l|}{ North } \\ \hline \multicolumn{7}{|l|}{ South } \\ \hline West & & & & & & \\ \hline \end{tabular} 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

Answer & Explanation Solved by verified expert

See Answer