SAP, Microsoft, Oracle, ERP & Excel CAPEX Optimization: From Corporate Data to Mathematical Capital Allocation

SAP, Microsoft Dynamics, Oracle, ERP systems, and Excel often contain essential data for CAPEX planning and investment planning. However, the critical portfolio question goes beyond mere data management and planning: Which combination of available investment projects should be selected given budget, resource, strategic, and dependency constraints?

Companies do not necessarily need to replace their existing systems to address this.

Financial, project, and planning data can be sourced from existing systems and used in a separate mathematical decision layer for capital allocation and portfolio optimization.

The basic approach is:

Existing corporate data → structured decision model → mathematical portfolio optimization → management decision.

An important distinction must be made here:

The use of data from SAP, Microsoft Dynamics, Oracle, other ERP systems, or Excel does not automatically mean that native technical integration with the respective system exists.

Depending on the technical environment, data can be provided, for example, via structured files, exports, or—in the future—via custom-built interfaces.

This Decision Guide therefore does not explain supposed standard integrations, but rather shows how data from existing enterprise systems can be used for mathematical CAPEX optimization and capital allocation.

Table of Contents

• Why Are Companies Seeking ERP CAPEX Optimization?
• System of Record vs. Mathematical Decision Layer
• SAP CAPEX Optimization
• SAP Capital Allocation
• SAP Investment Planning
• Microsoft CAPEX Planning
• Microsoft Dynamics Investment Planning
• Oracle CAPEX Planning
• Oracle Capital Planning
• ERP CAPEX Optimization
• ERP Capital Allocation
• Excel CAPEX Planning
• Excel Portfolio Optimization
• What data is required for CAPEX Optimization?
• How can data be exchanged between existing systems and the optimization tool?
• From the ERP Data Set to the Optimization Model
• ERP Data and Portfolio Constraints
• ERP Data for Multi-Year CAPEX Optimization
• Example: 200 CAPEX projects from existing enterprise systems
• Excel as a Bridge Between ERP and Portfolio Optimization
• Possible System Architecture
• From ERP Data to Live Boardroom Simulation
• StratePlan as a Mathematical Decision Layer
• Frequently Asked Questions

Why Are Companies Seeking ERP CAPEX Optimization?

In many companies, the information needed for investment decisions is already stored in various systems.

These include, for example:

• ERP systems
• Financial planning systems
• PPM systems
• Controlling systems
• Business intelligence platforms
• Excel files

These systems may contain important information:

• Capital expenditures
• Budgets
• Forecasts
• Cash flows
• Business units
• Locations
• Project Information
• Resources
• Planning Periods

However, the data alone does not necessarily answer the central portfolio question:

Which combination of available projects should be selected?

This is precisely where the connection between enterprise data and mathematical optimization comes into play.

System of Record vs. Mathematical Decision Layer

ERP and planning systems can serve as important systems of record or systems of planning.

They capture and structure business information.

A Mathematical Decision Layer serves a different function.

These levels do not have to work at cross-purposes.

Data from existing systems can serve as the basis for mathematical portfolio decisions.

SAP CAPEX Optimization

Those looking for SAP CAPEX Optimization often want to combine existing business and financial data with a systematic approach to optimizing investment decisions.

A relevant management question, for example, is:

“How can we use our existing CAPEX data to determine the best combination of projects within a fixed budget?”

One possible process involves structuring and making relevant data from the existing SAP environment available, and then using it in a separate mathematical optimization model.

This may include, for example:

• Project ID
• Investment
• Budget
• Business Unit
• Location
• Planning Period
• Financial Metrics

This information can be supplemented with additional decision-making parameters:

• NPV
• Strategic Value
• Resource Requirements
• Dependencies
• Mandatory Projects

Important: The description of this data process does not imply that StratePlan has an existing native or certified SAP integration.

SAP Capital Allocation

With SAP Capital Allocation, the challenge often does not lie in storing investment data.

The real decision point arises from capital constraints.

Assume:

Requested CAPEX = €1.4 billion

Available CAPEX = €900 million

In that case, 500 million euros of the requested investments must either be deferred, reduced, or not funded.

A mathematical capital allocation model can be used to determine:

• Which projects should be financed together
• Which combination generates the highest defined portfolio value
• How capital is allocated among business units
• Which projects will be displaced by budget cuts
• Which additional projects become feasible with a higher budget

The available company data thus serves as the basis for a mathematical decision model.

SAP Investment Planning

Companies looking for SAP Investment Planning typically focus on the structured planning of future investments.

For mathematical portfolio optimization, the link between planning data and selection decisions is particularly relevant.

Planning answers:

“Which investments are planned?”

Optimization answers:

“Which combination of these investments should we select under our defined conditions?”

A possible data flow is therefore:

Investment Planning Data → Data Preparation → Mathematical Optimization → Portfolio Scenarios → Management Decision.

The specific technical implementation of the data exchange depends on the respective system landscape.

Microsoft CAPEX Planning

Microsoft-based enterprise environments can use various systems and tools for financial planning, data analysis, and project management.

For CAPEX Optimization, the specific system in which the source data resides is not the primary factor.

What matters is whether the relevant information can be provided in a structured format.

A typical CAPEX dataset might include, for example:

• Project ID
• Investment
• Expected Return
• Business Unit
• Year
• Resources

This data can then be processed in a mathematical portfolio model.

Using data from a Microsoft environment in this way does not automatically imply that native Microsoft integration is in place.

Microsoft Dynamics Investment Planning

In Microsoft Dynamics Investment Planning, a mathematical decision layer can complement the existing planning and data landscape.

The basic process might look like this, for example:

Microsoft Dynamics Data → structured data export → Portfolio Optimization → Decision Scenario.

The optimization can then address questions such as:

• Which investments fit within the available budget?
• Which combination maximizes the defined portfolio value?
• How does the selection change when the budget is reduced?
• What impact do resource constraints have?
• Which projects are selected when new strategic priorities are established?

The technical integration can be implemented differently depending on the enterprise architecture and should not be equated with an existing standard integration.

Oracle CAPEX Planning

Oracle-based financial and planning systems can contain relevant data for CAPEX planning and investment decisions.

A mathematical optimization layer can be built on top of structured data.

The key distinction is:

Planning structures investment assumptions.

Optimization calculates portfolio combinations.

For example, a company can provide information on planned investments from its existing systems and then optimize these within a shared budget.

Here, too, the following applies:

The ability to use Oracle data for an optimization model is not the same as having native Oracle integration.

Oracle Capital Planning

Capital Planning addresses long-term capital requirements, budgets, and investment programs.

Mathematical Capital Allocation adds an additional level of decision-making:

How should the available capital be allocated among the existing investment opportunities?

To this end, planning information can be linked, for example, to the following data:

• NPV
• Expected Value
• Strategic Criteria
• Resources
• Dependencies
• Mandatory Projects

The result is not merely a new capital plan.

It produces a mathematically calculated portfolio tailored to the defined objectives and constraints.

ERP CAPEX Optimization

ERP CAPEX Optimization describes the integration of corporate data with mathematical investment optimization.

ERP systems can provide important input data.

Based on this, mathematical optimization answers questions such as:

“Which projects should we finance?”

“Which combination of projects maximizes our NPV?”

“How can we reduce CAPEX while minimizing the impact on portfolio value?”

“How do we allocate capital across multiple locations or business units?”

“What are the implications of a new mandatory project?”

This does not replace the ERP.

It remains a data and process system, while mathematical optimization forms an additional decision layer.

ERP Capital Allocation

ERP Capital Allocation links available corporate data to the question of how limited capital should be allocated.

For example, a company may have 300 investment proposals from various business units.

The ERP system contains, among other things:

• Project
• Business Unit
• Investment Amount
• Location
• Planning Year

Additional information may be required for capital allocation:

• Expected Financial Value
• Strategic Value
• Resources
• Dependencies
• Mandatory Status

This information is used to create a portfolio decision model.

ERP provides data. Optimization calculates combinations. Management makes the decision.

Excel CAPEX Planning

Excel is a key tool for CAPEX planning in many companies.

The reasons are understandable:

• High flexibility
• Quick adaptability
• Familiar user interface
• Custom calculations
• Easy data import and export

A typical CAPEX plan may already contain key data for portfolio optimization.

This allows Excel to be used as a data preparation layer.

Mathematical portfolio optimization then takes place in an optimization engine designed for this purpose.

Excel Portfolio Optimization

Excel Portfolio Optimization becomes relevant when companies want to not only sort and analyze their existing Excel data but also use it to make mathematical portfolio decisions.

For a small number of projects, different combinations can still be explored manually.

However, as the number of projects grows, the combinatorial complexity increases very rapidly.

For 20 independent yes/no projects, there are theoretically:

2^20 = 1,048,576 combinations.

For 50 projects:

2^50 ≈ 1.13 × 10^15 combinations.

For 100 projects:

2^100 ≈ 1.27 × 10^30 combinations.

That’s why a specialized optimization layer can be useful, while Excel continues to be used as a data source or preparatory tool.

Excel doesn’t have to go away. The portfolio decision-making process is simply expanded to include a mathematical layer.

What data is needed for CAPEX optimization?

A mathematical portfolio model can start out surprisingly simple.

For example, a basic data structure requires:

• Project ID
• Investment
• Expected Value

Depending on the use case, the expected value could be, for example:

• NPV
• Expected Revenue
• Financial Contribution
• Utility
• Strategic Value

For more complex models, the following can be added:

• Business Unit
• Plant
• Country
• Year
• Resources
• Strategic Criteria
• Dependencies
• Mandatory Status
• Risk Parameters

The data structure should be determined by the decision-making problem—not by the desire to use as much data as possible.

How can data be exchanged between existing systems and Optimization?

Data exchange can generally be carried out in various ways.

Which option is appropriate and technically feasible depends on the specific system landscape and the project in question.

Possible approaches include, for example:

1. Structured file export

Data is exported from the existing system and made available for Portfolio Optimization.

2. Excel- or CSV-based data exchange

A standardized data set serves as the transfer format between the enterprise system and the optimization layer.

3. Custom technical interface

If an automated connection is required, a technical interface can be designed and implemented on a project-specific basis.

Which specific interfaces are available or need to be developed must be technically assessed for the respective system environment.

From the ERP Data Set to the Optimization Model

The process of transforming existing enterprise data into a mathematical decision can be carried out in several steps.

Step 1: Identify Relevant Projects

Which investments should be included in the decision-making scope?

Step 2: Prepare Financial Data

For example:

• Investment
• NPV
• Expected Revenue
• Cash Flow

Step 3: Define the decision objective

For example:

Maximize portfolio NPV.

Step 4: Define Constraints

Budget, resources, dependencies, and mandatory projects are modeled.

Step 5: Calculate the portfolio

The Optimization Engine calculates a portfolio configuration for the defined model.

Step 6: Compare Scenarios

Management modifies assumptions and examines alternative portfolios.

ERP Data and Portfolio Constraints

ERP data often constitutes only a part of the decision model.

Additional business constraints can also be defined.

For example:

Total CAPEX ≤ 800 million €

Business Unit A ≥ €100 million

Business Unit B ≤ 250 million €

Engineering ≤ 30,000 hours

Project 17 = Mandatory

Project 22 requires Project 9

Project 31 and Project 32 are mutually exclusive

This results in a realistic mathematical decision-making model based on company data.

ERP Data for Multi-Year CAPEX Optimization

Long-term investment programs often span multiple budget periods.

For example:

2027 CAPEX ≤ €400 million

2028 CAPEX ≤ €450 million

2029 CAPEX ≤ €500 million

The following can also be taken into account:

• Project start
• Project duration
• Annual cash flow
• Annual resource requirements
• Dependencies between periods

This transforms traditional planning data into a multi-year portfolio optimization model.

The question is no longer just:

“Which projects do we choose?”

But rather:

“Which projects should we finance and start, and when?”

Example: 200 CAPEX projects from existing enterprise systems

An international corporation has 200 potential CAPEX projects.

The project data is stored in various systems and spreadsheets.

Total requested CAPEX:

€2.4 billion

Available budget:

€1.5 billion

In addition:

• €300 million in mandatory investments
• Engineering Capacity
• Business Unit Limits
• Project Dependencies
• Strategic Criteria
• Multi-Year Budgets

The existing systems provide the relevant source data.

This data is integrated into a standardized decision-making framework.

It can then be mathematically analyzed:

Which combination of the 200 projects best fulfills the defined objective function under all modeled constraints?

The existing system landscape remains the data foundation.

The Optimization Layer complements the portfolio decision.

Excel as a Bridge Between ERP and Portfolio Optimization

Excel can serve as a pragmatic bridge between existing enterprise systems and an optimization engine, particularly in the early stages.

One possible process is as follows:

ERP → Export → Excel Data Preparation → Portfolio Optimization → Analysis of Results.

This offers several advantages:

• Existing data can be reused.
• The source data remains traceable for business units.
• An optimization model can be tested without immediately launching a large-scale IT integration project.
• The required data structure can be validated first.

If the process is to be repeated and automated later, it can then be determined which technical integration makes the most sense.

This allows for business validation to take place before a comprehensive technical integration.

Possible System Architecture

A possible architecture for mathematical capital allocation could look like this:

SAP / Microsoft Dynamics / Oracle / ERP / PPM

↓

Data Provision

↓

Data Preparation

↓

Mathematical Portfolio Optimization

↓

Scenario Comparison

↓

Management Decision

This diagram illustrates a conceptual architecture.

It does not imply that a native technical interface is already available for every system mentioned.

From ERP Data to Live Boardroom Simulation

Once the relevant data has been prepared and the decision model defined, management questions can be examined as scenarios.

The CFO asks:

“What happens if we reduce CAPEX by 100 million euros?”

The budget constraint is adjusted.

The portfolio is recalculated.

The COO asks:

“What happens if we have 20 percent less engineering capacity available?”

The resource constraint is changed.

The portfolio is recalculated.

The CEO asks:

“What happens if we place greater strategic emphasis on growth?”

The decision-making logic is adjusted.

The portfolio is recalculated.

As a result, existing company data becomes the basis for an interactive portfolio decision.

Data. Question. Calculation. Comparison. Decision.

StratePlan as a Mathematical Decision Layer

StratePlan is designed as a mathematical decision layer for CAPEX, investment, and project portfolio decisions.

The approach does not involve replacing existing ERP, planning, PPM, or Excel systems across the board.

Instead, relevant data is transferred into a mathematical portfolio model.

A simple data structure might start with:

• Project ID
• Investment
• Expected Value

For more complex decisions, the following can be added:

• NPV
• Strategic Criteria
• Resources
• Dependencies
• Mandatory Projects
• Business Unit Constraints
• Multi-Year Budgets

Based on this, the following questions, for example, can be addressed:

• Which combination of projects maximizes the defined portfolio value?
• Which combination maximizes NPV within a fixed budget?
• Which projects should be selected when CAPEX is lower?
• How does capital allocation change across business units?
• What are the effects of resource constraints?
• How does a mandatory project affect the entire portfolio?
• How do project dependencies affect the selection?
• How does the portfolio change over several planning years?

The data can come from various existing enterprise systems.

The specific form of data exchange and possible technical interfaces must be defined separately for each system landscape.

This results in a clear division of roles:

ERP provides data.

Planning provides assumptions.

StratePlan calculates portfolio options.

Management makes the decision.

Don’t just rely on your data—turn it into decisions.

Frequently Asked Questions

What does SAP CAPEX Optimization mean?

SAP CAPEX Optimization refers to the use of relevant CAPEX and corporate data from an SAP environment for a mathematical portfolio optimization model. The specific technical data transfer depends on the respective system architecture.

Does StratePlan have native SAP integration?

The use of SAP data for StratePlan should not be equated with native SAP integration. Data can initially be provided in a structured format; any further technical integration would need to be evaluated and implemented separately, depending on the specific system and use case.

What does SAP Capital Allocation mean?

In the context of mathematical optimization, SAP Capital Allocation describes the use of existing corporate and investment data to address the question of how limited capital should be allocated among competing projects.

Can SAP Investment Planning be linked to Portfolio Optimization?

Planning data can generally serve as input for a separate Portfolio Optimization model, provided the necessary information can be made available in a structured format. The technical implementation depends on the specific system.

What is Microsoft CAPEX Planning?

Microsoft-based tools and enterprise systems can provide data for CAPEX planning. This data can then be prepared for mathematical portfolio optimization.

Can Microsoft Dynamics provide data for investment planning?

Relevant planning and business data can generally be sourced from a Microsoft Dynamics environment and prepared for a separate decision model. This is not automatically equivalent to an existing native integration.

What is Oracle CAPEX Planning?

Oracle-based planning and financial data can serve as a starting point for CAPEX planning. For portfolio optimization, relevant data can be provided in a structured format and processed in a separate mathematical model.

What is Oracle Capital Planning in the context of portfolio optimization?

Capital Planning structures long-term investment budgets and assumptions. Portfolio Optimization addresses the question of which combination of available investments should be selected under these conditions.

What is ERP CAPEX Optimization?

ERP CAPEX Optimization combines relevant data from enterprise systems with a mathematical optimization model to select investment projects subject to budget and other constraints.

What is ERP Capital Allocation?

ERP Capital Allocation uses existing company data as the basis for deciding how limited capital can be allocated to projects, locations, or business units.

Can an ERP system perform portfolio optimization?

That depends on the specific ERP system, the modules used, and the configuration. Alternatively, a separate optimization layer can import data from the ERP and supplement the mathematical portfolio decision.

Can Excel be used for CAPEX planning?

Yes. Excel is suitable for data collection, business cases, planning models, and custom analyses, and is used for CAPEX planning in many companies.

Can Excel be used for portfolio optimization?

Excel can provide data for portfolio optimization and even has built-in optimization functions for suitable models. For larger combinatorial decision problems, a specialized optimization engine can offer additional capabilities.

Does Excel need to be replaced for mathematical portfolio optimization?

No. Excel can continue to be used as a data source or data preparation layer, while the actual portfolio optimization takes place in a specialized mathematical environment.

What data is needed from SAP, Microsoft Dynamics, Oracle, or an ERP system?

That depends on the decision model. A simple portfolio might start with Project ID, Investment, and Expected Value. More complex models may use NPV, resources, strategic criteria, dependencies, mandatory projects, and multi-year budgets.

Does Portfolio Optimization require a direct ERP interface?

Not necessarily. For an initial use case, structured file or spreadsheet exports may be sufficient. Whether an automated interface makes sense depends on data volume, frequency of use, governance, and the technical system landscape.

Can portfolio optimization be tested initially without an IT integration project?

Yes. If the required data can be exported in a structured format, a decision model can initially be validated independently of deep system integration.

Can an automated interface be set up later?

In principle, an automated data connection can be evaluated and developed on a project-by-project basis. Which technical solution is feasible and appropriate depends on the systems involved, available interfaces, and IT requirements.

What is the difference between an ERP system and a Mathematical Decision Layer?

ERP systems manage core business data and processes. A Mathematical Decision Layer uses relevant data to calculate alternative decisions or portfolio combinations under defined objectives and constraints.

What is the advantage of a separate Optimization Layer?

A separate Optimization Layer can continue to use existing systems and complement the Mathematical Decision Layer without having to replace the entire ERP, PPM, or planning landscape.

Can StratePlan use data from existing enterprise systems?

StratePlan can work with portfolio and planning data provided in a structured format. How this data is provided from a specific ERP, planning, or other enterprise system must be determined based on the respective technical environment.

What is the core of the connection between ERP and StratePlan?

Existing systems provide relevant business data. StratePlan uses structured decision data for mathematical portfolio optimization. Management makes the decision based on the calculated alternatives.