Combining Excel-based sales estimate with Exact Online actuals

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 center 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):

exact-online-excel-table-sample-sales

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 2
         ,       salesperiod number   position 3
         ,       revenue     number   position 4
         ,       returns     number   position 5
         ,       quantity    number   position 6
       )

The estimated sales resembles:

exact-online-excel-table-sales-estimates

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: