Plataan

How to use “Balance” in Recurring Journals

In the Recurring Journals of Dynamics NAV, there is a Recurring Method "Balance". This is probably the least used method, but according to the standard training material and the help files, this method could be useful to redistribute general expenses to (for example) a cost centre at the end of the month.

An Example: a company purchases from time to time office supplies. Because all the departments are using these office supplies, the purchase is posted to a general dimension. At the end of the month the "general" office supplies could be redistributed by using Recurring Journals.

Description	Debit	Credit	Dimension	Allocation
Purchase office supplies during month	1000		GENERAL
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"		1000	GENERAL
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	600		ADM	60%
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	300		SALES	30%
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	100		PROD	10%

 

This is the theory, but it's not always obvious to put theory into practice. However, in this case it's not that difficult. But when I first tried this, it didn't work out as I wanted because of the following reason. And this reason is a requirement in order to use the above principle in a successful way:

It is important only to use the general dimension during the posting of the office supplies during the month.

For example: the accountancy department purchases office supplies that will be used only in their department. Normally this purchase could be posted directly to the dimension ADM. But if you do this on the same G/L account as you would post the general office supplies, at the end of the month while redistributing using the Recurring Journal, also the ADM office supplies will be redistributed using the allocation key.

The way most users want it

Description	Debit	Credit	Dimension	Allocation
Purchase office supplies during month for general purpose	1000		GENERAL
Purchase office supplies during month for Accountancy Department	200		ADM
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"		1000	GENERAL
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	600		ADM	60%
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	300		SALES	30%
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	100		PROD	10%

 

The way NAV does it

Description	Debit	Credit	Dimension	Allocation
Purchase office supplies during month for general purpose	1000		GENERAL
Purchase office supplies during month for Accountancy Department	200		ADM
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"		1200	GENERAL
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	720		ADM	60%
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	360		SALES	30%
Recurring Journal: Method "Balance" – G/L Account "Office Supplies"	120		PROD	10%

 

You notice the difference?

An alternative is using 2 G/L accounts for office supplies: one for the assignable expenses and one for the non-assignable expenses. It would be the last one that you then can redistribute using a recurring journal.

Plataan GOLD Certified Partner for Learning Solutions

It is with great pleasure that we can announce that Plataan has achieved the GOLD CERTIFICATION status.

Gold Certified Partners represent the highest level of competence and expertise with Microsoft technologies, and have the closest working relationship with Microsoft.

We explicitly want to thank our team (the MCT 's : Microsoft Certified Trainer) for their commitment, knowledge and hard work over the last three years. We also want to thank our training customers (about 150 customers after three years, both domestic as international customers). We want to thank Microsoft and all our vendors and contacts.

Use Live search as your calculator

Did you know that you can use Live Search as your calculator?

In the search window enter a formula like 10*5, then press ENTER. The result is:

Unleash the power of Excel Data mining

Did you know that you can download and install a Microsoft Office Excel Data mining Add In? Once installed you get an extra ribbon in Excel called Analyze:

In this ribbon you will find buttons you can use to analyze your data in Excel. You can use the Data Mining algorithms to detect Key Influencers, Categories, Forecasts, Exceptions … So by exporting your data towards Excel you are able to use these Data Mining capabilities to learn from your data and get to know your business even better.

Example:

Starting from Microsoft Dynamics NAV 5.0 you have the possibility to export data towards Microsoft Excel, with 1 mouse click. Open for example a Customer List form and click on the Export to Excel button:

Result:

You then need to format this data as a table:

As soon as you have done this, the Analyze ribbon becomes available:

Then you can start Data Mining. Let's for example do a Category Detection for our Customers:

A popup window appears asking for which columns to use to detect categories. For this example I will use the columns proposed by the wizard itself. After clicking on the Run button he starts analyzing:

At the end you get an extra page in the Excel workbook containing a report that shows the results of the Data Mining Analysis:

In this example, 5 categories of customers were detected, and by filtering the table below you can see what types of customers were detected by Excel, by analyzing your data. And in the original excel table an extra column was added showing the category:

This is how simple it is to use Data Mining technology in Microsoft Excel.

Requirements:

1. You need to have Microsoft Office Excel 2007.
2. And you need somewhere on your network (or desktop) a running instance of Sql Server Analysis Services. The data mining Add In uses this for his calculations.

Of course the Data Mining algorithms are based upon Mathematical and Statistical formulas, meaning that the more data you have, the more precise the results will be.

If you are convinced about the advantages of Data Mining and you would like to go deeper, you can also use Microsoft Sql Server Analysis Services. With Analysis Services you can also do Data Mining, but there you have many more possibilities.

Would you like to know more about the Data Mining possibilities of Excel or were to download this free add in, then go to this website:

http://www.microsoft.com/sql/technologies/dm/addins.mspx

Reporting Services 2008: Introducing Tablix

In the past we only had these containers: List, Table, Matrix, Chart in Reporting Services. In Sql Server 2008 there's a new container, a Tablix: a Table and a Matrix in one.

On the left is a table with fixed columns and dynamic rows. On the right is a matrix with dynamic columns and rows. But what do you do if you want to mix dynamic and static columns like this? With tablix, it's easy.

Example:

Tablix gives you all the features of both a table and a matrix. You can mix and match behavior of fixed or dynamic as needed. In addition, you can group data more flexibly. Groups can be nested and recursive just as in SSRS 2005, but you can also place groups adjacent to each other.

To summarize:

Matrix is dead, long live Tablix!

The Sql Server 2008 Report Designer

In Sql Server 2008 Reporting Services, the Report Designer had changed a lot. Here's a screenshot of the new Report Designer:

You can drag global expressions from Built-in Fields onto the design surface directly. Use the Insert ribbon to add report items to the design surface.

Editing parameters, data sources, and datasets is made very easy in the Data pane. Just right-click the object and click Edit.

You can still drag and drop fields from the Data pane onto a data region. Each textbox also includes smart tags that you can use to select a field quickly.

Expression placeholders are intended to show you as much information as possible. Parameters indicated by @ symbol and built-in fields indicated by & symbol. Dataset fields use no symbol. A complex expression is shown as <<Expr>>.

As you can see the Report Designer now has a ribbon at the top and zoom capability (right bottom), just like in Microsoft Office. And like I explained in the previous Blog post, you can minimize this ribbon by double-clicking on it.

How to minimize the ribbon in Microsoft 2008 applications like Office ?

Are you also sometimes a little bit annoyed when working with Microsoft Office 2007 with the ribbon toolbar? Sometimes it just takes up to much space, don't you agree?

Well, when I was on a training this week I heard about a very nice feature that allows you to minimize this ribbon toolbar. I was following training about what's new in Microsoft Sql Server 2008, and in the new Report Designer for Reporting Services 2008, there's now also a ribbon, like in Microsoft Office. And when you double-click on it, it disappears and reappears again when double-clicking on it again. This seems to be a standard feature of all ribbons in Microsoft Office, Sql Server,…

This is how you do it. Just double-click on the toolbar menu and what do you see: It minimizes. When you double-click on it again, it reappears.

How cool is that?