Welcome
to this small video where I will demonstrate how
Dynamics 365 for Finance and Operations,
Enterprise edition works with
Excel and how cost accounting data can be exported to
excel. And you can actually then create your insights using
the Excel and PowerPivot in Excel. So I'm looking at the demo data that will be shipped with
the Spring release. We have
three legal entities. For this demonstration here, I will use the legal entity of USP2.
I will not go into details of how this company is configured. And
explain all of the data that is there in cost accounting as I will focus mostly on the reporting
in Excel. We have two options of exporting data to Excel. If you go to the cost accounting ledger,
we have on the View,
the option of Open entries in Excel. And that will take all the
data off this cost accounting ledger to Excel. We also have it
that you can do it within the individual
control units where you only export data that is
associated with this control unit. So here, if I go on the View, we have the same option of Open
entries in Excel. In this case, I actually want to take all the data to Excel for both
control units for this cost accounting ledger. So I'll go back
here and go on the View, and click Open entries in Excel.
The system will ask me what data I want to
export to Excel. So here, I only want to look at actual cost.
And I will take my cost element dimension
hierarchy and I will use my statistical dimension hierarchy here.
The cost object dimension hierarchy is also taken by
default for each of the control units. So here I will press Open entries in Excel.
And the system will actually start exporting the data when I say Download.
And because we have these data entities, that data will now be exported to Excel. We have two flat
tables. So my hierarchy will be flattened out and therefore I can make Pivot on these in Excel.
We can see that the Excel file is open here. I'll click Open.
And, of course this might take a few minutes the first time. So here I will
click Enable editing and we can see here that the Microsoft Dynamics Office
Add-in connects to the Microsoft Dynamics 365 for Finance and Operations.
And, it will also log me in here.
And you can see here that we actually have two tabs.
One for aggregated cost entries and one for the aggregated statistical
entries. So I can actually report both statistical and cost entries
In Excel.
And right now the system is transferring the data.
The benefit of using this way of exporting data to Excel is instead of going
to the normal form of cost entries and export is that this is actually updatable.
So when you manipulate or build
your insights in Excel,
You don't have to redo it every single time you export. You can simply
do your Pivot on this the sheet here of cost entries
or statistical entries. And when you get new data into the system, you simply just
press Refresh here
in this Add-in and then you get the new status over.
So, what we can see now is that the data is getting updated into my two sheets and we can see
now all the data is actually exported to Excel.
And we can see we have data of aggregated cost entries and
aggregated statistical entries. In this little video, I will only concentrate on the
cost entries. The data is here.
And, I can now actually start creating my first Pivot on the data to create by insight because this
is all flattened out data coming from the cost accounting.
So, what I want to do is I want to insert
... a Pivot table.
On this first sheet.
And, what my achievement will be from inside is that I want to create
a cost distribution sheet
for my cost objects.
So, I will give it a name and I will actually start defining my Pivot table here.
So, first I want to take the fiscal year.
into my filters.
I only want to look at one period.
So, I will take the period name here as well. We can start by filtering and saying we only want to look at
2017 and the demo data with the Spring release
will have data for the full 2017. So for every single month,
of 2017, there will be demo data available. And I can also say that I now
know I want to look at the period number six.
We will also add the control names in
Because I took the data out from the
cost accounting ledger. So I have data for this cost accounting ledger
per my control unit name. So I'll put it into my filter right now. I want to look at all my
cost objects. So I actually want not to filter on the control name
to a specific control at this point. So we'll keep it at all.
And I will put in the version
that I want to report on into my filter as well. So now I have my filters in place.
And, I'll come back to the filters later on in the building here how we actually select which filters to have.
I want to look at all my
cost objects. So I want to take the cost object dimension member name into my columns.
And, we can see we get them out here.
We can also ... I want to look at the cost entries. So the data I want to put in
the values is the amount, so I'll put amount in here.
And now I need to build the details of my
structure of reporting and I will use my cost element dimension hierarchy.
I don't want to use the element level number one, which is really just the title of the hierarchy, so I will
pull in directly the hierarchy level number two.
I will put in number three.
I will put in a number four.
And I also want to add in number five. I'll just
collapse
a little here. We can see here that Excel uses alphanumeric sorting
order. And if I want the personal cost to go above expenses, it is very simple in
Excel. Just mark it. Right-click and you say Move. And you
can say move up and then it is going to be safe like that. So,
this is how you're going to see your data. So now you actually expand it and see it.
We can do this same here. And I can also look at my expenses.
So, now I have my data.
And, one very important thing here is because I actually
stated
that I wanted to look at cost objects. And I'm looking and I exported data from the cost accounting
ledger and not per control unit. We can see here that all my cost objects
are actually here. So I have both
cost centers and I have my two product groups, which is my orange juice.
for the business-to-business and my orange
juice for consumer. So in this case, you can say it's actually a cross dimensional
cost object look I have in the CDS chart here.
Sorry, the table.
So, right now I'm looking at the data that is in the control and we can go down
here and see that I'm looking at a lot of data.
So, now I want to be more specific on the data I'm looking at. So here I can go to
versions.
I can go to multi-select.
And what I'm going to select is the specific data I want to look at. So here I will say
Deselect this All. And I'll go and say I want to look at my USP2.
So I select USP2
that means all my primary cost of USP2.
And here we can see all of overhead calculations that have been performed.
And what I want to take is my period number six.
And, yes, unfortunately this is not filtered by period because this is a text string.
That would be nice and this is something we want to work with for the future. But if I
had, and you can see I have another calculation of period number six that I've done.
at 01:16 pm and I've done another one and
a newer version on the same date for period number six
at 07:30 pm. And I want to take the latest calculation.
So I can actually in Excel see and compare different calculations and the results of different
calculations. But in this case, I want to look at the latest calculations, so I click okay. And now all my
data is actually filtered to show the data of these two specific
so, meaning the primary and secondary costs. The primary cost of USP2 and the calculation
results are actually posted in that specific calculation record
I selected. So again now have all my numbers. So what is actually opt for me to do now is we need
to make this nice. And the way we can do that if I make it small here.
We can always use and make the numbers look nice. So first of all, I can actually filter on the data here.
And say you want to look at the data here as.
Go back to home and say all of the states is.
In accounting numbers.
We can make this one small as well. We can even hide it.
Close for now.
We can actually also
add the template to make the design look nice.
So, let's take a template here.
So, the our numbers look much nicer.
So, that are really two things that I can use this CDS for this cost distribution sheet.
And, just make the columns so we can see all the numbers here.
And, I was just highlighting here again and say.
...and the cost centers here. So I know all of these are my cost centers.
So, I want to make them.
Also nicer to look at.So this is about how you can actually as a user
make your report nice for reporting to your management.
For these two here, let us give...say. I know that these are not
cost centers. These are cost objects of my product groups. So I want to make them a
different color. So when I'm looking at my report I understand it.
And, we can make it the same size here.
And again,
imagine that you have many cost centers and product groups. So here you will actually be able to distinguish
them by adding different colors on them.
So, in this case, and then a demo company, the cost center 094 is really a
dummy cost center that in the financial accounting where cost
so, financial people will post expenses
of types that they don't know how to distribute. So the first check I want to do here
but I can easily do is to say Has all the cost
all the expenses that have been posted to 094 been redistributed
meaning that this account of this cost center needs to have a balance of zero. Because if it
does not, then I know that I need to go and fix some of my allocation of distribution rules in the
cost accounting module. So here I can easily validate that it is actually zeroed out.
The next I can do is I can actually look at my
allocations. And here we can then see all the allocations
that take place so here we can actually go down here sorry.
We can actually see here that we have our
cost center 007. We can see that it has some primary costs coming in. We can
also see that some secondary cost comes into the cost center. And we can also see
that it allocates all of the costs out and to which cost center it actually allocates out to.
We can actually in the end see, because I'm looking at all my cost objects
we can actually see all of these allocations between my cost centers 007,
008, and so on, happens on the
cost center allocation level. And because in my
hierarchy, I distinguish between the cost centers that will then be allocated
to my product groups. We can actually see also here that we allocate in and then all these cost
centers also allocate them out, and they will actually be allocated out to my
two product groups.
So, here I get a full picture, cross dimensional, how my allocation calculation has
been performed in the cost accounting module. And I can actually sit here in the report
and trace that first, how the primary cost comes into the cost centers,
how the primary cost is allocated between the cost centers, and finally I can see how the
cost centers allocating to my product groups.
So, this is really the power of Excel that you get by simply exporting the
data to Excel and using PowerPivot in Excel by looking at
the cost entries that we can explore. And again you simply just
can keep this refreshing the data here.
And, you can just go up here and select to look at another
period, and you will don't have to rebuild thie UI
of the reporting every single time. So this is really the benefit of Dynamics 365 for Finance and
Operations and Microsoft Excel.
I hope this little video can be the aspiration of using
the open entries in Excel,
and the cost accounting. So thank you for listening.