Design a finance database
When is a finance database not a finance database? You might already have access to a General Ledger (GL) or similar database in Phocas, but unless it was created specifically as a finance 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 finance database, even if the original one contains financial information. You have to flag a finance type database when you first create a database (this is what gives you the default structure required for a finance database).
You need to have the Financial Statements license to be able to design a finance database.
If you have financial data already synced (for your standard database), you might be able to reuse this data to build a new finance 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 finance database correctly displays your financial statements.
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:
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.
Opening balances for your Balance Sheet accounts.
Chart of accounts.
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
Retained Earnings appears on the Balance Sheet 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).
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 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.
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 |
---|---|
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 | 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 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.
You can add a budget file to your finance database but this is optional. You can add it later. |
Map the data columns in the GL transaction file to the template elements:
| |
After you map the data, you can take some further action to set up the finance database: 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. 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.
| |
Proceed to save and build the database. |