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:
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:
Ch. 3
folder of the book's exercise files, copy the container called 1002.Financial_Perspective
to the QDF
folder located on your computer. By default, the QDF
folder will be C:QlikSourceData.
QDF
folder, open the VariableEditor
shortcut in the 0.Administration
container.Container Folder Name
column, enter the name of the new container that we just copied, 1002.Financial_Perspective
, into the input field.Variable Prefix
as Financial
and the Container Comments
as Container for Financial Perspective.
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.
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:
intervalmatch()
in the script to link this table with our GLAccounts
table. For more information on intervalmatch()
review the QlikView help documentation where you can find a great example of how it works.<b>
), italic (<i>
), or bold italic (<b><i>
) . If we want the text to be normal, we leave the cell blank.rgb(128,128,128)
or DarkGray()
.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.