Shelly Cashman Excel 365 | Module 6: SAM Project A Starr Consulting

GETTING STARTED

1. Save the file SC_EX365_6A_FirstLastName_1.xlsx as SC_EX365_6A_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 SC_EX365_6A_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. Marcelo Madeira is a financial analyst for Starr Consulting, a management consulting firm in Providence, Rhode Island. Marcelo is analyzing recent and proposed consulting projects for the firm. He asks for your help in using Excel tables to complete the analysis. Go to the Completed Consulting worksheet, which lists the consulting projects completed in the previous year. Create a table as follows so that Marcelo can summarize and filter the data and display projects with the highest budgeted contract amounts:

a. Format the completed consulting projects data (range A2:F18) as a table with headers using Red, Table Style Medium 21.

b. Use CompletedProjects as the name of the table.

2. Starr Consulting gave each client a $1,000 discount for projects completed in the previous year. Marcelo asks you to reflect this discount in the CompletedProjects table. Add a calculated column to the table as follows:

a. Add a column named Final Contract to the right of the Budget column.

b. In cell G3, insert a formula that uses a structured reference but not a function to subtract 1000 from the amount in the Budget column.

c. Copy the formula in cell G3 to the rest of the column if Excel does not fill the column automatically.

3. Filter the CompletedProjects table using a custom AutoFilter to display projects with a Budget amount greater than $40,000.

4. Go to the Current Consulting worksheet, which contains the Projects table listing active consulting projects. Marcelo wants to quickly identify the projects by their budget amount. Sort the Projects table in ascending order by Budget amount.

5. Marcelo asks you to create a separate list of projects that have been assigned to a consulting team. Use an advanced filter as follows to list these projects in a new range:

a. In cell H17, type Yes as the value to filter on in the criteria range.

b. Create an advanced filter using the Projects table (range A2:H12) as the List range.

c. Use the range A16:H17 as the Criteria range.

d. Copy the results to another location, starting in the range A19:H19.

6. As a contrast, Marcelo also wants you to list the projects that have not been assigned to a team. Filter the Projects table as follows:

a. Display the filter arrows in the Projects table.

b. Filter the table to display only projects that are not assigned to a team.

7. Go to the Proposals worksheet, which lists projects the firm proposed to clients in the current year. Marcelo suspects the Proposals table has a duplicate record. Identify the duplicate as follows:

a. Clear the filter from the Proposals table to display all the records.

b. Remove duplicates from all the columns in the table.

8. Add a Total Row to the Proposals table, which automatically sums the budget amounts. Using the total row, display the count of the contracts in the Contract Type column.

9. Marcelo asks you to identify the projects that require 120 days or more to complete, those that require 60 days or more to complete, and those that require less than 60 days to complete.

a. In the Days column (range C3:C16), create a new Icon Set conditional formatting rule using the 3 Traffic Lights (Unrimmed) icons.

b. Reverse the icon order.

c. Display the red circle icon in cells with a Number type value greater than or equal to 120.

d. Display the yellow circle icon in cells with a Number type value greater than or equal to 60.

e. Display the green circle icon in cells with a Number type value less than 60.

10. Marcelo wants you to summarize the number of projects proposed by the project type and calculate their total and average budget amounts. Calculate this information for Marcelo as follows:

a. In cell I3, enter a formula using the COUNTIF function that counts the number of proposed Improvement projects, using Proposals[Project Type] as the range and cell H3 as the criteria.

b. Fill the range I4:I7 with the formula in cell I3.

c. In cell J3, enter a formula using the SUMIF function that totals the budget for proposed Improvement projects, using Proposals[Project Type] as the range, cell H3 as the criteria, and Proposals[Budget] as the sum_range.

d. Fill the range J4:J7 with the formula in cell J3.

e. In cell K3, enter a formula using the AVERAGEIF function that averages the budget for proposed Improvement projects, using Proposals[Project Type] as the range, cell H3 as the criteria, and Proposals[Budget] as the average_range.

f. Fill the range K4:K7 with the formula in cell K3.

11. In the range H10:K15, Marcelo asks you to insert a summary of the current consulting projects. Insert this data as a table as follows:

a. Insert a table in the range H10:K15, specifying that the data has headers.

b. In the new table, enter the data shown in Table 1.

c. Apply Red, Table Style Medium 21 to the new table to match the formatting of the Proposals table.

Table 1: Data for the New Table

| H | I | J | K 10 | Project Type | Started | Completed | Budget 11 | Improvement | 4 | 1 | 118,000 12 | M&A | 1 | 0 | 35,000 13 | Strategy | 3 | 1 | 82,000 14 | Supervision | 1 | 0 | 26,000 15 | Turn Around | 1 | 0 | 13,000

12. Go to the Project Budgets worksheet, which lists all the current and proposed consulting projects. Marcelo wants you to display the data by contract type and then list the projects by start date. Sort the data in the table in ascending order first by contract type and then by start date.

13. Marcelo also wants you to calculate subtotals for each contract type. (Hint: You must complete all actions of this step correctly to receive full credit.)

a. Convert the table to a range.

b. Insert a subtotal at each change in the Contract Type value.

c. Use the Sum function to calculate the subtotals.

d. Add subtotals to the Budget values only.

e. Include a summary below the data.

f. Collapse the outline to display only the subtotals for each contract type and the grand total.

14. Go to the Project Lookup worksheet, which contains a table named Lookup that lists project details, including the ID code that staff in the Accounts Receivable Department use to refer to the projects. Marcelo asks you to complete the Project Information data in the range G3:H15. Create a formula that looks up a project name based on its ID as follows:

a. In cell H4, begin to enter a formula using the VLOOKUP function.

b. Use the Project ID (cell H3) as the lookup_value.

c. Use the Lookup table as the table_array.

d. Return the value in column 2 of the table_array.

e. Specify an exact match.

15. The next formula looks up a project name based on its start date. To provide this information, create a formula using a function and structured references as follows:

a. In cell H7, begin to enter a formula using the XLOOKUP function.

b. Use the start date (cell H6) as the lookup value.

c. Use the Start Date column as the lookup_array.

d. Use the Project Name column as the return_array.

16. The next formulas identify the number of projects that have budgets of more than $25,000 and calculate the average budget amount of Improvement projects. Create formulas that provide this information as follows:

a. In cell H11, create a formula using the DCOUNT function to count the number of projects with budget amounts greater than $25,000, using the Lookup table (range A2:E27) as the database, "Budget" as the field, and the range G9:G10 as the criteria.

b. In cell H15, create a formula using the DAVERAGE function to average the budget amounts for Improvement projects, using the Lookup table (range A2:E27) as the database, "Budget" as the field, and the range G13:G14 as the criteria.

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: Completed Consulting Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 2: Current Consulting Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 3: Proposals Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 4: Project Budgets Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.

Final Figure 5: Project Lookup Worksheet

Windows, Access, Excel, and PowerPoint are registered trademarks of Microsoft Corporation. Microsoft and the Office logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. This product is an independent publication and is neither affiliated with, nor authorized, sponsored, or approved by, Microsoft Corporation. Copyright (c) 2025 Cengage Learning. All Rights Reserved.