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 cash flow prognosis for Exact Online (consolidated across companies).