Import an opening balance | Odoo Accounting

Introduction to Opening Balances

Hi dear doers and welcome to this video on importing and opening balance. If you've previously worked with a different accounting software before transitioning to Odoo, you'll need to import your opening entries so that your account balances start from the right place.

Transitioning to a new software usually consists of four parts:

• The general ledger, which is simply the balance of each account.
• The open receivables and payables. This means any unpaid invoices, bills, or credit notes.
• The bank balance. And we'll show an example with just one bank account, but the process is the same even if you have multiple bank accounts.
• And the current inventory accounts. Since we'll be using a manual inventory valuation, we don't have to worry about Odoo automatically creating journal entries when we import our inventory, so we'll leave that out of this video.

Before importing, it's important to follow a few best practices. It's important to transition using the data from the end of the fiscal year or the end of a tax period. The general ledger must be balanced, meaning it must have an equal value of debits and credits. The total of your accounts receivable on the general ledger must equal the sum of your open or unpaid customer invoices minus any unpaid customer credit notes. And the total of your accounts payable on the general ledger must equal the sum of your open or unpaid vendor bills minus any vendor credit notes.

So can we just import the general ledger as it is currently in our old system?

The Suspense Account Strategy

So unfortunately, we can't just import the general ledger exactly as it is. When we create an invoice or bill in Odoo, it automatically hits the account receivable or payable. This means that if we import the account receivable balance and we create the open invoices, we'll have double the desired amount in our account receivable. And we can't just remove the accounts receivable and payable from the general ledger since then it wouldn't be balanced. So to solve this, we'll use a suspense account that will just temporarily hold that balance.

We'll just replace the receivable and payable accounts on the general ledger with this suspense account. And on the invoices and bills, we'll make sure that they also hit that same suspense account so it's balanced and we don't affect any income or expense accounts.

Next, we run into a similar problem with the bank account. If we just import directly to the bank account, we won't have anything to reconcile the opening bank transaction against. To fix this, on the general ledger import, we'll simply replace the bank account with the bank outstanding receipts account, because we have a positive balance in our bank account. Otherwise, we'd want to use the bank outstanding payments account.

Okay, now that we've gone through the theory, let's put it into practice.

Preparing Your Import Files

We'll start by opening our spreadsheet. In this first sheet is what we exported from our old system, and the accounts that need to be changed are highlighted in red. Then in the second sheet, balance Odoo, we have that same information but ready to import into Odoo. We have the date, the journal, which we'll create just for this opening balance so that everything is transparent, and the reference. Then we have the journal items, one line for each account with its balance. And the account names are here just for reference, but we'll identify the accounts by their account number in the previous column instead of by name. Finally, we have the debits and the credits. Note that you cannot have a debit and credit on the same line, so we've just left the payables and receivables as two separate lines. You'll see we have also replaced the bank account with the outstanding receipts account so that we can reconcile. So we will import this sheet into the journal entry model, but first let's take a look at our other sheets.

In the receivables tab, we have one line for each invoice with the total amount due in column F. Columns A through F will have your data, but column G should be the same or something similar to indicate where each invoice is coming from, and column H must have the code of the same suspense account used in the previous sheet. We'll import this sheet to the customer invoice model.

And the payables tab is more or less the same. The sequence in the number column starts with 'Bill' instead of 'INV' and the reference column lists the purchase orders from our old system instead of the sales orders. We'll import this sheet to the vendor bill model.

What if I don't have anything to put in the reference field? So no problem, that's actually an optional field. So now let's go to our database and see what it looks like to import our opening entries.

Importing the General Ledger Entry

So we'll start by creating a new journal that we'll call 'opening balance' just so we can keep all of this separated and clear. So let's go to Accounting > Configuration > Journals and create a new one: Opening Balance. And it's going to be a miscellaneous type journal and we'll give it the short code OPB.

Now we can get started with that general ledger import. Again, we're going to import that as a journal entry. So let's go to Accounting > Journal Entries and we'll select the action menu here and import records. We can upload our file and then we'll select the correct sheet, so that's 'balance Odoo'. Now because of how we've named our columns, Odoo automatically knows the right fields to map them to, with the exception of this account name. But like I said before, we're identifying the account by the code in the previous column, so we don't want to import this column and we can leave it as is.

It's important to always test your import before actually importing in case there's some error with the file. But looks like everything's good here, so we can go ahead and import it. Now we can find this journal entry that we've created by getting rid of this 'Posted' filter, since it's still in draft. Next, we can open it, take a look, everything looks good, so we'll go ahead and post.

Next, let's take a look at our general ledger and we'll see that it almost looks like the one from our old system. But we still need to import our invoices and bills to get the receivables and payables looking right, and we need to reconcile our opening bank balance to move this value to the actual bank account.

Importing Open Invoices & Bills

So next, we'll import the invoices. To do that, we can go to Customers > Invoices and again we'll use the action menu to import records. Upload that same file and we'll just go to the 'Receivables' sheet. Again, Odoo is matching the fields for us automatically, so we can go ahead and test. And since everything looks valid, we can import.

Now we'll look at one of these invoices. We'll take a look and see that we have the customer, we have the invoice and the due dates, and we have a single line for the total amount due.

Can we have all of the invoice lines instead of having them all combined into one? So while it is possible to include all the lines of each invoice, the return on the time it takes to create the detailed invoices is really minimal, so this is the best solution for the vast majority of businesses.

And then if we look at the 'Journal Items' tab, we can see that we are hitting the receivable account called 'Customers' in the Belgian localization and the suspense account. So we'll go back to the list view, select all, and post all at once. Great.

So this process is exactly the same with the open bills. So we'll go to Vendors > Bills, action button, then import records. We'll upload that same file and go to the 'Payables' sheet. We can test it, everything looks good, so we'll import. And just like the customer invoices, we can select all of the bills at once and post them.

Reconciling the Opening Bank Balance

So let's take another look at our general ledger. And we can see that we do now have those balances in our customers and suppliers, the receivable and payable accounts. And that 499 suspense account is fully balanced. So all that we have left to do is create our opening bank transaction and reconcile.

So I'm just going to copy this bank amount for our opening balance and we can go back to the accounting dashboard. And while we could connect our bank account to automatically synchronize and create our bank transactions, we're instead going to just manually create this opening transaction. So we'll click on 'New Transaction'. We'll add a label of 'Opening Balance' and we'll set it for April 1st. And for the amount, we will paste in that amount. We can go ahead, save and close. And now we have the option to reconcile one item.

And here we'll see our opening entry is suggested as the top entry. We can select that and then click 'Validate'. And now we can go back to that general ledger, and you'll see that the outstanding payments account is balanced, the bank account has its balance, and our general ledger looks just like the one we took from our old system.

Final Summary

And that's it. While there are many minor details that can add complexity to importing an opening balance, this method is perfect for those who just need a standard solution and it provides a great jumping off point for anyone who has more complex needs.

That's all for this video. This has been your pal, Al. I'll see you in the next one.