General Ledger

Dynamics 365 Business Central – How I post Opening Balances for Bank Accounts

20th Feb 2024Gavin Whittaker7 Comments

Introduction

This blog was inspired by a forum post I answered recently that you can find here. The OP had issues reconciling their bank accounts because the opening balance hadn’t been posted. It also follows on nicely from other blogs I’ve posted with regards to posting opening balances. These are below:

How I post opening balances for reducing balance fixed assets and How I post opening balances for Expected Costs

In this blog I’ll show the steps I use when posting opening balances for bank accounts.

Preparation

When going LIVE with Business Central I always recommend reconciling all the outstanding bank entries in the source system. This is much easier nowadays as most payments are made electronically. Therefore there tends to be less “Unpresented Cheques” delayed by either the postal service or waiting to be deposited in the bank. This includes cash received from customers or payments made to suppliers.

If everything has been reconciled, it makes posting the opening balance much easier and cleaner.

Method for Posting the Opening Balance

When going LIVE with Business Central, I’ll usually have been provided with an opening trial balance, which includes the reconciled bank account balance which has been posted to the GL. I’ll use this as the basis of the bank account opening balance journal.

Its worth noting you could post the bank account opening balance at the same time as the GL opening trial balance, however I prefer keeping the two entries separate. Also, there are situations where an opening trial balance might not be available, whereas the opening bank position has been provided. (in the forum post the client had gone LIVE without a bank opening position, and this method can also be used to post a retrospective bank opening position)

For the bank account opening balance, I then need to post an entry that posts a Debit and Credit to the same Bank account GL code, but also creates a Bank Account Ledger Entry.

Worked Example

In this example I have a bank account that has an opening balance of £1000.00, which has already been posted to the GL. Therefore the GL code has a balance of £1000.00, however the Bank Account has a £0.00 balance.

As the GL has the correct balance, I need to post an entry that only updates the Bank Account Balance.

As mentioned above, to do this I’ll create a journal that debits and credits the 40050 code, but also creates a Bank Account Ledger Entry I can reconcile.

Therefore I’ll post the journal below, that posts to the Bank Account and balances to the Bank Account GL code

This creates GL entries that both debit and credit the Bank Account GL code but also a Bank Account Ledger Entry that hits the Bank Account

After posting this I have a Bank Account Balance that matches the GL balance

Conclusion

This is a quick and easy method for posting opening balance entries to Bank Accounts. This can be done either during or after you receive the General Ledger opening balances.

I hope this helps when posting your opening balances or retrospectively fixing any issues with the bank account.

Thanks for reading!

Dynamics 365 Business Central – Configuring Partial Exemption VAT using standard features

25th Jan 2024Gavin WhittakerLeave a comment

Introduction

If your business is partially exempt for VAT, you’ll need a method for tracking your VAT entries, so you can post adjusting entries to the VAT return.

In this post I’ll go through the basics of what being partially exempt from VAT means, and how you can configure Business Central to accommodate this using standard features.

** Please note there are specific non-recoverable VAT features in Business Central which I don’t cover in this post however those features compliment this configuration rather than replace it.

Normal VAT

Normally, a business would supply goods or services and charge VAT (output VAT) and also buy goods or services and pay VAT (input VAT). Then, at the end of the VAT period, the business calculates whether they need to pay HMRC (i.e. the output VAT is more than the input VAT) or reclaim VAT from HMRC (i.e. their input VAT is more than their Output VAT).

This is easily setup using standard Business Central VAT Product Posting Groups as per below:

What is Partial Exemption for VAT

The above description of Normal VAT assumes all supplies the business makes are subject to VAT (i.e. all supplies the business makes are taxable at 20%, 5%, 0% VAT). These rules don’t apply when a business makes both taxable and exempt supplies. When a business makes exempt supplies, they can’t reclaim all the input VAT on all their expenses.

The rules for claiming input VAT when you make exempt supplies and taxable supplies are as follows:

Input VAT Type	Rule
Input VAT that relates directly to exempt supplies	None of the Input VAT can be reclaimed
Input VAT that relates directly to taxable supplies	All of the VAT can be reclaimed.
Input VAT that can’t be directly attributed to exempt or taxable supplies	A portion of the VAT can be reclaimed.

With regards the final point, this relates to expenses like accountancy fees or rent. Basically any expenditure that is incurred for the business to operate. The business can only reclaim a portion of the incurred VAT for these expenses, with the amount that can be recovered being calculated based on the exempt and taxable supplies during the specified period. (worked out as a percentage)

Business Central Configuration

Now we know the basic rules around partial exemption, it becomes clear that if we qualify as partially exempt for VAT, we need a way of easily identifying whether our expenses relate to exempt supplies, taxable supplies, or are general business expenses when entering them in Business Central. (basically where they sit according to the table above)

Therefore, the first step is to add more VAT Product Posting Groups to our configuration.

We then need to add the the new VAT Product Posting Groups into the “VAT Posting Setup” as per below (note the new VAT Product Posting Groups still calculate VAT at 20%. This doesn’t change, only the amount of VAT we can reclaim is affected).

Using this new configuration, when entering a Purchase expense, and we can recover all the VAT, we should use the “VAT20” VAT Product Posting Group. When entering a Purchase expense, and none of the VAT can be recovered, we can use “PNVAT20” and when we can partially recover some VAT we should use “PRVAT20“.

Therefore, if we had a Purchase Invoice for Office Expenses, which we know we can only reclaim a portion of the VAT back, we’d use the PRVAT20 VAT Product Posting Groups as per below. This would then be easily identified in the VAT Entries.

VAT Entries – Example

Now suppose we have entered transactions to the different VAT Product Posting Groups to get VAT entries as per below:

As we have posted our purchase expenses using the correct VAT Product Posting Groups we can easily identify the following:

We have £60.19 posted to PNVAT20. We can’t reclaim any of this VAT.
We have £45.86 posted to PRVAT20. We can reclaim a portion of this VAT.
We have £88.85 posted to VAT20. We can reclaim all of this VAT.

VAT Adjustment

Let’s say we have reached the end of the VAT period, and the transactions shown above are the only transactions we have posted.

We have also performed our partial exemption calculation, and we can only reclaim 10% of the VAT posted to the PRVAT20 VAT Product Posting Group.

This means of the £45.86 posted to PRVAT20 we can’t reclaim £41.27. (90% we can’t reclaim)

We also can’t reclaim any of the £60.19 VAT posted to the PNVAT20 VAT Product Posting Group.

Therefore, we’ll need a combined adjustment of £101.46 to the VAT account, which we can offset against an irrecoverable VAT expense account. Therefore we would post a General Journal as per below:

When posting this journal we should also use the FULLVAT VAT Product Posting Group so we have a VAT entry that will be picked up the VAT Statement reducing the amount we can reclaim. (an adjustment to the VAT statement configuration may be needed for this if the FULLVAT Vat Product Posting Group isn’t on the VAT statement)

Conclusion

This is one method you can use for partial exemption in Business Central. It involves creating different VAT Product Posting Groups to post your purchase expenses against depending on whether the VAT will be fully recoverable, partially recoverable, or unrecoverable.

Its worth noting that Business Central does have additional irrecoverable VAT features that I’ve haven’t covered in this post. This includes a change to the VAT Posting Setup window giving you the ability to automatically post a portion of the VAT to an irrecoverable VAT GL code. I might do another post on that in the future 🙂

Thanks for reading!

Dynamics 365 Business Central – A closer look at the Net Change, Balance at Date and Balance FlowFields on the Chart of Accounts page

12th Jan 202424th Jan 2024Gavin WhittakerLeave a comment

Introduction

The chart of accounts page is one of the most frequently used pages in Business Central. It can provide a wealth of information, offering unique insights into the balances on GL accounts, at the click of a button.

When showing new users the Chart of Accounts page in Business Central, I always go through how the Net Change, Balance at Date and Balance columns work and what information they provide.

In this post, I will delve into the details of each column, highlighting their significance and how they work.

Add the Balance at Date Column

By default the Chart of Accounts page only displays the “Net Change” and “Balance” columns. Therefore the first thing to do is to personalise the page and add the “Balance at Date”.

You can do this in the usual way as per below:

FlowFields and FlowFilters

Providing some context, the “Net Change,” “Balance at Date,” and “Balance” columns featured on the Chart of Accounts page are referred to as FlowFields. This means they are calculated fields, and the values they display are influenced by any FlowFilters that are added to the page. In this case the FlowFilter is any filter added to the “Filter totals by” option on the filter pane as shown below

Here I’ve added a date filter which will directly affect the values displayed in the “Net Change”, “Balance at Date” and “Balance” columns.

Lets see them in action

To describe how each column works I’ve posted the following transactions in January, February and March, each for £100.00

Without any filter, all columns will show £300.00, however I’ll then add a filter for February in the “Filter totals by” as per below

As you can see each column now has a different value, so let’s go through what each is showing:

Net Change:- This is only totalling the transactions posted within the filter. In this case we have a filter of 01/02/24..29/02/24 and we have one transaction for £100.00 posted in this range, therefore the value shown is £100.00. If I drill down on this value the system opens the “General Ledger Entries” page with this filter applied showing the entry:

Balance at Date:- This is totalling the balance at the end of the filter. In this case the end of the filter is 29/02/24 and I have two transactions that match this criteria, one in January and one in February, both for £100.00. Therefore the balance shows £200.00. Again, if I drill down on this value the system opens the “General Ledger Entries” page with this filter automatically applied showing the entries that make up the balance:

Balance:- This disregards the filter and shows the total of the account regardless of the filter. In this case the balance shows £300.00, as I have three entries totalling £300.00. Once again, if I drill down on this value the system opens the “General Ledger Entries” page, but this time with no date filter applied:

Hopefully this demonstrates how each of the columns work and how when you drill down on the values the system automatically applies filters to the “General Ledger Entries” page showing the detail of the calculation.

How about filtering by Dimension

In this example you can see how the values are being influenced by a FlowFilter entered using the posting date, giving useful insights into the balances of your GL accounts over different periods of time.

However, what might be even more interesting is rather than using date, you could use your Global Dimensions, which are also available as FlowFilters. (this is why its important to choose the correct Global Dimensions when configuring Business Central, as I explain in this video series)

In my demo system I have “Department” and “Customergroup” as Global Dimensions so I could apply a FlowFilter on one of my Departments to filter the values for just one department. For example below I add a filter on the Department SALES to filter values that have been posted to the SALES dimension.

Here, at the click of a button, I can real time insights into values posted to the SALES dimension.

Conclusion

The chart of accounts page perfectly showcases the FlowFilters and Flowfields highlighting how you can get insights into data very quickly and easily.

These filters are available throughout Business Central on pages like Customers and Vendors, granting users immediate access to essential data without the need for complex reports.

Thanks for reading!