1. Open the NP_EX_11-5.xlsx workbook located in the Excel11 > Case1 folder included with your Data...

Transcribed Image Text:

1. Open the NP_EX_11-5.xlsx workbook located in the Excel11 > Case1 folder included with your Data Files. Save the workbook as NP_EX_11_Alcmaeon in the location specified by your instructor. 2. In the Documentation sheet, enter your name and the date. 3. In the Revenue Report worksheet, in cell B4, create a PivotTable named Revenue by Month. Place the Date field from the Sales table in the Rows area. After Excel automatically groups the dates, remove the Date field from the Rows area, leaving only the Date (Month) field showing the names of the months. Change the Pivot Table report layout to tabular form and do not display any grand totals in either the rows or the columns. 4. Place the Revenue field from the Sales table into the Values area four times and do the following to display sales revenue in different ways in your table: a. In the first column, Sum of Revenue, display the rank of the revenue values from largest to smallest and change the field label to Rank. b. In the second column, Sum of Revenue2, change the field label to Total Revenue. c. In the third column, Sum of Revenue3, track the increase in revenue over the year by display- ing the running total. Change the field label to Running Total. d. In the last column, Sum of Revenue4, display the revenue values as a percentage of the run- ning total. Change the field label to Percentage. 5. Examine how sales vary by region and agent. Go to the Region Report worksheet and create a PivotTable in cell B4 named Revenue by Region. Place the Sales Region and the Name fields from the Agents table in the Row area. Place the Revenue field from the Sales table in the Values area and change the field label from Sum of Revenue to Total Revenue. Change the layout to Outline form and add subtotals to the top of each region group. 6. Show the percentage of revenue generated by each agent and each region by placing the Revenue field a second time the Values area, displaying its values as a percent of the parent row total. Change the field label name to Percent. 7. To better view the top selling agents and regions, sort the items in the Sales Region column in descending order of the Percent field, and then sort the items in the Name field also in descend- ing order of Percent. 8. Determine how many hospitals bought a software license during the past years and from which agents. In the Hospital Sales worksheet, in cell B4, create a PivotTable named Clients. Set the report layout to tabular form, showing grand totals for both rows and columns. Place the Name field from the Agents table in the Rows area and place the Date (Month) field from the Sales table in the Columns area. 9. Place the Hospital field from the Sales table in the Values area. Show how many clients each agent had during the year by displaying the distinct count of the Hospital field. Change the field label to Clients. 10. In the PivotTable Options dialog box for the PivotTable, on the Layout & Format tab, enter 0 as the value to show for empty cells. 11. Calculate each agent's total compensation from the past year. In the Sales Commissions work- sheet, create a PivotTable in cell B4 named Commissions. Set the PivotTable layout to tabular form with no grand totals. 12. Place the Name field from the Agents table in the Rows area. Place the Base Salary field from the Agents table in the Values area. Change the field label to Base Salary followed by a blank space. Place the Commission field from the Agents table in the Values area. Change the field label to Commission Rate. 13. Place the Revenue field from the Sales table in the Values area. Change the field label to Revenue Generated. 1. Open the NP_EX_11-5.xlsx workbook located in the Excel11 > Case1 folder included with your Data Files. Save the workbook as NP_EX_11_Alcmaeon in the location specified by your instructor. 2. In the Documentation sheet, enter your name and the date. 3. In the Revenue Report worksheet, in cell B4, create a PivotTable named Revenue by Month. Place the Date field from the Sales table in the Rows area. After Excel automatically groups the dates, remove the Date field from the Rows area, leaving only the Date (Month) field showing the names of the months. Change the Pivot Table report layout to tabular form and do not display any grand totals in either the rows or the columns. 4. Place the Revenue field from the Sales table into the Values area four times and do the following to display sales revenue in different ways in your table: a. In the first column, Sum of Revenue, display the rank of the revenue values from largest to smallest and change the field label to Rank. b. In the second column, Sum of Revenue2, change the field label to Total Revenue. c. In the third column, Sum of Revenue3, track the increase in revenue over the year by display- ing the running total. Change the field label to Running Total. d. In the last column, Sum of Revenue4, display the revenue values as a percentage of the run- ning total. Change the field label to Percentage. 5. Examine how sales vary by region and agent. Go to the Region Report worksheet and create a PivotTable in cell B4 named Revenue by Region. Place the Sales Region and the Name fields from the Agents table in the Row area. Place the Revenue field from the Sales table in the Values area and change the field label from Sum of Revenue to Total Revenue. Change the layout to Outline form and add subtotals to the top of each region group. 6. Show the percentage of revenue generated by each agent and each region by placing the Revenue field a second time the Values area, displaying its values as a percent of the parent row total. Change the field label name to Percent. 7. To better view the top selling agents and regions, sort the items in the Sales Region column in descending order of the Percent field, and then sort the items in the Name field also in descend- ing order of Percent. 8. Determine how many hospitals bought a software license during the past years and from which agents. In the Hospital Sales worksheet, in cell B4, create a PivotTable named Clients. Set the report layout to tabular form, showing grand totals for both rows and columns. Place the Name field from the Agents table in the Rows area and place the Date (Month) field from the Sales table in the Columns area. 9. Place the Hospital field from the Sales table in the Values area. Show how many clients each agent had during the year by displaying the distinct count of the Hospital field. Change the field label to Clients. 10. In the PivotTable Options dialog box for the PivotTable, on the Layout & Format tab, enter 0 as the value to show for empty cells. 11. Calculate each agent's total compensation from the past year. In the Sales Commissions work- sheet, create a PivotTable in cell B4 named Commissions. Set the PivotTable layout to tabular form with no grand totals. 12. Place the Name field from the Agents table in the Rows area. Place the Base Salary field from the Agents table in the Values area. Change the field label to Base Salary followed by a blank space. Place the Commission field from the Agents table in the Values area. Change the field label to Commission Rate. 13. Place the Revenue field from the Sales table in the Values area. Change the field label to Revenue Generated.