Budgeting is a crucial process for any business, as it helps to plan out expenses. Budgeting helps in reaching business goals, in addition to anticipating operational changes. Without a budget, a business may experience overspending and underperformance, which could ultimately lead to the company’s closure. In this blog post, we will show you how to implement custom budget check in D365 Finance and Supply Chain using the logic of Actual vs Budget Report.

 

There is a standard report in D365 Finance and Supply Chain which shows the amount of budget allotted vs the amount of budget spent against an account. Users can use this report to see at multiple drill downed levels along with budget model. By using the main logic in this report, you can implement custom budget check in D365 on any out of the box process.

What is Actual vs Budget Report in D365?

D365 Actual vs Budget Report is a standard report that shows the difference between the budgeted and actual amounts for a financial account.  The user can access it from the Budgeting module, under Reports > Budget Reports > Actual vs Budget Report. You can also filter out or drill down to see the details of each transaction that contributes to the budget or actual amount. Alternatively, you can export the report to Excel or PDF format, or print it for further analysis

How to use Actual vs Budget Report’s logic for implementing budget check?

You need to understand the logic behind the report and the views that are used to calculate the data to use Actual vs Budget Report for budget check. Microsoft uses two main views for the calculating data for this report, which are PSNBudgetAnalysisBudgetView and PSNBudgetAnalysisActualView. One view shows the amount of budget allotted and one shows the actual spent amount. We will need a main account and a budget model ID against which the amounts will be calculated to find out the current value of budget amount from these views.

Use the following queries to get the amount of allotted and actual budget amounts from both of the views:

• To get the allotted budget amount, use this query:

select sum(AccountingCurrencyAmount) from psnBudgetAnalysisBudgetView

where psnBudgetAnalysisBudgetView.MainAccountId == “MainAccountId”

&& psnBudgetAnalysisBudgetView.BudgetModelId == “BudgetModelId”

• To get the actual budget amount spent, use this query:

select sum(ReportingCurrencyAmount) from psnNBudgetAnalysisActualView

where psnNBudgetAnalysisActualView.MainAccountId == “MainAccountId”

availableBudgetAmount = psnBudgetAnalysisBudgetView.AccountingCurrencyAmount – psnNBudgetAnalysisActualView.ReportingCurrencyAmount;

By taking the difference from both sum values  we will get current budget amount available for the mainAccountId.

Note:

If calculation needs to be against a specific financial dimension then add condition on the DisplayValue column in both views.