69 - Account Schedules in Dynamics NAV 2017 - Part 4

69 - Account Schedules In Dynamics NAV 2017 - Part 4             

We are going to keep going on account schedules.

Today I want to talk about how we can interact with Excel. And this being a Microsoft product, we can interact with excel in so many different ways. Let's say for example we need to share an account schedule that we created with somebody in our organization that does not have access to NAV but they have access to excel of course, and so we are going to send them an excel sheet for them to look at.

I'm gonna go here into account schedules, so I'm logged in here into account manager in the analysis ribbon tab I can get into account schedules, pick the account schedule I just created and hit overview and then I get straight into the numbers.

So I'm looking at the first quarter of 2018, I’m gonna go into the third quarter of 2017, and I'm gonna change my column layer to be the one I actually created in a previous video which is called 4 quarters. 4Q, and this is looking at the current quarter that I’m in here pretending to be in the 3rd quarter of 2017 and them I'm seeing the previous quarter and the quarter before that, and since we only have three quarters of the year in, obviously I can't see the fourth number here.

Create an excel sheet
I’m gonna go hit actions, export to excel, create a new document. What that does, it takes whatever if created here and creates an excel sheet out of that, and it comes in like this. It tells me we are looking at this particular period and everything is in US dollars and here are the number, now the formatting here is of course not exactly like we want it to be, it is ok, usually when you have things coming into excel you want to do something more with it.

I'm actually going to create a graph so I’m gonna select the header, the sales, and the gross profit, now I have to include the third quarter even though nothing is in here, I’m intentionally including that.I go ahead and hit insert chart and I’ll use the clustered column, I actually want to change this title and call it revenue / gross profit per quarter.

Now I don't want to do this every single time, let's say I create lots of charts with lots of things, all kind of analysis in excel based on this account schedule that I'm dropping in here, and I don't want to constantly be redoing all of that work. So I’m going to save this onto my one drive because I can't share my one drive excel with anybody. This is in the cloud; really a topic for another video, but one drive is really good because I can give a link to someone to this Excel book and they can take a look at it, again we are saving paper.

Update excel sheet 
This is going to be gross profit by quarter, and I'll just save that. Ok. So now it's saved, Ill close it out and let's say or pretend that next quarter passes and now we are in the final quarter of 2017, so I’ll just go into the next period right here and I’ll have the numbers for all 4 quarters, now I need to update my excel sheet and of course I don't want to recreate anything or even be copy-pasting into some kind of template.

It would be just nice to update, and we can do that, I go up here and just click update existing document, just click on that and I go into my one drive and I hit the GP by quarters and it goes in and updates the document. If I go in here and it starts blinking and I click on it, now you can see that it updated the existing document, now I have the current quarter, quarter before that, all the way four quarters all updated on the chart.

Again we just did a little chart here to show that it actually updates, and doesn't change the information in there, you can do much much more.