1. Roshana Haddad is a financial analyst for Peak Partners, a full-service recruiting agencywith headquarters in New York City and offices in five other U.S. cities. Roshana asks foryour help in producing a revenue report. She wants to analyze revenue for the past yearand project future sales for all the offices. To create the report, you need to import datafrom various sources and use the Excel Power tools.Go to the Revenue History worksheet, where Roshana wants to view how sales havechanged since the business was founded in the year 2000. She has a text file thatalready contains this data. Create a new query that imports data from theSupport_EX19_EOM10-1_Annual.csv text file. Edit the query to remove the first fourrows of data and to use the titles in the fifth row as column headers. Close and load thequery data to a table in cell B3 of the existing worksheet. Apply the Dark Green, TableStyle Medium 3 table style to the new table to coordinate with the Peak Partners logo.2. Roshana asks you to create a chart that compares the revenue for each business year.Create a scatter chart of the Business Year and Annual Revenue data (range C3:D25).Move and resize the chart so that its upper-left corner is in cell E3 and its lower-rightcorner is in cell K16. Add a Linear Forecast trendline to the chart, and then forecast thetrendline forward 2 periods.3. Go to the Monthly Sales worksheet, where Roshana has already imported monthly salesdata from a text file and loaded it into the worksheet as a table. She now wants toforecast the monthly sales data for the next year. Create a Forecast sheet based on thedata in the range B3:C27. Forecast sales through 12/31/2022. Set the seasonality to 12months. Name the new worksheet 2022 Forecast. Move and resize the forecast chartso that the upper-left corner is within cell C2 and the lower-right within cell E25.4. Go to the Positions Pivot worksheet. Roshana wants to display information about PeakPartners’ clients, contracts, and positions. She has been maintaining this data in anAccess database. Create a new query importing data from the Support_EX19_EOM10-1_Peak.accdb database, selecting all five tables in the database for import. Only createa connection to the data and add the data to the Data Model. Use the Power Pivotwindow to add a PivotTable to the Positions Pivot worksheet in cell B3.5. Add the Category field from the Positions table to the Values area to count the numberof positions in each category.6. Roshana wants to view the position data organized as a hierarchy. Maximize the PowerPivot window, display the imported tables in Diagram view, and then add a hierarchynamed Position List to the Positions table. Add the Category, Group, and Position fieldsto the Position List hierarchy. (Hint: You might need to scroll the Power Pivot window todisplay all the tables.) Return to the Positions Pivot worksheet. Add the Position Listhierarchy to the Rows area of the PivotTable and then remove the Position field from theRows area (if necessary).7. Roshana wants to display the same position information as a chart, and then displayonly positions in the three groups in the Technology category. Create a clustered barPivotChart based on the PivotTable on the Positions Pivot worksheet. Hide the fieldbuttons and legend, and then move and resize the chart so that the upper-left corner iswithin cell D3 and the lower-right corner within cell I15. Drill down the bar chart throughthe Technology category to display the number of positions in each Technology group.8. Go to the Revenue by State worksheet, where Roshana wants to view recruiting revenueby state and office specialty. Use Power Pivot to insert a PivotTable in cell B3 of theRevenue by State worksheet. Add the State field from the Offices table to the Rowsarea. Add the Type field from the Offices table to the Columns area. Add the Charge fieldfrom the Invoices table to the Values area.9. In order to relate the data in the Offices and Invoices tables to make a propercomparison, use the Power Pivot window to create a relationship between the Invoicesand Offices tables based on the Office ID field.10. Roshana also wants to focus on revenue by month in the PivotTable. Insert a TimelineSlicer that uses the Date field from the Invoices table. Use the Timeline Slicer to displayrevenue from March to June of 2020. Move and resize the Timeline Slicer so that itcovers the range B13:G19.11. Roshana wants to display the revenue by state data in a Map chart. Copy the data in thenon-adjacent range B5:B10 and G5:G10, and paste it beginning in cell I5. Resize columnJ to display revenue data. Type State in cell I4 and type Revenue in cell J4. Create aFilled Map chart based on the range I4:J10. Move and resize the chart so the upper-leftcorner is within cell I13 and lower-right corner is within cell N25. Remove the chart title.