Segment Reports Using Excel (Descriptive and Diagnostic Analytics) Southern Comforts, Inc, is a department store chain with stores in North Carolina, Tennessee, Kentucky, and West Virginia. Its corporate headquarters are located in Charlotte, North Carolina. In the past, the store owners only received financial reports for the company operations overall. They have recently asked for reports of costs and profitability by segment (location and department). Southern Comforts' locations include the four stores (Charlotte, Nashville, Virginia Beach, and Louisville) and the corporate office (Charlotte HQ). Department include the product lines (Mens, Womens, Kids, Shoes, and Home) and the overhead expense types (Facilities, Labor, an Other). They have provided you with an Excel workbook that includes Southern Comfort transactions for 2020. (The workbook, Segment Report Data Set.xlsx, is accessible on the textbook's website. A video demonstrating Excel tools used to answer the questions in this problem is also available on the website.). The first step is to make sure the data is in the form needed. - All transactions are included in the Transactions column. You will need to separate revenue transactions from expense transactions. Add two columns to the table. Use the If function to create a Revenues column and an Expenses column. (All positive numbers in the Transactions columns are Revenues; all negative numbers are expenses.) HINT: To save some time, convert the data to a Table. - Add a column after Month and call it Month Name. Use the TEXT function to convert the date format to a text format. 1. Use pivot tables to answer the following questions: a. Which store was the most profitable (in dollars)? What was that store's profit? $ b. Which store had the most revenue? How much? $ Which month had the highest revenue? How much? \$ What percentage of total sales occurred during that month? (Round % to two decimais. Example, 0.234555= 23.46%)x. c. Which month was the least profitable (in dollars)? What was the net profit that month? \$ d. What was the total gross margin (in dollars) for 2020?$ Which store had the highest gross margin? How much? HINT: Consider creating a calculated field using the Revenues and Expenses columns in the data sheet. Slicers can be used to filter out the overhead expense categories (Facilities, Labor, and Other). \$ e. What was the total gross profit ratio for 2020 ? (Round % to two decimals. Example, 0.234555=23.46%.) % Which store had the lowest gross profit ratio? What was it? (Round % to two decimal places. Example, 0.234555=23.46% ). % Which product line (department) had the highest gross profit ratio? What was it? (Round % to two decimal places. Example, 0.234555=23.46% ). % 2. The store with the highest sales (b.) and the highest gross margin (d.) was not the most profitable (a.). Why? Look at the revenues, gross profit margins and ratios, and the overhead expenses for both stores. 3. Create pivot charts from some of your pivot tables and use them to create an interactive dashboard. Include slicers on the dashboard that allow management to filter by location or month. Segment Reports Using Excel (Descriptive and Diagnostic Analytics) Southern Comforts, Inc, is a department store chain with stores in North Carolina, Tennessee, Kentucky, and West Virginia. Its corporate headquarters are located in Charlotte, North Carolina. In the past, the store owners only received financial reports for the company operations overall. They have recently asked for reports of costs and profitability by segment (location and department). Southern Comforts' locations include the four stores (Charlotte, Nashville, Virginia Beach, and Louisville) and the corporate office (Charlotte HQ). Department include the product lines (Mens, Womens, Kids, Shoes, and Home) and the overhead expense types (Facilities, Labor, an Other). They have provided you with an Excel workbook that includes Southern Comfort transactions for 2020. (The workbook, Segment Report Data Set.xlsx, is accessible on the textbook's website. A video demonstrating Excel tools used to answer the questions in this problem is also available on the website.). The first step is to make sure the data is in the form needed. - All transactions are included in the Transactions column. You will need to separate revenue transactions from expense transactions. Add two columns to the table. Use the If function to create a Revenues column and an Expenses column. (All positive numbers in the Transactions columns are Revenues; all negative numbers are expenses.) HINT: To save some time, convert the data to a Table. - Add a column after Month and call it Month Name. Use the TEXT function to convert the date format to a text format. 1. Use pivot tables to answer the following questions: a. Which store was the most profitable (in dollars)? What was that store's profit? $ b. Which store had the most revenue? How much? $ Which month had the highest revenue? How much? \$ What percentage of total sales occurred during that month? (Round % to two decimais. Example, 0.234555= 23.46%)x. c. Which month was the least profitable (in dollars)? What was the net profit that month? \$ d. What was the total gross margin (in dollars) for 2020?$ Which store had the highest gross margin? How much? HINT: Consider creating a calculated field using the Revenues and Expenses columns in the data sheet. Slicers can be used to filter out the overhead expense categories (Facilities, Labor, and Other). \$ e. What was the total gross profit ratio for 2020 ? (Round % to two decimals. Example, 0.234555=23.46%.) % Which store had the lowest gross profit ratio? What was it? (Round % to two decimal places. Example, 0.234555=23.46% ). % Which product line (department) had the highest gross profit ratio? What was it? (Round % to two decimal places. Example, 0.234555=23.46% ). % 2. The store with the highest sales (b.) and the highest gross margin (d.) was not the most profitable (a.). Why? Look at the revenues, gross profit margins and ratios, and the overhead expenses for both stores. 3. Create pivot charts from some of your pivot tables and use them to create an interactive dashboard. Include slicers on the dashboard that allow management to filter by location or month