How to Create a Quick Customer Profitability Analysis with Dynamics 365 Finance and Excel

How often do you get the question:

Can we produce a full customer P&L that allows us to analyze customer profitability?

Of course you can!

In Dynamics 365 Finance there is a number of ways to achieve this, but in this example we will leverage the new “customer account and name on finance transactions” feature to achieve our objective.

Firstly, I need to get the data I need for my analysis. A simple way to do this is to use General ledger / Inquiries and reports / Voucher transactions. The following screenshot shows my query:

Since I would only like to see a customer P&L, I am only interested in Revenue and Expense accounts.

Once I have made my selection, I get the result as raw finance transactions.

As you can see from the screen, the transactions now include fields for customer ID and customer name.

From here I want to export the raw data into Excel for further analysis. In the ribbon I click on the Office icon and select General journal entry.

The data is now in Excel as shown below.

However, since the data is based on financial transactions the signed is reversed for our purpose, revenue is after all positive. To fix this, I create a new column in the table named Reversed Amount where I multiply the Amount in reporting currency by -1.

Now we are ready to perform our analysis. First, I mark the table and click on PivotTable in the Insert menu.

This creates a new PivotTable in a blank workbook.

Just to get us started, I add Customer name and Account name to the Rows section of the PivotTable designer. Also, I add the Reversed Amount field and rename it to Amount ($). Now the PivotTable has the basic ability to show the P&L by customer.

In this example, I only have two customers, but in reality I may have many. Therefore, I would like to add a slicer to allow me to filter the customers. The following screenshot shows how the slicer is added.

There is now a customer slicer and in the below I have selected customer “US-017” and the PivotTable is filtered accordingly.

Also, it is often relevant to filter on periods. To do this, I add a Timeline slicer as shown below.

I now have the ability to filter the data on days, months, quarters and years.

Lastly, I do a bit of formatting to make it look a bit nicer.

This was just an example to show how easy it is to use the new customer and vendor fields in the finance transactions to perform ad hoc analysis. Obviously, using financial dimensions for customer and vendor instead could achieve something similar.

Also, the CFO Overview analytical workspace provides the ability to show a customer trial balance with budget variance and drill-down.