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

    • Having Trouble Opening The Candidate Portal

      Recently am having trouble opening the Candidate Portal. It keeps loading but cannot display any widgets. Tried Safari, Chrome and Edge. Non of them work. Please solve the problem ASAP.

    • Forms - Notification When Response Submitted

      How do I set it up to generate an email notification when a response (class request) is submitted?

    • how to use validation rules in subform

      Is it possible to use validation rules for subforms? I tried the following code: entityMap = crmAPIRequest.toMap().get("record"); sum = 0; direct_billing = entityMap.get("direct_billing_details"); response = Map(); for each i in direct_billing { if(i.get("type")

    • Notes Issues

      Been having issues with Notes in the CRM. Yesterday it wasn't showing the notes, but it got resolved after a few minutes., Now I have been having a hard time saving notes the whole day. Notes can't be saved by the save button. it's grayed out or not grayed

    • How to disable user entry on Answer Bot in Zobot

      Hi, I have an Answer Bot in my Zobot, here is the configuration: I only want the user to choose 1 of the 4 the options I have provided: When no answer found, user chooses 'I'll rephrase the question' or 'Ask a different question When answer is found,

    • More admin control over user profiles

      It's important for our company, and I'm sure many others, to keep our users inline with our branding and professional appearance. It would be useful for administrators to have more control over profile aspects such as: Profile image User names Email signatures

    • Please Make Zoho CRM Cadences Flexible: Allow Inserting and Reordering Follow-Up Steps

      Sales processes are not static. We test, learn, and adapt as customers respond differently than expected. Right now, Zoho Cadences do not support inserting a new step between existing follow-ups or changing the type of an existing primary step. If I realize

    • Changing the Default Search Criteria for Finding Duplicates

      Hey everyone, is it possible to adjust the default search criteria for finding and merging duplicate records? Right now, CRM uses some (in my opinion nonsensical) fields as search criteria for duplicate records which do nothing except dilute the results.

    • Clear Tag & Linking Between Quotes and Sales Orders

      Hi Zoho Team, In Zoho Books, when a quote is converted into a sales order, it would be extremely useful to have: A clear tag/indicator on the quote showing that it has been converted into a sales order. A direct link in the sales order back to the originating

    • Zoho Books Sandbox environment

      Hello. Is there a free sandbox environment for the developers using Zoho Books API? I am working on the Zoho Books add-on and currently not ready to buy a premium service - maybe later when my add-on will start to bring money. Right now I just need a

    • Add Direct Ticket Link to Zoho Help Center Portal in Email Replies

      Hi Zoho Support Team, We hope you're doing well. We’d like to request a small but valuable improvement to enhance the usability of the Zoho Help Center portal (https://help.zoho.com/portal/en/myarea). Currently, when someone from Zoho replies to a support

    • [Webinar] Deluge Learning Series - AI-Powered Automation using Zoho Deluge and Gemini

      We’re excited to invite you to an exclusive 1-hour webinar where we’ll demonstrate how to bring the power of Google’s Gemini AI into your Zoho ecosystem using Deluge scripting. Whether you're looking to automate data extraction from PDFs or dynamically

    • Connecting Zoho Inventory to ShipStation

      we are looking for someone to help connect via API shipStation with Zoho inventory. Any ideas? Thanks. Uri

    • Subform edits don't appear in parent record timeline?

      Is it possible to have subform edits (like add row/delete row) appear in the Timeline for parent records? A user can edit a record, only edit the subform, and it doesn't appear in the timeline. Is there a workaround or way that we can show when a user

    • New in Cadences: Option to Resume or Restart follow-ups when re-enrolling records into a Cadence, and specify custom un-enrollment criteria

      Managing follow-ups effectively involves understanding the appropriate timing for reaching out, as well as knowing when to take a break and resume later, or deciding if it's necessary to start the follow-up process anew. With two significant enhancements

    • Im Stuck in an EDIT ONLY WITH WIZARD issue

      So I found Wizards to be a really helpful tool in minimizing the exposure of redundant, superfluous fields to staff that would never otherwise have to edit those fields. My issue is, that when the record (in this case a lead) is created with a wizard,

    • Account upgrade

      Good evening, I upgraded my account and paid for it. From standard to professional. Unfortunately after the paiment my account was not upgraded. Please your advise. Best Regards Erik van Staverden

    • How to set ALL default dates of my organization to DD-MM-YYYY format?

      All replies to this question comes from a time where the UI was different. It's extremely frustrating not being able to find how to do this simple setting change. I want everything and everyone in my organizations to have DD-MM-YYYY date format by default.

    • How can I sync from Zoho Projects into an existing Zoho Sprints project?

      Hi I have managed to integrate Zoho Projects with Zoho Sprints and I can see that the integration works as a project was created in Zoho Sprints. But, what I would like to do is to sync into an existing Zoho Sprints project. Is there a way to make that

    • Can we generate APK and IOS app?

      Dears, I want to know the availability to develop the app on zoho and after that .. generate the APK or IOS app  and after that I added them to play store or IOS store.. Is it possible to do this .. I want not to use zoho app or let my customers use it. thanks 

    • Zoho Subform Workflows onAdd of Row

      Suppose I have a form with attached workflows onLoad. If I use the form as a subform, will it inherit the workflows or do I need to create new ones onAdd of row?

    • Session Expired

      I constantly get "Session Expired" and need to relogin or close and open the application again. This gets really frustrating during the day. Is this something that can be solved? This really makes me want to leave the app as it is no go to need to reopen

    • Super Admin removal

      I brought a sub, and I gave the Super admin rights to a person who is no longer with us, so I need to change, and I need to make myself the Super admin

    • Better Notes Commenting

      Hi, I'd like to suggest better collaboration tools for NOTES. The current notes section for Accounts, Contacts and Deals is not ideally suitable for any degree of communication or collaboration. When responding to a note, there is no ability to leave

    • Formula field with IF statement based on picklist field and string output to copy/paste in multi-line field via function

      Hello there, I am working on a formula field based on a 3-item picklist field (i.e. *empty value*, 'Progress payment', 'Letter of credit'). Depending on the picked item, the formula field shall give a specific multi-line string (say 'XXX' in case of 'Progress

    • Exporting Templates

      I have just spent 2 hours creating a project template for a Netsuite configuration, and want to share it with other Zoho Projects users - who have a different account. Is there any way to do this?

    • Power of Automation:: Streamline Associated Teams based on the Task Owner update.

      Hello Everyone, A Custom function is a user-written set of code to achieve a specific requirement. Set the required conditions needed as when to trigger using the Workflow rules (be it Tasks / Project) and associate the custom function to it. Requirement:

    • No Response from Zoho Support in 8 Days - Typical?

      I have a couple of issues I'm trying to work through. Initially, I was getting support from support@zohofsm.com, but I have not received a response in 8 days (11 on another question). Is this typical? Can I pay for support? For context, I am not spamming

    • Add QUOTE OWNER profile image to a Quote Template

      I can add their email address.. phone number, DOB. I need to add a users profile picture so when they assign a template to a quote they own it adds their picture to the cover page. I've tried hacking a solution together but there has to be an easier way.

    • Elevate your CX delivery using CommandCenter 2.0: Simplified builder; seamless orchestration

      Most businesses want to create memorable customer experiences—but they often find it hard to keep them smooth, especially as they grow. To achieve a state of flow across their processes, teams often stitch together a series of automations using Workflow

    • Zoho Connections Desk API relative URL PATTERN_NOT_MATCHED

      While i am trying to do this: async function fetchTicketsFromDesk(timeFilter = 'current_month') { try { const response = await ZOHO.CRM.CONNECTION.invoke("desk_connection", { url: "/api/v1/tickets", method: "GET", }); const data = response.details ? JSON.parse(response.details)

    • Zoho CRM - Custom Views for Portal Users

      I'm looking for an option to customise custom views for portal users in CRM. It would be great if "portal user" was a permission on custom views.

    • 【参加無料】10/17(金) 東京 ユーザ交流会 Vol.3 参加登録 受付開始！

      ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 10/17(金)に、東京・新橋で「東京 ユーザー交流会 Vol.3」を開催します！ 今回のユーザー事例セッションのテーマは、「Zoho Flowを活用した他社の決済サービスとの連携事例」です。 さらに、Zoho Flowに限らず、Analytics や Campaigns などの多彩なZohoサービスの活用方法について、豊富なご経験をもとにご紹介いただきます。 また、Zoho社員セッションでは、Zoho CRMを活用して日々の営業業務を効率化する具体的な事例をお話しします。業界を問わず、幅広い方にご参考いただける内容となっています！

    • Zoho Meeting Plug compatibility with newer versions of Outlook

      Documentation states that the zoho meeting plug in for outlook is only compatible with versions up to Outlook 2019 What is available to users of more up to date versions of outlook/office 365?

    • Getting Attachments in Zoho Desk via API

      Is there a way to get attachments into Zoho Desk via an API?      We have a process by which a zoho survey gets sent to the user as a link in a notification.    The survey has several upload fields where they can upload pdf documents.    I've created

    • Introducing Zoho's own SMS gateway

      We're thrilled to announce the launch of our own SMS gateway feature within Zoho Marketing Automation! This new feature enables seamless SMS campaign management alongside your email marketing initiatives, providing a more integrated and efficient way

    • Embedding in Desk articles

      We would like to embed documents in our Desk articles. When we use an iframe for the embed, we get scrollbars and a frame border. Neither of those is acceptable. I've spoken with the Desk Support team about what we want and they tell me that it cannot

    • Zoho CRM button to download images from image upload field

      Hello, I am trying to create a button in Zoho CRM that I can place in my record details view for each record and use it to download all images in the image upload fields. I tried deluge, client scripts and even with a widget, but feel lost, could not

    • Mass Update Contacts In Zoho Campaigns

      Is there a way to mass update contacts in zoho campaigns? I want to be able to change the content of a field for a few hundred contacts, and can't go through all of them individually.

    • report showing assignment type

      Hi, We've created a number of workflows to allow us to auto assign tickets to agents based on keywords and other criteria. I'm struggling to create a report that would show me what is the percentage of tickets that are assigned automatically via workflows

    • Next Page