PROJECT STEPS

$1.$ Nadia Ivanov is a partner at Qualey Consulting, a consulting firm with headquarters in Hoboken, New Jersey. She uses an Excel workbook to track sales related to consulting 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 Sales Report worksheet, which contains a table named Sales listing details about consulting projects completed in $2024.$ In the range M$3$:P$8,$ Nadia wants to summarize project information. Start by calculating the number projects for clients in each business category. In cell N$4,$ enter a formula using the COUNTIF function that counts the number of projects for clients in the Banking category, checking that the Category column in the Sales table $($Sales$[$Category$])$ is equal to the value in cell M$4.$ Fill the range N$5$:N$8$ with the formula in cell N$4.$

$2.$ In column O$,$ Nadia wants to calculate the total sales for each category. In cell O$4,$ enter a formula using the SUMIF function that totals the sales for Banking projects, checking that the Category column in the Sales table is equal to the value in cell M$4,$ and that the formula totals the sales for all four quarters $($Sales$[$Total Sales$]).$ Fill the range O$5$:O$8$ with the formula in cell O$4.$

$3.$ In column P$,$ Nadia wants to calculate the average sales for each category. In cell P$4,$ enter a formula using the AVERAGEIF function that averages the sales for Banking projects, checking that the Category column in the Sales table is equal to the value in cell M$4,$ and that the formula averages the sales for all four quarters $($Sales$[$Total Sales$]).$ Fill the range P$5$:P$8$ with the formula in cell P$4.$

$4.$ Nadia needs to identify the number of projects that have total sales more than $$10,000$ and those for client KERRA. In cell N$12,$ create a formula using the DCOUNT function to count the number of projects with total sales of more than $$10,000,$ using the data in the entire Sales table $($Sales$[$#All$])$ and counting the values in the Total Sales column $("$Total Sales"$)$ that are equal to the values in the range M$10$:M$11.$

$5.$ In cell N$16,$ create a formula using the DCOUNTA function to count the number of projects for client KERRA, using the data in the entire Sales table $($Sales$[$#All$])$ and counting the values in the Client ID column $("$Client ID$")$ that are equal to the values in the range M$14$:M$15.$

$6.$ Nadia also needs to calculate the total sales for marketing plans and the average sales for clients in New Jersey. In cell N$20,$ create a formula using the DSUM function to calculate the total sales for marketing plans, using the data in the entire Sales table $($Sales$[$#All$])$ and totaling the values in the Total Sales column $("$Total Sales"$)$ for services equal to the values in the range M$18$:M$19.$

$7.$ In cell N$24,$ create a formula using the DAVERAGE function to calculate the average sales for clients in New Jersey, using the data in the entire Sales table $($Sales$[$#All$])$ and averaging the values in the Total Sales column $("$Total Sales"$)$ for clients in states equal to the values in the range M$22$:M$23.$

$8.$ Go to the Sales by Category worksheet. Nadia has created a PivotTable named CategoryPivot to list the sales by business category, date, and service 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.

$9.$ Go to the Sales by Client worksheet. Nadia created a PivotTable named ClientPivot that lists sales by client and state, but she wants to simplify the PivotTable by displaying the sales by client and region. Manually group the CT$,$ NJ$,$ and NY column labels $($cells B$3,$ E$3,$ and F$3).$ Use North as the name of the group. Manually group the GA and FL column labels in row $4$ and use South as the name of the group. Remove the State field from the Columns area. Rename the State$2$ field to use Region as the custom name. Sort the South values in ascending order to list first the North sales amounts in column B followed by the South sales amounts in column C$.$ Hide the field headers to further streamline the layout.

$10.$ Go to the Sales by Service worksheet, which contains a PivotTable named ServicePivot that compares the Quarter $1,$ Quarter $4,$ and Annual sales for each service provided in $2024.$ Nadia wants to know 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 E$2,$ use $+/-$ Q$4$ to Q$1$ as the column label.

$11.$ 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 F$2,$ use $\%$ Q$4$ to Q$1$ as the column label. Change the number format of the $\%$ Q$4$ to Q$1$ amounts to Percentage with $2$ decimal points.