1

basheddockSoftware and s/w Development

Feb 21, 2014 (3 years and 3 months ago)

160 views

1

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

FINDINGS AND FUTURE AREAS OF
INVESTIGATION (7/25/13)

John Mueller

Intern, Sales Operations

2

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

UPDATES

3

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

The majority of revenue and forecast clutter lies in the Top 15

DA Rank

Group

% Rev

#

Accts

Disty

/ Non
-
Disty

# Forecasts

from Non
-
Disty

Accts

Avg
.
# Forecasts

per

Acct

1
-
15

70%

0 / 15

71 (41 direct, 30 CM/ODM)

~ 4.7

16
-
65

(48 accts)

20%

15 / 33

35

~ 1

66
-
135

(42 accts)

5%

18

/ 13

5 no
fcst
, 6 no GAM reply

15

~ 1

REVENUE % AND NUMBER OF FORECASTS BY CUSTOMER


4 Customers send 7+ files


#3 Cisco

(11 files)


7 direct reports are system
-
generated (some not strictly forecasts); 4 from CMs contain some duplicate information


#7 HPQ

(13 files)


2 contain <5 rows; 1 is very “dirty” (created by hand?); remaining 10 are “clean” (very likely system
-
generated)


#10 ALU

(
16
files)


2 directly from ALU;
remaining 14
from CMs (many system
-
generated
); some include PO data


#11 Dell

(7 files)


Majority of revenue contained in 3 forecasts

4

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

The majority of revenue and forecast clutter lies in the Top 15

DA Rank Group

% Rev

ISR Response Rate and Forecast

Breakdown

1
-
15

70%

72 forecasts (41 direct, 29 CM/ODM, 1
disty
, 1 undetermined)

16
-
65

(excluding #62)

20%

17 accts send direct/CM forecasts (total 19: max 2
fcsts

per acct),

11
disty

accts, 4 send none (
fcst

created manually), 2 pending GAM info

66
-
135

(exclude

multiple)

5%

13 accts send direct/CM forecasts (total 15; 3 from Nokia Siemens),

18
disty

accts, 4

send none (one may be), 9 pending GAM reply, 2 N/A

136+

5%

1 direct
fcsts
, 1 CM
fcst
, 6
disty

accts, 3 send none, 1 N/A / 12 polled accts

REVENUE % AND NUMBER OF FORECASTS BY CUSTOMER


4 Customers send 7+ files


#3 Cisco

(11 files)


7 direct reports are system
-
generated (some not strictly forecasts); 4 from CMs contain some duplicate information


#7 HPQ

(13 files)


2 contain <5 rows; 1 is very “dirty” (created by hand?); remaining 10 are “clean” (very likely system
-
generated)


#10 ALU

(
16
files)


2 directly from ALU;
remaining 14
from CMs (many system
-
generated
); some include PO data


#11 Dell

(7 files)


Majority of revenue contained in 3 forecasts

5

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

Spinnaker: Supply Chain Consulting, used by
Alterra
: spinnakermgmt.com


What
Alterra

is doing


There is no tool that inputs raw Excel
fcsts


Alterra

worked with customers transitioning to “EDI”


Spinnaker Recommendations: Input


EDI


Give
customer
Excel template
, then use scripts to extract data


Low
-
cost data entry team “wouldn’t be a bad idea,” but training and travel time spent is a valid concern


Agreed that input is the primary problem


Spinnaker Recommendations: Output


Agreed that desired outputs are simple enough to create in Excel


Highly recommend a “spreadsheet program that sits on top of a database”


Boardwalk Tech S&OP
:
#1
recommendation, “costs money, but not an absurd amount”


JDA
: they said this suits our needs but would be a waste of resources considering the pending SAP transition


IBM
Cognos

TM1


Regarding
Demantra
: SAP’s SCM Advanced Planning and Optimization (APO) is a direct alternative


http://wiki.sdn.sap.com/wiki/display/SCM/SCM+Advanced+Planning+and+Optimization+(APO)

SPINNAKER TAKEAWAYS

6

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

Only remaining off
-
the
-
shelf data input option


Autonomy findings:


Autonomy
Teleform

is “unstructured data input software,” but focuses on document capture and extraction


Monarch findings:


Critical to keep formatting consistent


Feeding in Excel files as
-
is usually does not work; need to convert to
PRN

(fixed
-
with) file first, then input


Standard “Save As
PRN
” functionality doesn’t work; John has written Excel
-
to
-
PRN

converter from scratch


Loss of all non
-
textual data (i.e. black numbers are PO’s, red numbers are forecast)


Data transformation within Monarch is crude; likely need many external lookups


Date conversion (W22, ~6/19, 2013_02_01_02, etc.)


Bad MPNs (P
-
codes, notes within same cell, etc.)


Can read and scrape EDI feeds


Means we do not have to rely on BRCM IT resources, just need willing customers


Would like to test with a feed we are currently receiving (John will ask Jim Ginn for further info)


Data Pump findings:


Automates Monarch input and checks for errors; logging; automatic data accumulation and dispersal


Not needed if Monarch can be run from command line (received shady answer), certainly needed otherwise


May encounter significant IT problems

DATAWATCH

OFFERINGS: MONARCH, DATA PUMP

7

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

CURRENT OUTLOOK

“Clean”
Forecasts
Other
Forecasts
EDI Feeds
Monarch
(
Data Pump
)
Excel
-
to
-
PRN
Converter
SAP Tool
(
Long Term
)
Excel
PowerPivot
Microsoft
Analysis
Services
Database
8

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

Objective #1

DATA INPUT & TRANSFORMATION

9

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

What is meant by data input & transformation?


What is involved:


Cleansing:

Extracting all useful data from the customer forecast, discarding the rest


Can include basic text replacements


Manual data modifications (by ISR) may occur before (on original
fcst
) or after (on DB data) input process (TBD)


Structuring:

Transforming
(1)

one line item (part) with

multiple dates/
qtys

into
(2)

one part/date/
qty

per line


There is no known commercial utility that accomplishes this





Temporary Storage:

Possible local container (Access DB,
csv

file) before data is processed into EDW/SAP


Serves mainly as a security buffer so input method does not directly connect to central Broadcom systems


Manual data modifications may occur here, ensuring central data is as accurate as possible


What is NOT involved:


Any data summary or analysis


Any program (homegrown or commercial) serving the same function as a B3 utility

BACKGROUND & SCOPE

MPN

JAN 13

FEB 13

MAR 13

BCM1xxxx

3

4

5

MPN

FCST_DT

QTY

BCM1xxxx

2013
-
01
-
01

3

BCM1xxxx

2013
-
02
-
01

4

BCM1xxxx

2013
-
03
-
01

5

(1)

(2)

10

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.


Units


CPN, MPN, Box/Platform
N
umber (includes multiple parts), chipsets


Incomplete/wrong MPNs (
customer thinks
a part has a
BCM

prefix, or AVL not updated by customer)


Some MPN’s have p
-
codes (not recognized by Broadcom systems)


MPN/IPN matching sometimes needs to be manually specified


Time frames


Daily
,
weekly (may use weeks of year),
monthly,
quarterly (may be different from Broadcom quarters)


Dates


Date format (YY/MM/DD, MM/DD/YY, other)


D
ock
dates vs. ship
dates


Formats


Vertical vs. horizontal data


E
mailed forecasts (unconvertible plain text)


Other


On
-
hand inventory adjustments; transit inventory adjustments


System shows demand when CRDs are missed, even though there is backlog to cover in another quarter


Human
-
created forecasts

FORECAST TRANSLATION CHALLENGES

11

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

Better chance of converting entire OEM to B2B if used for portion of business

NUMBER OF FORECASTS BY CUSTOMER (TOP 37)

RANK

ACCOUNT

# F/Cs

RANK

ACCOUNT

# F/Cs

RANK

ACCOUNT

# F/Cs

1

SAMSUNG

4

13

LG

1

26

ERICSSON

2



APPLE

4



NETGEAR

1



SPREADTRUM

?

CISCO

11

HUMAX

2

NOKIA

(Confirmed)

?

ARRIS/MOT

5

BRITISH

SKY

?

NEC

?

HUAWEI

5

AMAZON

2

IBM

2

THOMSON

1

AVAYA

1

D
-
LINK

1

HPQ

13

HTC

1

FUJITSU

?

PACE

2

NINTENDO

1

ARISTA

?

ZTE

2

(1)

SHENZEN G.

1

LENOVO

2

ALU

16

22

FIBERHOME

1

KTOUCH

1

DELL

7

24

ASUS

1

UBEE

1

12

ECHOSTAR

1

25

JUNIPER

5

37

GOOGLE

1



Disty

forecasts



Accounts suspected to use B2B for POs and/or VMI. Represents ~35% of 2013 Broadcom revenue.

12

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

INPUT OPTIONS (BEST TO WORST)

What:

Automated electronic transfer
of
fcst

data

Resources Required:

IT, customer

Scalability:

Infinite, but depends on
IT resources and customer

+
No translation effort, cost

+
No data translation errors


Up
-
front IT investment required;
depends on customer willingness


Customers may agree to B2B but
wait for several quarters


Data in certain
fcsts

may have to
be edited after transfer

Recommendation:

Use where
possible; poll relevant ISRs first to
determine how much data needs
editing

Roll out immediately: poll all accounts

B2B (EDI,
RosettaNet
)

What:

Develop a tool that transforms
forecasts into database
-
ready format

Resources Required:

1 programmer
(temp or perm) reporting to manager

Scalability:

Ensure code is properly
documented for future additions

+
Fast forecast loads

+
Reduced workload for WW ISRs /
BMgrs

to offload analysis portion

+
Possible real
-
time data return


Programmer requires supervision,
clearance to access forecast data


Work required for format changes


May require slight edits before load

Recommendation
:

Use where B2B
not an option (majority of
fcsts
); pay
programmer by goals achieved

Roll out gradually: large accounts first

Standardized Input Tool

(
Usability Study
,
Implementation
)

What:

Low
-
cost team copies data into
Excel template → loaded by macro

Resources Required:

~5 personnel

Scalability:

ISRs must constantly
explain how to read
fcsts
; becomes
better as team learns over time

+
Inexpensive; quickest start time


No business continuity advantage
(1:1 knowledge)


Turnover = knowledge loss


Human error (possibly significant)


Ongoing ISR workload explaining
fcsts

to individual team members;
customer formats change


Language / time zone barriers

Recommendation:

Not advised.
Minimal advantage over ISR data
entry; ISRs may just continue
as
-
is

Low
-
Cost Data Entry

13

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

Objective #2

OUTPUT & ANALYSIS

14

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

SAP group already being investigated by B3 team


Non
-
SAP Utilities


Not listed on SAP.com


SAP compatibility required for consideration; extent varies between utilities, TBD


May be able to access SAP data
directly through
a connector or through EDW (SQL Server)


SAP Utilities


Listed as a product on
SAP.com


Fully guaranteed compatibility with upcoming SAP implementation

TYPES OF UTILITIES UNDER CONSIDERATION

15

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

Currently being investigated


Spinnaker offerings (used by
Alterra
)


May also include input tool; call on Thursday


Microsoft
PowerPivot


Free

addin

for Excel


imports data into separate sheets without row references (like a database)


Import options: SQL Server, Access, SAP, Oracle, Teradata, Sybase, Informix, DB2, Excel/text files


Pivot tables and charts use
PowerPivot

data


Known problems:


Cannot edit data in
PowerPivot

sheet


need to modify original database


PowerPivot

cannot join databases on more than one key (i.e. Customer and IPN, for pricing table)


Joins must be done through a SQL code import or within database before import


For further investigation:


Full capabilities (known abilities: quarterly summary, waterfall charts), automated reporting (likely requires macro)


Demantra


Formerly used by
NetLogic
; basic run rate forecasting capabilities (used for
disty
)


Purchased by Oracle, but does
not require Oracle to
run


No
longer used by NL because pricing model is based
on revenue (negotiable?)


Impermissible per B3 guidelines?

NON
-
SAP TOOLS UNDER CONSIDERATION (1/2)

16

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

Original tools mostly eliminated


Birst


Out
-
of
-
box capabilities explored with garbage data; not a perfect
-
fit solution


Confirmed: skews by ship
-
to location will require custom additions


$30
-
60K / year for 10 users, depending on functionality


SAP connector only in Enterprise Edition (more pricey)


may be able to pull SAP data through EDW (SQL Server)


Recommendation: eliminate; too pricey for post
-
DB analysis tool
(impermissible per B3 guidelines?)


Cloud9 (eliminated)


Requires
Salesforce


Vanguard
Forecast Server
(eliminated)


Direct replacement for 290
-
1 and 205
-
1; does not specialize in desired functionality


A
nalyzes historicals to determine future trends; provides run rate


$48K / year for 5+ users


SAS Forecast Server (eliminated)


Same target market as Vanguard; much more powerful but difficult to set up; too many IT resources


Axonom

Powertrack

(eliminated)


Out
-
of
-
date appearance

NON
-
SAP TOOLS UNDER CONSIDERATION (2/2)

17

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

Under investigation by B3 team


icon
-
SCM


Others?


Ping
B3 team (
Guenter
) to coordinate efforts on forecast analysis
tool

SAP TOOLS UNDER CONSIDERATION

18

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

APPENDIX

19

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

ACTIVE INVESTIGATIONS; TIMELINE

Now


Datawatch
:
Webex

anticipated tomorrow;
loop in Bernard




Soon


If Monarch is incapable of correctly scraping
Excel files, finish
Excel

PRN

utility


Run Monarch on wide variety of forecasts


Future


Ask which OEMs/ODMs will send B2B
fcsts


Data Input
& Transformation

Now


Elevate IT request for local DB (for testing)


Excel
PowerPivot
: give definitive YES or NO
to each output analysis requirement


Spinnaker: conference call on Thursday


Soon


Feed HPN (Stuart Ito’s) forecasts into DB,
run through entire process for final results


Explore Spinnaker capabilities or end
investigation, based on call

Data Output & Analysis

20

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

Survey results of every forecast received from Top 15 accounts (74 total)


Files excluded (12):


3 No Response: file known to exist but ISR has not replied


5 forecasts change format on a regular basis, or will change in the future (2 sent through email)


5 files do not contain forecast data (PO / inventory data, lookup tables, etc.) (1 file overlap with above)


Files investigated (62):


57 should be fully extractable by a well
-
written utility


7 files contain less than 10 line items; ~ 50% could be extracted with very simple rules


Inherently bad values still need to be corrected manually before upload


In
certain cases, the ISR should create lookup Box/CPN/MPN lookup tables
(one
-
time) to
avoid complex rules


4 files may be extractable as
-
is, but would require complex rules (longer to program)


2 files from Apple: possibly could use Jim
Ginn’s

macros and bypass the utility altogether


1 file from HPQ and 1 from ALU: data everywhere in both cases


1 file cannot be used in current form


1 Dell forecast contains MPNs in text boxes placed around the sheet


needs manual intervention

HOMEGROWN TOOL: USABILITY EVALUATION

21

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

Provide goals to temp worker with agreed
-
upon incentives (examples below)


Set baseline goals which
must

be accomplished (examples below)


Website integration


User can upload following file types: .
xls
, .
xlsx
, .
xlsm
, .
csv
, .txt


Save upload configuration for future use


Send output to email server as .
csv

or .
accdb

(TBD)


Data extraction


Pull from editable list of possibilities: MPN, CPN, Project, etc.


User
-
specified values (EC/CM, received date)


Absolute
-
positioned ranges (MPN in Columns A:B, start date in Cell C3)


Relative
-
positioned ranges (QTY in Column E until blank cell in Row 1)


Add range exclusions (skip rows where cell in Column B is blank)


Data manipulation


Substitute values (“dog” for all values “dog ”)


Fill in
blank data
(if
row blank in Column A, copy CPN from above cell)


Add additional goals which enhance functionality


Real
-
time data return and visualization


Data checking (CPN not found in existing lookup table)

HOMEGROWN TOOL: SUGGESTED IMPLEMENTATION STRATEGY

Tool can be used for a
progressively larger set of
forecasts as functionality is
added

22

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

May require custom configuration to achieve desired functionality

BIRST


Features


Automated ETL:
discussed in Appendix; connects to wide variety of databases:


RDBMS (Relational Database Management System): Oracle, DB2,
SQLServer
, MySQL, Sybase,
PostgreSQL


SAP:
ERP (Enterprise Resource
Planning), BW (Business Warehouse) DSO (Data Source Objects)


Salesforce.com; any application supported through
SnapLogic

(

)


Automated data pulls


Data warehousing:
Stores “cleaned” forecast data


OLAP (Online Analytical Processing):
Fast data visualizations without SQL queries


Interactive dashboards (



Real
-
time plots of stored data; ability to zoom in by bucket


Reporting capabilities


Ad hoc query:
multiple users can pull data and generate reports simultaneously


Pixel
-
perfect reports:
online reports are created and edited like Word documents


Automated scheduling and distribution:

reports created and sent automatically


Export options: PDF, PowerPoint, Excel, CSV, etc.


Feedback


Queries seemed fast during use; webpage was not hogging system resources


Pivot tables were easy to assemble

Selected Customers

Citrix

Motorola

Aruba Networks

Sonus

Networks

AT&T
AdWorks

RBC

Kellogg’s

23

Broadcom Proprietary and Confidential. © 2012 Broadcom Corporation. All rights reserved.

Answers will reveal comprehensive implementation time and cost


How many end customers can we convert to B2B? Revenue %?

How many CMs will send us B2B forecasts (and for which ECs)?


B2B: no input work


Some data may require manipulation


From Huawei ISR: “[Bad accuracy] if
our system just simply follow
fcst

via EDI and transfer to sales
fcst
.”


Can/should we modify quantities
after

import into database?



How many of the remainder (will) give us system
-
generated forecasts?


Homegrown utility: no/minimal recurring input work


Prototype developed by John Mueller


Extracts data from several “simple” forecasts based on user
-
specified parameters



How many customers keep changing formats? Revenue %?


Most time
-
consuming group; requires
constant
re
-
parameterizing
of
data input tool


Recommend paste/modification into constant format by ISR, then parsed by tool

ESSENTIAL QUESTIONS