The Pivot Table is the functionality which as a standard accompanies Microsoft Excel. This is an interactive table which automatically organises data from the Mamut Systems.
If you are not familiar with the Pivot Table we recommend that you read more about how to use these on the Microsoft website: http://office.microsoft.com/en-gb/excel/HP052743561033.aspx (external link).
Terms used in Pivot Tables:
Page field: A field from the data source which has been added to a page area (or filter) in a Pivot table report. For example, in the table below, ‘year’ is a page field. You can use the ‘year’ field to show summarised data for 2004 alone, 2005 alone etc.
Data field: A field from the data source containing values that are to be summarised. For example in the table, ‘Sum of revenue’ is a data field.
Column field: A field from the data source dedicated to one column in the Pivot table report. For example ‘Category’ is a column field.
Item: A sub category of a row, column, or a page field. For example the `category´ field contains the following items: Distributor and Customer.
Row field: A field from the data source which you have dedicated to a row area in a Pivot Table report. For example ‘Product’ is a row field.
Data area: The cells in a Pivot Table report that contain summarised data. For example, the value in cell B27 is the total of all direct sales in the data field.
Selecting figures in the Pivot Table
A number of figures and carriers will be visible in the Pivot table in Excel. You can use the functions in Excel for analysis by creating the tables and graphical presentations you want, and selecting the data carrier and diagrams/graphs to illustrate the figures.
Example:
You can set up an analysis of sales of the most important products distributed by sales person, customer, distributor and supplier (some of the suppliers are also wholesalers), and illustrate these graphically. Most of these figures already appear in the columns of the Excel sheet that has been created.
Add ‘our_ref’ to the carrier, which is sales per sales person, from the PivotTable Field List. You do this by selecting the carrier and clicking Add to and selecting Row area from the drop-down list. Alternatively, you can select ‘our_ref’ in PivotTable Field List and drag it to the row area in the table in the Excel sheet (next to the product row).
You can then use the four best-selling products in the analysis. You will see the sales figures for all the products in the table in the Excel sheet. Select the four best-selling products in the drop-down menu in the Product cell.
Graphical display of figures in the Pivot table
With the Pivot table functions in Microsoft Excel you can also illustrate the figures graphically. A new Excel sheet with a bar chart based on the figures in the table is generated.
For the presentation to be as clear as possible, you can have the products to be analysed separately on the Y axis. You move the product categories by dragging them from the X axis at the bottom to the Y axis on the right. You then move the sales categories (customer, distributor and supplier) to the X axis by dragging them from the right side to the bottom next to the ‘our_ref’ cell.
The bar chart is now ready to be used and can be printed or linked to a PowerPoint presentation. The Sales Manager is now ready for the management meeting.
That was an illustration of just a few of the things that you can do with Mamut Enterprise Business Intelligence and Microsoft Excel. Pivot tables can seem complicated, but once you have mastered the technique, there are countless possibilities. The tables allow you to make all possible category combinations from the key figures in Mamut Business Software, so that you quickly find the connections you are looking for in relation to your business activities.
Feel free to read up on how to use Pivot tables in Excel on the Microsoft web pages, but after a little bit of practice it is simple to master the functions. (This is a good link: http://office.microsoft.com/en-us/assistance/HA010346321033.aspx)
Read more about: