Demand planning: comparing Excel-based forecasting utilities versus Power BI integration

ERP Microsoft Dynamics 365
, , , , , , , ,
1

Our organization has been using Excel-based forecasting for demand planning with custom macros and templates that our team has developed over the past five years. These Excel tools are deeply embedded in our workflow - everyone knows how to use them, they’re flexible, and they handle our statistical forecasting models well.

With the upgrade to version 10.0.38, we now have access to Power BI integration for demand planning, and management is pushing us to consider migrating our forecasting workflow. I’m trying to understand the real-world trade-offs from teams who’ve made this transition.

My main concerns are around user adoption (our planners are Excel power users but have limited Power BI experience) and data refresh cycles. Our Excel macros pull fresh data on-demand, while Power BI seems to work on scheduled refreshes. How do teams handle scenarios where you need immediate forecast recalculation based on just-received order data?

Would love to hear experiences from others who’ve evaluated or made this transition. What functionality did you gain or lose?

system 2

Excel → Power BI Demand Planning: Pre-upgrade Checks, Migration Path, and Rollback

Pre-Upgrade Checks (source: Excel-based stack → target: D365 10.0.38 + Power BI)

  • Data model compatibility: Audit your Excel macro logic for hardcoded fiscal calendars, custom ABC segmentation, or proprietary smoothing algorithms. These won’t auto-translate — each needs a mapped equivalent in Demand Forecasting (module path: Master Planning → Demand Forecasting) or a Power BI measure.
  • Refresh architecture: Confirm whether your Power BI workspace is on Premium Per User (PPU) or Shared capacity. Shared capacity caps scheduled refresh at 8×/day — this directly impacts your near-real-time recalculation requirement (verify in your version).
  • DirectQuery vs. Import mode: Near-real-time order data requires DirectQuery against your D365 Dataverse or Azure Synapse Link endpoint, not Import mode with scheduled refresh. Validate network latency and query fold capability on your specific data volumes before committing.
  • Security model parity: Excel files often carry implicit row-level access via SharePoint permissions. Document this before migration — Power BI RLS (Row-Level Security) requires explicit role definitions.
  • Macro inventory: Catalog all VBA macros. Classify each as: (a) pure calculation → migrate to DAX/Power Query, (b) UI workflow → migrate to Power Apps or D365 workspace, (c) external API call → requires gateway or dataflow replacement.

Migration Sequence

  1. Stand up Azure Synapse Link for Dataverse or Direct Query connection to D365 FO entities (SalesOrderLines, InventForecastSalesTable, demand forecast entities). Validate row counts against source.
  2. Recreate core statistical measures in DAX — weighted moving average, safety stock calculations, seasonality indices. Keep Excel models live in parallel during this phase.
  3. Implement incremental refresh policy in Power BI on the order data table. Set the incremental window to match your shortest acceptable recalculation cycle (for near-real-time: DirectQuery only — incremental refresh still has a latency floor).
  4. Pilot with one product category and two planners. Track forecast accuracy KPIs against the Excel baseline for a minimum of two planning cycles.
  5. Configure Power BI Embedded or publish to D365 FO analytical workspaces so planners access reports without leaving the ERP context.
  6. Decommission Excel templates category-by-category, not all at once. Maintain a read-only archive copy.

Rollback Procedure

  • Excel models are stateless and version-independent — rollback is file restoration, not a technical procedure. Maintain SharePoint version history on all template files throughout migration.
  • If Power BI workspace is decommissioned prematurely, restore from Power BI Deployment Pipeline snapshot or .pbix export taken at each milestone.
  • For D365 Demand Forecasting baseline (Azure ML-generated), rollback means re-running the baseline generation job with the prior period’s parameters — document those parameters before cutover.

Functional Gaps to Expect

Your on-demand recalculation concern is legitimate. Power BI does not replicate VBA’s synchronous, on-demand execution model. The realistic mitigation stack: DirectQuery for order data freshness + Power Automate trigger on SalesOrderConfirmed event to invalidate and refresh specific report pages. This gets latency to minutes, not seconds. If sub-minute recalculation is a hard requirement, Power BI is the wrong primary tool — consider D365’s native master planning real-time recalculation (DDMRP or Planning Optimization) as the calculation engine, with Power BI as the visualization layer only.

This draft is based on general Microsoft Dynamics 365 knowledge. It has not been verified against your specific version and environment. Practitioners: verify the steps and share your experience below.

3

We went through this exact evaluation six months ago and ultimately kept our Excel templates while adding Power BI for executive dashboards. The reality is they serve different purposes. Excel macros give you immediate calculation control and flexibility for ad-hoc analysis. Power BI excels at visualization and standardized reporting but isn’t designed for the iterative forecast adjustments that planners need to do throughout the day. We use Power BI to present finalized forecasts to leadership, but the actual forecasting work still happens in Excel. The data refresh issue you mentioned is real - Power BI’s scheduled refresh model doesn’t fit well with dynamic demand planning workflows.

4

I’ve implemented both approaches across multiple clients. The key insight is that Excel macros and templates aren’t going away - they’re too valuable for detailed analysis. However, Power BI integration in 10.0.38 brings significant advantages for collaboration and version control that Excel struggles with. Multiple planners can work on Power BI datasets simultaneously without the file-locking issues you get with shared Excel workbooks. The data refresh concern is valid but can be addressed with DirectQuery mode instead of Import mode, giving you near-real-time data access. That said, DirectQuery has performance implications you need to test with your data volumes.

5

From a user adoption perspective, the learning curve for Power BI is steeper than people expect. We spent three months training our planning team, and they’re still more comfortable in Excel for anything beyond basic reporting. The real question is what problem you’re trying to solve. If your Excel templates work well and your team is productive, migrating to Power BI just for the sake of using newer technology doesn’t make business sense. Focus on where Power BI adds clear value - maybe that’s executive dashboards, cross-functional visibility, or automated distribution of forecast reports. You don’t have to choose one or the other exclusively.

6

One aspect that often gets overlooked in this discussion is data governance and auditability. Excel macros and templates are difficult to version control and audit. When a planner makes changes to formulas or assumptions, tracking those changes across multiple Excel files is nearly impossible. Power BI with proper setup provides much better audit trails and data lineage. For compliance-heavy industries, this alone can justify the migration. However, you need to architect it properly - use Power BI dataflows to centralize your transformation logic rather than embedding it in individual reports. This gives you reusability and governance while maintaining flexibility.

7

I want to address the data refresh concern specifically because we solved this in our implementation. You’re right that scheduled refresh doesn’t work for demand planning. We configured our Power BI reports to use composite models - DirectQuery for the real-time transactional data (sales orders, inventory) and Import mode for the historical data used in statistical models. This hybrid approach gives you on-demand access to current data while maintaining good performance for complex calculations on historical datasets. The trick is identifying which data needs to be real-time versus which can be refreshed daily or weekly. Our planners can now see today’s orders immediately while still running sophisticated forecasting algorithms on imported historical data.

8

Here’s a hybrid approach that’s worked well for several organizations I’ve worked with: keep Excel for the forecasting engine itself, but use Power BI for data preparation and output visualization. Your Excel templates can connect to Power BI datasets as data sources, giving you the best of both worlds. The planners continue using familiar Excel interfaces with their custom macros and formulas, but the underlying data comes through Power BI’s robust data transformation and refresh capabilities. This approach also addresses user adoption concerns since planners don’t need to learn Power BI’s DAX language or report building - they just consume the prepared datasets in Excel.

9

After implementing demand planning solutions across 20+ D365 deployments, here’s my comprehensive perspective on the Excel versus Power BI question:

Excel Macros and Templates - Current State: Your five years of Excel template development represents significant intellectual capital. These templates are widely used because they offer unmatched flexibility for planners who need to perform what-if analysis, adjust assumptions on the fly, and run custom calculations. The familiarity factor is huge - your team’s productivity with Excel shouldn’t be underestimated. Excel’s calculation engine is also incredibly powerful for statistical forecasting models, and the ability to pull fresh data on-demand through macros gives planners control over their workflow timing.

However, Excel has inherent limitations: version control challenges, difficulty sharing live data across teams, file corruption risks, limited scalability with large datasets, and lack of audit trails for formula changes.

Power BI Integration in 10.0.38: The Power BI integration available in your version brings several compelling capabilities. First, the data refresh architecture has evolved significantly. While scheduled refresh is the default, you can implement near-real-time scenarios using DirectQuery or composite models as others mentioned. More importantly, Power BI’s integration with D365 provides automatic schema updates when your data model changes, reducing maintenance overhead.

The collaboration features are genuinely transformative. Multiple planners can work with shared datasets simultaneously, comments and annotations are built into the platform, and you can publish parameterized reports that different business units can customize without creating separate copies. The row-level security in Power BI also enables you to share forecast data selectively based on user roles, which is difficult to achieve with Excel templates.

User Adoption and Data Refresh Concerns: Your concerns about user adoption are valid and commonly encountered. The solution isn’t to force planners to abandon Excel entirely. Instead, consider a phased approach:

Phase 1: Use Power BI for data staging and preparation. Build Power BI dataflows that clean, transform, and aggregate your demand planning source data. Planners continue using Excel but connect to these Power BI datasets instead of running their own data extraction macros. This immediately solves the data refresh timing issue - Power BI can refresh every 30 minutes with Premium capacity, and planners trigger Excel recalculation whenever they need updated forecasts.

Phase 2: Migrate visualization and reporting to Power BI while keeping calculation logic in Excel. Your planners generate forecasts in Excel, but instead of distributing Excel files, they publish results back to Power BI datasets for visualization. This addresses executive visibility and cross-functional collaboration without disrupting the planning workflow.

Phase 3: Gradually migrate calculation logic to Power BI for scenarios where it makes sense. Not all forecasting needs to move - complex statistical models or highly customized algorithms can remain in Excel. But standard calculations benefit from Power BI’s DAX engine for performance and maintainability.

Practical Recommendations: Don’t view this as an either-or decision. Build a hybrid architecture:

  1. Data Layer: Power BI dataflows pull data from D365, perform cleansing and aggregation, and refresh on a schedule that matches your business needs (hourly for fast-moving items, daily for others)

  2. Calculation Layer: Keep Excel for complex forecasting algorithms and what-if analysis where planner judgment is critical. Use Power BI for standardized calculations that benefit from centralized logic

  3. Presentation Layer: Power BI for dashboards, KPIs, and cross-functional visibility. Excel for detailed planner worksheets and ad-hoc analysis

This architecture preserves your team’s Excel expertise while gaining Power BI’s collaboration and governance benefits. You can pilot this approach with one product category or planning team before rolling out broadly, which helps manage the change management and training investment.

The data refresh issue you raised is solvable but requires architectural planning. With DirectQuery or composite models, planners can see current data in Power BI reports. For Excel-based forecasting, use Power BI datasets as OLAP sources that Excel can query on-demand, giving you the immediate recalculation capability your workflow requires.

Start by mapping your specific forecasting workflows to identify which parts benefit most from Power BI capabilities versus which should remain in Excel. The goal isn’t technology replacement - it’s augmenting your existing capabilities with better collaboration, governance, and scalability where those factors matter most to your organization.