New Perspectives Excel 365 | Module 6: SAM Project B Charlotte Development

Описание: If you directly want to get the project from us then contact us on Whatsapp. Click on link below👇👇
https://wa.me/message/PPXGSJS2TLWUA1

Whatsapp Contact Number:
+91 8114420233

Email ID:
[email protected]

New Perspectives Excel 365 | Module 6: SAM Project B Charlotte Development MANAGING YOUR DATA WITH DATA TOOLS

PROJECT STEPS
1. Teresa Rosales is a project assistant for Charlotte Development, a real estate firm specializing in commercial property in North Carolina. She tracks the real estate agents' properties and sales in an Excel workbook and asks for your help in updating and analyzing the data.
Go to the Carter Properties worksheet, which lists sales data for Derrick Carter. Teresa asks you to format the data as follows to match the data for the other agents:
a. Format the range A2:F12 as an Excel table with headers using the Olive Green, Table Style Medium 5 table style.
b. Use Carter as the name of the table.
2. Teresa wants you to add a new property for Derrick Carter. Add the record shown in Table 1 to the end of the Carter table.

3. She also wants you to focus on Derrick Carter's properties with listing amounts over $1,500,000.
Use a custom Number filter to display only properties with listing amounts greater than 1,500,000.
4. Go to the Quinn Properties worksheet, which contains a table named Quinn for Helena Quinn's properties. Unfreeze the panes in the worksheet because it is not long or wide enough to scroll.
5. Teresa asks you to organize the data by property type and listing amount and to display the average amounts of listings and sales. Modify the Quinn table as follows:
a. Remove the filter from the Quinn table to display all of the data.
b. Sort the data in ascending order first by the Property Type field and then in descending order by the Listing Amount field.
c. Insert a Total row in the Quinn table.
d. Use the Total row to calculate the average listing amount and average sales amount.
e. In cell A11, change the text to Average to indicate what the amounts represent.
6. Go to the Cortez Properties worksheet. Edgar Cortez has sold more properties than any other agent. Teresa wants you to summarize the Cortez property data using subtotals to show the value of the properties of each type. Modify the Cortez data as follows:
a. Sort the Cortez table in ascending order by the Property Type field.
b. Convert the table to a normal range.
c. Insert subtotals into the range A2:F17, with the subtotals appearing at each change in the Property Type column value.
d. Add subtotals using the SUM function to the Listing Amount and Sales Amount columns.
7. Go to the Mikos Properties worksheet. The Mikos table on this worksheet lists commercial properties for Constantine Mikos. Format the table to match others in the workbook as follows:
a. Apply the Olive Green, Table Style Medium 5 table style to the Mikos table.
b. Display the filter buttons.
8. Teresa knows that the Mikos table includes a duplicate record and asks you to remove it.
Use a table tool to remove the duplicate record based on the values in all the columns in the table.
9. Go to the All Agents worksheet, which contains a table named Agents listing data for all Charlotte Development real estate agents. Freeze the first two rows of the worksheet to display the worksheet title and table column headings when the worksheet is scrolled.
10. Teresa asks you to calculate the difference between the listing amounts and sales amounts. Modify the Agents table as follows:
a. Add a table column to the end of the Agents table.
b. Use Difference as the column heading.
c. Apply a middle vertical alignment to the column heading and use AutoFit to adjust the width of the column.
d. In cell G3, enter a formula using structured references and no function to subtract the value in the [Listing Amount] column from the value in the [Sales Amount] column.
e. Fill the range G4:G39 with the formula in cell G3 if Excel does not automatically do so.
11. She also wants you to make the following calculations in the range J3:J5:
a. In cell J3, use the COUNTA function with a structured reference to count the values in the [Property ID] column of the Agents table.
b. In cell J4, use the SUM function with a structured reference to total the values in the [Listing Amount] column of the Agents table.
c. In cell J5, use the SUM function with a structured reference to total the values in the [Sales Amount] column of the Agents table.
12. Teresa wants you to make it easy to filter the Agents table based on the property type and city. Add slicers to the table as follows:
#samproject1b #samprojects #cengageassignments #NP_EX365_6b #NP_EXCEL #excel_assignments #excel_sam_projects #Module6SamProjectb #charlotte_development #new_perspectives_excel #NP_EX365_6b_FirstLastName_1.xlsx #NP_EX365_6b_FirstLastName_2.xlsx