Exact Globe Next and PowerPivot
Introduction
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.
Prerequisites
- Microsoft Excel 2010 or higher
- Exact Globe Next product update 407 or higher
Download and install Powerpivot for Microsoft SQL Server 2012
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 template
Download the template which is attached to the bottom of this document and save it on your hard drive.
Connect with your database
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.
Explanation of the reports
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:
| Report Name |
Description |
| Revenue Dashboard |
Revenue Dashboard
Revenue / YTD Revenue
Revenue excluding VAT based on the invoice history.
Invoices / YTD invoices
Number of invoices based on the invoice history.
Invoices per customer / YTD invoices per customer
The number of invoices divided by the number of invoided customers (based on the invoice history).
AVG Invoice Amount / YTD AVG Invoice Amount
Average invoive amount excluding VAT (based on the invoice history). |
| Purchase |
Purchase
Purchase Amount Invoiced
The total amount of the purchase invoices which are entered for purchase orders.
Top 10 Suppliers
The 10 suppliers with the highest amount of purchase invoices which are entered for purchase orders.
Top 10 Items with highest price difference
- The total amount of the purchase invoices which are entered for purchase orders for this item
- The total amount of purchase orders for this item
- A divided by B
Top 10 Items with lowest price difference
- The total amount of the purchase invoices which are entered for purchase orders for this item
- The total amount of purchase orders for this item
- A divided by B
Top 10 Most frequently purchased items
- Te average quantity from the purchase invoices which are entered for purchase orders for this item
- The average invoice amount for this item
- The number of purchase orders
|
| Manufacturing |
Manufacturing
# Production Orders
The number of production orders with a specific status. Split per On-schedule or Overdue (end time of production order < today)
Quantity in Production Orders
The number of make items from the production orders. Split per On-schedule or Overdue (end time of production order < today)
Value of Production Orders
The quantity * de default cost price of the item. Split per On-schedule or Overdue (end time of production order < today)
Machine hours
- Planned Run Time: The number of planned machine hours where the BOM condition is 'Always'.
- Planned Setup Time: The planned setup time where the BOM condition is 'Onz eper production order'.
- Actual Run Time: The actual number of machine hours.
- Actual Setup Time: The actual setup time.
- Planned / LeadTime: The planned machine hours divided by the lead time
Labour hours
- Planned RunTime: The number of planned labour hours
- Planned SetupTime: The planned setup time
- Actual RunTime: The actual number of labour hours
- Actual SetupTime: The actual setup time.
- Planned / LeadTime: (Planned labour hours / total quantty) / lead time labour hours
|
| Stock |
Stock
Stock value
The value of the stock
Top 10 Items moving quicker than Previous Year
- The number of outgoing stock transactions for this item
- The number of outgoing stock transactions for this item last year
- A / B
Top 10 Items moving slower than Previous Year
- The number of outgoing stock transactions for this item
- The number of outgoing stock transactions for this item last year
- A / B
|
| Sales Account Analyze |
Sales Account Analyze
Top 10 Accounts
- Highest invoice amount based on the invoice history (quantity * price from the invoice history lines)
- Invoice history: Invoice quantity * (price from invoice line - standard cost price item)
- Invoice quantity *Price per unit from invoice history * discount from invoice history /100
Bottom 10 Accounts
- Highest invoice amount based on the invoice history (quantity * price from the invoice history lines)
- Invoice history: Invoice quantity * (price from invoice line - standard cost price item)
- Invoice quantity *Price per unit from invoice history * discount from invoice history /100
|
| Sales Employee Analyze |
Sales Employee Analyze
Top 10 Employees
- Highest invoice of the invoices where the employee is linked in the header of the invoice (based on the invoice history)
- Invoice history: Invoice quantity * (price from invoice line - standard cost price item)
- Invoice quantity *Price per unit from invoice history * discount from invoice history /100
Bottom 10 Employees
- Highest invoice of the invoices where the employee is linked in the header of the invoice (based on the invoice history)
- Invoice history: Invoice quantity * (price from invoice line - standard cost price item)
- Invoice quantity *Price per unit from invoice history * discount from invoice history /100
|
| Sales Item Analyze |
Sales Item Analyze
Top 10 Items
- Highest invoice amount based on the invoice history (quantity * price from the invoice history lines)
- Invoice history: Invoice quantity * (price from invoice line - standard cost price item)
- Invoice quantity *Price per unit from invoice history * discount from invoice history /100
Bottom 10 Items
- Highest invoice amount based on the invoice history (quantity * price from the invoice history lines)
- Invoice history: Invoice quantity * (price from invoice line - standard cost price item)
- Invoice quantity *Price per unit from invoice history * discount from invoice history /100
|
| Sales Item Group Analyze |
Sales Item Group Analyze
Top 10 Assortments
- Highest invoice amount based on the invoice history (quantity * price from the invoice history lines)
- Invoice history: Invoice quantity * (price from invoice line - standard cost price item)
- Invoice quantity *Price per unit from invoice history * discount from invoice history /100
Bottom 10 Assortments
- Highest invoice amount based on the invoice history (quantity * price from the invoice history lines)
- Invoice history: Invoice quantity * (price from invoice line - standard cost price item)
- Invoice quantity *Price per unit from invoice history * discount from invoice history /100
|
| Main Category: |
Support Product Know How |
Document Type: |
Support - On-line help |
| Category: |
On-line help files |
Security level: |
All - 0 |
| Sub category: |
Details |
Document ID: |
30.049.359 |
| Assortment: |
Exact Globe
|
Date: |
01-04-2021 |
| Release: |
407 |
Attachment: |
|
| Disclaimer |