DASHBOARD USABILITY IN FINANCIAL MODELING

This article is focused on the possibility of utilizing dashboard applications in financial modeling. In the era of easy access to the spreadsheet generator, as well as applications and the Internet, it is appropriate to use these tools in each area. The increasing amount of data and the need for daily monitoring and evaluation lead to the development of management applications with graphical data analysis known as “Dashboards.” The manager has the option to buy the app with a predefined environment, or create his own version—specific to the company. If only a simpler design is needed, without an online base and a connection with external databases, then it is possible to create such a simple dashboard using Excel and manage it by trained personnel. In the article, we, therefore, introduced a sample of financial indicator suitable for analysis through the application process and also showed how to create the basic interactive application. JEL Classification Numbers: C58, DOI: http://dx.doi.org/10.12955/cbup.v3.579


Introduction
The financial analysis of the company is the source of current, objective, and comprehensive information to senior management. Financial modeling is now the new standard for an explanation and representation of financial processes, increasingly associated with their forecast. Market-available software solutions for corporate financial modeling generally contain basic calculations for about 50 basic indicators of financial analysis, complemented by a pyramidal decomposition and analysis of cash flow (CF) with graphical and tabular display of the resulting statistics (Campbell, Lo, & McKinley, 1996). If it is not a pure accounting program, in which the variables are incorporated as additional modules in the overall assessment, then these parts can be replaced by cheaper and more variable-packed manner by creating Excel dashboards (or SQL .NET applications for more advanced use).
In contrast to purchasing preconfigured software package with a fixed logical structure, Excel modeling offers the option of regulating only by considering significant indicators and statistics.
We can say that constructing Excel applications require IT experts or trained developers with several years of experience. However, there is a current situation of decreasing quality in graduates from economically and socially-oriented disciplines (Centre for educational research and innovation, 1995; Creemers & Kyriakides, 2011). The lack of innovative progress in the curriculum from their lecturers often leads to negligence in the interconnection and development of economic and social courses with IT technologies (Bobot, Jakubeková, & Rurak, 2012;Juszczyk, 2003). Even trivial operations with basic Office packages are dissuasive for employees in the company since they cannot imagine their workflow application in practice (Benninga, 2014).
In the next part of the article, we will provide a simple procedure for creating a basic functional structure for an interactive Excel dashboard supported by Excel VBA-controlled macro.

Dashboard development in Excel
In the illustrative example, we will demonstrate the use of Excel for modeling and prediction of selected financial indicators and CF. Since these variables are taken from the accounting data of the company, as an essential source of data, we will use the Balance sheet (BS) and a Profit and Loss statement (PLS). In the demonstration, with regard to the scope, we will work only with annual data. in which the data are summarized is specified separately for each company (semi-annually, quarterly, monthly, etc.).
The first step is to organize the data into a suitable format for computational tasks, or for automatic filtering using the control macro. During the development of the dashboard, however, care should be taken to allow the possibility of data input from untrained staff in its structure. Therefore, it is first necessary to create a formal structure of the BS and PLS (Figure 1) reports with a simplified form with necessary data (Figure 2). The green column "Year" is an artificially-added variable to allow replenishment of new BS or PLS reports. Profit and Loss Statement will then be repeated in columns but with the new values for another period in column "Year" (or other suitable time period for the company). Regarding the BS/ PLS tables placement, it is advisable to place each on a separate sheet with a unique title (Zounek & Sudický, 2012). This is to avoid duplication or overwriting the format, and it will be easier to make any legislative changes in the structure of BS or PLS.
Creating a simplified form is the most appropriate solution. From the formal form of BS/PLS reports, we have to create a pivot tables with the filtered data for the purpose of calculation. Orientation/key feature in this case will be for rows-column "Serial number" and the single BS/PLS reports will be divided by "Year" values ( Figure 2).

Porfit and loss
Serial number  Once we have all the data sorted and available, we can start by modelling the financial indicators with their forecasts. As an example for one of the indicators, we can make the calculation of the 1 st grade liquidity ratio. The theoretical formula is:  (Table label) PLSl -Profit and Loss Statement liabilities/assets (PLSa) (Table label) Before we begin with the calculations, we need to create a sheet with some manageable constants first. We will call it "constants" and we will choose "Year" as our changeable key. Then, our first simple, manageable Pivot table will contain all the years occurring in our first formal BS and PLS tables ( Figure  3). BS table is sufficient because "Year" column has to logically match with that in the PSL tables.    The first row of this calculation is filled with formula for liquidity as mentioned before, with error handling in case of no value. We used "N/A" value as an easy text prefix for no-value case, which is suitable for future calculation needs. For this reason, we have to rewrite values into second row where the "N/A" value will be replaced by #VALUE, which will be in the final chart shown as an empty space.
Our final calculation in this example (not necessary) is shown on the forecast line ( Figure 6). For easier presentation, we only used a basic Excel forecast method, but for financial indicators, especially in seasonal conditions, it is more appropriate to use more complex forecasting methods such as Holt-Winter or similar technique. In the first row, the classical excel forecast function is used and supported by ISNUMBER function for securing the no-number case. The first row is conditional with the second summary row, which combines the liquidity calculation and the forecast together.  If we need to use a line-type chart, we can simply modify the 17 th row of forecast as follows: "=IF(AND(ISNUMBER(E12);F12="N/A");E12;IF(ISNUMBER(E12);NA();E15))," which will secure a connection between the two lines ( Figure 8).

Control macro
In effective financial modeling, we need to create more useful and less time-consuming application from static, non-flexible charts. The most essential item in financial analytics models is time comparison (Jorion, 1986;Frost & Savarino, 1986). From this reason, we have to be able to adjust any date/time period that we wish to conduct deeper analyses.
There is a simple example on how to make time-changeable charts using the previous pivot table ( Figure  3). First, we need to establish our two control cells for the year range setup, using list option with source from the pivot table (Figure 3). The resulting table will look like Figure 9.  It can be seen that macro contains classical variable initiation for basic calculation needs. The first IF requires that we select the years in the right order (ascending). Then, we need to make sure that one of pivot items will still be visible in the pivot table (pivot table cannot contain 0 visible items). For this purpose, "Blank" is the most suitable pivot item. Next, we count all the pivot items in the pivot table and make them invisible in the pivot table (except for the last one, "Blank") with FOR loop. It is also suitable during this step to ensure that the pivot table will always be sorted from "A-Z," when we add new rows, by typing: "Worksheets("Constants").PivotTables("Pivot .AutoSort xlAscending, "Year" after the row with "Blank" item is set up to "visible" value.
Once we have all the pivot items hidden, we can start to uncover the years we need. We can do so by using the second FOR loop command where the starting point will be variable "year 1" followed by count of deducting "year 2 -year 1." To avoid any crack in the code, we can add "On Error" statement into the loop.
At the end as fuse for wrong year range set up, we can use Error message in prime IF condition announcing that wrong or inappropriate years range was chosen.
Private Sub is established as Worksheet Change type, so the whole macro will be executed immediately after a change of any of control cells (Figure 9).
When we add more indicators and all calculations connect to one prime Pivot table, as shown in Figure  4, we can control all the charts on one or more sheets at once with this macro. So, we will not need to make charts for each time period, or indicator separately. The final summary dashboard will look like Figure 11.

Conclusion
Nowadays, cost savings through lean management and optimization of processes is one of the most popular ways of organizational change, especially through the application of fundamental analysis of its own resources (DeMiguel, Garlappi, & Uppal, 2007). Many companies underestimate the possibilities of building human capital, despite the possibility of improvement through training, which will ultimately streamline work and supply of new ideas and solutions (Freeth Della, Hammick, Reeves, Koppel, & Barr, 2005). Younger employees now possess a higher technical skill level compared to a few years prior, and developing their skills can save on the cost of purchasing overpriced applications and software, which can be developed in-house (Nightingale & O'Neil, 2012).
As discussed in this article, creating application for basic financial modeling is not a complicated process as it may seem. In fact, this type of dashboards only requires intermediate Excel and basic programing skills, which most of younger employees have already mastered in schools or universities. It is only up on the company to take advantage of this application and improve it to produce valuable outcome.