Hi Please help me solve this simple excel take home test. I will give positive feedback for your help thansk! Below is simple question and the data for the company R3000. 
DATA:
| ID | Ticker | ID | Ticker | ID | Ticker | ID | Ticker |
| 1 | A | 2 | A | 3 | A | 4 | A |
| 2 | AA | Alcoa Corporation | Roy Harvey | M | Materials | $15,960,369 | $102,645 |
| 3 | AAL | American Airlines Group Inc. | W. Parker | M | Industrials | $7,238,011 | $62,765 |
| 4 | AAN | The Aaron's Company, Inc. | Douglas Lindsay | M | Consumer Discretionary | $5,998,425 | $33,632 |
| 5 | AAON | AAON, Inc. | Gary Fields | M | Industrials | $2,912,108 | $52,267 |
| 6 | AAP | Advance Auto Parts, Inc. | Thomas Greco | M | Consumer Discretionary | $10,052,271 | $24,960 |
| 7 | AAPL | Apple Inc. | Timothy Cook | M | IT | $98,734,394 | $68,254 |
| 8 | AAT | American Assets Trust, Inc. | Ernest Rady | M | Real Estate | $5,061,774 | $71,312 |
| 9 | AAWW | Atlas Air Worldwide Holdings, Inc. | John Dietrich | M | Industrials | $2,532,231 | $135,543 |
| 10 | ABBV | AbbVie Inc. | Richard Gonzalez | M | Healthcare | $23,912,154 | $149,662 |
The R3000 Index is a market index that measures the performance of the top 3000 U.S. publicly traded companies as ranked by market capitalisation. The file CEO Pay Data R3000.xlsx contains year 2022 data on 2172 of those companies including: - Ticker - Company Ticker Code - Company Name - CEO - Name of the CEO - Gender - Gender of the CEO restricted to Male (M) and Female( (F) - Sector - Industry Sector for the company - CEO Salary - 5 - Median Company Pay - median earnings of company workers in $ For this assessment you will need to carry out a number of tasks on the spreadsheet. At the end of each task, copy the result to a Word document. At the end, save your solution Word document as a PDF for submission. Please submit. only the Word document, but keep the Excel document in case it is required for further checking. Please save the two documents as: Yourname_PST1.PDF Yourname_PST1.xlsx Your Task In the spreadsheet CEO Pay Data R3000xisx: a. Calculate a new column called Salary Ratio showing the ratio of CEO salaries to the Median company pay. Save that column as values to 2 decimal places. b. Create a Pivot Table which includes all of the data including your new variable Salary Ratio c. Use the Pivot Table to construct a Frequency Distribution for the variable Salary Ratio using an appropriate number of classes. Copy the Frequency Distribution to your solution document. d. Using the Frequency Distribution in c. create an appropriate Graph and label it. Save the graph to your solution document e. Which CEO has the maximum ratio of their pay to their workers? Do you think this is an accurate result or a data error - why? What should you do with this observation? f. Re-do the Frequency distribution and graph excluding this observation and copy them to your solution document. Are there any other unusual observations? g. In one sentence what can you say about the ratio of CEO Salaries to their workers Median pay. h. Use your Pivot Table to calculate the proportion of Male and Female CEO's by Industry sector. Copy the results to your solution document i. Create a graph to show the relative rates of Male and Female CEO'S by Industry Sector. j. What is the overall proportion of Female CEO's among R3000 companies? Which sectors have the highest and lowest proportion of Female CEO's k. Having looked at the data, in one sentence nominate one question which you could use this data to calculate which you would like to investigate regarding CEO salaries. The R3000 Index is a market index that measures the performance of the top 3000 U.S. publicly traded companies as ranked by market capitalisation. The file CEO Pay Data R3000.xlsx contains year 2022 data on 2172 of those companies including: - Ticker - Company Ticker Code - Company Name - CEO - Name of the CEO - Gender - Gender of the CEO restricted to Male (M) and Female( (F) - Sector - Industry Sector for the company - CEO Salary - 5 - Median Company Pay - median earnings of company workers in $ For this assessment you will need to carry out a number of tasks on the spreadsheet. At the end of each task, copy the result to a Word document. At the end, save your solution Word document as a PDF for submission. Please submit. only the Word document, but keep the Excel document in case it is required for further checking. Please save the two documents as: Yourname_PST1.PDF Yourname_PST1.xlsx Your Task In the spreadsheet CEO Pay Data R3000xisx: a. Calculate a new column called Salary Ratio showing the ratio of CEO salaries to the Median company pay. Save that column as values to 2 decimal places. b. Create a Pivot Table which includes all of the data including your new variable Salary Ratio c. Use the Pivot Table to construct a Frequency Distribution for the variable Salary Ratio using an appropriate number of classes. Copy the Frequency Distribution to your solution document. d. Using the Frequency Distribution in c. create an appropriate Graph and label it. Save the graph to your solution document e. Which CEO has the maximum ratio of their pay to their workers? Do you think this is an accurate result or a data error - why? What should you do with this observation? f. Re-do the Frequency distribution and graph excluding this observation and copy them to your solution document. Are there any other unusual observations? g. In one sentence what can you say about the ratio of CEO Salaries to their workers Median pay. h. Use your Pivot Table to calculate the proportion of Male and Female CEO's by Industry sector. Copy the results to your solution document i. Create a graph to show the relative rates of Male and Female CEO'S by Industry Sector. j. What is the overall proportion of Female CEO's among R3000 companies? Which sectors have the highest and lowest proportion of Female CEO's k. Having looked at the data, in one sentence nominate one question which you could use this data to calculate which you would like to investigate regarding CEO salaries
