Operational transfer pricing - case studies

Support of custom-built Hyperion Planning operational transfer pricing solution used to calculate year-to-date transfer pricing entries for a multinational technology company.

The Company wanted to replace its quarterly operational transfer pricing Excel model because it was difficult to scale and required significant manual effort to prepare during quarterly close. The Company wished to leverage its investment in Hyperion Planning, but required assistance liaising with the tax department to gather functional requirements and provide testing support. The solution needed to easily scale by automatically sourcing Hyperion Planning data to calculate multiple transaction types, including cost allocation, cost share, return on sales, royalty, and cost plus. 

KPMG supported the design, development, operation, and maintenance of a custom-built Hyperion Planning operational transfer pricing approach by combining Oracle and transfer pricing expertise. Specifically, KPMG efforts created the following benefits: 

• Fact gathering and documentation of transfer pricing functional requirements; 
• Provision of feedback on solution design and development sprints ; 
• Preparation and execution of solution testing; 
• Performance of multiple quarters of parallel runs; 
• Documentation of solution logic and operational procedures; 
• Assistance in operation of solution after go-live; and
• Provision of recommendations for system enhancements after go-live. 

Operational transfer pricing model to perform year-to-date transfer pricing calculations and create legal entity forecast for a multinational medical device company.

The client wanted to consolidate multiple transfer pricing models, reduce risk of errors resulting from manual calculations, and have advanced reporting and scenario-planning capabilities. KPMG was engaged to build a single transfer pricing model with the ability to calculate cost share, cost allocation, limited risk distributor, and cost plus service calculations. 

KPMG delivered a customized, user-friendly transfer pricing model in Power Excel and Power BI with the following benefits: 

• A single model that calculated monthly transfer pricing adjustments, layered transfer pricing into management forecasts, and provided the capability to evaluate multiple scenarios;
• Flexible and scalable model inputs to accommodate changes to company structure and transfer pricing strategy;
• Streamlined import of company financials using Power Query;
• Advanced reporting capabilities using PowerPivot, including the ability to toggle between year-to-date actuals and full-year forecasting and between multiple scenarios;
• Automated journal entry templates;
• Import of model outputs into Power BI to gain insights from data visualizations.

Operational transfer pricing model and legal entity forecasting approach for a multinational pharmaceutical company utilizing Excel, SQL, and Tableau.

The Company required a legal entity forecast that included transfer pricing results to enable the calculation of its quarterly tax provision. The Company struggled with large data volumes from disparate sources and numerous contributors to its transfer pricing process who were spread across the globe. 

KPMG delivered an integrated series of Excel models leveraging a shared SQL database. The models calculated the Company’s transfer pricing and layered it into its legal entity forecast with transaction types including profit split, limited risk distributor, services, and royalties. The benefits of this custom approach included: 

• Automated sourcing and aggregation of millions of records of data from multiple ERP/forecasting systems into a SQL database; 
• Managed secure access provided to over 200 users in more than 50 countries; 
• A single, standardized global model template to replace several manual limited risk distributors excel workbooks; 
• Ability for the corporate tax team to manage transfer pricing rules and automatically update local finance users; 
• Automated entity segmentation by function with flexibility to perform manual overrides;
• Ability to set budgeted transfer prices by SKU and continuously monitor profitability throughout the year; 
• The calculation of transfer pricing-affected legal entity forecasting including automated conversion of management forecast logic; 
• Audit trail workflow capabilities with time-stamped preparer, reviewer, and approval user roles; and
• Dynamic reporting including trend analysis, budget variance analysis with Tableau data 

Operational transfer pricing approach for a Fortune 500 multinational technology company utilizing Excel, Alteryx and Power BI. 

The Company needed to replace its existing transfer pricing tool because it was slow, lacked documentation, and was difficult to reconfigure in response to business changes. The methodology needed to calculate transfer pricing adjustments to target profit at an entity and product level, which would affect its entire complex supply chain.

In anticipation of an upgrade to the Company’s ERP system, the new tool needed to be configured to source data from both the old and new ERP systems.

KPMG delivered a flexible and scalable approach to perform complex transfer pricing logic at a detailed level of granularity, with the following benefits: 

• Significantly reduced end-to-end run time (from >10 hours to <1 hour); 
• Assumptions and transfer pricing logic configurable through Excel to provide transparency and flexibility around previously ‘hardcoded’ logic; 
• Ability to accommodate changes to trading structure and transfer pricing rules; 
• Automated journal entry templates and standardized Excel reporting packages; and
• Advanced reporting and visualizations in Power BI, including trend analysis and a detailed drilldown. 

Ongoing technical and developmental support for implementation of OTP software for a multinational media conglomerate. 

As part of the simultaneous implementation of a new global transfer pricing structure and OTP software, the Company requested assistance with:

• Technical transfer pricing support to the software implementation team; and 
• Developing a methodology to validate the implementation. 

KPMG supported the Company’s OTP software implementation by providing several benefits, including: 

• The design, development, and implementation of a database operational transfer pricing solution to validate the software implementation of the new transfer pricing structure; 
• Testing support through the development and review of new OTP reports; 
• Collaboration with the transfer pricing accounting team and the software development team to select relevant data points from OTP software and other data sources to replicate intermediate allocation steps; and
• Development of Alteryx workflows to produce multiple standalone Excel reports to be shared with stakeholders with filtering and drill-down capabilities to allow users to get insight into the allocation process.

Operational transfer pricing technology implementation utilizing SAP HANA and BPC 10.0 to automate the calculation of year-to-date transfer pricing for a renewable energy multinational. 

The Company’s transfer pricing process was a significant manual effort in data collection and processing that left little time for analysis of results and was not flexible for business expansion. The Company needed to improve the quality and reliability of its process by reducing the manual manipulation of data.

KPMG delivered a solution utilizing SAP HANA and BPC 10.1 to automatically source data and perform complex transfer pricing logic with the following features: 

• Calculation of intercompany services, cost charges and royalties, and limited risk distributors; 
• Sequencing of multiple transfer pricing transactions in the appropriate order; 
• Automation of journal entries; 
• Complete audit trail to trace outputs throughout calculation back to original source data; and
• Archiving function created for the historical data and historical configuration settings.

Support of custom-built Hyperion Planning operational transfer pricing solution used to calculate year-to-date transfer pricing entries for a multinational technology company.

The Company wanted to replace its quarterly operational transfer pricing Excel model because it was difficult to scale and required significant manual effort to prepare during quarterly close. The Company wished to leverage its investment in Hyperion Planning, but required assistance liaising with the tax department to gather functional requirements and provide testing support. The solution needed to easily scale by automatically sourcing Hyperion Planning data to calculate multiple transaction types, including cost allocation, cost share, return on sales, royalty, and cost plus. 

KPMG supported the design, development, operation, and maintenance of a custom-built Hyperion Planning operational transfer pricing approach by combining Oracle and transfer pricing expertise. Specifically, KPMG efforts created the following benefits: 

• Fact gathering and documentation of transfer pricing functional requirements; 
• Provision of feedback on solution design and development sprints ; 
• Preparation and execution of solution testing; 
• Performance of multiple quarters of parallel runs; 
• Documentation of solution logic and operational procedures; 
• Assistance in operation of solution after go-live; and
• Provision of recommendations for system enhancements after go-live.