Excel Module 6 SAM Project A | Starr Consulting | CREATE, SORT, AND QUERY TABLES #shellycashman

Excel Module 6 SAM Project A | Starr Consulting | CREATE, SORT, AND QUERY TABLES #shellycashman

If you directly want to get the project from us then contact us on our Whatsapp. Link is given here,

Whatsapp Contact Link:
https://api.whatsapp.com/message/4B6NMKKBKUFYN1?autoload=1&app_absent=0

Whatsapp Number:
+919116641093
+918005564456

Gmail Id:
singhal.agrawal.bharati@gmail.com

We are providing help in all Online Courses, Computer Science, Business and Management, Business Math, Business and Finance, Business and Accounting, Human Resource Management, History, English.

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 green 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 red circle icon in cells with a Number type value less than 60.

Видео Excel Module 6 SAM Project A | Starr Consulting | CREATE, SORT, AND QUERY TABLES #shellycashman канала Commerce Classes