Design a finance database

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

A finance 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 finance databases for you. However, you can design new databases by yourself. The basic process of designing a finance 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 finance 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.

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.

Learn about the finance database template

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

Open

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 finance 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 finance database, as outlined in the table below.

Step in the basic design process	Differences or points to notes for finance database

Step in the basic design process	Differences or points to notes for finance database
Create the database	You must select the Finance Database checkbox. This presents the finance database template in Designer and connects the database with the Financial Statements module. See the Learn about the finance 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. Open *You need to separate your P&L data from your Balance Sheet data. You will receive a system error later, if you have accounts in both streams. If this data was already separated in your raw data file, you can add the file to both streams, then filter the data in each stream to display the applicable data. If this was not already done in your raw data, you can do it using the Transform column feature. You can add a budget file to your finance database but this is optional. You can add it later.
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. Map the Account dimension directly from your GL transaction data. Click the mapped Account dimension, then add the appropriate raw data files (probably the Chart of Accounts). Map the Category and Classification dimensions. Map the Name properties. Map the other data to the date, dimensions, properties and measures as required.
Customize the database components	After you map the data, you can take some further action to set up the finance database: Set the Retained Earnings account code You need to tell Designer which Retained Earnings account code you want to use for the purpose of calculating Retained Earnings for the Balance Sheet. See Set the Retained Earnings account code. 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.

On this page

Related pages

• Designer

• Transform a column in Designer

• Analyze financial statements in Phocas