How-to
How to build a financial statement in Hubviz Studio
Build an income statement or another report line by line from cube measures, with totals, average and accumulated columns and vertical analysis.
A financial statement is a table built line by line, like an income statement: revenue, costs, profit and margin, with the months in the columns. Each line is a formula made from cube measures, just like the series of a chart.
When to use it
- For a management income statement, with title, value and total lines in the order your leadership reads them.
- When you need average and accumulated columns next to the months.
- To see the weight of each line over a base, such as each cost against revenue (vertical analysis).
To cross fields freely, without building line by line, use a pivot table.
Before you start
- Create the measures the lines will use, such as Revenue and Cost (see Measures).
- Load the cube data. Without data, the line window warns You need to load data before creating or editing a serie.
Create the statement
-
In the project side menu, open Charts, click the + button (Add New Chart/Demonstration/Filter) and choose Financial Demonstration. Another way: in the Financial Demonstrations section, click Add New.
-
Type the name in Financial Statement Title, such as Management income statement.
-
In Demo Template, keep Standard Model.
-
Turn the extra columns on or off (see the options table below).
-
Click New Line to open Line Adjustments. Type the name, choose the Line Type and build the Formula with Measures and Operators. The Result Preview shows the line with its values.
-
Click Save and repeat for each line. To change the order, drag the lines in the list. Drop a line onto another one to place it inside, as a sub-line.
-
Save the statement with Ctrl + S.
Statement options
| Option | What it does |
|---|---|
| Demo Template | Standard Model for regular statements. DMPL replaces each line formula with two: Movement Formula and Start of Period Formula. |
| Average Column | Adds a column with the average of the displayed periods. |
| Accumulated Column | Adds an accumulated column. What goes into it depends on each line’s behavior (see below). |
| Invert Month Order | Shows the months from newest to oldest, or the other way round. |
| Sum monthly percentage values | On percentage lines, the accumulated column adds up each period’s percentage instead of recalculating the formula over the totals. |
| Custom Title Row Background Color | Color of the title lines and the header. |
| Edit Vertical Analysis Formula | Sets the base value for vertical analysis, such as Revenue. |
The Market Comparison template also shows up in the list, but it is not documented here: it depends on comparison data that is not part of the project.
Line options
| Option | Choices | Default |
|---|---|---|
| Line Type | Separation Title Line, Title Line, Total Line, Value Line, Blank Line | Value Line |
| Value Formatting | Currency, Number, Number without decimal places, Percentage | Currency |
| Accumulated Value Column Behavior | Sum of values for the displayed periods, Value of the first period displayed, Value of the last period displayed | Sum of values for the displayed periods |
| Show categories as children | Opens the line into sub-lines, one per category of the measure. | Off |
| Invert child category values | Flips the sign of the sub-line values. | Off |
Title, separation title and blank lines have no formula; the window shows Formula not applicable for this line type.
Example: management income statement
With the Revenue and Cost measures of the Sales cube, build these lines:
| Line | Line Type | Formula | Value Formatting |
|---|---|---|---|
| Sales results | Title Line | (no formula) | |
| Revenue | Value Line | Revenue | Currency |
| Cost | Value Line | Cost | Currency |
| Gross profit | Total Line | Revenue minus Cost | Currency |
| Gross margin | Value Line | (Revenue minus Cost) divided by Revenue | Percentage |
Then, in Edit Vertical Analysis Formula, use Revenue as the base. Each line then shows how much it represents of revenue.
Watch out
- Available on every plan. The financial statement exists on Essential, Professional and Business, and in the Studio it is built from cube measures. What depends on the plan is accounting data (trial balance, ledger and chart of accounts) and the ready-made accounting dashboard, which are on the Professional and Business plans. See What your Hubviz plan includes for accounting and Plan and billing.
- Percentages in the accumulated column. On the margin line, the accumulated column recalculates the formula over the totals. Turn on Sum monthly percentage values only if you really want the sum of the percentages.
- DMPL turns off the accumulated behavior choices on the lines.
- To place the statement on a dashboard, use + in the dashboard edit mode. It shows up in the financial statements group of the picker.