We use the following user story to understand the needs of the business users that require a balance sheet.
As an executive, I want to understand the overall financial health of the business so that I can create the necessary strategy to ensure its future.
The balance sheet is a complete analysis of a company's financial situation. It is the sum of all GJ amounts divided into three principal groups: assets, liabilities, and capital. The income statement from the previous section is a small part of the balance sheet that is classified as Retained Earnings in the capital account group. The following is an example balance sheet:
Unlike an income statement where we only see financial movements over a certain period of time, a balance sheet shows us an accumulated total of all the financial movements that have occurred prior to the selected month. Another requirement is that total assets must always be equal to the sum of liabilities and capital.
Also, we often divide assets, liabilities, and capital into smaller account groups that permit us to perform a deeper financial analysis of the company. For each group we calculate its percentage contribution with reference to total assets or total capital and liabilities. Finally, variation is calculated between consecutive periods. Year-over-year analysis is less common because seasonality is not as important for the balance sheet as it is for the income statement.
We create a balance sheet in the same way that we create an income statement. Let's create one in the next exercise.
We start to create our balance sheet by cloning the income statement that we created in Exercise 3.2 and then go through the following steps:
- Change the placeholder expression to the following code:
only({1<[Account - Report]={'Balance_Sheet'}>} [Account - Order]) - Change the expression that calculates the current month to the following code:
sum({$<[Account - Report]={'Balance_Sheet'},[Month]=,[Year]=,_MonthSerial={'<=$(=max(_AsOfMonthSerial))'}>} [GJ Amount]) * only([Account - Factor]) - Change the expression that calculates the percentage contribution liabilities to the following code:
sum({$<[Account - Report]={'Balance_Sheet'},[Month]=,[Year]=,_MonthSerial={'<=$(=max(_AsOfMonthSerial))'}>} [GJ Amount]) * only([Account - Factor]) / sum({$<[Account - Report]={'Balance_Sheet'},[Account - Concept]={'Total Assets'} ,[Month]=,[Year]=,_MonthSerial={'<=$(=max(_AsOfMonthSerial))'}>} Total [GJ Amount]) - Replace the remaining two expressions in the cloned chart by repeating steps two and three to calculate the previous month's balances. In doing so, we change the set analysis that refers to
_MonthSerialfrom{'<=$(=max(_AsOfMonthSerial))'}to{'<=$(=max(_AsOfMonthSerial)-1)'}. - Add variation and percentage variation columns as shown in the example balance sheet.
- Change the set analysis in the sort expression so that it refers to
[Account -Report]={'Balance_Sheet'}.
When we create data visualization that supports a balance sheet, we tend to analyze the ratio between amounts. For example, a metric such as return on assets, which is the net income divided by the average total assets, tells us how well a company uses its assets to earn a profit. Another example is the acid test ratio that divides current assets, such as cash, accounts receivable, and short-term investments, by current liabilities, such as accounts payable. This ratio tells us how well the business can cover short-term liabilities. Similar to these there are numerous other ratios that the accounting department may use to evaluate the current financial situation of a company. You can find out what a certain financial ratio means and how to calculate it at http://www.investopedia.com/.
The actual visualizations for these ratios are often quite simple. In part, this is true because The balance sheet has relatively few dimensions that pertain to it. Fields related to company and time are usually the only applicable dimensions that are available. Even so, it can be difficult to calculate them in QlikView and we often calculate financial ratios for a selected moment in time using set analysis. However, when we add the AsOfCalendar to the data model, we have the ability to analyze how they change over time.
In the next exercise, let's make a simple line chart that shows how return on assets behave over time.
- Add the following variable that includes two parameters that allow us to see the end-of-month balance of any concept. The first parameter defines the concept, and the second determines whether the balance is from the current month or any previous month. Zero (0) is the current month, one (1) is the previous month, two (2) is the month before that, and so on:
Name
Definition
vExp_EOM_GJBalancesum({$<[Account - Report]={'Balance_Sheet'},[Account - Concept]={$1},[AsOf Months Ago]={">=$2"}>} [GJ Amount]) - Create a bar chart with
[AsOf YearMonth]as the dimension and with the following expression. The expression divides the last three months of net income by the three-month average of assets:(sum({$<[Account - Report]={'Balance_Sheet'},[Account - Concept]={'Current Retained Earning'},[AsOf Months Ago]={">=1<=3"}>} [GJ Amount LC]))*-1 / ( RangeSum( $(vExp_EOM_GJBalance('Current Assets',0)) ,$(vExp_EOM_GJBalance('Current Assets',1)) ,$(vExp_EOM_GJBalance('Current Assets',2)) )/3 ) - Adjust the bar chart's properties to produce a graph that is similar to the following figure:
Without the
AsOfCalendartable, this trend analysis and visualization would be difficult and slow to create. We use the[AsOf Months Ago]field in the set analysis to calculate over a rolling period that depends on the value of the[AsOf YearMonth]dimension.Unlike the Accumulation option in the Expression tab or the
above()function, we don't have to worry about the first few values of a selected data months being incorrect. Whether the user selects one month or one year, every bar in this chart will show the correct value. Although the final visualization is simple, the data model and calculation that we need to create it is quite elegant.