Cash Flow in Microsoft Dynamics NAV 2016
Hello and welcome to The Coffee Mug Tutorials. Today what I wanted to go through is cash flow. We here at Coffee Mug International are very concerned about cash flow, as are most businesses. Cash flow has always been sort of a problematic area because you can get some of the detail for cash flow or cash flow forecast out of the system: AR, AP, and Bank etc.
But there are manual entries that are not reflected on the GL that affect cash flow and we would like to see those manual entries in the system. Normally, we’d just solve this with Excel, but obviously as we know, Excel is great for many things but once it becomes something that you go into every day and update it should belong in the ERP.
Now NAV has come out with a great module - it’s actually a couple of years old - called ‘Cash Flow’, and we are going to go a little bit through that today.
Now I am logged in here as the Accounting Manager; this is the Accounting Manager role, and inside the Accounting Manager role there is a Cash Flow Chart, as you can see right here. And this chart is telling me a few things. For example, over here I see that my Receivables for January 2017 - we operate in the future so it’s a very revolutionary business, everything happens 2017 - so here we have receivables of $953,000.
We got some manual revenue it’s going to be influx of money from investors for $300,000. The expenditures are the payables that we have outstanding and those are due in January for $500,000, and some manual expenses meaning that we actually have to pay out to investors $220,000. And then we got our Purchase Orders that have not been converted into AP, but they are expected to be converted obviously because they are placed, for $535,000. So my Net is close to $0 here in January; we are playing a very close game.
However, in February we see that the receivables are less and the payables are way less, and then there are some manual expenses and manual revenue and our cash is going up. It happens to be early in January so we actually got a notification that we are going to be getting an extension of the credit line. And I want to reflect that in here so I can push this line up. And how does this all work?
Well if I go into Cash Flow, or type in Cash Flow right here, I can get into Chart of Cash Flow Accounts. And I’ve actually set up a cash flow accounts. These are not Chart of Accounts. These are separate cash flow accounts, and they are not touching the GL. There is data that comes from the GL into this, but this is not necessarily reflecting the GL exactly. How do things come into here?
If I go into my Cash Flow Setup, I can see that I tied together the Receivables account - this is all a customer ledger - to Receivables here. Payables to Payables etc. So I can tie together Service, Sales Orders, Purchase Orders etc, so that whenever I actually post into those ledgers it will be updated here through a certain process. And ok, that’s great so far.
So how do we actually change these numbers? If I go into my Cash Flow Worksheet, I have a separate Worksheet to interact with these numbers. If I hit Suggest Worksheet Lines it will take any change in AR and suggest it to update that in here. But, right now I’m actually not getting the AR in here, or AP, or anything like that. I’m putting in a manual entry.
This entry has nothing to do with my GL at the moment, or bank; I just want cash flow to reflect that we are getting a credit line in the future. So it’s going to be 01/15/17. Document is going to be Credit Line - CL0001. My forecast - I have one forecast.
The Source Type is going to be...it’s a Cash Flow Manual Revenue. And the Source is...we can call this in this case, Private Investment, actually, Financial Asset and the account number I want it to hit, let me see. We’re going to get in this case...I’m just going to hit Miscellaneous Receipts, because I don’t have anything for a credit line increase. We are going to get $300,000.
Now when I actually register this, I post into my cash flow. I’m only posting into my cash flow; I’m not posting it to Bank, I’m not posting to the GL, I’m only affecting the Cash Flow. And now if I refresh this, I can see that Miscellaneous Receipts is now $300,000 and if I take a look at my Home screen again, now you can see that the line is not close to $0 anymore. It starts at $300,000 and goes up.
So we are looking good. Another really good aspect is the...if I go to Cash Flow Forecasts...and there is a great screen here called Cash Flow Availability By Periods. If I open this up it shows me how I’m doing. Now these are Net for each month which is kind of nice to see whether each month is positive or negative; we’re looking great here with all positives.
But technically, or most of the time, we want to see the balance; so I can just change that here and now I see my cash flow balance at date. So for the time I have cash flow forecast in, I can see my cash increasing going up to $100,000 in December. January it goes up to $400,000.
You can see here that they call it Cash Flow Manual Expense, it should be Revenue. That might be a typo on Microsoft’s part. But if I click in here I can see my Miscellaneous Receipt for $300,000.
So how does this, how does AR and AP get added into this? If I go back into my worksheet, like so, if I Suggest Lines and I want to see for example just Receivables; I want to get the Receivables in. It suggests the Receivables that I have and they come in here by invoice as you can see.
I can go over this now and say well this is great I might be able to post this to my cash flow, but wait a minute, if I go into this $170,000 here from John Haddock Insurance, I don’t think they are going to pay me on the 9th. I think they are going to be a month late, so I can change this.
I’m going to move that into February, let’s say February 1st. So, here I am actually changing the date that the cash flow is affected even though the invoice due date stays the same. So if I register all of this, now it’s successfully registered. If I go in and look at my availability by periods - this is great. I go into February, let me see and I have the cash flow forecast, oh that’s 2016, I want to go into February 2017.
I go in here and now I can see that that was actually posted on this date and updated. So even though this invoice was due in January, I moved it to February because that’s when I expected it to get paid.
So, all in all, this is a fantastic module to forecast cash flow, and you can interact with it both by getting data from the GL or from the ledgers and then doing manual things, so kudos to Microsoft for solving this problem. Thank you.