Chapter 2. Pivot Tables and Problem Solving


Business data analysis is a search for relationships. How does advertising impact sales? Does an increase in returns mean we have a quality problem? Whether we are looking for problems or identifying best practices, it all comes down to relationships. Business analysis requires a complex, multidimensional approach. Businesses capture and store large amounts of data. As companies try to become more efficient, the job of sifting through this data looking for valuable insight is becoming more common and important.

Excel is ideal for this kind of work. It can import data from most databases, it can handle almost any statistical or formatting problem, and it has a great pivot table feature. Pivot tables were designed for researching relationships in data. They allow us to try different combinations by dragging and dropping, making it easy to check a large number of relationships quickly. They create interactive tables and charts and can quickly filter the data or change point of view.

In this chapter we use pivot tables to analyze a business process. This is an ad hoc activity and the end product is information, not an application. So, formatting and appearance are not the main concerns. The real goal is to find specific problems or opportunities.

We work with two kinds of data. First, there are categorical items. These elements separate data into well-defined groups. If you look at a customer file, one of the fields might be the customer's ZIP code. It is a number, but it has no real numeric value. Its only purpose is to identify a group or category of customers.

The second type is scalar items. Scalars have a numeric value, like cost or square feet. They can be added up or averaged. In pivot tables we use categoricals and scalars differently.

The sample data for this chapter consists of 10,000 rows of data from an order processing operation. The layout is shown in Figure 2-1.

Figure 2-1. Order data


It takes too long to handle orders, and we want to find specific problem areas. We also want to find examples of good performance to establish best practices. Pivot tables will provide an excellent tool for exploring this kind of data.



Analyzing Business Data with Excel
Analyzing Business Data with Excel
ISBN: 0596100736
EAN: 2147483647
Year: 2006
Pages: 101
Authors: Gerald Knight

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net