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:

Voucher Transactions 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.

Query Result

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.

Export to Excel

The data is now in Excel as shown below.

Data Exported to Excel

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.

Reversed Amount Column

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

Insert PivotTable

This creates a new PivotTable in a blank workbook.

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.

Basic Customer P&L

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.

Adding Customer Slicer

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

Customer Slicer Applied

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

Adding Timeline Slicer

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

Timeline Slicer Added

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

Final Formatting

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.

Published by Henrik Marx Larsen

This blog is my personal contribution to the Microsoft Dynamics 365 for Operations and Finance (D365F&O) community debate. Having worked with Axapta / Dynamics AX / D365F&O for 20 years , I think I may have some interesting insights and views to share. However, I tend to always look forward so I will mostly be writing about current subjects. I have a vain hope that my musings may inspire some of you and provoke some new thoughts in the interest of our community. My day job is to work as a Senior Technical Specialist at Microsoft and help take D365F&O capabilities to new and existing customers. Please note, the views expressed in this blog are mine alone and do not necessarily reflect the views of my employer, Microsoft.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: