The Dyn365 ERP Analytics MCP translates natural language into DAX and queries BPA data directly from an agent in Copilot Studio. But having the tool is not enough: the prompt matters as much as the data. Each level of a financial organization has a different time horizon, accesses different information, and makes decisions of different scope.
In this blog I wanted to go a little further so we can see the potential of what we can achieve with real-life scenarios: I designed four prompts for four roles (Senior Analyst, Manager, Director, and CFO) and explain the logic behind each one: what agent capabilities can be used, how the model reasons over all the data it can access, and how to maximize the benefit of the MCP Analytics based on the hierarchical level and the type of decision that role needs to make, so that each person can deploy everything they have to offer.
🌱 WHAT IS THE Dynamics 365 ERP Analytics MCP?
The ERP Analytics MCP allows us to retrieve information from the database where BPA (Business Performance Analytics) data is stored, directly by asking questions in natural language. We describe what we need and the MCP translates the instruction into DAX (Data Analysis Expressions). And DAX is the language of formulas that its used in data analysis tools of Microsoft like Power BI.
But the potential is not limited to obtaining insights from transactional and accounting data. Thanks to the reasoning model, we can go much further: the agent can propose concrete steps to execute in the system to take corrective actions, identify specific transactions to review, suggest actions to coordinate with our stakeholders, draft communications or executive summaries ready to share (always reviewing them first), and even generate follow-up checklists based on the findings. In other words, the agent does not only analyze: it can also guide what to do next and with whom.
By combining tools, a reasoning model, and instructions, instead of navigating through multiple BPA or system reports and performing calculations manually, we simply describe what we need and the agent (AI) generates the analysis itself, following this logic:
- We write the prompt in natural language, and the AI understands it.
- It searches its database to check whether it has or can access the data.
- It generates the input: translates the query into DAX.
- It generates the output JSON with the information.
- It returns the response as specified (in the instruction or the prompt).
⚠️ At the moment I’m writing this blog it’s in preview.
🌱 ERP Analytics MCP TOOLS
If we consider that our MCPs work with tools that allow them to execute actions, here are the two tools available in this one:
| Tool | Description |
|---|---|
| get-bpa-dataset-schema | Retrieves the schema of dimensional models, including tables, columns, measures, and relationships from your Business Performance Analytics environment. |
| execute-dax-query | Executes DAX (Data Analysis Expressions) queries against your Business Performance Analytics data with automatic row-level security enforcement. |
🌱 PREREQUISITES
Before starting to use the Dynamics 365 ERP Analytics MCP, check the following:
🌿 Dynamics 365 Finance and Operations: Version 10.0.38 or higher.
🌿 Business Performance Analytics: Version 2.4.3224 or higher.
🌿 Security roles and permissions:
- Basic user role in the Dynamics 365 Finance and Operations environment.
- Business Performance Analytics user role to access analytical information.
🌿 The same configurations already done for the Dynamics MCP (available here in this blog: https://natisalas.com/2026/02/04/episode-3-unlocking-the-door-enabling-the-mcp-server/).
🌱 SETUP
Configuring an agent in Copilot Studio with this MCP is no different from the others we have been setting up:
🌿 Name and description
🌿 Instructions
🌿 In tools, select the Dynamics 365 ERP Analytics MCP, review security and the connection
For more information, I have this blog I wrote when the MCP first launched (and I promise a new one soon! 😉): https://natisalas.com/2026/02/04/episode-4-build-connect-copilot-studio-mcp-server/
And now, let’s get into the financial prompts! 🥳
I divided a financial organization into five typical levels (there can be more or less, you can adapt it to the organization you are thinking about or working with):
🌱 Level 1 | CFO (Chief Financial Officer): top financial authority
🌱 Level 2 | Directors (FP&A, Treasury, Controller): responsible for each major area
🌱 Level 3 | Managers: operational management of specialized teams
🌱 Level 4 | Senior Analysts: technical execution and specialized analysis
🌱 Level 5 | Junior Analysts: analytical support and operational reporting
Each level has a different time horizon, needs information from different sources, and makes decisions of different scope. A well-designed prompt respects that: it asks the agent for exactly what that role needs, with the right level of detail and the output format that is most useful to them.
✨NOTE: I left out Junior Analysts from the prompts because their typical task is mostly data entry.
🌱 Prompt 1: Level 4 | Senior Analyst: Intercompany report with anomaly detection
🌿The prompt
«Run an intercompany report for all active legal entities in BPA. Show the GL summary by entity and account type, including net balance, total debits, total credits, and entry count. Identify any intercompany indicators such as intercompany packing slips, linked intercompany ledgers, intercompany sales orders, and reversing journal entries. Summarize purchase activity by entity, including purchase order count and amount, invoice count and amount, vendor count, and invoice matching variances. Finally, flag any anomalies such as negative expense balances, invoices without purchase orders, open POs without invoices, or missing intercompany reconciliation activity, and provide actionable recommendations for each.»
🌿Why this prompt works best for this level
The Senior Analyst is the most technical person on the team. Their job is to go deep into the data, understand it thoroughly, and detect what does not add up. A prompt for this role needs to be exhaustive in data coverage and specific in D365FO terminology (packing slips, GL summary, reversing journal entries).
What makes this prompt work especially well is the part at the end: «flag any anomalies… and provide actionable recommendations for each.» Listing data is not enough. The Senior Analyst needs the agent to have already pre-processed the findings and present a clear starting point for investigation. And this is precisely what matters: the AI presents previously processed and analyzed data, and with the Senior Analyst’s knowledge of the business and the organization, they have a solid starting point to reach the deepest level of detail and the best conclusion, in less time.
🌿What the agent does for them: consolidates multiple queries (GL summary, purchase activity, and anomaly detection) into a single structured response, saving hours of manual reconciliation work across multiple companies.
🌱 Prompt 2: Level 3 | Managers: Projected cash flow with liquidity gap analysis
🌿The prompt
«Analyze the projected cash flow at 30, 60, and 90 days from today ([TODAY’S DATE]). For each period: Expected Collections: Sum sales invoices (Sales sub-ledger / Sales invoice) with a due date within the range, grouped by currency. Committed Payments: Sum pending purchase invoices (Purchase sub-ledger) with a due date within each range, grouped by currency and vendor. Foreign Currency Payments: For payments in currencies other than the base currency, apply the current day’s exchange rate. Convert the amount to the base currency and verify whether the projected bank balance covers the payment. Liquidity Gap: For each period, calculate: Opening Balance + Collections – Payments (converted at the day’s exchange rate). Indicate whether there is a deficit and how critical it is: CRITICAL if > 20% of the balance, MODERATE if between 5-20%, LOW if < 5%. Variance Forecast: Based on the collection history of the last 90 days, estimate a collection realization rate (e.g., if 80% of collections arrive on time) and recalculate the pessimistic scenario. Present the results in a summary table by period, along with a conclusion on the criticality of each gap.»
🌿Why this prompt works best for this level
The Manager does not analyze line by line: they manage. Their central concern is: do we have enough to pay what we owe, and when does it become critical?
This prompt has three characteristics that make it ideal for this role.
First, the 30/60/90-day time scale is exactly the management horizon of a treasury or accounting manager. It is not the day-to-day of the analyst nor the fiscal year of the CFO: it is the operational horizon.
Second, the criticality classification (CRITICAL / MODERATE / LOW) turns a number into an action signal. The Manager does not need to interpret whether an 18% gap is serious: the agent already tells them, and they can act directly from there.
Third, a simple but very important calculation: will I have enough liquidity to make payments? This allows the Manager to alert their colleagues in time to take collections management actions before it is too late.
Finally, the pessimistic scenario based on collection history is the difference between an analyst’s report and one that is actually useful for the manager. If in the last 90 days only 80% of collections arrived on time, the agent recalculates using that pessimistic adjustment factor. This is what managers do (with Finance Insights, Management Reporter, BPA, or a good old Excel), but now it is already calculated directly.
🌿What the agent does for them: cross-references AR, AP, the day’s exchange rate, and the projected bank balance in a single query, delivering an actionable table ready to share with the director.
🌱 Prompt 3 : Level 2 | Directors: Spending behavior analysis vs. budget
🌿The prompt
«Analyze the distribution transportation spending behavior during the current year (January-June 2026), comparing it month by month against the approved budget. For each month, indicate:
The actual amount executed vs. the budgeted amount.
The variance in absolute value and in percentage.
The months with the greatest variance (favorable and unfavorable).
The possible causes of those variations (internal factors such as delivery volume, routes, suppliers; and external factors such as fuel prices, seasonality, macroeconomic context).
Corrective recommendations if the trend shows a risk of over-executing the annual budget.»
🌿Why this prompt works best for this level
The FP&A Director or operational unit director needs to understand the behavior of a spending line over time and make corrective decisions before the problem escalates. Their conversation is with the CFO and the business areas, not with journal entries. And this is where we start to understand that the external context influences the results we see in our organization (not just the internal numbers anymore).
What makes this prompt precise for this role is the combination of quantitative data with causal interpretation. Knowing that March had a 23% deviation is not enough: the Director needs to know why. Was it a spike in delivery volume? Did fuel prices go up? Was there a supplier change? They need to be able to argue in front of the management committee.
The instruction to include external factors (fuel prices, seasonality, macroeconomic context) is especially powerful because it forces the agent to cross the internal ERP data with market context. This is something that normally requires an analyst to spend time researching separately.
The corrective recommendations complete the loop: the Director not only understands the problem, they start analyzing it already armed with options that can explain it.
🌿What the agent does for them: generates a month-by-month budget vs. actuals analysis with causal interpretation and risk forecast, ready to review whether everything adds up.
🌱 Prompt 4 | Level 1: CFO: ROI, budget execution, and available capital for new initiatives
🌿The prompt
«For the eCommerce business line (Business unit 253), during the first half of the current fiscal year (January-June 2026):
Investment ROI: What was the return on investment (ROI) of expenditures on software and employee training, broken down by category and compared to the previous semester?
Budget execution: What percentage of the budgeted revenue was actually achieved, and what were the main causes of deviation (positive or negative)?
Available capital: Considering actual revenues, operating expenses, and current reserves, how much free capital exists to finance the launch of a new Digital Marketing line, and under which scenarios (conservative, base, optimistic) would it be viable?»
🌿Why this prompt works best for this level
The CFO speaks in terms of investment returns, viability, and strategic risk. They do not want to know how much was spent on software: they want to know whether it was worth it and how much is left for the next investment.
This prompt has a three-question architecture that reflects exactly the mental process of a CFO when reviewing a business unit:
What return did we get on what we invested? (ROI by category, compared against the previous semester, not in the abstract, but in historical context.)
Did we deliver what we said we would bill? (Revenue budget execution, with causes of deviation, positive or negative.)
Can we finance what comes next? (Free capital under three scenarios: conservative, base, and optimistic.)
The granularity at the business line level (Business unit 253, eCommerce) is intentional: in this case, the CFO needs to evaluate whether the spending in a new business line was in line with the budget, whether the expected revenues were met, whether the expected return was achieved, and whether that gain can be reinvested in a new line. If that data is not in the system, it will be very difficult to analyze all the information needed because there is no way to distinguish which expenses and revenues were specifically generated by the new line.
What distinguishes this prompt from a Director’s is the forward-looking, capital allocation thinking. The CFO does not only look back to understand; they look ahead and make decisions, aligning with what the other C-levels consider to be the right direction for the organization.
🌿What the agent does for them: integrates revenue, expense, investment, and reserve data into a near-complete strategic analysis, with financial viability scenarios that would normally take several days to prepare.
🌱 TWO VERY IMPORTANT CONCLUSIONS
✨ If you work in an organization (setting aside consulting for a moment): the language model does not know your business. You do. The combination of your context and its capacity to process data is what makes this work in practice.
And if you work in consulting or across multiple industries, you most likely know what your users need. The best approach is to adapt this to their requirements and help them work in a more dynamic way, with the judgment you can bring based on your experience working with these new technologies.
✨Now that we have been talking about the importance of treating the AI response as a starting point so that people can do the analysis with the right knowledge of the industry and context, the CFO prompt is a good example of why having the right information structure in place matters: data correctly recorded in the system, and D365FO well configured, so the AI can also deliver the expected result.
📎 Leaving here the doc of the learn: https://learn.microsoft.com/en-us/dynamics365/finance/business-performance-analytics/erp-analytics-mcp-overview
And see you in the next one! 💖✨