PlutoCon2021

xxxxxxxxxx

7.6 μs

xxxxxxxxxx

112 s

xxxxxxxxxx

7.7 ms

Introduction

This Pluto notebook, is meant for ERP consultants, IT Developers, Finance, Supply chain, HR & CRM managers, executive leaders or anyone curious to implement data science concepts in ERP space.

Author: Amit Shukla
Publish Date: Apr 02, 2021
https://github.com/AmitXShukla

xxxxxxxxxx

5.7 μs

About ERP Systems, General Ledger & Supply chain

A typical ERP system consists of many modules based on business domain, functions and operations. GL is core of Finance and Supply chain domains and Buy to Pay, Order to Cash deal with different aspects of business operations in an Organization. Many organization, use ERPs in different ways and may chose to implement all or some of the modules.

You can find examples of module specific business operations/processes diagram here.

A typical ERP modules list looks like below diagram.

xxxxxxxxxx

22.7 μs

PlutoCon2021

xxxxxxxxxx

4.7 μs

Current Solutions

Big Organizations have been using big ERP systems like SAP, Oracle, PeopleSoft, Coupa, Workday etc. systems over few decades now and Recent popularity of softwares like Quickbooks, NetSuite, Tally in medium, small organizations are proof that ERP are the way to manage any business successfully.

Finance analysts, supply chain managers heavily rely on using Business Intelligence tools like Microsoft Excel, Microsoft Power BI, Tableau, Oracle Analytics, Google Analytics, IBM Cognos, Business Objects etc.

These BI tools provide a self-service reporting for analytics and often are used for managing daily ad-hoc reporting and anlysis.

xxxxxxxxxx

11.0 μs

Problem Statement

"Read, Write and Understand" data are three aspects of any ERP system. While big and small ERPs master "write aspect" of ERP, there is lot needs to be done on "read & understand" data.

I would rather not waste your time talking about how one BI Tools compare with Pluto or others,

instead, in this notebook, I will show some sample reports I built in Pluto last year for Pandemic reporting, and then let Analysts decide, if They would have rather used Traditional BI reportings tools to build these reports.

Point is, How easily, Pluto can create real time ad-hoc, Reactive dashboard analytics to support critical business operations.

xxxxxxxxxx

12.3 μs

understanding Finance, Supply chain data

A typical Finance statement look like this. click here

below are sample data sets,

Accounts, Dept (or Cost Center), Location, and Finance Ledger may look like

Accounts Dimension

xxxxxxxxxx

10.2 μs

	AS_OF_DATE	ID	CLASSIFICATION	CATEGORY	STATUS	DESCR	ACCOUNT_TYPE
	Date	Int64	String	String	String	String	String
1	1900-01-01	11000	"OPERATING_EXPENSES"	"Travel"	"A"	"operating expenses"	"E"
2	1900-01-01	16000	"NON-OPERATING_EXPENSES"	"Facility"	"A"	"non-operating expenses"	"E"
3	1900-01-01	21000	"ASSETS"	"Depreciation"	"A"	"assets"	"A"
4	1900-01-01	26000	"LIABILITIES"	"Receipt"	"A"	"liability"	"L"
5	1900-01-01	31000	"NET_WORTH"	"Cash"	"A"	"net-worth"	"N"
6	1900-01-01	36000	"STATISTICS"	"FTE"	"A"	"stats"	"S"
7	1900-01-01	41000	"REVENUE"	"Sales"	"A"	"revenue"	"R"

xxxxxxxxxx

1.6 s

accounts_size

xxxxxxxxxx

2.4 μs

Department Dimension

xxxxxxxxxx

3.5 μs

	AS_OF_DATE	ID	CLASSIFICATION	CATEGORY	STATUS	DESCR	DEPT_TYPE
	Date	Int64	String	String	String	String	String
1	2000-01-01	1100	"SALES"	"sales"	"A"	"Sales & Marketing"	"S"
2	2000-01-01	1200	"HR"	"human_resource"	"A"	"Human Resource"	"H"
3	2000-01-01	1300	"IT"	"IT_Staff"	"A"	"Infomration Technology"	"I"
4	2000-01-01	1400	"BUSINESS"	"business"	"A"	"Business leaders"	"B"
5	2000-01-01	1500	"OTHERS"	"others"	"A"	"other temp"	"O"

xxxxxxxxxx

65.3 ms

dept_size

xxxxxxxxxx

1.5 μs

Location Dimension

xxxxxxxxxx

3.5 μs

	AS_OF_DATE	ID	CLASSIFICATION	CATEGORY	STATUS	DESCR	LOCA_TYPE
	Date	Int64	String	String	String	String	String
1	2000-01-01	11	"Region A"	"Region A"	"A"	"Boston"	"Physical"
2	2000-01-01	12	"Region A"	"Region A"	"A"	"New York"	"Physical"
3	2000-01-01	13	"Region A"	"Region A"	"A"	"Philadelphia"	"Physical"
4	2000-01-01	14	"Region A"	"Region A"	"A"	"Cleveland"	"Physical"
5	2000-01-01	15	"Region B"	"Region B"	"A"	"Richmond"	"Physical"
6	2000-01-01	16	"Region B"	"Region B"	"A"	"Atlanta"	"Physical"
7	2000-01-01	17	"Region B"	"Region B"	"A"	"Chicago"	"Physical"
8	2000-01-01	18	"Region B"	"Region B"	"A"	"St. Louis"	"Physical"
9	2000-01-01	19	"Region C"	"Region C"	"A"	"Minneapolis"	"Physical"
10	2000-01-01	20	"Region C"	"Region C"	"A"	"Kansas City"	"Physical"
11	2000-01-01	21	"Region C"	"Region C"	"A"	"Dallas"	"Physical"
12	2000-01-01	22	"Region C"	"Region C"	"A"	"San Francisco"	"Physical"

xxxxxxxxxx

119 ms

location_size

xxxxxxxxxx

1.5 μs

Dimesions visuals

xxxxxxxxxx

3.3 μs

xxxxxxxxxx

16.3 s

Using Pluto for Finance & SCM analytics

xxxxxxxxxx

3.6 μs

Finance Ledger

below is sample Finance Ledger Data

xxxxxxxxxx

4.9 μs

	LEDGER	FISCAL_YEAR	PERIOD	ORGID	OPER_UNIT	ACCOUNT	DEPT	LOCATION	POSTED_TOTAL
	String	Int64	Int64	String	String	Int64	Int64	Int64	Float64
1	"Actuals"	2020	1	"ABC Inc."	"Region B"	18000	1200	20	5.36743e7
2	"Actuals"	2020	1	"ABC Inc."	"Region A"	38000	1500	21	7.31121e7
3	"Actuals"	2020	1	"ABC Inc."	"Region C"	28000	1500	17	9.59382e7
4	"Actuals"	2020	1	"ABC Inc."	"Region C"	44000	1100	14	4.13784e7
5	"Actuals"	2020	1	"ABC Inc."	"Region B"	29000	1200	14	5.10691e7
6	"Actuals"	2020	1	"ABC Inc."	"Region C"	28000	1300	20	8.54745e7
7	"Actuals"	2020	1	"ABC Inc."	"Region B"	37000	1400	20	3.86977e7
8	"Actuals"	2020	1	"ABC Inc."	"Region B"	41000	1100	19	4.44001e7
9	"Actuals"	2020	1	"ABC Inc."	"Region B"	37000	1400	15	9.25009e6
10	"Actuals"	2020	1	"ABC Inc."	"Region C"	45000	1400	12	3.49491e7
more
2800000	"Budget"	2021	12	"ABC Inc."	"Region C"	19000	1500	13	1.604e7

xxxxxxxxxx

7.9 s

ledger_size

xxxxxxxxxx

2.1 μs

GL BalanceSheet, IncomeStatement & CashFlow

xxxxxxxxxx

3.3 μs

"df_dimensions_size"

xxxxxxxxxx

66.3 μs

"df_ledger_size"

xxxxxxxxxx

9.4 s

Balance Sheet

xxxxxxxxxx

3.4 μs

xxxxxxxxxx

132 ms

xxxxxxxxxx

40.1 μs

2020

xxxxxxxxxx

33.6 ms

xxxxxxxxxx

56.0 μs

	FY	Qtr	Org	Accounts	Dept	Loc	TOTAL
	Int64	Int64	String	String	String	String	String
1	2020	1	"Region A"	"STATISTICS"	"OTHERS"	"Dallas"	"USD 13943.87m"
2	2020	1	"Region A"	"ASSETS"	"SALES"	"Richmond"	"USD 11199.75m"
3	2020	1	"Region A"	"NON-OPERATING_EXPENSES"	"IT"	"Philadelphia"	"USD 12035.71m"
4	2020	1	"Region A"	"OPERATING_EXPENSES"	"BUSINESS"	"Richmond"	"USD 13961.85m"
5	2020	1	"Region A"	"STATISTICS"	"BUSINESS"	"Chicago"	"USD 11931.71m"
6	2020	1	"Region A"	"NET_WORTH"	"SALES"	"New York"	"USD 13371.4m"
7	2020	1	"Region A"	"NON-OPERATING_EXPENSES"	"IT"	"Dallas"	"USD 11700.1m"
8	2020	1	"Region A"	"REVENUE"	"HR"	"Dallas"	"USD 12615.51m"
9	2020	1	"Region A"	"STATISTICS"	"BUSINESS"	"Minneapolis"	"USD 13011.79m"
10	2020	1	"Region A"	"NON-OPERATING_EXPENSES"	"OTHERS"	"Cleveland"	"USD 13264.78m"
more
420	2020	1	"Region A"	"NON-OPERATING_EXPENSES"	"OTHERS"	"Boston"	"USD 12112.76m"

xxxxxxxxxx

2.3 s

Income Statement

xxxxxxxxxx

3.3 μs

	FY	Qtr	Org	Accounts	TOTAL
	Int64	Int64	String	String	String
1	2020	1	"Region A"	"ASSETS"	"USD 11199.75m"
2	2020	1	"Region A"	"NET_WORTH"	"USD 13371.4m"
3	2020	1	"Region A"	"REVENUE"	"USD 12615.51m"
4	2020	1	"Region A"	"LIABILITIES"	"USD 13514.18m"
5	2020	1	"Region A"	"LIABILITIES"	"USD 12500.55m"
6	2020	1	"Region A"	"NET_WORTH"	"USD 11366.54m"
7	2020	1	"Region A"	"REVENUE"	"USD 11713.12m"
8	2020	1	"Region A"	"REVENUE"	"USD 12301.52m"
9	2020	1	"Region A"	"NET_WORTH"	"USD 11704.11m"
10	2020	1	"Region A"	"LIABILITIES"	"USD 12379.11m"
more
240	2020	1	"Region A"	"REVENUE"	"USD 11841.43m"

xxxxxxxxxx

290 ms

Cash Flow Statement

xxxxxxxxxx

3.3 μs

	FY	Qtr	Org	Accounts	TOTAL
	Int64	Int64	String	String	String
1	2020	1	"Region A"	"NON-OPERATING_EXPENSES"	"USD 12035.71m"
2	2020	1	"Region A"	"OPERATING_EXPENSES"	"USD 13961.85m"
3	2020	1	"Region A"	"NON-OPERATING_EXPENSES"	"USD 11700.1m"
4	2020	1	"Region A"	"NON-OPERATING_EXPENSES"	"USD 13264.78m"
5	2020	1	"Region A"	"OPERATING_EXPENSES"	"USD 10289.98m"
6	2020	1	"Region A"	"NON-OPERATING_EXPENSES"	"USD 11969.24m"
7	2020	1	"Region A"	"NON-OPERATING_EXPENSES"	"USD 11660.73m"
8	2020	1	"Region A"	"NON-OPERATING_EXPENSES"	"USD 12733.28m"
9	2020	1	"Region A"	"NON-OPERATING_EXPENSES"	"USD 11367.25m"
10	2020	1	"Region A"	"NON-OPERATING_EXPENSES"	"USD 11325.82m"
more
120	2020	1	"Region A"	"NON-OPERATING_EXPENSES"	"USD 12112.76m"

xxxxxxxxxx

662 μs

Ledger Visual

xxxxxxxxxx

3.3 μs

xxxxxxxxxx

35.6 μs

2021

xxxxxxxxxx

41.3 μs

xxxxxxxxxx

27.7 μs

xxxxxxxxxx

2.3 ms

xxxxxxxxxx

30.3 μs

xxxxxxxxxx

26.8 μs

xxxxxxxxxx

1.4 s

Actuals vs Budget comparison

xxxxxxxxxx

3.3 μs

xxxxxxxxxx

718 ms

raw data in table format

xxxxxxxxxx

3.5 μs

	LEDGER	FISCAL_YEAR	QTR	OPER_UNIT	ACCOUNTS_CLASSIFICATION	DEPT_CLASSIFICATION	LOCATION_DESCR	TOTAL
	String	Int64	Int64	String	String	String	String	Float64
1	"Actuals"	2021	1	"Region A"	"OPERATING_EXPENSES"	"SALES"	"Boston"	1.15107e10
2	"Actuals"	2021	2	"Region A"	"OPERATING_EXPENSES"	"SALES"	"Boston"	3.5665e9

xxxxxxxxxx

100 ns

xxxxxxxxxx

1.3 s

xxxxxxxxxx

214 ms

what-if, would, could, should

Region A is merged with Region B
Employee resume work from office, how much Travel amounts % will increase.
% of Office supply expenses given to Employee as home office setup

would Region A, Cash Flow Investment have returned 7% ROI
would Region B received Government/investor funding

could have increased IT operating expenses by 5%
could have reduced HR temp staff

should have paid vendor invoiced on time to recive rebate
should have applied loan to increase production
should have retired a particular Asset

xxxxxxxxxx

4.4 μs

Real-time TimeSeries, StatsModel predictions

Predict Operating and non-operating expense for year
Predict Actuals to Budget variance and FORECAST
using SARIMA model to predict "Region A" NET-WORTH

xxxxxxxxxx

3.9 μs

Supply chain Dashboard - live inventory

below is an example dashboard (image) built in Pluto
This dashboard uses OnlineStats.jl for "real-time" udpates

Supply Chain Dashboard

xxxxxxxxxx

6.1 μs

Feature Requests

Pluto as an Enterprise Reproting tool.

Pluto provides a cohesive real-time, reactive data wrangling, tranformation, reporting & analytics framework for big data /ERP data sets.

Cloud/on-Premise Server deployment
PIN - live KPI Reports like TOC (Floating fluid content)
Integarete pluto with BI tools like Microsoft Power BI, Tableau etc.
Drill-through, Drill-down functionalities
linking variables for easy navigation

xxxxxxxxxx

5.7 μs

contact information

contact: amit@elishconsulting.com
https://github.com/AmitXShukla

xxxxxxxxxx

4.1 μs