[SOLVED] Enterprise data analysis management Matlab

The Excel File

To submit your excel assignment file, please upload an “Excel Macro-Enabled Workbook” to the Avenue.

You can select “save as” and from the file type list choose Excel Macro-Enabled Workbook (*.xlsm).

The PDF File

This Memo document should include your findings in addition to any screenshots of charts and tables as

instructed in the assignment tasks.

NOTE

Save ALL the pivot tables independently so we can match them to the answers in the memo.

Background

You are hired by an online office store in the US to analyze sales data by reviewing and evaluating

the orders and shipments details. You are supposed to use the advanced Excel skills you noted on

your CV to help them with these analyses. You must analyze and report on data for all the regions

(i.e., Central, East, South, West) that received orders from different customer segments including

consumer, corporate, and home office.

These filtered data have been provided to you in the worksheet titled “Sales Data“. Management

is interested in the regions, customer segments, product categories, and subcategories that are most

problematic in terms of profit, shipping, and preparation. Management would also like to know

which product subcategories have the best and worst gross profit margins. You know that using

pivot tables and macros can help you manage the large volume of data you have been given, and

upon which asked to perform. the following specific tasks. For parts 1 to 3, create the appropriate

pivot tables and pivot charts and include your report in the memo file to the management.

Part 1

Task 1.1: The online store is interested in having categorized information about the total sales of

each region from 2013 to 2016 (i.e., sales, region, orderdate_year, and category). Therefore, you

are asked to provide the management with a PivotTable and a PivotChart (line chart) that include

total sales of product categories. Present your work on a separate worksheet labeled “Q1”.

Task 1.2: Include a synopsis of your findings and trends in your memo to management.

Part 2

Task 2.1: Insert five PivotTables to calculate the sum of sales, quantity, and profit of the products,

along with the average discount and preparation time for all the regions. Next, insert three

PivotCharts with the type of “PieChart” for the total of sales, quantity, and profit and also insert

two PivotCharts with the type of “Clustered Bar” for the average discount and preparation time.

Present your work on separate worksheets labeled “Q2.1-Q2.5”. Then, in a different worksheet

entitled “Dashboard” put all the PivotCharts and apply visual filtering for subcategories to create

an interactive dashboard by which you can compare the values in the charts in terms of product

subcategories.

Task 2.2: Try to find important or interesting information for each item (sales, quantity, profit,

discount, and preparation time) in terms of different subcategories in the four regions. Then include

a synopsis of your findings in your memo to the management.

Part 3

Task 3.1: The company wants to evaluate the average gross profit margin for each month and

region from 2013 to 2016. You realize that you can provide management with a pivot table showing

the gross profit margin attributable to the sales. Using this PivotTable, determine which month in

each region for each year has the highest gross profit margin and highlight them with the help of

conditional formatting.

Task 3.2: Provide your interpretation of the results in the memo to management. You may select

either region or year to interpret your findings.

Note: To perform. the tasks of this part, you need to create a new field (i.e., gross profit margin).

Make sure that you create it in your PivotTable and not in the original dataset (i.e., Sales Data

Worksheet). Be sure to label your worksheet “Q3”.