Dimensions, Create a Budget in Microsoft Dynamics NAV                      

So this is a follow up video on the Dimensions that I did just recently. There I explained how to set up dimensions for coffee regions and how that posts, and how we can use analysis views to take a look at the breakdown.

And I’m actually going to keep going with that and show you how to create a budget in NAV that is based on dimensions. I’m kind of jumping the gun a little bit as I haven’t shown you how to make a budget based on GLs – which is the standard way – but dimensions are just a lot more fun than GLs so I’m just going to take the liberty of showing that.

Recapping the previous video
Now to show what I had done before, just to recap.

Click on Dimensions.

I created a new dimension called Coffee Region, right here.

Click on Dimension Values.

And the values are the regions of the world where coffee comes from.

Then I posted a transaction or a Sales Order that generated sales and I set up Analysis Views so I go to Analysis by Dimensions to take a look at that.

So here we go, Sales Revenue – and you can see one of the dimensions here is Coffee Region.

If I go to Edit Analysis Views.

I want to just see things in the year 2017, again we are in the future – it’s nice in the future.

Click on Show as Lines.

I want to see the Lines as Coffee Region. Period is as Columns. 

Show Matrix. 

And now I can see that in January I had sales of Mexican, Colombian and Hawaiian coffee and that’s really the only thing that has happened. 

Now what I was trying to show on the previous demo and I think I sort of failed on that is the Customer Group.

So I select Show as Columns, another dimension.

I had Business Group which we didn’t have any data for. I added Customer Group and we’ve created some values for Customer Groups and assigned that to the Customers.

And I hit OK and I show the matrix here. 

Now I’m no longer looking at Period, I’m looking at Customer Groups, looking for the origin of the coffee and I get no data here…why? 

This thing tends to reset my date so, just a little nuance, I correct the Date Filter range to 12-31-17.

Show Matrix.

Look at that, now we have data. 

So just keep your eyes open for all these little parameters; if you’re not getting data into your matrix it’s probably because of some filters that are either incorrectly set or automatically set to something that you might not want.

The reason why the filter jumped into February 2017 is that’s where I had my work date but that can be easily fixed just by entering the filter there.

Now when you ask a Sales department to do a budget, you can’t hand them the GLs and tell them to budget the GLs because they don’t care about GLs.

So what you would want to do is give them a list like this. All right, tell me how much you are going to sell; of what type of coffee; to what type of customers.

That might be something they could fill out and they could of course take a look at last year etc. But what I’m going to do right now is just very preliminarily put in a few numbers into this matrix as budgets. And that’s the fantastic thing about dimensions is that you can budget them.

Creating a Budget
So what I’m going to do is go into Budgets – again I’m jumping the gun a little bit because I’m not going to do a GL budget, I’m just going to do a Dimension budget – but that is done in the same way as GL budgets. 

So I just go in here, I go into my GL Budgets, I’m going to create a new one.

It’s going to be a budget for 2017. And notice I have budget dimension. So I’m going to just pick the coffee region dimension.

Ok, so in this case it’s telling me hey it’s a Global Dimension so you’re going to get it anyways so you don’t need to specify it here. That’s fine. 

So I’ll just take that out. Let me just make sure that my Customer Group is in there; that’s not a Global Dimension so it’s going to want to see that.

So in these budgets, you’re always going to get the Global Dimensions and I mentioned what the Global Dimensions are in previous demo.

Then you add on more dimensions – the Shortcut Dimensions or even further dimensions in these columns here. You can 4 additional dimensions.

Then I just go into Edit Budget.

Now it’s sort of important here, you have to know GLs. You can’t get away with not knowing what a GL is even though you’re budgeting by dimension because ultimately this will hit a GL.

Budgeting a GL Account
So what we’re going to do is take a look at the GL here, this is the standard GL.

And I am just going to budget one GL account – it’s the revenue account. So it’s the Revenue and I have something called Sales of Retail, this one.

I just want to budget this one. So how do I do that? This is 44100, I’m just going to copy that here.

I have something called Filters down here and I’m going to just filter that one.

Now I’m only looking at that, all right. I hit Previous Column here to get January 2017 because I’m just going to budget January and February for argument’s sake.

And Show As Lines.

Now that I’ve fixed GL, what I want to see as Lines is not GL accounts but Coffee Region.

Voila.

And for Show as Columns, maybe for now I’m just going to do the Periods.

Ok so let me just close this out so I have more space here, and in January (remember sales are negative, they are credits) we’re going to do 10,000 Costa Rica, 5,000 Guatemala, Brazil 7,000, Colombia 15,000, Ethiopian maybe 7,000.

Kenyan 900 and Hawaii 6,000 and then we’re going to have a sale on Jamaican coffee so we’re probably going to do 19,000 here and the other ones we haven’t started carrying yet so I’m going to leave them at zero.

Ok, so I’ve now budgeted how much we’re going to sell of each category for January. All right. I could actually export this to Excel, do the numbers there and import back from Excel. That’s not the scope of this, but I could do that. All right now so we’re done.

View Analysis by Dimensions
And what I want to do now is go back to my Analysis Views (Analysis by Dimensions) and take a look at that.

Select Sales Revenue.

If I just hit Edit here.

I can see that it is including budgets.

Just going to update it. It is wise to have it updated regularly. I actually have it set to Update on Posting so whenever I post it gets updated.

These things generate transactions in the background to maintain the integrity, so it’s always good to make sure that it’s updated before you take a look at the numbers. 

So now I hit Edit Analysis Views.

What do I want to Show as Lines?

I want to see the Coffee Region as Lines.

Show as Column: Period.

Let me just make sure I’m in the right date again, 12-31-17. I am only looking at the revenue and I’m going to show here the actual just so you can see.

Let me just copy this Date Filter because it’s probably going to get wiped out.

Show Matrix.

Now we have what we had before. Sales in Colombia, sales in Mexico, sales in Hawaii.

View Budget Dimensions
If I go back out and I can change to Show Budget.

Ok show me the budget. Show Matrix.

Now I see the budget that I just keyed in. Notice that it’s coming in positive, why is that? 

Because I have it set to show the opposite sign here.

Show Variance
All right, now I can actually go ahead and say well show me the Variance.

If I look at this now I see well I’m under 10,000 in Costa Rica, under 5,000 in Guatamala, actually I’m ahead in Mexico because we didn’t budget anything for Mexico. Colombia I still have 11,500 to go, 7,000 etc. 

Show Variance Percentage
I can actually look at variance by percentage.

Show Matrix.

And then I’m 100% off in Brazil, but I’m getting there in Colombia, 76.67%.

So that’s how you tie the budgets into Analysis Views in NAV.