Delta 1 products can still present a data challenge due to the number of rules involved
Inconsistent, manually adjusted P&L process
A global soft commodity company was calculating its P&L and P&L explain in excel across multiple locations. It needed a standard process that worked with and accepted the data nuances across all centres, complicated by a mix of trading and physical processing.
CPRA designed and implemented a full reval P&L explain with 200+ single and pairwise components for volume, plan and market data variations. Results were displayed in a dynamic dashboard.
Average 99+% P&L explain.
Excel P&L Process
Excel is great. It’s flexible, powerful and quick. In general, it’s the number one choice for ad hoc analysis.
But it’s not so good at connecting to data sources, or for large amounts of data or repeatable workflow.
And the latter is where we see it so often because it is so flexible and quick to react to the nuances and variations in data.
A full revaluation P&L attribution is conceptually simple. Isolate the drivers and re run the P&L calculation by varying the drivers one at a time.
The practical difficulties often arise in getting all the requisite data from different sources and running all the scenarios whilst isolating second order effects. Often, the underlying P&L reporting process is not fully automated or may not allow for multiple scenario data to be generated and then run through it.
So parallel excel spreadsheets are made.
Building a better P&L process
An automated process had to have a standard set of rules. Working with finance to identify some of the inconsistencies, reduce redundancies and duplication and streamline the logical processes was key.
Mark to observable market calculations were streamlined, formulae were made consistent for the different pricing combinations and the number of intermediate calculations was reduced.
P&L explain is simply a re-run of the P&L calculation but with carefully controlled variations of input data. Our modular data analytical tool can easily share developed logic across multiple processes.
- Spreadsheets had evolved in different directions to take into account local data nuances and trading conventions
- FIFO waterfall calculation was required to allocate contracts to physical forecasts so variations in scheduling, volumes or planning would change P&L due to phasing
- The P&L model meant that data variations could have upstream and downstream effects
- Management overlay rules created additional second order effects
- Fifty to sixty single and pairwise scenarios would typically be required to explain the P&L
The existing output was in excel and static ppt slides which have limited flexibility. Historical trend data was also not available.
The client chose to use Tableau as its data visualisation tool so our P&L process outputted data in the optimal format for Tableau.
CPRA’s finance expertise was used to design and develop the dashboards for subsequent review by the client: it was faster than asking users to provide the design ideas from scratch.
The detail: what does our P&L process do?
- Location variables
- Starting stock
- Yield assumptions
- Conversion costs
- Forecast processing
- Logistics and grade premiums
- Market variables
- Exchange prices
- Conversion factors
- Contract data
- Cost price
- Ship to/from port
- Processing waterfall of inventory and physical contract delivery
- Contract allocation to waterfall and prices
- Carry costs based on contract delivery schedule
- Contract cost calculation
- Isolating changes in plan from changes in supply and scheduling of such supply
- Application of custom limitations of physical processing
- Diversion of physical to cash settlement and re-calculation of logistics costs
- Processing P&L
- KPIs eg P&L:sell side contract ratio
- Contract analysis and breakdown into new, expired, amended and unchanged contracts
- Input variable analysis and delta calculated
- Full revaluation P&L attribution for each scenario
Accuracy and Precision
Increased accuracy of contract usage, contract costs and carry calculations
Systematic breakdown of P&L drivers into components rather than aggregation of user defined sceanrios
P&L Average explain of 99+%
Audit and Stability
Automated, consistent P&L process, eliminating 150+ MB spreadsheets
Audit trail from new protected data environment
Open source database used to provide a more stable data environment
Signed off logic controlled and protected by IT
Elimination of manual untraceable adjustments
Access to fast, consistent and complex calculation that existing tools could not do
Dynamic dashboard including historical trends
Ability to run and track multiple data versions