Скачать или смотреть Shelly Cashman Excel 365 | Module 6: SAM Project A Starr Consulting

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

https://wa.me/message/PPXGSJS2TLWUA1

Whatsapp Contact Number:
+91 8114420233

Email ID:
[email protected]

Shelly Cashman Excel 365 | Module 6: SAM Project A Starr Consulting CREATE, SORT, AND QUERY TABLES

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.
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.