Dynamics 365 Business Central

Dynamics 365 Business Central – How to quickly split the balance of a General Ledger account across Dimensions using a Recurring General Journal

Gavin WhittakerLeave a comment

Introduction

At the end of a period it maybe necessary to split the balance of an General Ledger account across dimensions. This might be useful if you accumulate costs in a General Ledger account and only split those across cost centres at the period end.

In this post I’ll go through how this can be achieved quickly and easily using the Recurring Journal.

Recurring Journal

The recurring journal in Business Central provides the means of handling repetitive journals that occur on a regular basis.

One example might be a monthly depreciation journal or recurring revenue and expenses. (although I’d hope anyone would be using the Fixed Assets module for monthly depreciation 😊). Typically you’d be using the same or very similar amounts so would use a “Recurring Method” of “Fixed” or “Variable” for those journals.

However there is a “Recurring Method” of “Balance” that provides the ability to transfer the balance of an General Ledger account to another General Ledger account, or more interestingly, split the balance of an account across dimension values.

For example, let’s say I’ve been accumulating “Office Supplies” expenses under one General Ledger code and at the end of the month I want to split this by departmental cost centres.

This see how this works with a Recurring General Journal.

Worked Example

Through the month I’ve accumulated £1000 worth of costs in the “Office Supplies” account.

If I look at a “G/L Balance by Dimension” you can see I have no Dimensional analysis.

At month end I now want to split these costs across my departments. To do this I open the “Recurring General Journal” page, select the “Balance” recurring method, and pickup the “Office Supplies” account. (Note I don’t enter any amount in the journal)

Next we’ll allocate the balance across the “Department” dimension. I want to allocate 20% to the Sales department, 50% to Production and 30% to Admin.

To do this click “Allocations” in the ribbon and pickup the Office Supplies General Ledger account and enter 20% percentage and click “Dimensions”

I then select “Department” as the Dimension Code and “SALES” as the Dimension Value.

I repeat this for Dimension Values “ADM” and “PRODUCTION”, each time entering the Office Supplies General Ledger account, then clicking “Dimension” in the ribbon, and picking up the relevant dimension value.

I now have the allocations I require as per below.

Now when I preview post the journal I can see how this will now be split over the various departments

Finally when I view the “G/L Balance by Dimension” I can see the split across my Department dimension

Conclusion

Although I could have used a normal “General Journal” for this entry, this method shows how you can utilise the “Recurring General Journal” to split costs across dimensions, without needing to know the actual balance of the account therefore reducing errors and saving time.

Thanks for reading!

Dynamics 365 Business Central – How to manage different VAT rates when posting Sales Prepayment Invoices

Gavin WhittakerLeave a comment

Introduction

In Business Central you can post a Sales Prepayment Invoice if you want to raise an invoice before shipping the goods or supplying a service.

Its therefore used when you require your customers to pay up front or pay a deposit before you supply the goods and services.

For this to work you specify a Sales Prepayment General Ledger code for the posting group combination in “General Posting Setup”. (this tends to be a Balance Sheet Liability account). The system then posts to this General Ledger code when you raise the Sales Prepayment Invoice. (incidentally this entry is reversed when you post the Sales Invoice and the relevant Income code is updated)

However this poses a problem if the goods or services you sell have different VAT rates, as when you raise the Sales Prepayment Invoice it uses the “VAT Product Posting Group” on the Sales Prepayment GL Account for VAT calculation purposes.

In this post I’ll go through the issue in more detail and also the solution I’ve used to resolve this.

Sales Prepayment Setup

Please note this doesn’t cover all of the Sales Prepayment Setup. You can find that in the Microsoft Learn site here

First I’ll create a General Ledger Code which we’ll then select in the “General Posting Setup” as the Sales Prepayment Account. When setting up the General Ledger Code, you specify the relevant posting groups, including the VAT Posting Groups. As the vast majority of the time it will be using standard VAT this is the posting group I’ll select as per below:

I’ll then select this in my “General Posting Setup” as the “Sales Prepayment Account” for all my posting combinations

I’ll now raise a Sales Order with 100% prepayment with Standard VAT and this works fine:

However what if I’m selling another line with a Zero VAT Product Posting Group:

When I try creating the Sales Prepayment Invoice I now get the message “The Prepayment account is assigned to a VAT Product Posting Group where the VAT percentage is not equal to zero….” as shown below:

I’m getting this error because there is now a mismatch between the VAT Product Posting Group we assigned to the Sales Prepayment General Ledger Account, and the VAT Product Posting Group on the Sales Line.

Incidentally you would also get this error message if you had one single line item on the Sales Invoice with a VAT Product Posting Group of ZERO.

A Workaround

You can workaround this error by creating a second Sales Prepayment General Account and assigning it a VAT Product Posting Group of ZERO and then tweaking the General Posting Setup as I’ll demonstrate below.

I’ll therefore create another Sales Prepayment General Ledger Code with a VAT Product Posting Group of ZERO

Next I create two new General Product Posting Groups called PREPAY0 and PREPAY20 and then set their combinations in the General Posting Setup page up as per below:

Now when I raise the Sales Order I just need to select the correct “General Product Posting Groups” for each line so they use the relevant Sales Prepayment Account.

I’ll therefore select “PREPAY20” for the line that has VAT and PREPAY0 for the line that has ZERO VAT.

When I try and post this it works, and when I look at the General Ledger Entries I can see both my Sales Prepayment General Ledger accounts are being used.

Conclusion

Although I’ve had to create an additional Sales Prepayment Account this does provide a method of posting Sales Prepayment Invoices for items with differing VAT Amounts. (I could also post a journal at month end to transfer the balance from account to another using a journal)

It also solves the need to have to change the “VAT Product Posting Group” on a Sales Prepayment General Ledger account if you post Sales Prepayment invoices with different VAT amounts.

Thanks for reading!

Dynamics 365 Business Central – How to post a Payment in one company and apply to an invoice in another company using Intercompany functionality

Gavin Whittaker3 Comments

Introduction

A client has multiple companies set up in Business Central, and trades with the same customers in many of these companies. Occasionally, a customer would pay cash to one company even though the Sales Invoice has been posted to a different company. They would make several adjustments in both companies to reconcile this. However, upon investigation, I found that this can be managed using the standard Intercompany functionality.

Pre-requisites

To demonstrate the solution, we’ll be using the Intercompany functionality. This needs to be set up beforehand, but the setup process won’t be covered in this post.

Scenario

As mentioned previously the scenario is that we have two Business Central companies and these companies trade with some of the same customers.

The companies in Business Central in my scenario are called “Cronus Intercompany Company A” and “Cronus Intercompany Company B”

In this scenario, we have raised a Sales Invoice for £1000.00 in “Cronus Intercompany A” as shown below

This shows outstanding in the Customer Ledger Entries on the account of “Joe Bloggs Trading Ltd” in Cronus Intercompany A.

The payment to pay the Sales Invoice is then deposited by “Joe Bloggs Trading Ltd” into the bank account of “Cronus Intercompany B” rather than “Cronus Intercompany A”.

Solution using Intercompany Journals

In order to resolve this we need to do two things. The first thing is record the payment in Cronus Intercompany B and the next is close (apply) the open Sales Invoice in Cronus Intercompany A.

Therefore we will first record the cash in Cronus Intercompany B, using an Intercompany Journal as per below:

A quick summary of the steps above are as follows:

  1. Select a Document Type of Payment and Account Type of Bank Account and then pickup the relevant Bank Account. Then enter the value of the cash payment, in this case £1000.00
  2. Balance the line to the Intercompany Partner
  3. Enter a Intercompany GL account. (this will be changed to the customer “Joe Bloggs Trading Ltd” when we come to post the Intercompany Journal in “Cronus Intercompany A”)

The General Ledger Entries we get when posting this journal are as follows:

We also get a Bank Account Ledger Entry we can reconcile when we come to do the Bank Reconciliation

Once this has been posted we can log into “Cronus Intercompany A” and the Intercompany Journal is initially showing as per below:

I’ll now change that second line to the Customer “Joe Bloggs Trading Ltd” and also click “Apply” and apply the journal to the posted Sales Invoice.

When I look at the General Ledger Entries I can see this is hitting the Intercompany GL Account and also Crediting my Accounts Receivable (Debtors Control)

I also get a Customer Ledger Entry and Detailed Customer Ledger Entries to close the Sales Invoice on the customers account.

Therefore after posting this the customer account is as per below:

The process is now complete. The payment has been recorded in Cronus Intercompany B and applied to the Sales Invoice in Cronus Intercompany A.

Conclusion

This is a unique scenario however using the method described and demonstrated above we have shown how its possible to post a Cash payment in one company and apply this to a Sales Invoice posted in another company in two simple steps.

Thanks for reading!