Summary
This note illustrates combining Excel-based sales estimates with sales actuals taken from two Exact Online companies using Invantive SQL. It can be deployed on any Invantive SQL platform such as Excel, web application or iPhone.
Exact Online ERP and Invantive SQL
Exact Online is a standard ERP solution with reasonable budget pricing. For a fixed fee, it supports accountants and entrepreneurs in areas of trade, logistics, manufacturing, projects and accounting. However, the downside is that it is a one-size-fits-all solution. Whereas premium products such as Salesforce allow a user to in principle indefinitely extend the base functionality with data storage and business logic, Exact Online enforces a standard solution on your business.
The Exact Online App Store apps.exactonline.com provides hundreds of add-on and add-in products that extend the functionality both in terms of business logic as well as data storage. However, ISVs will only develop a standardized add-on at a budget price when they think the market demand in terms of volume sales warrants their investment. For system integrations however the number of combinations for industry, related software product and market is huge.
To really bring Exact Online to life, there is a huge demand for one-of-a-kind solutions. This is one of the most used types of application of Invantive SQL, since Invantive SQL makes it easy to create a reliable and cost-effective integration between Exact Online and other systems for one or thousand Exact Online companies of a subscription.
This post demonstrates both reporting numbers from Exact Online combined with Excel-based data.
Of course, you could just as easily upload the Excel-based estimated sales into budgets with Invantive SQL and every month replace them with new estimated sales for the next periods.
Other Types of Use
Another most used type of application of Invantive SQL with Exact Online is data replication for accountants using Invantive Data Replicator. Although there are more and more similar products such as Excel Controller Cloud and QICS, Invantive SQL is the sole product in the market that combines over 50 (mostly European-based such as Loket, NMBRS, Teamleader and audit files) platforms, high performance in terms of memory and throughput with the capability to replicate thousands of companies with little maintenance effort even during data model changes. It is combined with over 700 XML and REST based Exact Online APIs and adherence to compliance and regulatory rules.
Excel-based Sales Data
In this case a user is confronted with the fact that his sales team maintains their expected sales volumes in a list of Excel sheets on Dropbox, with one Excel sheet per sales representative. However, the historical expected sales volumes must be matched against actual sales registered as sales invoices in Exact Online.
Each Excel sheet has the following structure (we will leave the sales rep out for privacy reasons):
The table with data is identified using an Excel named range ‘salesdata’, but could have been an Excel table or complete worksheet too:
Exact Online Sales Actuals
The actual data is distributed across two Exact Online company, since the South region is handled by a subsidiary. However, this subsidiary also sells to accounts in other regions.
Both companies share the products, such as:
Accounts have been assigned a region using account classification, but the classifications are different per Exact Online company: in the South division classification 3 is used for region where as the other one uses classification 1:
Sales invoices look like this:
Report
The following SQL statements collects the data from the two Exact Online companies and combines them with the manually maintained expected sales data. The SQL statements can be run by hand using a tool like Invantive Query Tool, but also easily integrated into a web application using Get My Report or Invantive Data Access Point, combined with a user interface in Invantive Business, processed in Microsoft Excel or Word using Invantive Office or automatically mailed or loaded into a enterprise warehouse using Invantive Data Hub.
The statements can be run using F5 in the Query Tool, but a stored procedure in Invantive PSQL can be created and stored too to run it with just a single statement.
In the first step we select the two Exact Online companies whose actuals are to be included:
--
-- Select the two Exact Online companies:
--
-- 102673: South region
-- 760970: North, East and West
--
use 102673,760970
In the next step we collect the actual sales invoice data across the two Exact Online companies. The data is stored into an in-memory table as an intermediate step to make it easier to chop up this explanation into smaller pieces.
Note the use of the ‘label’ keyword; it is used to enrich the metadata of a column in this case with specific instructions how to render data. Many Exact Online columns have already associated data like on Quantity, but the Quantity here actually represents ‘Actual Sales’ since only printed invoices are included.
Also, the financial periods of the General Ledger are used to calculate the Sales Period and a compensation is included for the different setup in the two companies regarding accounting classifications.
create or replace table salesinvoicelines@inmemorystorage
as
select sil.divisionlabel
, sie.invoicenumber
, sil.linenumber
, sil.itemcode
, sil.itemdescription
, sil.quantity label 'Actual Sales (units)'
, sil.amountdc label 'Actual Sales (EUR)'
, fpd.finperiod salesperiod label 'Sales Period'
, coalesce(acn.Code, 'Unclassified') region label 'Invoice Account Region'
from exactonlinerest..salesinvoicelines sil
--
-- Relate to sales invoice to find the account that pays the bill.
--
join exactonlinerest..salesinvoices sie
on sie.division = sil.division
and sie.invoiceid = sil.invoiceid
and sie.status = 50 /* Only processed invoices. */
--
-- Financial period can be determined in various ways.
-- You can join in the transaction lines created and use the
-- period from there. However, in this case we need
-- the sales period instead of the general ledger financial period.
--
join FinancialPeriods fpd
on fpd.division = sie.division
and fpd.startdate <= sie.invoicedate
and fpd.enddate > sie.invoicedate
--
-- Relate to the invoice account to find the
-- account classification with the sales region.
--
join exactonlinerest..accounts active
on active.division = sie.division
and active.id = sie.invoiceto
--
-- Relate to the classification with name 'Region'
-- of the invoice account. Some accounts might not have
-- a region but it is better to list their actual sales
-- under an unclassified region. Therefor we need a
-- 'left outer join' instead of a join.
left
outer
join exactonlinerest..accountclassifications acn
on acn.division = sie.division
and acn.accountclassificationnamedescription = 'Region'
--
-- The account classification structure has been set up
-- differently for the South company and the other company.
-- Compensate that.
--
and acn.id = case
when sil.division = 760970
then active.classification1
else active.classification3
end
--
-- Only include a limited number of products
-- for which sales targets have been agreed upon.
--
where sil.itemcode in ('Football', 'Pingpong')
The contents of the in-memory table with Exact Online actuals are similar to the following:
The sales estimates are extracted from one or more Excel sheets using the ‘exceltable’ statement. The Excel columns are mapped to SQL columns using their relative column number to the named range top-left corner:
create or replace table salesestimates@inmemorystorage
as
select *
from exceltable
( --
-- Extract data from the named range 'salesdata'.
--
name 'salesdata'
--
-- In this case from one Excel sheet.
-- Use 'from files('c:\dropbox', 'sales2018*.xlsx', false)@os fle'
-- and 'passing file fle.file_path'
-- to process all files named 'sales2018*.xlsx' in the folder 'c:\dropbox'.
--
passing file 'c:\dropbox\sales.xlsx'
columns region varchar2 position 1
, itemcode varchar2 position next
, salesperiod number position next
, revenue number position next
, returns number position next
, quantity number position next
)
Note that position next is only available on Invantive SQL releases after 20.1.417.
The estimated sales resembles:
In the last step we select sales periods 1 and 2 from both sales actuals and estimates and aggregate them per item, region and sales period.
This is a great sample of when to use a ‘full outer join’: Pingpong balls were sold in the West region, but not estimated, and no footballs were sold in West contrary to the estimate. Both types of cases should be included in the actuals versus estimates.
select dtl.itemcode
, dtl.region
, dtl.salesperiod
, sum(dtl.estimated_sales) label 'Estimated Sales (EUR)'
, sum(dtl.actual_sales)
, sum(dtl.estimated_quantity) label 'Estimated Quantity (EUR)'
, sum(dtl.actual_quantity)
from ( select 1 salesperiod label 'Sales Period' union select 2 ) periodstoinclude
join ( select coalesce(atl.itemcode, est.itemcode) itemcode label 'Item Code'
, coalesce(atl.region, est.region) region label 'Region'
, coalesce(atl.salesperiod, est.salesperiod) salesperiod label 'Sales Period'
, est.revenue estimated_sales
, atl.amountdc actual_sales
, est.quantity estimated_quantity
, atl.quantity actual_quantity
from salesinvoicelines@inmemorystorage atl
full
outer
join salesestimates@inmemorystorage est
on est.itemcode = atl.itemcode
and est.region = atl.region
and est.salesperiod = atl.salesperiod
) dtl
on dtl.salesperiod = periodstoinclude.salesperiod
group
by dtl.itemcode
, dtl.region
, dtl.salesperiod
order
by dtl.itemcode
, dtl.region
, dtl.salesperiod
The output resembles:
