Shelly Cashman Excel 365 | Module 6: SAM Critical Thinking Project C Starr Consulting

GETTING STARTED

1. Save the file SC_EX365_CT6C_FirstLastName_1.xlsx as SC_EX365_CT6C_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_CT6C_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. Format the completed consulting projects data as a table with headers using Red, Table Style Medium 21, using 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.

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

b. To calculate the final contract amount for SafeTran Security, 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 down to fill the rest of the column if Excel does not fill it 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 to list these projects in a new range.

a. Filter the table by the Team Assigned value by typing Yes in the appropriate cell in the Projects Assigned area.

b. Create an advanced filter using the entire Projects table as the List range, the two rows of the Projects Assigned area as the Criteria range, and copy the results to another location, starting with the lowest set of headers.

6. As a contrast, Marcelo also wants you to list the projects that have not been assigned to a team. Display the filter arrows in the Projects table, and then 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 by clearing the filter from the Proposals table to display all the records, and then remove duplicates from all the columns in the table.

8. Add a Total Row to the Proposals table, which automatically sums the budget amounts. Use it to 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. In the Days column, create a new Icon Set conditional formatting rule using the 3 Traffic Lights (Unrimmed) icons. Display the green circle icon in cells with a Number type value greater than or equal to 120, the yellow circle icon in cells with a value greater than or equal to 60, and the red circle icon in cells with a 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.

a. In the appropriate cell, enter a formula using a function that counts the number of proposed Improvement projects, based on the number of project types in the Proposals table.

b. Fill the area down with the formula you just made to count the other project types in the Proposals table.

c. In the appropriate cell, enter a formula using the a function that totals the budget for proposed Improvement projects, based on the number of project types in the Proposals table. Fill this formula down as well.

d. In the appropriate cell, enter a formula using the a function that averages the budget for proposed Improvement projects, based on the number of project types in the Proposals table. Fill the formula down to complete the data in the area.

11. In the range H10:K15, Marcelo asks you to insert a summary of the current consulting projects. In the appropriate cells, enter the data from Table 1, format it as a table with headers, and apply the Red, Table Style Medium 21 table style to the new table to match the formatting of the Proposals table. (Hint: If necessary, clear the formatting from any cells that don't look 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, and use the SUM function to calculate the subtotals.

c. Add subtotals to the Budget values only, and include a summary below the data.

d. 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. In the appropriate cell in the Project Information section, create a formula using a function that looks up a project name based on its ID. Lookup the Project ID in the cell above, in the Lookup table, and return the value in column 2 of the table_array using 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. In the appropriate cell, lookup the start date provided, find it in the Start Date column of the Lookup table, and return the value from the Project Name column.

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.

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

b. In the appropriate cell, create a formula using another database function to average the budget amounts for Improvement projects. Use the entire Lookup table 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.