Explore BrainMass

Basic of Data Analytics in Excel

This content was STOLEN from BrainMass.com - View the original, and get the already-completed solution here!

Assignment #4 -Data Analysis Using Excel Pivot Tables (see attached file for data)

Falls Department Stores, with corporate headquarters in Portland, Oregon, operates department stores in midsize towns in selected northwestern areas. Although the organization maintains a large computer system for its accounting operations, the sales department often downloads data to complete additional analysis of its operations. Daniel Partner, analyst for the corporate sales department, regularly downloads data by territories and product areas including automotive, electronics, garden centers, and sporting goods. He often presents reports based on his analysis of sales by product areas and territory, best and worst performing product group-periods, and total sales for certain regions and product groups. He asks you to help him compile and summarize the data.
Complete the following:
1. Download the Falls Department Stores workbook and save the workbook as YourNameAssignment 4 in your disk.
2. In the Documentation sheet, enter the date and your name then save the worksheet.
3. Switch to Q3 worksheet, create an Excel table. Rename the Table as ProductSales. Format the Sales column in the Currency number format with no decimal places. Save the worksheet.
4. Switch to Q4 worksheet and sort the table in ascending order by Territories, then by Product Group, then by Year, and then by Month. Month should be sorted in Jan, Feb, Mar, ... order, not alphabetically. Save the worksheet.
5. Switch to Q5 worksheet. Display records for Automotive and Electronic products in 2010, excluding sales in Vancouver. Sort this data by Sales in descending order. Add a Total row and calculate the average sales for the filtered data. Change the label in the Total row to Average. Save the worksheet.
6. Switch to Q6 worksheet. Display subtotals for sales (Sum) by Territory. Save the worksheet.
7. Switch to Q7 worksheet. Display total sales for sporting products, in Vancouver, during 2010. Save the worksheet.
8. Switch to Q8 worksheet. Create a PivotTable similar to the one shown in following figure, displaying percentage of sales by Product Group, Territories, and Year. Set grand totals on for columns only. Use a tabular layout, inserting subtotals at the bottom of each Product Group. Rename the worksheet as Q8 Percent of Sales.
9. Switch to Q8 worksheet. Create a PivotChart of Total Sales By Product Group. Insert this chart in a chart sheet. Rename this chart sheet as Sales by Product Group. Create a second PivotChart of total Sales by Territory. Change the chart type to Pie and follow the attached layout. Insert this chart in a chart sheet. Rename this chart sheet as Sales by Territory.
10. Using the following figure as a guide, create a PivotTable to show four calculations: minimum, maximum, average, and total Sales categorized by Territories and Product Group and filtered by Year. Display the results for 2010. Rename the worksheet as Q10 Statistical Summary.
11. Save and close the workbook. Submit the finished workbook into Assignment 4 area. Use "Your_Name_Assignment4" as the title for your submission.

© BrainMass Inc. brainmass.com October 25, 2018, 9:25 am ad1c9bdddf


Solution Preview

I have tried to explain how to perform various tasks in Excel by using a video, which is attached.

I must admit that this is the first time I made a video as I thought that would be the best way to let you know how ...

Solution Summary

This particular assignment was related to the basics of data analytics tools in Excel. It included sorting, subtotals, pivot tables, tables, pivot charts etc.

See Also This Related BrainMass Solution

Determine Mean, Deviation and Create Histogram for Data Analysis


1. Obtain the mean and standard deviation of the provided data. See below.
2. Create a histogram for data frequencies using the Microsoft® Excel® program. The bin range can be left blank.
3. Use the histogram to determine how many points are out of control according to the upper control limit and lower control limit.
4. Quality Textiles International (DATA NEED TO CREATE HISTOGRAM)
Quality Textiles International is an organization that weaves fabric and then prepares the required sheet lengths as desired by customers. They have received recent complaints that the fabric weight, measured in ounces per square inch, is not meeting customer requirements, which are very close to the control limits. Quality Textiles International's quality assurance department checks the fabric weight on regular basis and knows that their process for fabric weight has a mean of 2.73 oz/in2 and a standard deviation of 0.31.

The following table shows 50 fabric weight measurements of fabric samples collected by the quality assurance department:

Observation Fabric weight
1 2.78
2 2.46
3 3.00
4 2.80
5 2.71
6 2.61
7 3.12
8 3.02
9 2.94
10 2.44
11 3.06
12 2.90
13 2.74
14 2.57
15 2.06
16 2.32
17 2.62
18 2.41
19 2.43
20 3.44
21 2.69
22 2.36
23 1.92
24 2.00
25 2.63
26 2.75
27 2.79
28 2.81
29 2.81
30 2.43
31 2.86
32 3.09
33 3.18
34 3.26
35 3.29
36 3.30
37 3.30
38 3.31
39 3.33
40 3.35
41 3.35
42 3.34
43 3.50
44 3.55
45 3.65
46 3.78
47 4.00
48 4.01
49 3.99
50 4.00

Provide a summary of the analysis and what it means.

View Full Posting Details