opening balance

Dynamics 365 Business Central – A Method for posting VAT Opening Balances when going LIVE mid VAT Period

Gavin WhittakerLeave a comment

Introduction

In previous posts I’ve looked at how I post opening balances for various modules in Business Central. Links to these articles can be found below:

How I post opening balances for the General Ledger in Business Central

How I post opening balances for Fixed Assets

How I post opening balances for Bank Accounts

How I post opening balancing for Goods Receiving Not Invoiced (Expected Costs)

How I post opening balances for Sales and Purchase Ledger.

Today I wanted to discuss how I post opening balances for the VAT Statement when going live Mid VAT period.

Challenges Going LIVE mid VAT Period

If you are going LIVE with Business Central mid-VAT period, some of your VAT transactions will remain in your existing system. As a result, you won’t be able to submit your VAT return through Business Central, as it won’t include all the relevant entries. You could choose to use bridging software for submission, but you also have the option to post an opening VAT position via a journal entry.

This post will walk you through the steps to use a General Journal to record the VAT entries from your previous system enabling you to post your VAT statement from Business Central.

VAT Journal Example

To post the opening position, you’ll first need to gather the VAT figures for each box on the VAT statement from your existing system.

In this example, we’ll assume the following values have been collected from the existing system and are required for the various boxes on the VAT Statement:

VAT Box NumberAmount
Box 1 – VAT due on Sales£10,000.00
Box 4 – VAT to reclaim on Purchases£7,000.00
Box 6 – Total Value of VAT on Sales£100,000.00
Box 7 – Total Value of VAT on Purchases£70,000.00

To accommodate posting the journal we need to ensure we have a large value in the “Max VAT Difference Allowed” field in the “General Ledger Setup”

Then, to record the VAT entries we enter a General Journal as per below

Click to Enlarge

Here’s a breakdown of the General Journal:

Step 1:
Enter the VAT General Ledger codes in the “Account No.” and “Bal. Account No.” fields. This ensures the entries have no impact on the General Ledger.

Step 2:
Select the relevant VAT Posting Groups associated with the boxes on the VAT Statement.

Step 3:
Enter the VAT amounts you want reflected on the VAT Statement.

When I now preview the postings for this General Journal I can see the amounts net each other out so there’s no affect to the General Ledger.

However this journal has created the following VAT entries which can be reported on the VAT Statement.

I’ll therefore post this and view the VAT Statement and I can see the amounts are being picked up correctly.

Conclusion

In the past, bridging software was commonly used when going live with Business Central mid-VAT reporting period. However, this post explains how to post a journal to record VAT entries when moving to Business Central midway through a VAT period.

Thanks for reading!

Dynamics 365 Business Central – How I post opening balances for the General Ledger in Business Central

Gavin WhittakerLeave a comment

Introduction

In previous posts I’ve looked at how I post opening balances for various modules in Business Central. Links to these articles can be found below:

How I post opening balances for Fixed Assets

How I post opening balances for Bank Accounts

How I post opening balancing for Goods Receiving Not Invoiced (Expected Costs)

How I post opening balances for Sales and Purchase Ledger.

Today I wanted to discuss how I post opening balances for the General Ledger. (I perhaps should have started with this one 😀)

I use a couple of approaches when posting opening balances for the General Ledger, choosing the method based on the level of transactional detail required. In this post I’ll discuss all three methods including which one I commonly recommend and why.

Please note in all the methods described below, right after posting the opening balance journal, I print and save a copy of the General Ledger Trial Balance. I also make a note of the G/L Register from the “G/L Register” page in Business Central. (although I name the batch OPENBAL-GL so its easy to spot anyway)

Preparation

For all the methods, I use a predefined configuration package that I export to Excel, ask the client to populate with data, and then import back into Business Central.

This includes key fields like the Posting Date, Account Number, Dimensions, and Amounts. A copy of the configuration package can be found here along with other free stuff 😊

A screen shot of Excel Spreadsheet is below:

Example of Excel Template for Opening Balances

Method 1 – Post Total Opening Balances as of Go Live Date

This method involves posting the balance of each General Ledger code in one journal, using one document number, on the required go live date.

Therefore if you were going LIVE with Business Central on the 31st of May 2024 you’d collect your General Ledger balances as of this date from your existing system and populate the Excel Spreadsheet with this data.

Important considerations should be given to the control accounts such as the Sales and Purchase control account to ensure these balance to any imported Sales and Purchase Ledger transactions.

Once the journal has been imported, and you are happy with the balances, you can post the journal to record the opening balances.

This is the quickest and easiest way to post opening balances for the General Ledger, but it has limitations. The primary drawback is the lack of historical financial reporting in Business Central. In contrast, Method 2, while more time-consuming, provides greater detail.

Method 2 – Post Total Opening Balances and Period Balances

This method involves posting opening balances for a set period prior to your go-live date and then posting additional period balances up to the go-live date, resulting in a final go-live balance.

For example, if you plan to go live with Business Central on 31st of May 2024, and want 12 months’ worth of summary information prior to this, you would post an opening position as of 31st of May 2023, using a document number like “OPENBAL-GL-MAY23.” Then, you would post balances for each of the 12 monthly periods from June 2023 through May 2024 using different document numbers, such as “PERIODBAL-GL-JUN23” and “PERIODBAL-GL-JUL23.” all building up to a total balance as of May 2024.

This approach allows you to report month-on-month using only Business Central data as soon as you start using the system. For instance, after being live with Business Central for June 2024, you could compare June 2023 data to the live data for June 2024.

Method 3 – Posting Opening Balances and Detailed Information

The final method can only realistically be used when the company is very small and doesn’t have lots of transactions.

This method involves posting an opening position followed by the detail information for a set period of time.

For example if your year end is 31st of December 2023 and you wanted to go live with Business Central as of 31st of May 2024 you could post the opening balance from your existing system as of the 31st of December 2023 and then enter each transaction manually from 31st of December 2023 to the 31st of May 2024. (in this scenario it doesn’t really matter when you go live as you are entering detailed information from the 31st of December 2023)

As you can imagine this is the most labour intensive method so is generally only used on companies with few transactions.

** You also have to post opening balances for the sub ledgers as of 31st of December 2023 and then enter detail information. This can be very labour intensive if there are lots of transactions.

Conclusion

All methods provide a solution for posting opening balances into Business Central. I prefer to use Method 2 because, although it requires a bit more effort, it allows for comparison reporting as soon as you go live with Business Central.

Thanks for reading!

Dynamics 365 Business Central – A couple of methods I use when posting opening balances for Sales and Purchase Ledger

Gavin Whittaker3 Comments

Introduction

In previous posts I’ve looked at how I post opening balances for Fixed Assets, Expected Costs and Bank Accounts. Links to these articles can be found below:

How I post opening balances for Fixed Assets

How I post opening balances for Bank Accounts

How I post opening balancing for Goods Receiving Not Invoiced (Expected Costs)

Today I wanted to discuss how I post opening balances for Sales and Purchase Ledger.

The post will focus on opening balances for Sales Ledger, however I follow the exact same process for Sales and Purchase Ledger.

I used to stick with one tried and trusted method however I recently encountered an issue which has made me reassess and try something new.

I’ll detail both methods below including the issue I recently encountered with the first method.

Preparation

In order to prepare for the opening balances, there’s a couple of very important things to check and do.

Firstly, unless there are exceptional circumstances, I will only bring in outstanding Sales Ledger transactions, therefore I always recommend applying all open invoices, credits, payments in the source system. I then ask the client to ensure the debtors report balances to the General Ledger Accounts Receivable code.

Once everything is reconciled in the source system, I provide a data pack in the form of a Excel spreadsheet for the client to complete with their Sales Ledger opening balance transactions that can be easily imported into a General Journal via a configuration package.

I also include a “Readme” sheet as part of the Excel spreadsheet. This has more information on each column included in the data pack, to make this easier for the client to complete. A snippet of the readme is shown below however you can download the Excel Sheet from here along with other Free Stuff 🙂

Its also worth noting the General Ledger opening balances for the Accounts Receivable and Accounts Payable is posted separately via the GL Opening Balance Trial Balance.

Method 1 – Balance every entry to the Accounts Receivable code

Method one involves importing the data pack into a General Journal and then balancing each Sales Ledger opening balance entry (i.e. open Invoice, Credit, Payment etc) to the Accounts Receivable control account. This produces GL entries that will both Debit and Credit the same Accounts Receivable control account. (I do this as the Accounts Receivable opening balance is posted as part of the General Ledger opening balances).

After everything is posted I’ll then check the “Aged Accounts Receivable” report balances to the General Ledger Accounts Receivable code.

For example in the demo company below the Accounts Receivable GL account is 1030 and has a posted opening balance of £12,500.00

Therefore I import the Sales Ledger opening balances from the data pack into a General Journal and then ensure each entry balances to GL code 1030 and the total balances to £12,500.00.

When posted every entry then Debits and Credits the GL Code 1030 making no effect to the GL balance.

As a final check, after posting the opening balance I run the “Aged Accounts Receivable” report and ensure it balances to £12,500.00 and therefore matches the General Ledger balance.

At the end of the process I have a balance on the General Ledger for GL code 1030 of £12,500.00 and a balance on my Sales Ledger of £12,500.00.

Therefore everything balances 🙂

** There is a slight flaw with this which I’ll detail after Method 2 **

Method 2 – Balance every entry to a Suspense Code

The next method follows the same process except you balance each entry to a Suspense Account.

You then add an additional line that reverses the balance in the Suspense Account to the Accounts Receivable GL Code

The GL Entries for both the Accounts Receivable and Suspense net off to £0.00.

The final check would be to ensure the “Aged Accounts Receivable” report matches the Accounts Receivable GL balance just like Method 1.

Issue with Method 1

The issue with method one arises if someone reverses one of the Sales Ledger opening balance entries.

If this happens the GL entries debit and credit the Accounts Receivable GL code, as these were the original entries, which causes an imbalance between the GL and the “Aged Accounts Receivable” report (as the transaction drops off this report).

I’ve historically found this a tricky issue to spot and resolve without significant manual reconciliation.

If you were to reverse an opening balance entry posted using Method 2 this wouldn’t the case. The Suspense Account would be affected, as well as the Accounts Receivable account. Therefore the Aged Accounts Receivable would still balance, and you could also easily spot an issue as the suspense account would have a balance.

Conclusion

Both methods offer good solutions for importing and posting opening balances for both Sales and Purchase ledger opening balances.

Although the issue with Method 1 doesn’t happen often its something to be aware of as it can cause issues following a go live.

Thanks for reading!