Practically all Lucanet customers make at least partial use of Excel for their reports. A commonly used method to retrieve Lucanet data in Excel is the LN.VALUE formula. But how do you ensure that you build a robust report that also looks good? We offer six tips for making optimal use of the LN.VALUE formula. Read them below or watch our video.
A good example works wonders. Make sure you ask your consultant for a sample report and briefly review it together. Arithma offers its own customers a free sample report. You can request this by contacting our support department.
Once you have installed the Excel add-in, you immediately have access to the documentation. You can open it directly within Excel:
It is very useful to consult this documentation when you start using the LN.VALUE formula. In addition, you can also analyze the function directly in Excel. There, each variable is explained and it is indicated whether the variable is mandatory or optional:
The LN.VALUE formula works on the basis of 7 mandatory and 6 optional variables. We recommend fixing a number of the overarching variables on a cover sheet or front sheet. These can then be referenced in the sheets where you want to retrieve the figures. Think for example of the data dimension(s), valuation dimension(s), and period. This ensures that these overarching variables can easily be changed centrally.
Data can be retrieved based on names or based on Object IDs (OID). The latter is necessary if item names occur multiple times in Lucanet. You can easily retrieve the OID of multiple elements by using the ‘Copy as link’ option in Lucanet:
These links can then be pasted into Excel as plain text using the ‘Paste special’ option:
Many Lucanet users prefer to calculate subtotals in their Excel reports using Excel formulas instead of retrieving them directly from Lucanet. When you do this, we do recommend building in checks to verify that the (sub)totals match what is shown in Lucanet.
With the LN.VALUE approach, the Excel report template is also the output. It can therefore be stored separately from the Lucanet.Financial client. This is different from the Tilde reporting / ~Reporting methodology. We still recommend storing an LN.VALUE file in Lucanet. This ensures that everyone can access it and that Arithma’s Lucanet.Certified professionals can easily provide support if necessary.
Would you like to discuss how you can connect Lucanet to your Excel reports? Please contact us!