xlSync – integration with Microsoft Excel

BizController allows the User to use Microsoft Excel spreadsheets as an assumptions layer for plans and budgets. All assumptions should be divided into logical parts, e.g. by item nature, cost centre, person responsible, etc. and put into separate worksheets or files as needed.

User calculations, properly formatted and registered with the software will be fetched and treated as base transactions for generated plans and budgets. The xlSync Module lets the User register Excel files that contain assumptions for the application to monitor them automatically for changes. The files can be modified by other users if they are put on a network drive. In this way there is no need to use Excel links which often break or send the files by email.

The double entry rule

BizController enforces the double-entry rule as early in the process as possible. In this respect, it is similar to traditional accounting systems. The main difference here lies in the method of entering transactions into the system. Integration with Excel allows the user to formulate base transactions directly on a spreadsheet, and to plan by whole time series at a time.

Each assumption should be exposed to BizController as at least two rows of data, one for the debit entry and the other for the credit entry, following the standard accounting rules. If properly ‘tagged’, these rows will be synchronized with BizController automatically. This approach results in a plan that is more robust, more transparent and easier to audit and maintain, compared to the common ‘spaghetti’ approach in Excel. Later on, the User can quickly see precisely what factors contributed to a change in a given financial statement item that is being forecast by drilling on a report down to those base assumptions.

Arranging assumptions in a worksheet

In practice, it is useful to divide assumptions into logical sections or segments. These can be transaction sources, cost items, cost centres, scenarios, projects, departments, outlets, persons responsible for budgeting and filling in the budget forms, etc. For instance there may be separate worksheets for sales budgets, compensation costs, or separate but identically structured files for different outlets, shops or other profit centres.

BizController can synchronize, normalize, consolidate and produce reports from these sources along several dimensions (accounts, centres, scenarios and period). The application will ensure integrity of the data even when the layout of a spreadsheet is modified, e.g. when a user adds a new row or column, or deletes one. When problems are recognized it will warn the user by flagging an appropriate entry on the list of registered worksheets.

·         Transactions need to balance for each individual worksheet, i.e. the total of credit and debit entries must be equal. If practice it means that the user cannot ‘split’ individual transactions into several worksheets and show, for example, the cost-related entry in one place and the corresponding cash flow entry in another. This limitation improves transparency and control over integrity of all data.

·         In a FREE edition, xlSync scans the first five worksheets in every Excel file and top 150 rows in those worksheets. If more than five separate worksheets are to be synchronized, they should be put in separate files. BizController can synchronize an unlimited number of Excel files with its database.

How xlSync works

xlSync monitors Excel files containing worksheets that the User has previously registered in the system. If they have been modified, it checks if the worksheet tag is present in the first place (<3e> by default). If so, then it attempts to identify valid data headers using the names defined in Project Settings > xlSync as well as period labels. All the headers need to be present in the same row that contains the worksheet tag.

Period labels must follow the syntax: YYYYMXX (for monthly periods) or YYYYQX (for quarterly periods). „YYYY” stands for the year, „XX” indicates the number of the month (two digits: 01, 02,...,12) and “X” indicates the number of the quarter (one digit: 1, 2, 3 or 4).

Small and capital letters in labels are not considered the same.

In this way the program knows which columns contain data for synchronization.

After that, BizController checks which rows below the header row contain valid account numbers or FSI numbers.

Having determined both appropriate columns and rows, BizController synchronizes the financial data that are placed at the intersection of the above. Other cells will be ignored which gives the User substantial flexibility as to where to put the rest of calculations without the risk of collision with the xlSync engine.

·         The order of system headers is not important. Not all the headers have to be used, either.

·         For the data to be fetched, the spreadsheet needs to contain fields for at least AccountID, Description  and CostCentreID (if used) as well as at least one period label. Also, at least two rows should contain valid data.

·         The labels used in spreadsheets should have names that exactly match those defined in Project Settings.

·         Changed data from a spreadsheet are fetched when the User saves the respective file within Excel.