-) (+) 102% Question 1. Heart Meals Cafe, a famous restaurant chain, has cafes in Georgia, North Carolina, and South Carolina. As an employee in the home office, you are asked to create some reports about the employees. You will need the worksheet Q1-Employees for this set of questions. This worksheet contains the list of all the employees in the company. You will create three reports. Place each report in a separate worksheet, and name the worksheet accordingly (e.g., Q1a, Q1b, and Q1c) (a) Using Subtotals, create a report showing the total salary for each department in each city. [18 points] (b) Using Advanced-Filter, create a report showing female employees hired in the 1990s, as well as all employees earning more than $30,000 but less than $45,000. Sort the final list by Gender (with female listed first), then by Date of Hire (with the first hired listed first), then by salary (from lowest to highest). 25 points] create a report showing the number of employees hired in each year (row) for each state (column). Show the numbers as percentage of the total number of employees in each state [30 (c) Using points] Question 2. A manufacturer of plastic construction toys has prepared its inventory report for January. You are asked to create some reports about the inventory and identify the products eligible for in-store promotion. Using PowerPivot, you will need three worksheets for this set of questions-Q2-Inventory, 02. +1029% Question 2. A manufacturer of plastic construction toys has prepared its inventory report for January. You are asked to create some reports about the inventory and identify the products eligible for in-store promotion. Using PowerPivot, you will need three worksheets for this set of questions-Q2-Inventory, Q2 Product, and Q2-Store You will create a Pivot Table feport in a separate worksheet, and name the worksheet "Q2." (a) The inventory report (Q2-Inventory) shows the quantity of each product at different stores. The CFO would like to add a new column "Value" to calculate the values of each product at different stores (i.e., Quantity x Price) and a new column "Promotion" to indicate whether a product is eligible for promotion at a certain store. Eligible products in the promotional offer are those high in stock at the store (i.e., quantity >:500) and sold at a high price (i.e., Price>-$75). The formula in the"Promotion" column will show "Yes" if a product qualifies for in-store promotion or No" otherwise. You must use RELATED function to create these columns in PowerPivot in order to get credit. [34 points] Store Product Quantity Value Promotion MW088 ST106 MW006 VE141 MW008 ST118 MW018 ST122 MW097 BU116 MW040 ST105 845 330 461 371 259 (b) You will create a PivotTable report to show the total inventory values of each product category (column) at each city within each state (row). [18 points] -) (+) 102% Question 1. Heart Meals Cafe, a famous restaurant chain, has cafes in Georgia, North Carolina, and South Carolina. As an employee in the home office, you are asked to create some reports about the employees. You will need the worksheet Q1-Employees for this set of questions. This worksheet contains the list of all the employees in the company. You will create three reports. Place each report in a separate worksheet, and name the worksheet accordingly (e.g., Q1a, Q1b, and Q1c) (a) Using Subtotals, create a report showing the total salary for each department in each city. [18 points] (b) Using Advanced-Filter, create a report showing female employees hired in the 1990s, as well as all employees earning more than $30,000 but less than $45,000. Sort the final list by Gender (with female listed first), then by Date of Hire (with the first hired listed first), then by salary (from lowest to highest). 25 points] create a report showing the number of employees hired in each year (row) for each state (column). Show the numbers as percentage of the total number of employees in each state [30 (c) Using points] Question 2. A manufacturer of plastic construction toys has prepared its inventory report for January. You are asked to create some reports about the inventory and identify the products eligible for in-store promotion. Using PowerPivot, you will need three worksheets for this set of questions-Q2-Inventory, 02. +1029% Question 2. A manufacturer of plastic construction toys has prepared its inventory report for January. You are asked to create some reports about the inventory and identify the products eligible for in-store promotion. Using PowerPivot, you will need three worksheets for this set of questions-Q2-Inventory, Q2 Product, and Q2-Store You will create a Pivot Table feport in a separate worksheet, and name the worksheet "Q2." (a) The inventory report (Q2-Inventory) shows the quantity of each product at different stores. The CFO would like to add a new column "Value" to calculate the values of each product at different stores (i.e., Quantity x Price) and a new column "Promotion" to indicate whether a product is eligible for promotion at a certain store. Eligible products in the promotional offer are those high in stock at the store (i.e., quantity >:500) and sold at a high price (i.e., Price>-$75). The formula in the"Promotion" column will show "Yes" if a product qualifies for in-store promotion or No" otherwise. You must use RELATED function to create these columns in PowerPivot in order to get credit. [34 points] Store Product Quantity Value Promotion MW088 ST106 MW006 VE141 MW008 ST118 MW018 ST122 MW097 BU116 MW040 ST105 845 330 461 371 259 (b) You will create a PivotTable report to show the total inventory values of each product category (column) at each city within each state (row). [18 points]