Solution of the Week: Financial Reports with Impactful Visuals

Finance reporting is a critical part of understanding an organization’s financial health. With Zoho Analytics, you can easily create dynamic reports such as Balance Sheets, Budget Statements, and Profit and Loss Statements.

Now, with the release of Zoho Analytics 6.0, the platform offers even more advanced tools to optimize the financial report view. While Zoho Analytics has always enabled comprehensive financial reporting (through its integration connectors for Zoho Books, QuickBooks, Xero, Sage), the latest version enhances this with powerful visual features like Data Bars, which provide clearer insights into performance trends.

This document outlines how these reports can be generated using simple drag-and-drop functionality and customizable features in Zoho Analytics.

Creating a Balance Sheet Using Finance Data

Generating a Balance Sheet in pivot using Zoho Analytics allows you to visualize your organization’s financial position in a comprehensive and structured way.

Drag and Drop the Data: Start by selecting the Balance sheet base type, Account group, Balance Sheet Account Type, and Balance Sheet Account Name from the Accounts table and drop them into the Rows shelf. Drag and drop the Credit Amount, Debit Amount, and Balance Sheet Total from the Accrual Transactions table into the Data shelf.
Layout Adjustments:

Navigate to Settings > Layout.
Change the Layout to Compact.
Check Increase Font Size for each higher group in Rows.
Uncheck Show row numbers.
Uncheck Show vertical lines between them.
Set Column Width to Fit to Screen.

After making layout adjustments, click Apply to finalize the formatting.
Go to Visuals and select Data Bars With Data to improve the presentation of financial data, making it easier to interpret key insights visually.

By following these steps, you will generate a clear and structured Balance Sheet that highlights your organization's assets, liabilities, and equity.

Key Features of the Balance Sheet Report

Increased Font Size for Key Categories:
Emphasizes important financial categories like Assets, Equity, and Liabilities, improving readability and focus.
Compact Layout:
Ensures a clear, organized display of financial data, enabling efficient consumption of the report.
Line Removal for Clean Aesthetics:
Removal of vertical and horizontal lines results in a cleaner visual presentation, improving the user experience.
Data Bars for Quick Insights:
Visual data bars provide a quick snapshot of financial status, allowing users to identify key trends at a glance.
Timeline Filter for Flexibility:
Filters allow users to view financial data across different periods, making it easier to analyze financial trends over time.

In Zoho Analytics, utilizing the Timeline Filter allows users to transform static financial data into a time-based, dynamic report. The formulas used in creating this filter enable detailed insights into financial performance over a selected period. Below, we explain the key aggregate formulas and how they enable users to achieve greater flexibility and customization, going beyond the typical app potential.

Opening Balance Formula

sum_if("Accrual Transactions"."Transaction Date" < ${system.timeline.date.from},if("Accounts"."Account Base Type" in ('Asset') OR ("Accounts"."Account Base Type" in ('Equity') and "Accounts"."Account Name" in ('Drawings')),"Accrual Transactions"."Debit - Credit","Accrual Transactions"."Credit - Debit"),0)

This formula calculates the Opening Balance for a selected period by summing transactions that occurred before the specified date (timeline.date.from).

This approach allows users to view the Opening Balance as of a given date, making it highly flexible for periods that differ across user's datasets.

if("Accounts"."Account Base Type" in ('Asset') OR ("Accounts"."Account Base Type" in ('Equity') and "Accounts"."Account Name" in ('Drawings')),"Accrual Transactions"."Debit - Credit","Accrual Transactions"."Credit - Debit")

This part of the formula can be customized based on the unique financial structure you follow.

Period Balance Formula

sum_if("Accrual Transactions"."Transaction Date" > ${system.timeline.date.from} and "Accrual Transactions"."Transaction Date" <= ${system.timeline.date.to},if("Accounts"."Account Base Type" in ('Asset') OR ("Accounts"."Account Base Type" in ('Equity') and "Accounts"."Account Name" in ('Drawings')),"Accrual Transactions"."Debit - Credit","Accrual Transactions"."Credit - Debit"),0)

The Period Balance formula calculates the net balance of transactions within a specific time range, defined by the timeline filter. The formula sums transactions between the start date (timeline.date.from) and the end date (timeline.date.to). This provides users with a snapshot of their financial activity for any custom date range they select.

Closing Balance

sum_if("Accrual Transactions"."Transaction Date" <= ${system.timeline.date.to},if("Accounts"."Account Base Type" in ('Asset') OR ("Accounts"."Account Base Type" in ('Equity') and "Accounts"."Account Name" in ('Drawings')),"Accrual Transactions"."Debit - Credit","Accrual Transactions"."Credit - Debit"),0)

The Closing Balance formula calculates the final balance as of a selected date (timeline.date.to). It works by summing all transactions up until the specified date, following the same account type distinctions used in the previous formulas.

This formula is essential for determining the financial standing of an organization at the end of a reporting period, such as the end of a quarter or year.

Follow the below steps to create opening and closing balances with timeline filters:

Drag and drop the created aggregate formulas into the data shelf.
Select Data as row from the drop-down.
Click on Show/Hide, then select Totals.
Click on Columns drop-down, then set the Opening Balance as Top.
Navigate to Settings > Layout and set Column Width as Equal. Provide Width value as required in px.
Click Apply.
Check Include Timeline Filter.

The Timeline Filter is now enabled, and users can apply time-based filters to their data.

By utilizing these timeline-based formulas, Zoho Analytics allows users to move beyond static, historical reporting and into dynamic financial analysis. With the Timeline Filter, users can interact with their data in a more flexible way, selecting custom periods to analyze financial trends over time.

Click here to access the sample workspace.

Creating a Budget Statement

A Budget Statement helps compare actual financial performance against projected budgets, identifying variances and areas of concern. This visually enhanced report is designed to help businesses quickly assess deviations between expected and real expenditures, allowing for better budget management and financial decision-making.

Drag and drop Account into the Row shelf, and Actuals and Budget into the Data shelf from the Budget vs Actual table.
Add Report Formula:

Click Add Report Formula to create custom calculations.
Name the formula column appropriately (e.g., “Deviation”).
Set Data Type as Decimal Number
Create a formula to calculate the difference between actuals and budget as shown in the image.
Click Save.
By following the same steps mentioned above, create one more formula to calculate the percentage difference between actuals and budget as shown in the image.
Click Save.
The created formulas will appear in your data shelf and columns, reflecting in the report.

Go to Visuals and choose Data Bars With Data for a more intuitive understanding of budget performance.

By following these steps, you will have a detailed Budget Statement that compares actual spending to the budget, highlighting deviations.

Report Structure and Visuals

Account Breakdown
The report is organized by account categories, such as Bank Fees, Cost of Goods Sold, and more, showing both Budgeted and Actual amounts for each category.
Key Columns
The report includes the following key columns:

Total Budget - Displays the budget allocated for each account.
Total Actuals - Shows the actual amount spent or earned for each account.
Variance (Deviation) - Represents the difference between the budgeted amount and the actuals.
Variance Percentage (Deviation %) - This column shows the percentage difference between the budgeted and actual amounts, highlighting under- or over-expenditure.

Data Bars for Clarity

Green Bars indicate favorable performance (within or under budget).
Red Bars highlight overspending or budget deviations (e.g., Office Expenses or Rent).

Key Features of the Budget Statement Report

Visual Representation of Deviations
Data bars visually indicate how spending aligns or deviates from the budget, enabling quick identification of problem areas.
Concise Overview of Financial Performance
The report provides a clear overview of actual versus budgeted performance, allowing decision-makers to assess financial health efficiently.
Enhanced Usability with Percent Deviations
Percentage-based deviation columns allow users to understand the scale of over- or under-performance relative to the budget, making it easy to identify areas that are significantly off track.

Click here to access the sample workspace.

Creating Profit and Loss report

A Profit and Loss (P&L) report in Zoho Analytics helps businesses assess their revenue, expenses, and profitability over a specific period. By following these steps, you can create a detailed and visually engaging P&L report.

Drag and drop Account base type, P&L Operating Profit Type, P&L Gross Type, Base type P&L, and Account Name into the Columns shelf from the Accounts table and Profit and Loss Balance into the Rows from Data shelf from the Accrual Transactions table.
Layout Adjustments:

Navigate to Settings > Layout.
Change the layout to Compact.
Set Indent Level to 3.
Check Increase Font Size for each higher group in Rows to make key categories more visible.
Uncheck Show row numbers for a cleaner report.
Uncheck Show Vertical Line between Each Column to streamline the visual presentation.
Set Column Width to Equal and specify the width as required in px.

Once the layout adjustments are made, click Apply to lock in the formatting.
Click Themes, and select the second theme. Set Row Spacing as 2.
Navigate to Visuals and select Data Bars With Data to provide a quick and clear visual representation.

By following the above steps, you will create a Profit and Loss report which provides a concise and structured view of your business’s financial performance, making it easy to track profitability over time and identify key financial trends.

Click here to access the sample workspace.

Zoho Analytics provides an intuitive and customizable platform for generating comprehensive Balance Sheets, Budget Statements, and Profit & Loss reports. With its drag-and-drop functionality and powerful visual tools, Zoho Analytics transforms finance reporting into a data-driven process that improves decision-making, forecasting, and resource management.

Start leveraging Zoho Analytics today to create reports that provide actionable insights, helping your organization thrive financially!