Design a financial database

Special considerations for when designing a financial database.

User permissions: Administration > Databases and Sync

When is a financial database not a financial database? You might already have access to a General Ledger (GL) or similar database in Phocas, but unless it was created specifically as a financial database, it will behave as a standard database without any of the financial reporting elements.

It is not possible to convert a standard Phocas database to a financial database, even if the original one contains financial information. You have to flag a financial type database when you first create a database (this is what gives you the default structure required for a financial database).

You need to have the Financial Statements license to be able to design a financial database.

If you have financial data already synced (for your standard database), you might be able to reuse this data to build a new financial database without needing to sync a whole new dataset. However, please read through this page first, as there are a few special requirements to ensure the financial database correctly displays your financial statements.

A financial database opens in the Phocas Financial Statements module. All other types of databases open in the Phocas Analytics module.

A financial database displays consolidated financial statements in an accounting format and integrates account-based streams. It is usually based on General Ledger data displayed in the following financial statements: Profit and Loss (P&L), Balance Sheet, Cash Flow and Trial Balance.

As with standard Phocas databases, generally, the Phocas Implementations team will design your financial databases for you. However, you can design new databases by yourself. The basic process of designing a financial database is much the same as for a standard database, however, there are a couple of important points to note:

• Your data needs to be split between the P&L and the Balance Sheet. It also needs to include sufficient data to allow you to group the accounts in these statements to suit your needs, along with the dimensions (such as Country or Branch) required for analysis purposes. Often the data is categorized already but you can do this in Phocas during the design process.

• You need to map your data quite specifically. Every new financial database opens with a template (see below) to get you started with the data loading and mapping process.

Other differences to the standard design process and points to note are outlined in the table below.

Get your raw data ready

Before you start your database design, the following data needs to be available in the data sources panel in Designer:

1. General Ledger (GL) transactional data. It is easier if your P&L and Balance Sheet data is separated in your raw data file but you can do this in Designer.

   • Note about end-of year P&L roll-ups (clear-downs): When adding GL transactions from your system, the automated Retained Earnings allocations from the P&L should be excluded, as this would cause a double-count (Phocas automatically adds the sum of the P&L to the Retained Earnings account). Any P&L clear-down representing the sum of the P&L transactions for the year should be excluded from the transactions.

2. Opening balances for your Balance Sheet accounts.

3. Chart of accounts.

4. Budget data and statistical streams are useful but not essential. You can add these later.

It is good practice to get your FC or CFO or accountant involved to validate the data.

How Retained Earnings works in Phocas

Analytics forms the base value, which is calculated as follows:

• Retained Earnings appears on the Balance Sheet stream and can either be mapped to an existing GL code via the Settings option in Designer or left unmapped (displayed as Account 'Retained Earnings'). Retained Earnings is the sum of all the P&L transactions within the P&L stream in Designer plus any relevant Balance Sheet transactions. It is calculated as A + B, where: A = Sum of all transactions for all dates for P&L accounts from the database start date to the end of the current period. B = The amount already in Retained Earnings*, such as the opening Balance Sheet value and other manual transactions, such as a dividend payment recorded directly in Retained Earnings.

• *This could be an existing account that is already called Retained Earnings. Or it could be another account that you’ve manually mapped by going into Design > Options during the database setup.

• If you do not map a Retained Earnings account, Phocas will automatically create a Retained Earnings account for you (which will only contain A above).

In Financial Statements, if the financial year end has been set, the Retained Earnings value is adjusted by the Current Year Earnings, which appears as a separate row on the statement.

Phocas expects the raw data to contain typical financial credits and debits, for example, where Revenue, Liabilities and Equity are credit (negative), and Expenses and Assets are debits (positive). If this is not the case, it can be changed during the design process (using the Transform feature) but some features, such as the Cash Flow statement will not provide accurate results.

See Get your data into Phocas and the overview of Designer for more information before you start designing your database.

Learn about the financial database template

In the first step of the design process, where you create the database, you tell Phocas that you are creating a financial database. As a result, when Designer opens, you get the financial database template.

In this template, there are:

• Two streams; the Profit and Loss and Balance Sheet. These are the only streams you need initially.

  • Profit and Loss: This stream must only contain transactions from your P&L accounts. You can apply the filter to exclude any Balance Sheet accounts.

  • Balance Sheet: This stream must only contain transactions from your Balance Sheet accounts. You can apply the filter to exclude any P&L accounts.

  • You will receive a system error if you have accounts in both streams.

• Three placeholder dimensions; Account, Category and Classification. Phocas uses standard categories (such as Sales, Cost of Sales, Operating Expenses, Other Expenses and Other Revenue) and you can change these to suit your needs. You can add other dimensions but the template items are locked. This means you can map data to them but cannot edit, delete or move them. If you clone a financial database, these items remain locked.

• Two placeholder properties connected to the Account dimension; Name and IsCash.

  • Name is the name of the account. For example, an account might have the code X1234 and the name Repairs and Maintenance. When the database is designed and built, this Name property displays by default for users in Financial Statements.

  • IsCash is relevant to the Cash Flow statement and used to group the sum of the cash and cash equivalent accounts. This can be done in Designer or within the Financial Statements module later.

Design the database

Typically, you follow the basic process of designing a database in Phocas (open this page in another tab, to view the information side-by-side). However, there are some key differences for a financial database, as follows:

1. Create the database: You must select the Finance Database checkbox. This presents the financial database template in Designer and connects the database with the Financial Statements module. See the Learn about the financial database template section above. If you do not select the checkbox, you will only get a standard database (even if your source data contains financial information), which opens in the Analytics module.
2. Add raw data files (P&L and Balance Sheet data): Add the GL transaction data file to the P&L and Balance Sheet streams* and add the opening balances to the Balance Sheet stream.

   

   You can also add a budget file to your financial database but this is optional. You can add it later.
3. Map the data to the database: Map the data columns in the GL transaction file to the template elements.

Map the financial data to the template elements

1. Map the Account dimension directly from your GL transaction data.

2. Click the mapped Account dimension, then add the appropriate raw data files (probably the Chart of Accounts).

3. Map the Category and Classification dimensions.

4. Map the Name properties.

Then map the other data to the date, dimensions, properties and measures as required.

1. Customize the database components: After you map the data, you can take some further action to set up the financial database:

Map the Retained Earnings account code

You need to tell Designer which Retained Earnings account code you want to use for calculating Retained Earnings for the Balance Sheet. You do this within Financial Statements. See Map the Retained Earnings account.

Categorize the accounts

If required, you can categorize the accounts in two locations:

Designer: Your data (accounts) can be classified from your Chart of Accounts mapping. If you want to drive your account mapping directly from your ERP, you can create the categories (groups) as required. You might have groupings or sub groupings that can be created from the Chart of Accounts.

Financial Statements (font-end): If you have NOT mapped the categories in Designer, you or other users can do it within the Financial Statements module and map it however they want. See Manage financial statements.

If you have mapped the categories in Designer, any subsequent statements can also be mapped in Financial Statements, as it refers to a separate dimension, not the default Category dimension in Designer.

(Optional) Customize the columns: Use the Transform feature to create a new column containing a simple calculation, join columns or duplicate columns.

(Optional) Change the measure format. For example, you might wan the negative numbers to display in brackets. You can do this in Designer or leave it up to the user to change their own measure format.

Proceed to save and build the database.