Every month accountants need to prepare corrections for prepaid expenses and expense accruals. The reason why accountant make these corrections is simple, to ensure the costs (expenses) are accounted in the period in which it is incurred. For everyone who is not familiar with these financial concepts a quick introduction to these concepts.
Accrued expense must be recognized in the accounting period in which it occurs rather than in the following period in which it will be paid. In Dynamics costs are recognized when the supplier invoice has been posted or can be automatically accrued when a purchase order is received. There are still some scenario’s where accountants want to ensure they aren’t missing other costs, these other costs can be found in purchase order which have not yet been received and supplier invoice which have not been posted yet. Standard Dynamics doesn’t automatically adjust for these scenarios.
For prepayments accountants perform the opposite adjustment and look for prepaid expenses which are shown as expense in the accounting period but must be presented as such in the subsequent accounting periods in which the services where performed. An example of a prepayment expense is a quarterly rent invoice, which is fully accounted in the first period and is adjusted to account for 1/3 of the quarterly rent in the three accounting periods. In Dynamics (pending) supplier invoices cannot use the ledger accrual functionality an alternative solution is required.
In this post I will demonstrate my solution for the monthly corrections for expense prepayments and accruals. I have used concepts of Ludwig video about accruals, his video can help you understand the concepts of Excel reporting via Electronic Reporting.
In this blog I will discuss the following concepts:
the data sources needed to calculate expense prepayments and accruals
data extraction via electronic reporting
data transformation via PowerQuery
run electronic reporting report
After understanding these concepts you can create a similar report in Dynamics.
Data source needed to calculate expense prepayments and accruals
The problem for prepayments and accrual calculations is not the ability to record the data necessary for these calculates but the ability to extract this data via data entities. This makes it hard to automate reporting. In most circumstances the following data sources are needed to calculate prepayments:
posted supplier invoices
allocation schema per invoice
allocation start date per invoice
There is no field on the supplier invoice lines to record the allocation schema, however the reason code can be used to represent the allocation schema. Just as in Ludwig's video I use coding like M2, M3 etc. the M2 represents a split by 2 months. To determine the start date of the allocation schema I use the supplier invoice date. However, you can use other fields available on the supplier invoice.
Data needed to calculate accruals:
un-receipted approved purchase order lines
expected delivery date for order lines
un-posted non-po supplier invoices
allocation schema per invoice
allocation start date per invoice
You may also want to include fields like ledger and dimension postings and line identification fields such as purchase order and invoice number.
In this blog the expense accruals are based on un-receipted purchase order lines with an expected delivery data within or before the current accounting period. You my also want to include un-posted supplier invoices which are not linked to purchase orders, as purchase orders are accrued already and the costs shouldn’t be accounted twice.
Data extraction via Electronic Reporting
You can use data entities to extract some of the required data but you may soon realize not all required and desired columns are available through entities. Using the Electronic Reporting workspace you can create list reports which contain all required data.
To create ER excel reports the complete the following steps:
1. Create a data model
In your data model, you want to include all fields that are required in your output file. This also includes any calculated fields. To create a table or list report, click new to add a data model node of type ‘Record list’, afterward create node of type ‘Record’, followed by all fields you want to add to this report. This hierarchy is forms the basis for your report. When you add a new field to the data model, ensure you use the same data type as the mapped data source. Use ‘real’ for amount related fields, ‘string’ for text, ‘Int64’ for line numbers and ‘date’ for dates. I use the data type ‘string’ for enums, this work when a text function is applied. This may not be the correct way, but it's fast and works.
2. Define data
Once you have identified all necessary fields and structured your data model, it’s time to map the model to the data sources.
I used the VendInvoiceInfoLines, PurchLine and VendInvoiceTrans tables as my data source. You can use all the known relationships in Dynamics to extend the data source. You can also utilize display methods to display system calculated fields like receipts used on supplier invoice lines.
While using the designer I prefer to use the ‘group view’ and ‘details view’, this provides more information and structures the layout of available sources.
Select Table records from the data source types section and click ‘add root’ to add your data source. Add the tables you need for your report.
3. Map data model to data sources
When the tables have been added, the data model fields can be mapped. To map the two, simply press ‘bind’ while a data source field or method and data model field are selected.
You can also use formulas to map fields of the data model. For instance I used a text function to convert enum values to text. I use this function because the standard enum function are quite cumbersome.
REPLACE(TEXT(@.'>Relations'.VendInvoiceInfoTable.RequestStatus),"EREnumDataSourceHandler#VendInvoiceRequestStatus#","",TRUE)
Map the data model record list nodes to the data source tables. In order to retrieve the entire data set instead of the first record.
To retrieve the ledger account and dimension values from the various source documents I used the source document line > accounting distribution > ledger dimension relationship. Via this relationship you can retrieve the account display value, which contains the ledger account and dimension value. @.'<Relations'.SourceDocumentLine.'<Relations'.AccountingDistribution.'>Relations'.LedgerDimension.DisplayValue
Once all fields are mapped, you can test the configuration by pressing ‘run’. This process will download a xml file.
Once your setup has been completed and tested, set the configuration status to complete.
4. Create report layout
Once the report model has been set to complete, the output format of the report can be built. Start of by creating a table in excel with the desired report layout. Make sure you include all the date model fields in your Excel report. After the table has been setup the table columns need to be named. You can name fields or ranges in Excel using the ‘create from selection’ function or the name manager. The name ranges and constants are used in step 5. map data model to report fields. Make sure all fields in your report are ‘named’ otherwise they will not be available in Dynamics.
Once you have the tables and naming setup in Excel, you can import the file to Dynamics.
Select the data model configuration and create a new configuration based on the type format based configuration. Specify a name, description, format type, data model and data model version.
5. Map data model to report fields
Once the report configuration has been created, open the format designer. Now select the import tab and click Upload from Excel. Upload the created excel spreadsheet with both create Excel sheet format elements and Apply Excel data types for cells enables.
This should result in something like this.
Now that all Excel elements have been brought into Dynamics you can map the format design to the data model. Select a format designer field and data model field and click bind to map the two elements. Map the excel table to the data model ‘record-list’ fields.
After all the Excel fields have been mapped you can test your configuration by running the configuration from the main workspace page.
Data transformation using PowerQuery
Once all the required data has been exported to excel via Electronic Reporting the data can be used for accrual and prepayment calculations. An example of an accrual statement would be to only accrue the costs up until the current period for ‘no-po’ supplier invoices which have not been posted.
The above condition contains quite a few criteria already. Let’s split the condition in smaller pieces.
Accrue the costs up until the current period
For ‘no-po’ supplier invoices
Which have not been posted
For ‘no-po’ supplier invoices
Apply a filter to exclude supplier invoices related to purchase orders.
Which have not been posted
Apply a filter prior to exporting the report, to exclude any posted invoices.
Accrue the costs up until the current period
To determine the accrue value you need the reason comment as accrual schema, invoice date as accrual start date and a closed data to determine accrual cut-off date. The reason comment is just a value and doesn’t contribute to our calculations, to use this value we need a translation table. In the Excel report you can create an additional table to translate the reason comment to an accrual. My table columns are ‘Reason code’, ‘number’ and ‘month#’. The rows contain the ‘reason code’, the number of months and the number of additional months from the start date: for each reason code the additional month will start with 0 but will by 1 every month. This means that there are as many rows as there are months per reason code. As shown below.
With the power of PowerQuery you can duplicate the supplier invoice lines by the amount of months using a simple merge query (left outer join).
Expanded the join to apply the join and to bring in additional columns from the joined table. Add the number of months and the additional months per line. These values are needed to calculate the line accrual amount (line amount / number of months) and line accrual date (Date.AddMonths([InvoiceDate],[#"Month.Month#"])).
With these data lines you can complete your report to build accrual and reversal lines.
Once all transformations are complete, the Excel file in Dynamics needs to be updated.
Run report
Run the report and apply the required filters.
To learn more about filter options you can read my previous post. The following filters can be applied:
Pending supplier invoice lines
Invoice journal lines
Purchase order lines
I saved my design of the electronic AP report. You can find the files here.
That's it for now..
Comments