Roshana Haddad is a financial analyst for Peak Partners, a full-service recruiting agency with headquarters in New York City and offices in five other U.S. cities. Roshana asks for your help in producing a revenue report. She wants to analyze revenue for the past year and project future sales for all the offices. To create the report, you need to import data from various sources and use the Excel Power tools. Go to the Revenue History worksheet, where Roshana wants to view how sales have changed since the business was founded in the year 2000. She has a text file that already contains this data. Create a new query that imports data from the Support_EX19_EOM10-1_Annual.csv text file. Edit the query to remove the first four rows of data and to use the titles in the fifth row as column headers. Close and load the query data to a table in cell B3 of the existing worksheet. Apply the Dark Green, Table Style Medium 3 table style to the new table to coordinate with the Peak Partners logo.

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-right corner is in cell K16. Add a Linear Forecast trendline to the chart, and then forecast the trendline forward 2 periods.

Go to the Monthly Sales worksheet, where Roshana has already imported monthly sales data from a text file and loaded it into the worksheet as a table. She now wants to forecast the monthly sales data for the next year. Create a Forecast sheet based on the data in the range B3:C27. Forecast sales through 12/31/2022. Set the seasonality to 12 months. Name the new worksheet 2022 Forecast. Move and resize the forecast chart so that the upper-left corner is within cell C2 and the lower-right within cell E25.

Go to the Positions Pivot worksheet. Roshana wants to display information about Peak Partners' clients, contracts, and positions. She has been maintaining this data in an Access 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 create a connection to the data and add the data to the Data Model. Use the Power Pivot window to add a PivotTable to the Positions Pivot worksheet in cell B3.

Add the Category field from the Positions table to the Values area to count the number of positions in each category.

Roshana wants to view the position data organized as a hierarchy. Maximize the Power Pivot window, display the imported tables in Diagram view, and then add a hierarchy named Position List to the Positions table. Add the Category, Group, and Position fields to the Position List hierarchy. (Hint: You might need to scroll the Power Pivot window to display all the tables.) Return to the Positions Pivot worksheet. Add the Position List hierarchy to the Rows area of the PivotTable and then remove the Position field from the Rows area (if necessary).

Roshana wants to display the same position information as a chart, and then display only positions in the three groups in the Technology category. Create a clustered bar PivotChart based on the PivotTable on the Positions Pivot worksheet. Hide the field buttons and legend, and then move and resize the chart so that the upper-left corner is within cell D3 and the lower-right corner within cell I15. Drill down the bar chart through the Technology category to display the number of positions in each Technology group.

Go to the Revenue by State worksheet, where Roshana wants to view recruiting revenue by state and office specialty. Use Power Pivot to insert a PivotTable in cell B3 of the Revenue by State worksheet. Add the State field from the Offices table to the Rows area. Add the Type field from the Offices table to the Columns area. Add the Charge field from the Invoices table to the Values area.

In order to relate the data in the Offices and Invoices tables to make a proper comparison, use the Power Pivot window to create a relationship between the Invoices and Offices tables based on the Office ID field.

Roshana also wants to focus on revenue by month in the PivotTable. Insert a Timeline Slicer that uses the Date field from the Invoices table. Use the Timeline Slicer to display revenue from March to June of 2020. Move and resize the Timeline Slicer so that it covers the range B13:G19.

Roshana wants to display the revenue by state data in a Map chart. Copy the data in the non-adjacent range B5:B10 and G5:G10, and paste it beginning in cell I5. Resize column J to display revenue data. Type State in cell I4 and type Revenue in cell J4. Create a Filled Map chart based on the range I4:J10. Move and resize the chart so the upper-left corner is within cell I13 and lower-right corner is within cell N25. Remove the chart title

Roshana Haddad is a financial analyst for Peak Partners, a full-service recruiting agency with headquarters in New York City and offices in five other U.S. cities. Roshana asks for your help in producing a revenue report. She wants to analyze revenue for the past year and project future sales for all the offices. To create the report, you need to import data from various sources and use the Excel Power tools. Go to the Revenue History worksheet, where Roshana wants to view how sales have changed since the business was founded in the year 2000. She has a text file that already contains this data. Create a new query that imports data from the Support_EX19_EOM10-1_Annual.csv text file. Edit the query to remove the first four rows of data and to use the titles in the fifth row as column headers. Close and load the query data to a table in cell B3 of the existing worksheet. Apply the Dark Green, Table Style Medium 3 table style to the new table to coordinate with the Peak Partners logo.

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-right corner is in cell K16. Add a Linear Forecast trendline to the chart, and then forecast the trendline forward 2 periods.

Go to the Monthly Sales worksheet, where Roshana has already imported monthly sales data from a text file and loaded it into the worksheet as a table. She now wants to forecast the monthly sales data for the next year. Create a Forecast sheet based on the data in the range B3:C27. Forecast sales through 12/31/2022. Set the seasonality to 12 months. Name the new worksheet 2022 Forecast. Move and resize the forecast chart so that the upper-left corner is within cell C2 and the lower-right within cell E25.

Go to the Positions Pivot worksheet. Roshana wants to display information about Peak Partners' clients, contracts, and positions. She has been maintaining this data in an Access 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 create a connection to the data and add the data to the Data Model. Use the Power Pivot window to add a PivotTable to the Positions Pivot worksheet in cell B3.

Add the Category field from the Positions table to the Values area to count the number of positions in each category.

Roshana wants to view the position data organized as a hierarchy. Maximize the Power Pivot window, display the imported tables in Diagram view, and then add a hierarchy named Position List to the Positions table. Add the Category, Group, and Position fields to the Position List hierarchy. (Hint: You might need to scroll the Power Pivot window to display all the tables.) Return to the Positions Pivot worksheet. Add the Position List hierarchy to the Rows area of the PivotTable and then remove the Position field from the Rows area (if necessary).

Roshana wants to display the same position information as a chart, and then display only positions in the three groups in the Technology category. Create a clustered bar PivotChart based on the PivotTable on the Positions Pivot worksheet. Hide the field buttons and legend, and then move and resize the chart so that the upper-left corner is within cell D3 and the lower-right corner within cell I15. Drill down the bar chart through the Technology category to display the number of positions in each Technology group.

Go to the Revenue by State worksheet, where Roshana wants to view recruiting revenue by state and office specialty. Use Power Pivot to insert a PivotTable in cell B3 of the Revenue by State worksheet. Add the State field from the Offices table to the Rows area. Add the Type field from the Offices table to the Columns area. Add the Charge field from the Invoices table to the Values area.

In order to relate the data in the Offices and Invoices tables to make a proper comparison, use the Power Pivot window to create a relationship between the Invoices and Offices tables based on the Office ID field.

Roshana also wants to focus on revenue by month in the PivotTable. Insert a Timeline Slicer that uses the Date field from the Invoices table. Use the Timeline Slicer to display revenue from March to June of 2020. Move and resize the Timeline Slicer so that it covers the range B13:G19.

Roshana wants to display the revenue by state data in a Map chart. Copy the data in the non-adjacent range B5:B10 and G5:G10, and paste it beginning in cell I5. Resize column J to display revenue data. Type State in cell I4 and type Revenue in cell J4. Create a Filled Map chart based on the range I4:J10. Move and resize the chart so the upper-left corner is within cell I13 and lower-right corner is within cell N25. Remove the chart title