Homework answers / question archive / New Perspectives Excel 2019 Module 10: end of module project 1 Peak Partners ANALYZE DATA WITH POWER TOOLS   GETTING STARTED Open the file NP_EX19_EOM10-1_FirstLastName_1

New Perspectives Excel 2019 Module 10: end of module project 1 Peak Partners ANALYZE DATA WITH POWER TOOLS   GETTING STARTED Open the file NP_EX19_EOM10-1_FirstLastName_1

MS Excel

Share With

---

New Perspectives Excel 2019 Module 10: end of module project 1

Peak Partners

ANALYZE DATA WITH POWER TOOLS

 

GETTING STARTED

Open the file NP_EX19_EOM10-1_FirstLastName_1.xlsx, available for download from the SAM website.

Save the file as NP_EX19_EOM10-1_FirstLastName_2.xlsx by changing the “1” to a “2”.

If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

To complete this SAM Project, you will also need to download and save the following data files from the SAM website onto your computer:

Support_EX19_EOM10-1_Annual.csv

Support_EX19_EOM10-1_Monthly.csv

Support_EX19_EOM10-1_Peak.accdb

With the file NP_EX19_EOM10-1_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

To complete this project, you need to add the Power Pivot tab to the ribbon as follows:

From the File tab, click the Options button. In the Data options section of the Data tab, click the checkbox next to Enable Data Analysis add- ins: Power Pivot, Power View, and 3D Map, and click OK.

Click enable content at the top, if required.

When downloading Access support files, your browser may display a warning message similar to "This type of file can harm your computer." SAM Project files are always considered safe, so you can disregard this message.

PROJECT STEPS

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.

Your workbook should look like the Final Figures on the following pages. Save your changes, close the workbook, and then exit Excel. Follow the directions on the SAM website to submit your completed project. 

Final Figure 1: Revenue History Worksheet