Solution of the Week: Financial Reports with Impactful Visuals

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.
  1. 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.


  2. Layout Adjustments:
    1. Navigate to Settings > Layout.


    2. Change the Layout to Compact.
    3. Check Increase Font Size for each higher group in Rows.
    4. Uncheck Show row numbers.
    5. Uncheck Show vertical lines between them.
    6. Set Column Width to Fit to Screen.


  3. After making layout adjustments, click Apply to finalize the formatting.

  4. 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

  1. Increased Font Size for Key Categories:
    Emphasizes important financial categories like Assets, Equity, and Liabilities, improving readability and focus.
  2. Compact Layout:
    Ensures a clear, organized display of financial data, enabling efficient consumption of the report.
  3. Line Removal for Clean Aesthetics:
    Removal of vertical and horizontal lines results in a cleaner visual presentation, improving the user experience.
  4. Data Bars for Quick Insights:
    Visual data bars provide a quick snapshot of financial status, allowing users to identify key trends at a glance.
  5. 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:
  1. Drag and drop the created aggregate formulas into the data shelf.
  2. Select Data as row from the drop-down.


  3. Click on Show/Hide, then select Totals.


  4. Click on Columns drop-down, then set the Opening Balance as Top.

  5. Navigate to Settings > Layout and set Column Width as Equal. Provide Width value as required in px.


  6. Click Apply.
  7. 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.
  1. Drag and drop Account into the Row shelf, and Actuals and Budget into the Data shelf from the Budget vs Actual table.


  2. Add Report Formula:
    1. Click Add Report Formula to create custom calculations.


    2. Name the formula column appropriately (e.g., “Deviation”).
    3. Set Data Type as Decimal Number
    4. Create a formula to calculate the difference between actuals and budget as shown in the image.


    5. Click Save.
    6. 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.


    7. Click Save.
      The created formulas will appear in your data shelf and columns, reflecting in the report.


  3. 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

  1. 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.
  2. Key Columns
    The report includes the following key columns:
    1. Total Budget - Displays the budget allocated for each account.
    2. Total Actuals - Shows the actual amount spent or earned for each account.
    3. Variance (Deviation) - Represents the difference between the budgeted amount and the actuals.
    4. Variance Percentage (Deviation %) - This column shows the percentage difference between the budgeted and actual amounts, highlighting under- or over-expenditure.
  3. Data Bars for Clarity
    1. Green Bars indicate favorable performance (within or under budget).
    2. Red Bars highlight overspending or budget deviations (e.g., Office Expenses or Rent).

Key Features of the Budget Statement Report

  1. Visual Representation of Deviations
    Data bars visually indicate how spending aligns or deviates from the budget, enabling quick identification of problem areas.
  2. Concise Overview of Financial Performance
    The report provides a clear overview of actual versus budgeted performance, allowing decision-makers to assess financial health efficiently.
  3. 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.
  1. 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.


  2. Layout Adjustments:
    1. Navigate to Settings > Layout.


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


  3. Once the layout adjustments are made, click Apply to lock in the formatting.
  4. Click Themes, and select the second theme. Set Row Spacing as 2.


  5. 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!

    • Recent Topics

    • Create, collaborate, and manage agreements with Zoho Sign

      Agreements drive business. We launched Zoho Sign in 2017 as a simple digital signature tool to sign agreements from anywhere, at any time. Over the years, we've learned that most agreements go through last-minute changes before they're signed. Our users

    • Function #25: Automatically generate purchase orders from a sales order

      We kicked off the "Function Fridays" series with the goal of helping you automate your everyday accounting tasks. As we delve into today's post, I'm delighted to announce that we're here to present the 25th custom function in this series. While it is

    • Has Anyone successfully integrated Zoho and Sage Intact?

      Hey all, We’re evaluating Zoho One + Sage Intacct and I’m trying to connect with anyone who has actually implemented the two together.Specifically, I’d love to know: -- Which functions you kept in Zoho vs. Intacct (e.g., Product Catalog, AR/AP, invoicing,

    • DNS set up

      I want to create an email with my company domain. When I tried to add new record with cloudflare it didn't work. The DNS record can't be manually added. I followed the instruction but still can't add it. Could you help?

    • 5名限定 課題解決型ワークショップイベント Zoho ワークアウト開催のお知らせ (9/25)

      ユーザーの皆さま、こんにちは。Zoho ユーザーコミュニティチームの藤澤です。 9月開催のZoho ワークアウトについてお知らせします。 今回はZoomにて、オンライン開催します。 ▷▷参加登録はこちら：https://us02web.zoom.us/meeting/register/6OSF2Bh6TumsMIlDwaY_PQ ━━━━━━━━━━━━━━━━━━━━━━━━ Zoho ワークアウトとは？ Zoho ユーザー同士で交流しながら、サービスに関する疑問や不明点の解消を目的とした「Zoho

    • hiding a topic from all but one segment (or list)

      My organization sends out a number of newsletters using Zoho Campaigns. One of those newsletters is for volunteers. In order to become a volunteer, a person has to first go through our volunteer orientation (training). After that, they can receive newsletters

    • How do I set up this automation correctly?

      When contacts enter my Subscribers list, I want it to reference a custom field to see if it is empty. Then I want it to do two things: If empty: Assign a tag based on a different custom field. If that custom field is empty, assign a different tag. If

    • Function #62: Display associated Quote on Invoice details screen

      Hello everyone! Today, we are sharing a Related List script that makes it easy to view and access the quote from which an invoice was created right from the invoice details screen. This Related List displays the Quote number and Amount, allowing you to

    • Custom confirmation message

      How can I change the message that users see after they submit the booking form? I have to confirm some details before their appointment is officially "confirmed", so I want to change it where it doesn't say their appointment is "confirmed" but rather

    • email forwarding not working

      Your email forwarding service does not work. I received the confirmation email and completed the confirmation, after that nothing and nothing since no matter what I have tried. Shame as everything else was smooth. I spose it's harder to run one of these web based internet mail services than you guys thought!!! can you fix the email forwarding asap PLEASE!

    • Desk x CRM Integration

      Howdy! We currently use SalesIQ but we are considering moving across to Desk as it seems to have more functionality that we want. One of the pulls is the ability for our customers to self serve. But, I might be getting over excited and not actually need

    • Function #53: Transaction Level Profitability for Invoices

      Hello everyone, and welcome back to our series! We have previously provided custom functions for calculating the profitability of a quote and a sales order. There may be instances where the invoice may differ from its corresponding quote or sales order.

    • Issue with Save & Share Link – Works for Others but Not Creator

      I’ve enabled the "Save and Share" feature in Zoho Forms, and it works correctly for all users accessing the public link—except for me (the form creator). Issue Details: When I save and share the link, recipients see a “no permission” error. The form is

    • Save the pdf report from a registration form in a Folder

      Through Zoho Forms I created a registration form with quite a lot of rules to handle different variables (TEAM types, payment in installments, etc.), and the result is pretty good. The automatic PDF that is sent to the respondent upon submission is already

    • zoho forms integration to zoho work drive

      Scenario: A user fills out a Zoho Form, entering details such as email, mobile number, and other required information, and uploads supporting documents like PAN, Aadhaar, etc. Upon submission, the data is available in Zoho Forms Reports. Requirement:

    • Zoho Forms to Zoho CRM Integration failed - can I restart it?

      The integration of Zoho Form to Zoho CRM has failed. Is there a way for me to restart it for the impacted entry?

    • insert an equation, but in document, it shows an image not available.

      I'm new to Zoho writer and used Zoho writer in desktop app version . When I inserted an equation, I got an image not available sign. Any help is appreciated. Thanks, Charles

    • Zoho Desk Android app update - Swipe action customization

      Hello everyone! We have brought in support for the swipe action customization in latest version(v2.9.13) of the Zoho Desk Android app update, enabling you to configure left and right swipe gestures on tickets to carry out the actions swiftly without opening

    • Import KB template OR Export template for zoho desk?

      Greetings. Can you tell me if there is a way to get an EXPORT of my KB articles? OR is there a template you supply for importing KB articles into my zoho desk? I am looking for a method of understanding what fields can be imported, and what their possible

    • XML format to import knowledgebase into Zoho Desk

      Hi, We just started to use Zoho Desk and want to import our knowledgebase from our old support system (Freshdesk) to Zoho Desk. Can anyone give us information about the format of xml file to import? There is no explanation on the related page.

    • Zoho Desk integration with Power BI

      Hi, I want to be able to create a Power BI report which has live updates of ticket data from zoho desk, is this possile at all? Thanks Jack

    • Arranging Ticket Templates

      Is there any way to arrange our ticket templates? It doesn't look very organised when it's just arranged according to when they were created. We want the list to look more organised, by arranging/grouping them by topics, or even just by alphabetical

    • What they mean with "Portal"?

      I just downloaded this app to take my personal notes on digital art studio because it seemed to me the most comfortable app to do it, I use it very often, what do they mean by “no activity on your portal”? I have about 1 year of work here and I don't

    • Inactive User Auto Response

      We use Zoho One, and we have a couple employees that are no longer with us, but people are still attempting to email them. I'd like an autoresponder to let them no the person is no longer here, and how they can reach us going forward. I saw a similar

    • favorites no longer listed

      Hello, I can no longer navigate to my Favorites from the pulldown at the top of my grid view (see attached screenshot). I wasn't able to find it anywhere! Please help me display my Favorites. Thank you!

    • Notecards Disappeared

      Got a new phone. Downloaded and signed in Zoho Notebook. Not finding the Note cards. Just Empty Note books. Frustrating.

    • Table Errors

      Hello, I'm new to Zoho Notebook and using it on several Mac/Apple devices. My question is regarding tables: Why do they become broken, and how to fix the error without retyping all my data? (Please see the screenshot attached.)

    • Aggregating the First Value in the Group By of a dataset

      Hi I am trying to get the following Aggregate Formula to work in my chart, but cannot seem to get the right format. I have a series of data that I am running an include_groupby and want to SUM only a column in the first row of each group. So for example.

    • Online Calculator with Zoho Forms?

      I'd like to build a custom calculator on my website. Zoho Forms seems to offer all the fields for basic calculations performed live without pressing any "Calculate" buttons. Unfortunately, there is still that "Submit" button at the bottom. There is nothing

    • Zoho Tables instead of Zoho Creator Spreadsheet reports <3

      That would make my day for sure. Zoho Creator is create, but miss data entering as AirTable/Spreadsheet can. Seeing Zoho putting effort in this makes me think maybe one day we could see a similar interface for Zoho Creator spreadsheet reports. B.

    • Zoho Sheet - Desktop App or Offline

      Since Zoho Docs is now available as a desktop app and offline, when is a realistic ETA for Sheet to have the same functionality?I am surprised this was not laucned at the same time as Docs.

    • Notebook Stack

      Hi Everyone I Hope you´re fine, I´m sorry if this idea was posted before (I made a search but I haven´t find anything related). So, Having the possibility of stacking notebooks would be an interesting way to order notebooks that correspond to the same

    • Writing on sketch cards is bugged when zoomed in

      When zoomed in, it writes a noticeable distance above or to the side of where you're actually trying to write. The further you're zoomed in, the more noticeable it is. Zooming is also entirely absent on the desktop version.

    • Swipe between notes on iPhone

      It'd be convenient if I could move from one note to the next in a notebook simply by swiping left to right.

    • Sales IQ chat is not working in signed android apk

      I have integrated ZOHO sales IQ support chat and i have followed each step and its working fine in my development build but when i create signed APK for it. Chat does not work in it and showing awaiting for detail. I previously asked the same query but

    • COQL order by COUNT not working

      Dear community, I am trying to get a list of deal amounts per planner working on it and sorted to get see who has the least amount of deals. For some reason, I am unable to use sort by in combination with a COUNT. My original code was: query = "select

    • I want to duplicate a report and name it something else

      Hi, I have created a report, and now want to reproduce it and call it something else. so that I will end up with TWO separate reports with different titles. Please tell me how do I copy / reproduce a report please

    • Zoho CRM: Sales Rep Professional Certification Program on Coursera

      We are happy to share that we have published the Zoho Sales Representative Professional Certificate in partnership with Coursera, a leading platform for online learning and career development that offers access to courses and degrees from leading universities

    • OS X Notebook quits immediately upon launch

      NoteBook for OS X (Sequoia, but also under Sonoma) always quits immediately upon launch (so I cannot use the "Attach user log" option). I've restarted my MacBook but the problem persists. If it helps, attached is a diagnostic report from Library>Log

    • Can External users upload files or images to WorkDrive?

      I want to know if it is possible for someone externally through a link and PW be able to upload files and images onto WorkDrive?

    • Next Page