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

[answerhappygod]

1. Open the NP_EX_11-5.xlsx workbook located in the Excel11 >Case1 folder included with your Data Files. Save the workbook asNP_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 PivotTablenamed Revenue by Month. Place the Date field from the Sales tablein 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 thePivotTable report layout to tabular form and do not display anygrand totals in either the rows or the columns. 4. Place theRevenue field from the Sales table into the Values area four timesand do the following to display sales revenue in different ways inyour table: a. In the first column, Sum of Revenue, display therank of the revenue values from largest to smallest and change thefield 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 bydisplay-ing the running total. Change the field label to RunningTotal. d. In the last column, Sum of Revenue4, display the revenuevalues as a percentage of the run-ning total. Change the fieldlabel to Percentage. 5. Examine how sales vary by region and agent.Go to the Region Report worksheet and create a PivotTable in cellB4 named Revenue by Region. Place the Sales Region and the Namefields from the Agents table in the Row area. Place the Revenuefield from the Sales table in the Values area and change the fieldlabel from Sum of Revenue to Total Revenue. Change the layout toOutline form and add subtotals to the top of each region group. 6.Show the percentage of revenue generated by each agent and eachregion by placing the Revenue field a second time the Values area,displaying its values as a percent of the parent row total. Changethe field label name to Percent. 7. To better view the top sellingagents and regions, sort the items in the Sales Region column indescending order of the Percent field, and then sort the items inthe Name field also in descend-ing order of Percent. 8. Determinehow many hospitals bought a software license during the past yearsand from which agents. In the Hospital Sales worksheet, in cell B4,create a PivotTable named Clients. Set the report layout to tabularform, showing grand totals for both rows and columns. Place theName field from the Agents table in the Rows area and place theDate (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 displayingthe distinct count of the Hospital field. Change the field label toClients. 10. In the PivotTable Options dialog box for thePivotTable, on the Layout & Format tab, enter 0 as the value toshow for empty cells. 11. Calculate each agent’s total compensationfrom the past year. In the Sales Commissions work-sheet, create aPivotTable in cell B4 named Commissions. Set the PivotTable layoutto tabular form with no grand totals. 12. Place the Name field fromthe Agents table in the Rows area. Place the Base Salary field fromthe Agents table in the Values area. Change the field label to BaseSalary followed by a blank space. Place the Commission field fromthe Agents table in the Values area. Change the field label toCommission Rate. 13. Place the Revenue field from the Sales tablein the Values area. Change the field label to RevenueGenerated. Create a measured named Earned Commission in theSales table with the description Commission earned by revenuegenerated above a minimum amount that calculates the amount ofcommission earned by a sales agent for revenue generated above aminimum level. Use the following DAX formula and format valuesreturned by the measure as currency with no decimal places: =(SUM([Revenue]) - SUM(Agents[SalesMinimum]))*SUM(Agents[Commission]) (You can copy thisformula from the Tables and Measures worksheet if you have troubleenter-ing it; be sure to insert an equal sign to start the formulaand match all closing and opening parentheses.) 15. Create ameasure named Total Compensation with the description Totalcompensation from base salary and earned commission that calculatesthe total compensation for each sales agent by adding the basesalary and the earned commission. Use the following DAX formula and display total compensation in currency with no decimalplaces: =SUM(Agents[Base Salary])+[Earned Commission] 16.Make sure that the Earned Commission and Total Compensationmeasures are added to the PivotTable. 17. Sort the agent names indescending order of the Total Compensation column. 18. Save theworkbook, and then close it.