You have been hired as a sports team manager. Part of your job as the team manager is to keep track of player statistics and salary. Research the salary for your chosen team and its 10 players. Remember to calculate the salary per year, not per contract.

Creating the Workbook and Entering Data

• Create a workbook and save it with a descriptive filename and include your last name in the filename. (Example: Chicago Bulls Players Info Smith.xlsx)
• Rename Sheet1 to an appropriate name for the worksheet data and color the tab to a color of your choosing

For the following steps do not worry about adjusting the column widths or formatting. You will be formatting the worksheet after all of the data has been entered.

• Create title and subtitle rows
• Enter the following column headings:
  • Player Name
  • Height (in Inches)
  • Weight (in Pounds)
  • Position
  • Years in League
  • Current Annual Salary
• Enter data in for at least 10 players
• You are expecting to give each of the players a raise next year
  • To the right of the Current Salary column, create a column heading titled Expected Raise.
  • In cell A21, type Raise Percentage. In cell B21, type 11%.
  • For each player in the Expected Raise column, create a calculation using an absolute cell reference to B21 which multiplies B21 by the Current Salary
• To the right of the Expected Raise column, create a column heading titled Expected Salary
  • Create a formula that adds the Current Salary and the Expected Raise for each player
• You now would like to know the total Current Salary and Expected Salary for your team
  • In column A, below the last row of player data create a row heading titled Total Salary
  • Use a function to total the Current Salary column and the Expected Salary column, separately
• You would like to create some statistical information about your team. These statistics include average, highest and lowest Current Salary
  • Starting in A23, create 3 Row Headings titled Average Salary, Highest Salary and Lowest Salary
    • In the adjacent column use functions to find the average, highest, and lowest value for each category based on the Current Salary of the players.
  • Insert a footer to your data worksheet (Hint: click the Insert tab in the ribbon)
    • In the left section, type your course number and section (example: CMSY 110-002)
    • In the center section, insert a the current date field (Hint: click the Design tab in the ribbon to find the Current Date button)
    • In the right section, type your first and last name

Formatting your worksheet

• Merge/Center the Title and Subtitle rows across all Column Headings
• Format the Title and Subtitle with a Cell Style (do not use Normal style)
• Format the Total Salary row as a Total Cell Style and apply the Accounting format with two decimal places.
• Format player data appropriately.
  • Center and apply an appropriate Cell Style (do not use Normal style) to row containing the column headings.
  • Apply an appropriate Cell Style (do not use Normal style) to the rows containing the player data.
• Use the Accounting format for the first row data that contains monetary values. Use the Comma style for all monetary values in between the first row and the Total Salary row. Each number in the spreadsheet should have two decimal places. (See To Format Numbers in the Worksheet section of book)
• For the remaining monetary values of the worksheet apply the Accounting format with two decimal places.
• Assign Conditional Formatting to the cells in the Expected Salary column. Format any cell in the Expected Salary column whose value is greater than $850,000 to green fill. (Do not include values in the Total row.) NOTE: If the lowest salary is greater than "$850,000", use a comparison value of great than that lowest value (Example, if my lowest value is "$900,000", then I would use a conditional formatting of greater than $900,000 instead of $850,000).
• AutoFit all columns.

Create Charts

• Create a 3D pie chart on a separate sheet.
  • Select the Player Name and either the Current Salary or Expected Salary column for the 3D pie chart. ((Hint: You may need to use the Ctrl key to select non-adjacent cells)
  • Move the chart sheet to a separate location
    • Name the 3-D pie chart sheet tab appropriately to reflect the information on the chart
    • Change the tab color to a color of your choosing
  • Include a title for the chart but not a legend.
  • Include data labels to display the salary for each player and the player name.
• Create another chart (not a pie chart) with the player name and either the Height or Weight as data for the chart on a separate sheet.
  • Move the chart sheet to a separate location
    • Name the chart sheet tab appropriately to reflect the information on the chart
    • Change the tab color to a color of your choosing.
  • Include a title chart
  • Include axis titles for the chart

Check your Spelling and Grammar.

Please leave answer as a downloadable file.