917.848.7284Data for Finance and Accounting

Top

FINANCIAL REPORTING AND ACCOUNTING BLOG

I’m in the process of finishing up a data conversion from Lawson to Dynamics NAV 2016. The last piece to figure out are 1099 balances from checks issued before we converted. (We only converted open AP Items, so we don’t have the history necessary to calculate 1099s at year end.) I’ve looked around the web and found a variety of incorrect and partial answers. Many solutions have a lot of discussion about invoices and credits. But I think I’ve found a much simpler way of doing this. While converting 1099 balances is a fairly rare occurrence, you may find it useful for other scenarios as well. (For example, in many systems, I use “bank accounts” for clearing things that aren’t real payments.) But before we start, there are three things you need to remember about NAV and 1099s: 1. If you have paid invoices in the system for the year, it’s easy to update...

Dimensions are a wonderful feature of NAV. However, when doing General Ledger analysis, sometimes clients just want to dump everything into Excel and “play” with it—especially if they use Analysis Views. And to do this, clients want to see all dimensions, at the detail level, for each transaction. But here’s the challenge: You can only view or filter two dimensions directly on the General Ledger entries page. Fortunately, there’s a way around this restriction. How to See All Dimensions for NAV G/L Entries Let’s start by reviewing the problem: We want to see all transactions for a specified dimension, but when we look at General Ledger Entries, we can only see and filter by the two shortcut dimensions. We start with the basic General Ledger Entries page in Cronus, USA.: Out of the box, no dimensions are shown at all. If we choose Columns, we see the two global dimensions, in this case Department Code and Project Code: If...

Since most of my clients have switched to Dynamics NAV (many of them from Lawson), I’ve been busy writing a bunch of SSRS reports that facilitate specific finance and accounting functions my clients require. Here are the reports I’ve completed thus far: Using Totaling Accounts with SQL in Dynamics NAV AR Revaluation Report – Dynamics NAV RapidStart – A Query to Check Your Errors. I have more reports in the works. If you’d like my complete package of free SSRS reports for Dynamics NAV when it’s ready, let me know.    ...

I was at the NAVUG user group in NYC last week. There, I learned that NAV2017 now allows users to cancel posted sales invoices and sales credit memos. While I’m sure some users will welcome these changes (and I’ve yet to see exactly how they’ll work), my first impression is that this isn’t such a great idea. Part of my reasoning is that, honestly, I believe that people who “pay for their mistakes” are more likely to stop making them. But I also hesitate because I don’t like reversing transactions in NAV more generally. Using Reverse Transaction in Dynamics NAV NAV has at least three “reverse transaction” options: General Ledger Entries, Customer Ledger Entries, and Vendor Ledger Entries. When a customer bounces a check, you could usually reverse that in the customer ledger. To better understand my hesitation about reversing transactions, let’s look at a payment reversal as an example. Let’s start with payment 2596 (below) in...

Since 1995, I’ve worked extensively with Lawson software. Until this month, that is, as my last Lawson client is moving into Dynamics NAV. I’ve learned a lot about NAV over the last few years. And while I do miss Lawson at times, Lawson’s time has passed for my core clients (who aren’t in health care or government). Why My Clients Have Moved From Lawson Before I explain, note that my observations about Lawson are largely based on my for-profit clients in the greater NY area. Generally, these clients fall into two large categories: Companies that grew into the multi-billion dollar category As these clients grew, they saw no real added functionality in Lawson and have therefore moved to SAP and Oracle mostly. This move was often driven by the desire to consolidate on a single ERP—and they didn’t feel Lawson had the functionality they wanted or a strong interest in meeting their needs. Mid-market...

In my last series of posts, I wrote about NAV currency setup, entering foreign currency sales invoices and how adjust currency amounts can be run at period end to create gain loss entries. In that last post, I promised to show you a sample report that would allow you to see the gain loss before you actually post it. And here it is. Aged Accounts Receivable in NAV The Aged Accounts Receivable report in NAV has the option to print results in the customer’s currency: If we run this report for Beef House (49525252), and select Print Detail, we see the following: We can see the transaction in euros as well as a report total in USD. Which is all good. However, it’s important to note: The USD total reflects the current rates as of the cut off date for the report whether or not we’ve actually posted gains and losses to the ledger. How do I know...

If you’re like many of my clients, you didn’t clean up your chart of accounts when you installed your new ERP. Or maybe you did but then your business changed. So, instead of having a nice set of account ranges, like those in the sample Cronus companies, you need to create odd groupings of accounts. Totaling Accounts are your first option in NAV. And they work well. However, if you want to use them to write reports using SSRS or another SQL tool, they aren’t that easy to work with. But I’ve built some code to make it a bit easier. A Simple Example—Using “Or” and “BETWEEN” in SQL Let’s start by looking at the chart of accounts in CRONUS USA, Inc. (I’ve applied a filter to show the income statement.) Like most sample companies (and unlike most of my customers), the data is beautifully organized with total accounts having nice ranges. Scrolling down a...

In my previous post, I showed how to perform a month end revaluation in Dynamics NAV with an unpaid invoice. In this post, I’ll show you how to receive cash against a foreign currency receivable. To recap: We have an open invoice that was worth 15000 MXP when we invoiced and 17250 MXP when we closed the month. The peso continued to fall, and it’s now worth 20000 when we receive the cash on November 20th. We start by going to Cash Receipt Journals and make a receipt for the full amount, in this case 1150 USD. We’ll use BO-USD which is our USD bank account. We’ll use account 01454545, which is the bill to account for Spotsmeyer’s Furnishings. We first create a batch, called CURRTEST. We add the receipt: The key fields are: Posting Date: 11/20/16 (to prove the change in rates) Document Type: Payment Document Number: TEST123 Account Type: Customer Account No.: 01454545 Currency Code: Defaults to USD Amount: -1150.00 (because...

In my last post, I entered a sales invoice in NAV in a foreign currency. The invoice was posted on 10/5 and had a value of 1150 USD or 11,500 MXN (or MXP as Cronus has it, which still irks me). Now, it’s month end. As the invoice isn’t paid, we need to perform a revaluation (as it’s known in standard accounting parlance). The NAV system uses the terminology “adjust exchange rate.” (For more on revaluation, see my posts on exchange and revaluation and revaluation and translation.) To start, navigate to Adjust Exchange Rates: Here are the parameters you need: Start period: Leave blank (We’re revaluing AR right now, so we’re looking for anything that’s open) End period: Period end date, in this example 10/31/2016 Posting Description: Anything you want for the entry, here we’re using Test Adjust Exchange Rates Document Number: Here we’re using Test 1, but you should probably develop a naming convention. Adjust Customer/Vendor/Bank Accounting:...

In our previous post on understanding NAV currency, we covered the topic of NAV currency setup, including general ledger, currency and exchange rates. In this post, we’ll continue the discussion by entering a foreign currency sales invoice in NAV. As with our previous post, we’ll use Cronus Mexico for our examples. But before we start, let’s set up our currencies and exchange rates to make our examples super obvious. We begin by navigating to Currencies: And we select USD: After, let’s return to the exchange rate screen: We enter rates for 10/1/2016, 10/31/2016 and 11/15/2016. By entering 100 in the exchange rate amount column, we allow the entry of more decimals in the relational exchange rate column. (I followed the default setup in NAV for this, but it’s your choice.) Now, let’s enter an invoice. If we look up Spotsmeyer’s Furnishings, we see it’s a USD customer: Before we begin, we need to make sure that the Foreign Trade Fast Tab—which...