Excel Project 2

1. Rename the project workbook as your first name-project 2 (2 marks) 2. Based on the data in the Wine Sale worksheet, create a PivotTable that shows the total sales by country and product. Data in Wine Sale Data worksheet must be converted to a TABLE format before creating pivot table as learned in the class, so you are able to add new data after the pivot table is created. Format the data area with the Currency format. Rename the worksheet with the PivotTable as Wine Sales PivotTable. (10 Marks) 3. Format the headings in Sales Figures work sheet and assign a outside border to the data table. (4 marks) 4. Apply conditional formatting to the sales column so the sales between 150.00 and 300.00 is highlighted/visible. (4 marks) 5. Find total of sales figures based on the criteria in sheet name SALES FIGURES (Use: SUMIFS) (10 marks) 6. Find the average of sales figures using the data in the above sheet (Use: AVGIFS) (10 marks) 7. Use a function to count the Number of Regions and Segments for the criteria listed in Sales Figures worksheet. (Use: COUNTIFS) (6 marks) 8. Rename Sheet 2 Student Enrollment List and Sheet 3 Master Sheet. Create a Table for each column in Master Sheet and use the Indirect Function to create a drop-down list for the corresponding data in the Student Enrollment List. Any data added to the columns in master sheet must appear in the lists created in Student Enrollment List sheet (Hint: we have done this in class) (15 marks) 9. Apply appropriate formatting (bold, appropriate spacing, colours) to columns with data in Master Sheet (5 marks)

10. In the Employee Data worksheet use a function to do an exact match to lookup and calculate the annual medical plan cost. Use the data in Medical Rates worksheet which contains the monthly medical rates. (Use: XLOOKUP) (10 marks) 11. In the FUN Company work sheet use a function to calculate the age of each employee. Use the date in column U as the cut-off date (Use: YEARFRAC) (5 marks) 12. In the FUN Company work sheet assign the appropriate company pension matching value based on the salary and age of the employee. If the age is greater than 30 and salary is above $35,000 company will match 3% of the pension amount if both criterias are not met the amount is 1%. (Use: IFAND) (6 marks) 13. All (FT) and (PT) employees are eligible for a bonus. FT employee with a salary of $50,000 or higher receive $8,000 bonus and PT employees receive $3,000 bonus. Use a function to assign the appropriate bonus (column: S) based on salary and employment status. (Use: IFOR) (6 marks) 14. Convert the data in Employee Personal Data sheet into a Table use a filter function to extrapolate information from column C, D and E based on their department (column-b) and create a Dynamic graph. (We have done this in class) (