You have the option to complete this assignment with a partner or by yourself. Follow the instructions below.
- Open the file titled Excel Partner Project and begin on tab Data
- Freeze the top row
- Create a new column in column O
- Title it Total Bonus
- Create the values by multiplying the values in column J and K
- Create a new column in column P
- Title it Total Earned
- Create the values by adding the amounts in column J and column O
- Conditionally format Column P using the Green-White color scale
- Convert any instance of United States into USA using Replace (CTRL + F => Replace) in column L
- At the bottom of the excel document on the Data tab, calculate the mean Annual Salary of all individuals and put the value below that column
- At the bottom of the excel document on the Data tab, calculate the max Bonus % of all individuals and put the value below that column
- Create a pivot table on a new tab that:
- compares the countries (x-axis) with the job titles (y-axis) comparing the average salary of the jobs (values)
- compares the job titles (x-axis) and their min salary, max salary, and number of people with that job (y-axis, this will be three different columns). This will be on the same tab you did part a
- Create two more pivot tables on a new tab. Consider what would be important information to explain to your supervisor and create pivot tables showcasing this data. You will have to defend your choices of your two pivot tables later.
- On column Q, create a new column titled Supervisor
- Using VLOOKUP, attach the respective supervisor
- Go to the tab titled Solver
- Listed are manufacturing materials and how long it takes to produce in minutes. Your boss wants to know what is the minimum amount of products that can be produced in exactly one 8 hour day (480 minutes). You boss also wants to know the maximum amount of products that can be produced in one 8 hour day (480 minutes)
- Use solver to figure out the requested information. Your constraints need to include that we only want whole amounts of products produced and that the Total Time should be a SumProduct of 8 hours (480 minutes)
- Everything should be very similar to the example we did in our notes, use them. Be aware of the maximum part of this question.
- Hint: To solve for the maximum amount, pick MAX in the solver menu on line 2.
- Answer the questions on the file titled Final Questions 09/11
