PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within Microsoft Excel. It’s the user-friendly way to perform data analysis using PivotTable and, PivotChart views, and slicers. It’s the fast way to generate rich and interactive analysis tools that look like you’ve spent weeks building. It’s the right way to achieve deeper business insight and shorter decision cycles.
The PowerPivot add-on is a free download from Microsoft and allows users to work with extremely large data sets.
As of product update 407 several reporting views are available in Exact Globe Next. Based on these views, we have built a Manufacturing, Purchase, Revenue and Stock report. In this document you will find information about how to use PowerPivot and our PowerPivot for Excel sheet with some pre-configured charts and tables on it.
Download and install Microsoft SQL Server 2012 PowerPivot for Microsoft Excel 2010. You can download Powerpivot from the Microsoft Website. PowerPivot is not available as a free add-on for MS Excel 2013. In that version it is only available in the licence Office Professional Plus.
Make sure to check the system requirements and install instructions which can be found on the download page for PowerPivot.
Download the template which is attached to the bottom of this document and save it on your hard drive.
Before you can use the template you have to setup the connection to your Exact Globe Next database. This needs to be done by a user with administrator rights.
Open the Microsoft Excel sheet.
Select the PowerPivot menu option and click PowerPivot Window.
The PowerPivot window is opened.
Select the Design tab and click Existing Comnnections.
Select the connection and click Edit.
Enter the details for your connection:
Friendly connection name: Enter a name for the connection
Server name: Enter of select the SQL Server on which the Exact Globe Next database is located.
Database name: Enter or select the Exact Globe database.
Click Test connection to check if you are able to connect to the database.
Click Save.
Click Close.
Select the Home tab in the PowerPivot window.
Select Refresh - Refresh All.
The Data refresh window appears
Click close when refreshing is finished.
Now the Microsoft Excel sheets have to be updated with the data from the PowerPivot sheet.
Go back to Microsoft Excel screen.
Select Data and click Refresh All.
Now all data in the Microsoft Excel sheet is updated with the data of the selected database.
On the bottom of the template you can switch between the different reports by clicking the tab of the report you want to display.
The different reports contain slicers which enable you to filter the reports. e.g. to filter on May 2013 you click '201305' in the YEAR_MONTH slicer. The report is updated and shows only the data which applies to May 2013.Besides the YEAR_MONTH slicer several other slicers are available. Which slicers are available differ per report. To reset the selection of a slicer click on top top right of the slicer.
Below you will find some explanation of the different tabs in the template:
Revenue / YTD RevenueRevenue excluding VAT based on the invoice history.
Invoices / YTD invoicesNumber of invoices based on the invoice history.
Invoices per customer / YTD invoices per customerThe number of invoices divided by the number of invoided customers (based on the invoice history).
AVG Invoice Amount / YTD AVG Invoice AmountAverage invoive amount excluding VAT (based on the invoice history).
Purchase Amount InvoicedThe total amount of the purchase invoices which are entered for purchase orders.
Top 10 SuppliersThe 10 suppliers with the highest amount of purchase invoices which are entered for purchase orders.
Top 10 Items with highest price difference
Top 10 Items with lowest price difference
Top 10 Most frequently purchased items
# Production OrdersThe number of production orders with a specific status. Split per On-schedule or Overdue (end time of production order < today)Quantity in Production OrdersThe number of make items from the production orders. Split per On-schedule or Overdue (end time of production order < today)
Value of Production OrdersThe quantity * de default cost price of the item. Split per On-schedule or Overdue (end time of production order < today)
Machine hours
Labour hours
Stock valueThe value of the stock
Top 10 Items moving quicker than Previous Year
Top 10 Items moving slower than Previous Year
Top 10 Accounts
Bottom 10 Accounts
Top 10 Employees
Bottom 10 Employees
Top 10 Items
Bottom 10 Items
Top 10 Assortments
Bottom 10 Assortments