New Perspectives Excel 2019 | Module 11: SAM Project 1b | Romano Collectibles #newperspectives

New Perspectives Excel 2019 | Module 11: SAM Project 1b | Romano Collectibles #newperspectives

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. Anthony and Gina Romano have started to collect items such as sports memorabilia and comic books, anticipating that their collections will appreciate in value. Anthony is using an Excel workbook to track their collectible items and asks for your help in summarizing data so he can evaluate the items gaining the most value. To do so, you will use database functions and advanced PivotTable features.
Go to the Collections worksheet, which contains a table named Collectibles listing details about the items the Romanos collect. Start by clearing the filter from the Category field.
2. Instead of seeing the data sorted by purchase price, Anthony and Gina would like to see the data by year. Sort the table data by the Acquired field from smallest to largest.
3. In the range K3:N8, Anthony wants to summarize item information.
Start by calculating the number of items in each category as follows:
a. In cell L4, enter a formula using the COUNTIF function that counts the number of comic books, checking that the Category column in the Collectibles table (Collectibles[Category]) is equal to the value in cell K4.
b. Fill the range L5:L8 with the formula in cell L4.
4. In column M, Anthony wants to calculate the total value of the items in each category. Determine the total values as follows:
a. In cell M4, enter a formula using the SUMIF function that totals the value for comic books, checking that the Category column in the Collectibles table (Collectibles[Category]) is equal to the value in cell K4, and that the formula totals all the current values (Collectibles[Current Value]).
b. Fill the range M5:M8 with the formula in cell M4.
5. In column N, Anthony wants to calculate the average value of the items in each category. Determine the average values as follows:
a. In cell N4, enter a formula using the AVERAGEIF function that averages the value for comic books, checking that the Category column in the Collectibles table (Collectibles[Category]) is equal to the value in cell K4, and that the formula averages all the current values (Collectibles[Current Value]).
b. Fill the range N5:N8 with the formula in cell N4.
6. Go to the Value by Condition worksheet. Anthony has created a PivotTable on this worksheet to list the final current value of the collectible items by category, condition, and year. He grouped the year data into two-year spans, but wants them listed as separate years.
Ungroup the year data in the PivotTable.
7. Anthony thinks the PivotTable looks crowded in its default Compact layout.
Change the report layout to show the PivotTable in Tabular Form.
8. Go to the Items by Category worksheet. Anthony created a PivotTable that lists each item by category, and then counts the number of those items acquired each year. The data is sorted in alphabetic order by category, but Anthony wants to sort the data by total number of items. He also wants to focus on coins and comic books only.
Change the display of the PivotTable as follows:
a. Sort the data in descending order by Grand Total.
b. Apply a Label Filter that displays Category values that begin with Co.
9. Go to the Price and Value Comparison worksheet. The PivotTable on this worksheet lists the purchase prices and current values for each category of collectible item. Anthony wants to include the number of items in each category in the second column of the PivotTable.
Make this change for Anthony as follows:
a. Add the ID field to the Values area of the PivotTable, making it the first field in the Values area.
b. Change the number format of the Count of ID amounts to Number with 0 decimal places.
10. Anthony wants to know the difference and the percentage of difference between the purchase prices and the current values.
Provide this information for Anthony as follows:
a. Insert a calculated field named Difference that subtracts the Purchase Price field amount from the Current Value field amount.
b. Insert another calculated field named % Difference that subtracts the Purchase Price field amounts from the Current Value field amounts, and then divides the result by the Purchase Price field amount.
c. Change the number format of the Sum of % Difference amounts to Percentage with 2 decimal points.

Видео New Perspectives Excel 2019 | Module 11: SAM Project 1b | Romano Collectibles #newperspectives канала AssignmentsDoneByExperts