Jet Reports Made Easy - Creating an Income Statement
This video provides a quick overview on creating an income statement with Jet Professional or Jet Express. While a Jet report may look complex and difficult to create, the reality is far from that. Setting up a Jet Report is an easy and intuitive process. This video walks you through the steps of using Jet to create an income statement, including setting up the functionality through the application settings in Excel, entering filters and setting up functions, and using the Jet Function Wizard to create a dynamic, actionable report in minutes.
You can view this video and many other video tutorials for Dynamics NAV at ArcherPoint’s YouTube Channel.
A transcript of this video follows for your convenience:
Welcome everyone to Jet Reports Made Easy. My name is Jason Taylor and in this short video, I’m going to walk you through creating your first Jet report.
For this video, I chose an income statement because the GL function is enabled in the free version of Jet. So anybody that’s running Jet Professional or Jet Express should be able to create an income statement.
I’m going to start by opening a new Excel document. In this document, you will notice I have a Jet tab, so Jet Report is enabled and I am simulating Jet Express. So I have all the functions available free to an end user. If you’re curious on how to simulate Jet Express, you can always turn this on inside of your application settings.
Well, for this video, I said we’re going to create an income statement. So let’s go ahead and get started. My income statement is going to be pretty simple. So I will start here in D5. It seems as good as any. My income statement is going to consist of my sales, my COGS and then that’s going to give me my gross margin.
Your report can be as complicated as you want. Here I’m going to keep everything grouped together. The more lines you add, the further you can break this out.
I’m going to want my income statement over two periods. So I will be able to tap my report. I’m going to put in some filters for dates. So my beginning date will be the beginning of the year, end of the period, beginning and end of the next period.
I like to bring my filters outside of my functions and you will see why here in a minute. They make copying and updating a lot easier, especially if you set report options.
Next down here on my sales lines, I need to set my filter for the sales accounts and my chart of accounts, all of my sale accounts begin with four. So the four asterisks should bring back all the accounts that start with that.
My COGS are just as simple. They all start within the five range. All right. Now we’ve got our date and our account filters. We’re ready to build our first Jet function.
We’re going to do this by selecting the cell where we want the value and then coming up to the top and clicking on the function that we want. Here it’s going to be GL.
When we select the GL function, it brings up our function wizard and it says, “Well, what do you want?” In this case, we want the balance of the accounts found here in cell B5. So I’m going to lock down that column by pressing my F4 key. The start date is going to be here in E2 and I will lock down that row. The end date will be underneath it. Again, locking down that row and then if I wanted some additional filters, I could set them in here. So you could do different comparative income statements for your different business groups or customers or any other of the analysis views you may have programmed in.
My current demo system doesn’t have a lot of this. So we will just leave that off and just bring back everything for the 40,000 account range. There we go. It’s actually negative and if we copy that down, we will see that COGS is positive. So obviously the credit/debit side of the balance here. If we come in, we can multiply this by either negative one and affix it or what I prefer to do is just put a negative sign in front of the function and there we go.
Makes that look a little nicer. Maybe you don’t like decimals and let’s just say this is equal to this, minus that. So there’s our gross margin. Hey, not too bad.
Let’s keep going in our report here. Next in my income statement, I have the related expenses. So for our report here, we’re going to have – how about promotion, wages and others? We’re just going to keep this really simple. In yours, you’ll probably have a lot of detail there and as you will see here in a minute, we can specify these accounts by just typing in the account numbers themselves. So if this account was a – consisted of these two or these three, then we can simply type them in like that.
If next wages consist of – let’s say it’s 61250 through the range, then we could simply do it just like that. The other way is if we want everything after that, so other. Maybe all of my random account codes or from 70,000 forward. We just did 70,000 dot dot and that should get us all account codes that are higher than that.
Now I’m going to simply take my function here and copy it down. Copy this function down. There we go and we have the same problem with the negatives. So maybe I will copy the one before instead. So I’m just copying the function and since my cells are locked down, they just come right down.
So we have our sales, our COGS, our gross margin. Then we have all the rest of the expenses associated with our business.
Following this should give us our income before tax. Last we have tax and our tax code for this example is going to be 22300. Once again copy the function down and my demo database has some bad stuff in that account. Maybe it’s that right there.
So now we’re actually negative for the year. Look at that. Either way, creating your Jet report is that simple. Since we have everything locked down, we could now copy and paste this over to the next cell and save all the work of having to do everything twice.
Lastly, we could hide. So if we want to hide these cells, we can by using the keyword “hide”. If you’re not familiar with Jet Reports, then this is a reserved area. You don’t want to put anything in there that’s not a Jet keyword. So one of the keywords is “hide” just like that and now if we come in and press Report, all right, there we go. We have our report. Here’s our new comparative income statement.
We probably want to give it some additional headers, tell us the quarters and things like that. But creating your first Jet report is that easy. We simply put in the filters and then set up our function to use them.
That’s basically all there is to it. We could continue to add to this as needed. We could add another quarter, historical data. We could put in options that would allow viewers to run this. There’s a lot you would probably want to add if this was your report but creating them and modifying them is really that simple.
Oh, also, if you’re wanting to create a new report, you could always download Jet’s pre-built reports and then just go off one of those. This has been Jason Taylor. Happy Jetting everybody!
[Thanks for watching this ArcherPoint video. If you found it helpful, make sure to check out our website and blog at www.ArcherPoint.com. Additionally, if you have any questions regarding our products, services or information in this video, feel free to email us at info@archerpoint.com. Thanks.]
[End of transcript]