Working with Excel Add-in

1. Start Excel. 'Exact Financials' is added to the default menu bar.
2. Choose [Login database]. The login screen appears, in which you can use Exact by entering your normal user name and password.
3. Before you open or create a workbook, you should start [Login database], so that the Exact database can be approached and the extra functions are shown in the section 'user-defined'.

This can have several causes:

• You work with a so-called local configuration, with a 'single-user' license and you are already logged in on the database with Exact Financials. In this case you need to close Exact Financials.
• You cannot approach your database if the ODBC connection does not work properly. Consult your ODBC documentation or contact your system manager.

You can generate data from Exact in Excel using functions. These functions are explained in the next chapter. This chapter explains how you can enter a function in a worksheet and which resources can be used.

A function consists of:

• The '=' sign
• The function name
• Data that determine the use of the function. In this manual, this data is called parameters. Examples of parameters are general ledger account numbers, the financial year and the company number.

  Parameters are separated by so-called 'list separators'. It is recommended to use a comma or a semicolon. You can indicate this in [Settings, Control Panel, Regional Settings] in Windows.

Example

The function for calculating a general ledger account balance is as follows:

=ACC_BAL(acc_nr,co,FY,period,balance_type,value,calc).

The parameters indicate which data should be used, like the account number, the company number(co), the financial year (FY) and the period.

1. Select the cell in which the formula should be entered.
2. The Exact functions are available if you use the function wizard in Excel and choose 'User defined' in the 'Function Category' screen.
3. Select the function you want to use based on the data in this on-line help file.
4. Click 'Next' to enter the parameters for the function.
   The parameters determine which data is to be retrieved and how it is to be used.
   • To enter the function directly into the worksheet without entering the parameters, choose 'Finish', instead of 'Next'. You then need to enter the parameters manually. Some parameters should be entered with their numerical value. If you use the wizard, you can choose the actual values.
   • Note that not all parameters are directly visible, use the scroll bar to see more parameters.
5. Click 'Finish' to enter the filled function into the worksheet.

Example

You want to insert the balance of the general ledger account 8000 'Revenue accessories' in period 12 of 2002. Use the function = ACC_BAL(acc_nr,co,FY,period,balance_type,value,calc). First, select the cell in which the revenue amount should to be entered.

Activate the Exact function wizard, choose 'ACC_BAL' and click 'Next'. Then enter the following data:

Click 'Finish'. The chosen balance is shown in the worksheet. The formula entered in the activated cell is:

=ACC_BAL("8000",100,2002,12,1,3,1).

• Combining cell references with parameters.

  Instead of a parameter you can use a cell reference of the cell the parameter is entered in. With this your worksheet becomes very flexible.

  Example

  To retrieve the account balances of period 1 of all revenue accounts in column B. Enter the period number in cell B4 and refer to cell B4 in the functions. To retrieve the balances of period 2, you should copy column B to column C and enter period 2 in cell C4.

The function =ACC_BAL(acc_nr,co,FY,period,balance_type,value,calc) is used for retrieving account balances.

• Cell reference can be locked so that it does not change when copying. In the above example, the reference B4 will automatically change into C4 when copying. If you do not want this, you can lock the reference B4 by changing it into $B$4. This is called an absolute cell reference.

Additional remarks on refreshing data

If you want to see the changes in Exact Financials in Excel, Excel does not offer the possibility to update the data directly. The data will only be updated after closing and reopening the spreadsheet. However, by using references to one cell in all formulas, you only need to change the value in this cell to update the spreadsheet. This cell can, for instance, contain the company number.

Example

See also:

[Alphabetical overview of parameters]