Pivot Table assignment

Ex1

Exercise 1: *You have a list of repeating group names. *Using a pivot table, count how many times each group name appears in the list. *Keep pivot table in this tab. Notice that this pivot table is a list comprised of each group name only occurring once. Then, within the pivot table, put the list in decreasing order based on the number of times the name appears in the list. Create a Piovt Chart to supplement the Pivot Table.

Number of order

Total

Postcards Store Randles Roadshack Examineers Inc Buttersworth Co FASST Trax Hula Hoopers Hehehe Marleyville Tea Creepy Lada Jokes R Us Petie's Palace 42 37 34 32 32 32 30 25 20 16 11

Sheet1

Ex2

Exercise 2 There are preset 3 segments that all groups are put into: Big Medium Tiny Using a pivot table, create a table displaying Revenue for each segment. Keep pivot table in this tab. Large $ amounts (over $100 or so) are typically shown with zero decimals. Create a Pivot Chart to supplement the Pivot Table

Ex3

Exercise 3 Make a pivot table showing each segments revenue and expenses. Make sure to use both row headers and column headers. Have "Sum of Member Months" be the left most column. Create a column adjacent to the pivot table that represents the Revenue and Expenses PMPM's. PMPM's are typically represented with dollar signs ($) and to two decimal places. Keep the table in this tab.

Ex4

Exercise 4 Create a column entitled "Net Income/Loss." This column will represent Revenue minus Expenses. Make a pivot table representing Each Segment and Subdivision by Revenue, Expenses, and Net Income. Use row headers and column headers. Keep pivot table in this tab.

Ex5

Exercise 5 Make a pivot table of Expenses by Service Month and Paid Month for just the "Big" group segment. Keep pivot table in this tab. The result is referred to as a Claims Lag Triangle.

Ex6

Exercise 6 Make a pivot table for the Group Segment "Big" and show Revenue and Expense by Subdivision. Create a second Pivot Table. Copy and paste the entire pivot table and change the Segment to "Medium." Change the order of each of the rows. Meaning if it was the top row label, switch it with the bottom row label. Now, switch the Revenue column with the Expenses column. Keep both pivot tables in this tab. To go to Exercise 7, unhide the tab entitled "Ex7."

Ex7

Exercise 7 Because Ramone's Castle is a high outlier, take it out of the pivot table that is already done. Show Revenue and Expenses by Group Segment (excluding Ramone's Castle). Have "Sum of Revenue" be the left column (you may need to switch the two columns). Keep pivot table in this tab.

Ex8