New Perspectives Excel 365 | Module 11: End of Module Project 1 EcoPoint Engineering

GETTING STARTED

1. Save the file NP_EX365_EOM11-1_FirstLastName_1.xlsx as NP_EX365_EOM11-1_FirstLastName_2.xlsx

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .xlsx file extension, do not type it. The file extension will be added for you automatically.

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

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

PROJECT STEPS

1. Amara Gupta is a sales analyst at EcoPoint Engineering, an environmental engineering firm in Tulsa, Oklahoma. She uses an Excel workbook to track sales related to engineering projects and asks for your help in summarizing project data. To do so, you will use database functions and advanced PivotTable features. Go to the All Projects worksheet, which contains a table named Projects listing details about consulting projects completed in 2029. In the range M3:N18, Amara wants you to analyze selected project information. Start by identifying the number of projects that have total sales more than $15,000 and those for client Gossa. In cell N6, create a formula using the DCOUNT function to count the number of projects with total sales of more than $15,000, using the data in the entire Projects table (Projects[#All]) and counting the values in the Total Sales column ("Total Sales") that are equal to the values in the range M4:M5.

2. In cell N10, create a formula using the DCOUNTA function to count the number of projects for client Gossa, using the data in the entire Projects table (Projects[#All]) and counting the values in the Client ID column ("Client ID") that are equal to the values in the range M8:M9.

3. Amara also asks you to calculate the total sales for Assessment projects and the average sales for clients in Oklahoma. In cell N14, create a formula using the DSUM function to calculate the total sales for Assessment projects, using the data in the entire Projects table (Projects[#All]) and totaling the values in the Total Sales column ("Total Sales") for project types equal to the values in the range M12:M13.

4. In cell N18, create a formula using the DAVERAGE function to calculate the average sales for clients in Oklahoma, using the data in the entire Projects table (Projects[#All]) and averaging the values in the Total Sales column ("Total Sales") for clients in states equal to the values in the range M16:M17.

5. Go to the Projects by Type worksheet. Amara has created a PivotTable named ProjectPivot to list the sales by project type, date, and client type. The data would be easier to interpret if the dates appeared as quarter numbers. Group the Start Date field values by Quarters only. Change the report layout to Compact Form to make the PivotTable look less cluttered. Use Quarters in cell B3 to identify the row headings, and use Client Types in cell C2 to identify the column headings. Resize column C to its best fit to fully display the new text.

6. Go to the Projects by Client worksheet. Amara created a PivotTable named ClientPivot that lists sales by client and state, but she wants you to simplify the PivotTable by displaying the sales by client and region. Manually group the MO and AK column labels (cells E3 and G3). Use North as the name of the group. Manually group the TX, OK, and LA column labels in row 4 and use South as the name of the group. Remove the State field from the Columns area. Rename the State2 field to use Region as the custom name. Sort the column labels in ascending order to list first the North sales amounts in column C followed by the South sales amounts in column D. Hide the field headers to further streamline the layout.

7. Go to the Sales by Project Type worksheet, which contains a PivotTable named TypePivot that compares the Quarter 1, Quarter 4, and Annual sales for each project type. Amara wants you to include the difference and the percentage of difference between the Quarter 4 and Quarter 1 sales and display the average total sales for each service. Insert a calculated field named Difference that subtracts the Qtr 1 field amount from the Qtr 4 field amount. In cell F2, use +/- Q4 to Q1 as the column label.

8. Insert another calculated field named % Difference that subtracts the Qtr 1 field amounts from the Qtr 4 field amounts, and then divides the result by the Qtr 1 field amount. In cell G2, use % Q4 to Q1 as the column label. Change the number format of the % Q4 to Q1 amounts to Percentage with 2 decimal points.

9. Add the Total Sales field to the Values area of the PivotTable, making it the last field in the Values area. Use the Average calculation to summarize the Total Sales field data. Move the Annual field so that it is listed as the last field in the Values area.

10. Amara also wants you to display details about projects with annual sales over $70,000. Apply a Value Filter to the Project Type field that displays the projects with Annual sales greater than $70,000.

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 website to submit your completed project.

Final Figure 1: All Projects Worksheet

Final Figure 2: Projects by Type Worksheet

Final Figure 3: Projects by Client Worksheet

Final Figure 4: Sales by Project Type Worksheet