Data Object Definition

The Sales Forecast or Demand Forecast is a prediction of future sales for a given customer and product (CPC -  Customer Product Combination). It plays an important role in both Sales and Supply Chain for planning and decision-making.


Deployment Status

IN PROGRESS

This data is available in the Data Ocean as of Q4 2023.


Data Model 

Forecast data in the Data Lake is composed of below tables/views:

  1. V_FACT_sales_forecast_raw - This table/view will hold the forecast data in the RAW format as it is extracted from source (SAP-BW)
  2. V_FACT_sales_forecast_current - Data will be enriched from the raw table to calculate the activite_price per CPIC and CPC. This view will show only the latest month forecast details.  
  3. V_FACT_sales_forecast_snapshot - This table/view will capture the state of the forecast data on a daily basis. This snapshot table is mainly useful to track the forecast_price over the time and to refer the price at a specific point in time.  

Project Space: gcp-sqo-data-pricing-d
See how this entity relates to the others in the pricing data model here.


Additional Info & Comments

  • The Sales Forecast is managed in either Dynasis or Picasso, depending on the GBU.
  • Different GBUs manage the sales forecast at different levels. For some GBU's the physical location of the demand is important for production planning so the forecast needs to be managed at shipto level. For others, the physical location is less critical and the forecast can be managed at sold-to level, giving the customer flexibility in the ship-to location. Similarly, the specific packaging format is less relevant in some GBU's and the demand forecast can be managed at a higher level in the product hierarchy, typically at Product level rather than Material level (so without specifying packaging format).
  • Some GBU's maintain both an unconstrained forecast and a constrained forecast. The difference between the two is that the unconstrained forecast reflects the total demand the customer would have if Solvay had capacity available to meet all the customer's needs, while the constrained forecast reflects only the available capacity that has been allocated to the customer. The sales teams are typically responsible for providing an unconstrained forecast, while the supply chain and operations team perform the allocation and constraining to ensure that committed volumes do not exceed available capacity.

Sales forecast in the data lake: 

  • Will be extracted from SAP-BW on a daily basis from the BW query "Sales Forecast Revenue QV_BW_QRY_CPCOPC07_0001". 
  • Key info on COPC07 query  
    • Loading source: In sales forecast query there are two loading sources where the forecast transactional data are populated including DYNASYS and ORDERBOOK. In the context of pricing only DYNASYS is relevant
    • Running period: Is the month the forecast details are reported
    • Forecast month: It is the month in the query when the forecast sales measures are reported. In the context of pricing, the sales forecasts for the next 18 months are required to be taken into account. 
    • Unit of measure: specifies the measurement unit for materials in the query. Currently, the only conversion performed in the data lake is from tons to kilograms for the 'Net price' measure, which is the default unit used in the forecast query to calculate the forecast price (for details of forecast price see below)
    • Shipping plant: Plant from which the material is ship to a customer
    • Manufacturing plant: Last plant in the product/material production process
    • Incoterm: Specifies the transfer responsibility between the supplier and the customer. It has impact on the price. Incoterms
  • Measures available in the data lake based on the forecast query that are important in the context of pricing:
    • Forecast volume: This is the forecast sale quantity. It is directly retrieved from Forecast query.
    • Forecast price: This metric represents the unit price forecast and is determined based on a specific logic developed within the data lake using forecast query data. It's important to note that the calculation of the Forecast Price relies on the prior calculation of the Last Invoice Price.
    • Forecast sales: This is the forecast sale calculated by multiplying the forecast volume with forecast price. (It is important to note that although there exists a measure in BW representing forecast sales, it has NOT been utilized in the context of pricing.)


Data Flow

Loading process: 

  • Data will be refreshed from SAP-BW into data ocean on a daily basis

  • Will extract latest month of transactions on a daily basis and refresh into data ocean

  • Stage table will be partitioned on meta_stg_Insert_date  
  • ODS table will be partitioned on meta_ods_Insert_date 
  • FACT_sales_forecast_raw and FACT_sales_forecast tables will be partitioned on running_dt

               


Refer below data flow diagram for low level data flow design:    

Tables & Attributes

View ID: gcp-sqo-data-pricing-d.DataOcean.V_FACT_sales_forecast_raw

View ID: gcp-sqo-data-pricing-d.DataOcean.V_FACT_sales_forecast_current

View ID: gcp-sqo-data-pricing-d.DataOcean.V_FACT_sales_forecast_snapshot



  • No labels