The financial perspective includes arguably the most important measures of a business. We judge the actions and metrics of all other perspectives based on the effect that they have on the financial situation. Financial reports, such as the balance sheet, the income statement, and the cash flow statement, are universal measures of a company. These reports are used by outside investors, creditors, and the government, and there is a standard way that they are presented.
Accountants use standardized bookkeeping practices to record the financial data. Although we don't have to learn everything that they know about bookkeeping, we do have to understand the basic idea of what it means. For example, we have to understand how to interpret debits and credits in the data that originates from the accounting software. We also have to understand whether a measure is calculated over a certain period or based on an accumulated total. We review a financial data model that will consider these points and makes it easier to calculate financial metrics.
When we develop a QlikView financial perspective, we have to be ready for a challenge. The task is made even more arduous due to the static nature of the reports to which the business users are accustomed. QlikView is a data discovery tool and not a static report builder. Therefore, we need to add metadata to the data model that helps us to format these reports. We also review a few areas where we can take advantage of QlikView to visualize otherwise simple tables.
In this chapter, we will review the following topics:
- The data model for the financial perspective
- Metadata to format reports
- Standard financial reports
- Expenses and other financial indicators
Let's get started and review the data model that we use to create our financial perspective in QlikView.
The data model for our financial perspective is similar to our sales data model. Let's load the data model and review it.
For this exercise, you need to perform the following steps:
- In the
Ch. 3folder of the book's exercise files, copy the container called1002.Financial_Perspectiveto theQDFfolder located on your computer. By default, theQDFfolder will beC:QlikSourceData. - In the
QDFfolder, open theVariableEditorshortcut in the0.Administrationcontainer. - Click Container Map Editor.
- In the container map table, go to first empty line, and under the
Container Folder Namecolumn, enter the name of the new container that we just copied,1002.Financial_Perspective, into the input field. - Continue along the row and enter the
Variable PrefixasFinancialand theContainer CommentsasContainer for Financial Perspective. - Click the Update Map and create Containers button located at the top-left of the container map table, and when prompted, click Update Container Map.
- Save the QlikView file.
If we open 1.ApplicationFinancial_Analysis_Sandbox.qvw and look at the data model then we can review the following data model.
Similar to the data model for the sales perspective, the one that we use for the financial perspective contains a fact table surrounded by dimension tables. In the fact table at the center of the model, we store the following events:
General Journal (GJ) entries record all financial information. For example, different GJ entries are created to reflect the financial effects of a sales invoice, a purchase invoice, or a bank deposit. We can also create journal entry directly, without any supporting document.
A GJ entry consists of two types of numeric values: debit, and credit. Each entry assigns a debit or credit amount to two or more General Ledger (GL) accounts in such a way that the total debit amount always equals the total credit amount. The following diagram shows a general journal entry for a sales invoice:
Whether an account is debited or credited depends on the normal balance of the account. For example, GL accounts that measure sales have a normal credit balance. So, if we want to increase the value of sales, then we would credit the account. Inversely, if the customer cancels a sale, we decrease the value of sales by debiting the account.
As keeping track of debits and credits can become confusing, we simplify the handling of debits and credits in the data model and calculate a third field called [GJ Amount]::
[GJ Amount] = Debit – Credit
The following table shows the [GJ Amount] values for the previous GJ entry. At first it may seem counterintuitive that we increase sales with a negative amount, but we will talk about how to handle the sign of the Amount field when we talk about the data model's dimensions tables.
Similar to the sales data model, a GJ entry is a discrete event. Other than the date dimension, the financial data model does not have many dimensions. Let's take a look at the few dimensions that regularly describe GJ entries in the following table.
Tip
We can also encounter a financial data model that is based on a recurring event that measures the balance of each GL account on a monthly basis. We will look at this type of data model in Chapter 5, Working Capital Perspective.
|
Dimensions | ||
|---|---|---|
|
7Ws |
Fields |
Comments |
|
What |
|
This is the most important dimension that describes the GL accounts that correspond to the GJ entry amounts. We use it to identify the GL account type and how we should handle the amount in the reports. Great financial analysis is made easier when accountants precisely define and use a list of GL accounts called a chart of accounts (COA). |
|
Who / Where |
|
This is a field that usually defines the business department or unit to which a certain cost or expense can be assigned. The cost centers can be based on segmented numbers that, for example, define the company with the first two numbers, the branch with the next three numbers, and the department with the last three numbers. Revenue is described by a similar dimension called a profit center. |
|
What |
|
Project accounting is important to determine the cost and possible income of any business endeavor. Like this field, there may also exist other high-level groupings that are important to the company. |
|
When |
|
We record the exact date of the GJ entries. Our financial budgets are defined on a monthly basis, so we assign a budget to the first day of the month. |
|
Metrics | ||
|
7Ws |
Fields |
Comments |
|
How many |
|
This field is the result of subtracting the credit amount from the debit amount. |
The data model for our financial perspective is a slight variation of the star schema. As the AsOfCalendar dimension table is not directly linked to the Facts table, but rather, they are linked to other dimension tables; this data model is called a snowflake schema. We prefer to use the star schema, but we've kept two dimensions separate so that we can explain their purpose better in the next two sections. Even though we create an additional link in the data model, the small size of both dimension tables means that there will be no perceivable change to the application's performance.
The
GLAccountsGroup table contains information on how to organize and format the financial reports. The field called Account – Factor is of particular importance because it helps determine how to handle the sign of the GJ Amount for the reports. For example, if we sum the sales amount directly from the GJ Amount field, we will get a negative number because the GL account for sales has a normal credit balance. However, when we look at this number in a report, we want to see it as a positive number. So, we multiply the sum by the number in Account – Factor in order to change the sign of sales.
In general, the first digit of a GL account number indicates the account type and whether we need to change the sign of the amounts assigned to it. The following diagram shows the normal balance of the principal account types according to a common numbering scheme and the value we will store in Account – Factor:
Along with Account – Factor, we also store information about how each financial report groups the GL accounts differently. Unlike customer and product groups in the sales perspective, GL account groups are not only informative, but they are also an essential part of financial analysis. We must take care to verify each account's grouping with an accountant, or else we risk creating erroneous analysis.
Finally, we also include information about how we want to format our financial reports in the same table. We assign a particular format to each group and calculation. By defining that information in this table, we maintain the report's format much easier than if we defined the format directly in the QlikView object:
Let's review the data that we store in our GLAccountsGroup table in more detail. Each of the following numbers corresponds to one or more columns in the previous diagram:
- The first column defines the report that corresponds to the grouping or calculation that define in this row. In this case, we have three reports: an income statement, a balance sheet, and a cash flow statement.
- In the next column, we include the text description of the account grouping or calculation.
- Here, we define the order in which each concept must be displayed. We choose numbers in increments of a hundred so that we have room to insert new concepts in between two others without having to reassign the value of every other concept.
- Account groupings are usually defined by a range of GL accounts. We use
intervalmatch()in the script to link this table with ourGLAccountstable. For more information onintervalmatch()review the QlikView help documentation where you can find a great example of how it works. - We define factor to be negative one (-1) for all accounts with a normal credit balance. We also apply a factor of negative one (-1) to every calculated group that includes a credit account. For example, we use negative one (-1) as a factor for gross profit because it is the sum of income (a normal credit balance) and costs (a normal debit balance). In doing so, we obtain the following results:
- If income is greater than costs, we have a profit. As income is a normal credit balance, we first see this as a negative number. As we want to see profit as a positive number, we multiply it by a factor of negative one (-1).
- Otherwise, if costs are greater than income, we have a loss. As costs are a normal debit balance, we first see this as a positive number. As we want to see loss as a negative number, we multiply it by a factor of negative one (-1).
- We decide whether we want the account group's description and to appear in bold (
<b>), italic (<i>), or bold italic (<b><i>) . If we want the text to be normal, we leave the cell blank. - We use indentation to help users recognize any group hierarchies. The number represents the amount of spaces that we will use to indent the group's text description.
- We can color normal text dark grey and important text black. A good dark grey to use is
rgb(128,128,128)orDarkGray(). - Finally, we leave the option to highlight certain rows with a background color.
Once we define the financial report metadata in the data model, we can then easily format our financial reports. We can also use this technique to maintain the format of any other legacy report in QlikView. Before we create our first financial report, let's look at one other element in the data model that facilitates financial analysis.