A crystal ball for forecasting cellular sales

Margie van StratenAdrian Mattioli

Sales PersonHigh level sales forecast

Breakdown per storeBreakdown per manufacturer & model

Historically

Led to …Led to …

Historically (Cont.)

Procurement departmentAdding or subtracting margin to ensure “enough” handsets procured

This resulted in …Stock surplusStock shortage

of a particular handset model.

Principle followed

Very basicCollecting dataAnalysing dataReporting upon data

Collecting data

Source dataSource dataSource data

CSVCSV

Oracle

Eppix

PPAS

Access

ExcelExcelExcelExcelExcelExcelExcelExcel

SAS

ManualInput

ISIS

Historical Reports

Stock in channel at

store

Black Box

Sales per channel

Stock in the warehouse

Stock order

Historical Data Extraction

Ad-hoc manual processTime consuming to run (+- 10 hrs) Outdated before completeDuplication of dataManual intervention requiredNeed for daily update

Solution to data & reporting problem

had to be a data warehouse

… a SAS data warehouse

Why a data warehouse?

Single point of “Truth” = SPOTReporting more structuredUpdate data dailyCreate a production environment Reduce the load on production machines

Why SAS?

Extensive in-house SAS experienceEstablished softwareEasy access to data from various engines and sourcesComfortable with productOngoing support available

SAS’ Handset modeling solution

Identified most critical deliverableWhat quantity of which type of handset must be delivered when to whom?

Worked backwardsBrainstorm larger pictureFocused on a “bite size” phased project

Information Architecture

F u tu re In fo m atio n Arch itec tu re to b e C o n s id ered

U N IXS erver

U N IXS erver

S Q L S erver

O R AC L EIS IS

O R AC L EIM P I (E P P IX)

C ontrac tsB illing

C h alleng erD atab ase

S ervice & R epa ir

U N IXS erver

U N IXS erver

V M SS erver

O R AC L E F inanc ia ls

A ccounts In foC ustom er In fo

P roduct In foP roduct T rack ing

IM E I (IB T )

O R AC L E

IM E IT In foP D W arehouse

U N IXD erver

N om inated S erverfor warehouse

O R AC L E

P re P a id cardin fo

AV M S

A c tiva tion In fo

C u rren t In fo m atio n Arch itec tu re a t Issu e

Data Modeling

ID = Status ID

ID = Site ID

ID = Status ID

ID = Customer ID

ID = Parent Segment ID

ID = Segment ID

ID = Employee ID

ID = Position ID

ID = Customer Party ID

ID = Org PositionID

ID = Relationship Role

ID = Organisation Unit ID

ID = Type ID

ID = Child Org Unit IDID = Parent Org Unit ID

ID = Status ID

ID = Item ID

ID = Characteristic ID

ID = Item ID

ID = Category Set ID

Manufacturer ID = Manufacturer ID

ID = Address Role ID

ID = Item ID

Cell Number = Cell Number

Status Code = Status Code

ID = Site ID

ID = Address ID

ID = ID

ID = ID

ID = ID

ID = ID

Forecast Type = Forecast Type

Forecast ID = Forecast ID

ID = Item IDSerial Number = Quantity

Shipment ID = Shipment ID

Shipment Type = Shipment Type

ID = ID

ID = Product IDSerial Number = Serial Number

Activation Code = Activation Code

Type Code = Type Code

Order Number = Order Number

ID = Child Item ID

ID = Parent Item IDItem

ID NUMERICName CHAR(30)Introduction Date DATESales Discon Date DATESupport Discon Date DATEManufacturer ID NUMERIC

Item ComponentParent Item ID CHAR(10)Child Item ID CHAR(10)From Date DATETo Date DATEQuantity Used SMALLINT

ManufacturerManufacturer ID NUMERICName CHAR(30)Comments CHAR(30)

OrderOrder Number CHAR(10)Order Date DATEEntry Date DATEType Code CHAR(3)ID CHAR(10)Desired Date DATE

Order Type

Type Code CHAR(3)Description CHAR(30)

Order Line Item

Order Number CHAR(10)Line Sequence NUMERIC(2)ID CHAR(5)Miscellaneous Item ID CHAR(5)Quantity SMALLINTStatus Code CHAR(10)

Serialised Item

ID CHAR(5)Serial Number CHAR(5)Cell Number NUMERIC(10)

Stock Item Actication

Product ID CHAR(5)

Serial Number CHAR(10)Date DATETime TIMEActivation Code CHAR(3)

Activation TypeActivation Code CHAR(3)Description CHAR(30)

Cell NumberCell Number NUMERIC(10)

Shipment Line ItemShipment ID CHAR(10)Line Sequence SMALLINTItem ID CHAR(5)Quantity INTEGER

ShipmentShipment ID CHAR(10)Estimated Ship Date DATEActual Ship Date DATEEstimated Arr Date DATEActual Arr Date DATEShipment Type CHAR(3)

Shipment TypeShipment Type CHAR(3)Description CHAR(30)

Site

ID NUMERICName CHAR(30)Status ID NUMERICCustomer ID NUMERIC

Customer

ID NUMERICNumber CHAR(10)Name CHAR(30)Status ID NUMERIC

Forecast

Forecast ID CHAR(10)Forecast Date DATEID CHAR(10)Forecast Type CHAR(3)

Forecast Line Item

Forecast ID CHAR(10)

Sequence Number NUMERIC(2)ID CHAR(5)Quantity SMALLINTRequired Date DATE

Forecast TypeForecast Type CHARACTER(3)Description CHAR(30)

AddressID NUMERICAddress 1 CHAR(10)Address 2 CHAR(10)Address 3 CHAR(10)Address 4 CHAR(10)City CHAR(10)Postal Code NUMERIC(4)

Site AddressSite ID NUMERICAddress ID NUMERICOrganisation Unit ID NUMERICFrom Date DATETo Date DATEAddress Role ID NUMERICStatus ID NUMERIC

Address Role

ID NUMERICAddress Role Code CHAR(10)Description CHAR(30)

Order Status

Status Code CHAR(2)Description CHAR(30)

Serialised Item Cell Number

Item ID CHAR(10)Cell Number CHAR(10)Start Date DATEEnd Date DATE

Item Characteristic

Item ID NUMERICCharacteristic ID NUMERICFrom Date DATETo Date DATE

Characteritic

ID NUMERICDescription CHAR(20)

Category SetID NUMERICSet Name CHAR(30)

Item Category SetItem ID NUMERICCategory Set ID NUMERICFrom Date DATETo Date DATE

Site SegmentSegment ID NUMERICSite ID NUMERICFrom Date DATETo Date DATEComment CHAR(30)

StatusID NUMERICDescription CHAR(30)

Organisation PositionID NUMERICName CHAR(%n)Type ID NUMERIC

Organisation Position Type

ID NUMERICDescription CHAR(30)

Oranisation Structure

Parent Org Unit ID NUMERICChild Org Unit ID NUMERICFrom Date DATETo Date DATE

Org Unit Customer Relationship

Org PositionID NUMERICCustomer Party ID NUMERICFrom Date DATETo Date DATERelationship Role NUMERIC

Relationship Type

ID NUMERICDescription CHAR(30)

Employee

ID NUMERICEmp Number CHAR(10)Surname CHAR(30)First Name CHAR(20)Second Name CHAR(20)Third Name CHAR(30)National ID Number NUMERIC(13)

Employee PositionEmployee ID NUMERICPosition ID NUMERICFrom Date DATETo Date DATE

SegmentID NUMERICParent Segment ID NUMERICDescription CHAR(30)

Invoice

Prepaid Handset Analysis

0.00

0.10

0.20

0.30

0.40

0.50

0.60

0.70

0.80

0.90

1.00

A c tua l Hands e ts Pred ic tiv e Model Sa les Fo rec as t

Contract Handset Analysis

0.00

0.10

0.20

0.30

0.40

0.50

0.60

0.70

0.80

0.90

1.00

A c tual Hands ets S ales Forec as t P redic t ive M odel

Hurdles

Large amounts of data to be reported upon

Sceptical project buy-in

Impatient colleagues

Sales forecast input format too rigid

Forecasting hurdle

Excel Forecast Excel Forecast Excel Forecast

Staging

Data WarehouseData Warehouse

Web Data Capturing

MTN Reporting Vision

Summary

Collecting dataCreated SAS data warehouse

Analysing dataSAS Statistical Analysis Tools

Reporting upon dataWeb reporting

Conclusion

Know our product and organization better

Know our supplier better

Know our customer better