Excel Module 1

1. Open a blank worksheet in Excel. Enter the First name, Last Name, Gender, Department, and Yearly Salary (use salaries that are greater than $65,000) for ten fictitious people in Columns A-E, beginning in Row 2. Be sure to enter column headings in Row 1 and align them with the data. Adjust column widths as needed. Excel Module 1
2. Sort the data by the Last
3. Insert a column before the name (new column A), and enter the Employee number ID. Type in 1 and 2 for the first two employees only, then select both and copy down for autofill.
4. In Column G, type the heading “Tax Withheld,” and enter a formula that computes it. Assume 20% of the employee’s salary is withheld for taxes. APA
5. The new tax code gives a Tax Break of $2,000 for all those who have more than $20,000 withheld in column G. Use an IF statement to compute this in Column H.
6. Assume now that the Tax Break is more complex than the previous step. If you had a least $10,000 withheld, you get $500 back. If you had at least $15,000 withheld, you get $1,000 back. If you had $20,000 or more withheld, you get $2,000 back as before. Use a VLOOKUP statement to compute the New Tax Break in Column I.
   • Using the following table:

7. Draw a bar graph showing the salaries for each person, along with their last name. Label the axis appropriately.
8. Insert a new row right after all 10 row of data; use Excel formula to find the AVERAGE salary of all persons
9. Insert a new row right after average salary of all persons; use Excel formula to find the MEDIAN salary of all persons.
10. Sort the data by gender and insert a new row right after Median salary of all employees; use the Excel function to find the sample standard deviation (hint: the excel standard deviation function is STDEV.S not STDEV.P) of the female salaries.
    • Given that these ten people are a sample of the employees in your company, explain why you would use STDEV.S instead of STDEV.P?

Module 1 Excel Exercise 2

Download the dataset below (AIG Stock Series) and complete the assignment’s instructions.

Instructions: You are tasked to write a short description about the stock price of AIG from 2002 through 2013. Download the data file AIG Stock Series into excel and create the required graphs for your description.

Required Graphs:

• A time series plot of AIG’s stock closing price from 2002 through 2013. Label the axis appropriately.
• A histogram of AIG’s stock closing price from 2002 through 2013. Label the axis appropriately.
  • Use the following bins for the histogram

Write a short description about the AIG’s closing stock price over the time-period. Make sure you answer the following questions in your description.

• What features of the data can you see in the time series plot?
• What features of the data can you see in the histogram?
• Which graphical display seems the more appropriate for a report to the CEO of AIG? Explain your answer.

Instructions on how to create a histogram and time series plot in Excel is in chapter 3 of the textbook.

Submit both the module 1 excel exercise 1 and 2 and a word file answering the questions