Optimize Exact Online supply chain by forecasting on sales orders and future sales from quotations

Exact Online users sometimes have to replenish stock from their supply chain based upon the current inventory levels and the expected outflow caused by sales. Supply chain forecasting in Exact Online itself is only possible on sales orders.

However, the lead time may be such that the future demand must be based upon both open sales orders and future sales orders as indicated by open quotes.

The following query on real-time Exact Online data provides a forecasting method that combines customer demand from both open sales orders as well as quotes. The query returns a detailed overview of the needed quantities per article and orders. The demand pattern is combined using these two types of future demand, which after aligning the data elements are combined using a union all.

The articles on quotes are weighted upon the win probability of the linked opportunity. The articles needed to fulfill Exact Online open sales orders are based upon a win probability of 100%. The query assumes that products are not returned and that substitutes do not exist within the stock.

--
-- Generate forecast for needed article stock based upon
-- open quotes and open sales orders.
--

--
-- Items.
--
create or replace table itm@inmemorystorage
as
select *
from   ItemsIncremental

--
-- Main suppliers.
--
create or replace table sim@inmemorystorage
as
select sim.Item
,      sim.Supplier
,      sim.SupplierCode
,      sim.SupplierDescription
,      sim.SupplierItemCode
from   SupplierItems sim
where  sim.MainSupplier = 'Y'

--
-- All parties: customers, suppliers, etc.
--
create or replace table act@inmemorystorage
as
select *
from   AccountsIncremental

--
-- Generate a list of 
select /*+ result_set_name('pipeline') */ 
       'Quote' type label 'Type'
,      qle.QuotationNumber Number label 'Number'
,      qle.LineNumber
,      itm.Code ItemCode label 'Article Code'
,      itm.Description ItemDescription label 'Article Description'
,      sim.SupplierCode label 'Supplier Code'
,      sim.SupplierDescription label 'Supplier Name'
,      sim.SupplierItemCode label 'Article Code at Supplier'
,      qle.Quantity
,      ose.Code OpportunityStageCode label 'Phase'
,      coalesce(opy.Probability, ose.Probability, 0.5) Probability label 'Win Probability'
,      qle.Quantity * coalesce(opy.Probability, ose.Probability, 0.5) Quantity_weighted label 'Weighted Quantity'
,      qle.DueDate
,      qle.CloseDate
,      qle.QuotationDate label 'Registration Date'
,      qle.ClosingDate
,      actdly.code DeliveryAccountCode label 'Delivery Party Code'
,      actdly.name DeliveryAccountName label 'Delivery Party Name'
,      active.code InvoiceAccountCode label 'Invoice Party Code'
,      active.name InvoiceAccountName label 'Invoice Party Name'
,      actord.code OrderAccountCode label 'Order Part Code'
,      actord.name OrderAccountName label 'Order Part Name'
from   QuotationLinesIncremental qle
left
outer
join   ExactOnlineREST..Opportunities opy
on     opy.Id = qle.Opportunity
left
outer
join   ExactOnlineREST..OpportunityStages ose
on     ose.Id = opy.OpportunityStage
left
outer
join   ITM@InMemoryStorage itm
on     itm.Id = qle.Item
left
outer
join   sim@inmemorystorage sim
on     sim.Item = qle.Item
left
outer
join   act@inmemorystorage actord
on     actord.id = qle.OrderAccount
left
outer
join   act@inmemorystorage actdly
on     actdly.id = qle.DeliveryAccount
left
outer
join   act@inmemorystorage active
on     active.id = qle.InvoiceAccount
where  qle.Status in (20 /* Draft. */, 25 /* Open */, 35 /* Processing */, 40 /* Printed. */)
and    qle.LineNumber != 0
union all
select 'Order' type
,      sle.OrderNumber
,      sle.LineNumber
,      itm.Code ItemCode
,      itm.Description ItemDescription
,      sim.SupplierCode
,      sim.SupplierDescription
,      sim.SupplierItemCode
,      sle.Quantity
,      'ORDER' OpportunityStage
,      1 Probability
,      sle.Quantity * 1
,      null DueDate
,      null CloseDate
,      sle.OrderDate
,      sle.DeliveryDate
,      actdly.code DeliveryAccountCode
,      actdly.name DeliveryAccountName
,      active.code InvoiceAccountCode
,      active.name InvoiceAccountName
,      actord.code OrderAccountCode
,      actord.name OrderAccountName
from   SalesOrderLinesIncremental sle
left
outer
join   ITM@InMemoryStorage itm
on     itm.Id = sle.Item
left
outer
join   sim@inmemorystorage sim
on     sim.Item = sle.Item
left
outer
join   act@inmemorystorage actord
on     actord.id = sle.OrderedBy
left
outer
join   act@inmemorystorage actdly
on     actdly.id = sle.DeliverTo
left
outer
join   act@inmemorystorage active
on     active.id = sle.InvoiceTo
where  sle.Status in (12 /* Open */, 20 /* Partial */)
and    sle.LineNumber != 0
order
by     type
,      Number
,      LineNumber

--
-- Export the detailed logistical forecast as Excel sheet with a named range 'pipeline'
-- for further analysis as a Pivot-table in Excel.
--

local export results as "${system:userdocumentsdirectory}\purchase-order-candidates.xlsx" format xlsx include headers

This query can help you optimize your supply chain, but can also easily be extended by other types of predicted future demand or expected good receipts, such as from returns.

No historical data nor moving average forecasting is used in the calculation; this forecasting method assumes that the win probabilities have been tested to represent actual demand patterns. Other forecasting methods with Exact Online can easily be created by altering the query.

On Get My Report, there is a report to download the typical turnaround times of your articles based upon historical figures. The decision for forecasting method can be made by your analyst.

A similar sample for financial forecasting can be found in the topic Excel liquiditeitsbegroting voor Exact Online (cash flow met consolidatie) (Dutch).