Phoenix Entertainment (Financial Accounting) Learning objectives 1. Create a pivot table in Excel 2. Format a pivot table 3. Apply filters to a pivot table 4. Sort a pivot table . 5. Create sum columns in a pivot table 6. Modify Value Field Settings in a Pivot Table Group Pivot Table Fields into one Field 7. 8. Create Sparklines 9. Create a calculated field 10. Create a pivot chart 11. Create a slicer Data set background Thomas Mars, owner of Phoenix Entertainment has contacted your accounting firm to review his Sales and Costs records. Phoenix Entertainment sells all the entertainment a person needs to "raise fun from the ashes of boredom". They specialize in selling anything a person needs in their entertainment. center, and their main products are furniture, TVs, gaming consoles, and sound systems. The company has stores throughout the United States and divides its reporting into six regions (South, Mid-Atlantic, Southwest, Northeast, West, and Midwest). Thomas has provided you with a spreadsheet containing the company's sales data and has asked that your firm prepare a gross profit schedule and answer various questions concerning the company's sales and costs. In addition to the sales data, Thomas included the following information from the company's sales and supplies policies and procedures manual. Sales Prices are consistent across regions and have not been revised during the past year. The only change in price that occurred in the past year was an increase in selling price for Furniture at the . beginning of Q3 to keep up with rising costs. Sales prices for Gaming Consoles are set by the industry and we can't increase them. We can offer discounts and promotions to bring in more traffic to our stores. The main driver of the product cost is the price paid to suppliers to acquire the inventory for reselling. We use a total of 7 suppliers to acquire our products for reselling. We have suppliers for three distinct product categories: Furniture, Gaming Consoles, and Electronics. We use the same suppliers for our Northeast, South, Midwest, and Southwest regions. We use the same supplier for Gaming Consoles in all 6 regions. The supplier list is attached below: Supplier List Supplier Furniture to Go Big Furniture Furniture Fair Gaming Stop Best Electronics New Electronics Hope Electronics Data dictionary . . . . Product Furniture Furniture Furniture Gaming Consoles TVs and Sound Systems TVs and Sound Systems TVs and Sound Systems Region Northeast, South, Midwest, and Southwest West Mid-Atlantic All Regions Northeast, South, Midwest, and Southwest West Mid-Atlantic Region: Indicates the regional location of the physical store. Product: Indicates the type of product sold. Product Code: Indicates the region and product combined in a code Date of Sale: Indicates the day when a sale took place. Sale: Indicates the selling price of product sold. Cost: Indicates the cost associated with the product sold. For each of the following requirements, create a new pivot table in a new worksheet (Tabs at the bottom). Name each new worksheet as "Req 1," "Req 2," etc. Format all amounts using the Comma Style format with zero decimal places. Format percentage numbers with the Percentage Style format with zero decimal places. Req 1. Present total amount of items sold per product. (Hint: Go to Value Field Settings and summarize values by COUNT). Change the column name in the pivot table to "Total Units Sold". Which product sold more units in the year? Highlight this value in Green. Req 2. Prepare a gross profit schedule that contains the totals for Total Sales, Total Cost, Total Gross Profit, and Gross Profit Rate by REGION (Row). (Hint: Create a calculated field for Gross Profit and Gross Profit Rate. Remember that Gross Profit Rate is a PERCENTAGE.) . . . . Change the column names in the pivot table to match the headings listed above. (Hint: If you get a message saying the name exists, just add an extra space before or after the name). Sort the pivot table by Sales in descending order. Req 3. Prepare a schedule that shows Gross Profit Rate per Quarter (Rows) for each Region (Columns). (Hint: Use Date of Sale in Rows. A Month field in the Row section may have been automatically created. Make sure only one field in the Rows section remains. Right-click on any date in the pivot table and select group, then make sure only Quarters is selected.) Which region had the highest Sales? Highlight this value in Green. Which region had the lowest Sales? Highlight this value in Red. . Which region had the highest Gross Profit (highlight in Green) and lowest Gross Profit (highlight in Red). . Which region had the highest Gross Profit Rate (highlight in Green) and lowest Gross Profit Rate (highlight in Red). Req 4. Prepare a Gross Profit Rate Schedule per Quarter (Column) for each Product (Row). What is the Gross Profit trend you see throughout the year for each product? Insert a Sparkline on the right side of the Pivot Table that shows the linear trend for the year. What is the Gross Profit Rate trend through the year? Insert a PivotChart Line Graph to visually show this trend. Req 5. Prepare a schedule that shows the Gross Profit Rate per Quarter (columns) for each Region, sub- divided by Products (Hint: Each Product should show each Region underneath). Include a PivotChart Bar Graph. Include a Slicer for the Product Field. What do you note when comparing the different Regions for each Product? Req 6. Thomas Mars wants to obtain a loan from the bank to start a new and expensive project that is going to "revolutionize home entertainment as we know it". One of the requirements from the bank is to show that each Region has Gross Profit Rate that meets or exceeds the minimum required. You can see the bank's minimum Gross Profit per Region requirement on Cell P14 on the DATA tab. Prepare a Gross Profit Rate (column) schedule per Region (Row). Does Phoenix Entertainment qualify for the loan? Thomas Mars suggests grouping the regions that contain west (Midwest, Southwest, and West) since they are all located on the west part of the country. After grouping the three regions and creating a new "West Area" Region, does Phoenix Entertainment qualify for the loan? (Hint: Select the three regions by holding down the CTRL key while clicking on three regions if they are non-adjacent. Right-click to group.) Make sure you rename the new Group (automatically named Group 1) as indicated above and minimize all groups (click on the - button) On the Rows field, remove the "Region" field. Only Region2 should remain. On Req 2, Req 3, and Req 5 you should now see a Region 2 Field added. Remove it from the PivotTable Fields to return the tables to their original form. Bank Gross Profit Rate Minimum per Region 27% Phoenix Entertainment (Financial Accounting) Learning objectives 1. Create a pivot table in Excel 2. Format a pivot table 3. Apply filters to a pivot table 4. Sort a pivot table . 5. Create sum columns in a pivot table 6. Modify Value Field Settings in a Pivot Table Group Pivot Table Fields into one Field 7. 8. Create Sparklines 9. Create a calculated field 10. Create a pivot chart 11. Create a slicer Data set background Thomas Mars, owner of Phoenix Entertainment has contacted your accounting firm to review his Sales and Costs records. Phoenix Entertainment sells all the entertainment a person needs to "raise fun from the ashes of boredom". They specialize in selling anything a person needs in their entertainment. center, and their main products are furniture, TVs, gaming consoles, and sound systems. The company has stores throughout the United States and divides its reporting into six regions (South, Mid-Atlantic, Southwest, Northeast, West, and Midwest). Thomas has provided you with a spreadsheet containing the company's sales data and has asked that your firm prepare a gross profit schedule and answer various questions concerning the company's sales and costs. In addition to the sales data, Thomas included the following information from the company's sales and supplies policies and procedures manual. Sales Prices are consistent across regions and have not been revised during the past year. The only change in price that occurred in the past year was an increase in selling price for Furniture at the . beginning of Q3 to keep up with rising costs. Sales prices for Gaming Consoles are set by the industry and we can't increase them. We can offer discounts and promotions to bring in more traffic to our stores. The main driver of the product cost is the price paid to suppliers to acquire the inventory for reselling. We use a total of 7 suppliers to acquire our products for reselling. We have suppliers for three distinct product categories: Furniture, Gaming Consoles, and Electronics. We use the same suppliers for our Northeast, South, Midwest, and Southwest regions. We use the same supplier for Gaming Consoles in all 6 regions. The supplier list is attached below: Supplier List Supplier Furniture to Go Big Furniture Furniture Fair Gaming Stop Best Electronics New Electronics Hope Electronics Data dictionary . . . . Product Furniture Furniture Furniture Gaming Consoles TVs and Sound Systems TVs and Sound Systems TVs and Sound Systems Region Northeast, South, Midwest, and Southwest West Mid-Atlantic All Regions Northeast, South, Midwest, and Southwest West Mid-Atlantic Region: Indicates the regional location of the physical store. Product: Indicates the type of product sold. Product Code: Indicates the region and product combined in a code Date of Sale: Indicates the day when a sale took place. Sale: Indicates the selling price of product sold. Cost: Indicates the cost associated with the product sold. For each of the following requirements, create a new pivot table in a new worksheet (Tabs at the bottom). Name each new worksheet as "Req 1," "Req 2," etc. Format all amounts using the Comma Style format with zero decimal places. Format percentage numbers with the Percentage Style format with zero decimal places. Req 1. Present total amount of items sold per product. (Hint: Go to Value Field Settings and summarize values by COUNT). Change the column name in the pivot table to "Total Units Sold". Which product sold more units in the year? Highlight this value in Green. Req 2. Prepare a gross profit schedule that contains the totals for Total Sales, Total Cost, Total Gross Profit, and Gross Profit Rate by REGION (Row). (Hint: Create a calculated field for Gross Profit and Gross Profit Rate. Remember that Gross Profit Rate is a PERCENTAGE.) . . . . Change the column names in the pivot table to match the headings listed above. (Hint: If you get a message saying the name exists, just add an extra space before or after the name). Sort the pivot table by Sales in descending order. Req 3. Prepare a schedule that shows Gross Profit Rate per Quarter (Rows) for each Region (Columns). (Hint: Use Date of Sale in Rows. A Month field in the Row section may have been automatically created. Make sure only one field in the Rows section remains. Right-click on any date in the pivot table and select group, then make sure only Quarters is selected.) Which region had the highest Sales? Highlight this value in Green. Which region had the lowest Sales? Highlight this value in Red. . Which region had the highest Gross Profit (highlight in Green) and lowest Gross Profit (highlight in Red). . Which region had the highest Gross Profit Rate (highlight in Green) and lowest Gross Profit Rate (highlight in Red). Req 4. Prepare a Gross Profit Rate Schedule per Quarter (Column) for each Product (Row). What is the Gross Profit trend you see throughout the year for each product? Insert a Sparkline on the right side of the Pivot Table that shows the linear trend for the year. What is the Gross Profit Rate trend through the year? Insert a PivotChart Line Graph to visually show this trend. Req 5. Prepare a schedule that shows the Gross Profit Rate per Quarter (columns) for each Region, sub- divided by Products (Hint: Each Product should show each Region underneath). Include a PivotChart Bar Graph. Include a Slicer for the Product Field. What do you note when comparing the different Regions for each Product? Req 6. Thomas Mars wants to obtain a loan from the bank to start a new and expensive project that is going to "revolutionize home entertainment as we know it". One of the requirements from the bank is to show that each Region has Gross Profit Rate that meets or exceeds the minimum required. You can see the bank's minimum Gross Profit per Region requirement on Cell P14 on the DATA tab. Prepare a Gross Profit Rate (column) schedule per Region (Row). Does Phoenix Entertainment qualify for the loan? Thomas Mars suggests grouping the regions that contain west (Midwest, Southwest, and West) since they are all located on the west part of the country. After grouping the three regions and creating a new "West Area" Region, does Phoenix Entertainment qualify for the loan? (Hint: Select the three regions by holding down the CTRL key while clicking on three regions if they are non-adjacent. Right-click to group.) Make sure you rename the new Group (automatically named Group 1) as indicated above and minimize all groups (click on the - button) On the Rows field, remove the "Region" field. Only Region2 should remain. On Req 2, Req 3, and Req 5 you should now see a Region 2 Field added. Remove it from the PivotTable Fields to return the tables to their original form. Bank Gross Profit Rate Minimum per Region 27%