Feature of the Day: BAI Format - Inside Microsoft Dynamics GP - Site Home - MSDN Blogs

This is a nice Feature of the Day: BAI Format With bank consolidation going on in the US this comes up more and more.

Feature of the Day: Reconcile Bank Reconciliation to General Ledger - Inside Microsoft Dynamics GP - Site Home - MSDN Blogs

Microsoft is showing off another great Feature of the Day: Reconcile Bank Reconciliation to General Ledger

For those of you complaining because you're still running PowerPoint 2003 and can't open these, get the free PowerPoint Viewer from Microsoft at http://www.microsoft.com/en-us/download/details.aspx?id=13

Weekly Dynamic: Solve for Values - Bank Rec Example

Let's say for example that someone has done a poor job of entering deposits in Dynamics GP. Instead of selecting appropriate receipts and processing a deposit, they selected receipts that don't match the way the physical deposit was made. Now you have a mess trying to balance the bank account. Having to add multiple items to match a single number commonly comes up in Bank Rec. (I don't just mean GP bank rec either. I've seen cases where you have to combine items differently for bank recs by hand, with Quickbooks, Peachtree and Oracle financials. It's a bank rec thing.)

Excel can be a big help when trying to combine transactions to match so we're going to work through an example. One caveat, this method finds one match. It may not be the only combination that can match. You'll need the Solver add in that comes with Excel. Use File-Options-Add Ins to see if it's turned on and activate it if it's not.

1. Key amounts into Excel that look like this:

The Amounts column contains the amounts that you will ultimately use to try to find a match. Indicator is just the number 1 all the way down. We use it in the calculation.

2. In cell C1 type SumProduct(A2:A7,B2:B7). This will show the sum of A x B.
3. In cell D1 type 27.62. This is the amount we are trying to combine items to find. It's here just for reference.

Excel is going to add up the items in multiple combinations until it finds a combination that matches 27.62.

4. Click the Solver icon
5. In Set Objective enter or select $C$2
6. Check Value Of and enter the amount you want to find - 27.62
7. Set By Changing Value of Cells to $B$2:$B$7
8. Click Add. Set Cell Reference equal to  $B$2:$B$7 and the middle field to bin.
9. Click OK. Click Solve.

Excel changes the 1's to 0's except for the lines that add up to $27.62.

To reset to search for another number, change column B back to all 1's, click the Solver icon and change the amount.

Many times when working through a bank statement there is a pattern. Items are off by a day for example. These are pretty easy to find by hand. Sometimes it's a lot harder an you a tool like this.

How to reconcile the Checkbook Balance to the GL Cash Account in Microsoft Dynamics GP

Dynamics GP Support and Services details How to reconcile the Checkbook Balance to the GL Cash Account in Microsoft Dynamics GP

This was timely. The question came up just as this post went up.

Bank Rec with Belinda

MVP Belinda Allen covers bank reconciliation.

MS Dynamics GP How to Reconcile Bank Statement with cleared amount difference. | MS Dynamics GP

David Hobbs demonstrates How to Reconcile Bank Statement with cleared amount difference.

The Top Ten Reasons To Use An Automated Bank Reconciliation

Judy Hirst at Etelligent Solutions offers up The Top Ten Reasons To Use An Automated Bank Reconciliation

Undoing a bank reconciliation after it’s already been posted for Dynamics GP

Doug Pitcher has some SQL code for Undoing a bank reconciliation after it’s already been posted for Dynamics GP.

Very nice Doug!

Bank Reconciliation and Voided Checks | Interesting Findings & Knowledge Sharing

Sivakumar looks at Bank Reconciliation and Voided Checks

The Dynamics GP Blogster: Reconciling unchanged bank statements

Yesterday, Mariano showed us how to Reconcile unchanged bank statements.

In Touch with Dynamics (GP): Dynamics GP Bank Management - Checkbook [checkbook id] is already in user by user [username].

In Touch with Dynamics (GP) provides a solution for when Checkbook [checkbook id] is already in user by user [username].

Weekly Dynamic: Control Account Management for Receivables

One of my favorite unknown features for Dynamics GP is Control Account Management for Purchasing. This allows companies to process AP centrally and then roll down the AP balance at month end to the appropriate departments. A reversing entry then puts everything back into AP.

This is a great feature for companies who want a complete balance sheet for their departments but prefer the efficiency of centralized AP. What about AR? After all, AP is only part of the equation. Well, Dynamics GP 2010 adds this feature for AR as well.

With the addition of AR, the feature moves out of Purchasing and into the Financial Area Page. Setup is still under Setup and users select Payables or Receivables when setting up the appropriate roll out accounts. Processing the split at month end has moved from Routines to Transactions on the Financial Area Page.



I’m thrilled to see this addition. This has always been a surprisingly complete feature. Now, if it only supported intercompany…hint, hint.

Cash Receipts & Bank Deposits

Two different people in the past two weeks have inquired with me about the exact same process, so it seems like a good topic to briefly discuss – the Cash Receipts Entry window & the Bank Deposit Entry window are good, well-integrated friends.

Once a cash receipts batch has posted, one needs to tell Dynamics GP which receipts have been physically taken to the bank.  A cash receipts batch updates the A/R subledger and gives your customers credit for paying invoices due, but it doesn’t also assume that these receipts have been deposited.  The job of recording these deposits at the bank belongs solely to the Bank Deposit Entry window.

The Bank Deposit Entry window doesn’t create a journal entry when the ‘Post’ button is punched, but it does update the Select Bank Transactions window with “DEP’- type transactions so your bank reconciliation task can be finalized.  Paying a stack of bills via Payables Management, for example, places check documents into Select Bank Transactions window once posted, but whenever cash is received – be it from a customer via Cash Receipts Entry or for a non-trade receipt that is processed via Bank Transaction Entry - things aren’t 100% complete until this one extra step is performed.

I hope this clears things up a little.  Time to begin packing for Atlanta…hope all who are headed in that direction enjoy uneventful travels.

 

Bob McAdam is a Manager at Tribridge in Tampa, spending the majority of his time working in the GP delivery area with new & existing customers. He is also an active contributor to the Dynamics GP User Group (GPUG). Bob has been fortunate enough to be involved with Dynamics GP as both partner & customer since 1998, but still remains quite anxious to see what his friend & former colleague Mark Polino has ready with his newest ‘50 Tips in 50 Minutes’  session @ Convergence 2010.

Dynamics GP E-Reconcile

My colleague Amy Walsh has coverage of the electronic reconcile functionality in Dynamics GP in her new post Dynamics GP E-Reconcile up at DynamicsCare.

Only Transactions that have special characters in the Number field are displayed in Bank Statement Reconciliation

Mohammad Adour is wresting with odd Bank Rec issue in Dynamics GP.

Negative Amounts in Bank Rec Window

Amy Walsh has coverage of negative amounts in Bank Reconciliation over at DynamicsCare. Nice Job Amy!

Dynamics GP Bank Rec Table Info

I'm catching up from a few days mostly away from the PC and I see that Victoria Yudin has a nice article covering the Bank Rec tables in Dynamics GP.

Weekly Dynamic: Best Practice, Daily Bank Recons

In his various accounting best practices books like Fast Close Stephen Bragg recommends reconciling bank data on a daily basis. This is actually very easy to do with DynamicsGP and Great Plains. This process will shorten your month end close since you don't have to wait for the bank statement, and it will let you find and fix issues throughout the month, not just at month end. So how do you it?

• Option 1: Use the Electronic Reconciliation module and get a special file from your bank every day. If you're a large company dealing with large banks, this is a great option, but it's not for everyone.
• Option 2: Use your bank's website and normal reconciliation. Admit it, you get your bank balance off of your bank's website now so this is easy, even for little firms with small banks.

Here are the steps:

1. Go to your bank's website, get today's posted balance and print out the daily transactions back to when you last got your statement.
2. Start a bank reconciliation process in DynamicsGP.
3. Enter today's posted balance for the bank statement ending balance and use today's date for the dates.
4. Check off cleared items and balance the statement to a zero difference.
5. When finished, click OK. DON'T CLICK RECONCILE! Clicking OK will save the reconciliations.
6. On the next day, repeat these steps for 1 day's worth of transactions.
7. At month end, verify the totals with the statement and then click reconcile. The month end bank rec process is now minutes, not hours.

There a few potential pitfalls to look out for:

1. There may be a few days at month end where you can't reconcile into the next month until you have received the final statement. In this case you can either rely on the website as the final reconciling document or have a few days to make up early in the next month. Both options have problems, I've seen banks have website issues that cause the statement and website to not match, but this is pretty rare. However, Delaying a few days tends to break the nice daily balancing rythm you develop. There's no perfect option, pick whichever one fits your business requirements.
2. Make sure you know where you left off from the day before. Often bank websites won't let you print just one day, you have to print the whole screen which can overlap days.