917.848.7284Data for Finance and Accounting

Top

FINANCIAL REPORTING AND ACCOUNTING BLOG

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...

I’ve done a lot of work with currency accounting from a systems perspective. Now that I’m working in NAV, I thought it would be useful to explain how NAV works with foreign currency amounts in a series of posts. In this series, I’ll cover the following topics: General Ledger Setup Currency Setup Exchange Rate Setup Entering a Foreign Currency Sales Invoice Revaluation (i.e. revaluing the open amount at period end) Receiving Cash Against a Foreign Currency Receivable. In this post, I’ll cover the first three topics: how to set up the general ledger, currency and exchange rates. I’ll use Cronus Mexico for all my examples. But before we begin, a couple of clarifying notes: With currency, you need to determine if you are transacting in a foreign currency (i.e. billing customers and paying bills in a foreign currency) or whether you are consolidating multiple entities across currencies. Transacting is the more common scenario—companies typically...

If you know anything about NAV, you know it doesn’t really have a hard month end close. So, apologies for the somewhat misleading title of this post. However, month end close remains a topic for discussion for folks switching to Dynamics NAV from other systems (like Lawson) that have a more traditional, hard period end. Consequently, clients will ask for my advice on how to implement some level of period end control in NAV. (Yes, NAV does have a year-end process. But I’ll save that topic for another post.) To help these clients, I’ll walk them through the following period end control options: Accounting periods Date control in General Ledger Setup Date control in User Setup. In this blog post, I’ll outline these options for you as well. In addition, I’ll share with you a query/report you can use to look for un-posted transactions. (Indeed, for those of you who already understand month end,...

Our consultants write hundreds of SSRS reports each year. Now that we’re working with Dynamics NAV, we want to use our SSRS and SQL skills without having to train everyone in the intricacies of C/AL development. While C/AL uses SSRS to render reports, we only see value in going native C/AL when reports are tied to database updates or are a key part of a process—such as test reports or posting printouts. As I covered in Understanding the NAV Database, one of the challenges in working with NAV is that each company has its own set of tables. And this is one area where C/AL comes in handy. Take, for example, “Purch. Inv. Header” (for posted purchase invoices). Using the Cronus sample companies, we have three different tables: When writing a query for SSRS, you can deal with this in several ways: You could write your report for just one company (which works fine...

For the past few months, I’ve been spending a lot of time creating reports with both Jet Reports and SSRS, mostly with Dynamics NAV and a little with Dynamics GP. If you’re a NAV customer, you have some version of Jet included with your purchase. Like all folks with SQL server databases, Dynamics folks use SQL Server and therefore have access to SSRS. So which is better? Or rather, what works best in any given situation? In considering this question, let’s focus neither on regular reporting (sales order, inventory, etc.) nor financial reporting. Jet is clearly better suited for financial reports than SSRS. And if Jet runs out of steam, you’re often better off finding another financially oriented tool rather than going to SSRS. Instead, let’s focus on a few other factors that might nudge your decision one way or the other: Ease of Learning for End Users As I’ve said before, the biggest ERP...