As humans, we respond to and process visual data better than anything else. Information presented visually is more absorbent than large quantities of raw data. In business, the graphical representation of data is vital to provide fast and reliable insights for the management.
Two of the most used charts in the financial reports are Variance Charts and Cascade(waterfall) Charts, or the corresponding Sparklines if the analysis is made at a cell level. These robust graphics help the companies to transform their data into accurate, reliable, and clear-cut information, so needed to achieve their success and evolution.
Variance Sparkline
Variance Charts are often used for performance comparison.
For example:
- Comparison between Actual vs. Budget.
- Analyzing firm's productivity based on the annual target.
- Comparison between Previous year profit vs. current year profit.
In SpreadJS as a building option for this type of analysis is Variance Sparkline. With an instant formula insertion, you can have your data transformed into a visualized information utile for your company.
To Insert a Variance Sparkline in SpreadJS Designer, follow these steps:
- Click the cell you want to insert a Sparkline and click the Insert tab, then select Variance Sparkline from the Sparklines group.
- Then in the popped-out dialog, select the Variance, colors, and fill the other sparkline properties.
- Click OK to close the dialog. Then drag the fill handle to fill the range you need.
Or you can insert the Variance Sparkline by clicking in the desired cell and then entering the VariSparkline Formula.
=VARISPARKLINE(Variance, reference, mini, maxi, mark, tickunit, legend, colorPositive, colorNegative, vertical)
SpreadJS allows you to customize your Variance Sparkline by entering the parameters like tickunit, Sparkline's direction, the position of X or Y axes, color coding, etc.
Cascade Sparkline
The purpose of Cascade Sparkline is to show how an initial value (for example, net income) is affected by a series of positive and negative values (expenses or revenues). This Sparkline is used mainly in Time Analysis or Profit and Loss Statements.
As per Variance Sparkline, there are two ways of inserting Cascade Sparklines using SpreadJS products.
- In SpreadJS Designer, you can click the cell you want to insert a Sparkline and click the Insert tab, then select Cascade Sparkline from the Sparklines group:
- Or select the destination cell and enter there the formula for the Cascade Sparkline:
=CASCADESPARKLINE(pointsRange, pointIndex, labelsRange, minimum, maximum, colorPositive, colorNegative, vertical, itemTypeRange)
Presented below is an Income Statement that has listed the major categories of assets and expenses for the previous year and the actual one.
Note: the system would accumulate all the table elements since every one of them represents a positive number.
To give a solution for this issue, in SpreadJS add an itemTypeRange parameter, represented here with the highlighted column that would determine if you should:
- Add the quantity as an additional contribution (+)
- Subtract the quantity as it is considered an expense (-)
- Do not add or subtract since we are dealing with a sub-total (=)
In the following diagram, you can see a comparison scenario between the Previous Year and Actual. We have created a cascade sparkline for both series and used as itemTypeRange parameter the same range of values.
To make it easier to see the Variance between those two series, there is even a Variance Sparkline providing insights of the absolute deviation of each element.
This visualization shows clearly how the company Revenue is transformed to NET Income Total.
Use of Cascade Sparkline for Variance-Analysis
To analyze the company's performance, management often uses the year-over-year (YoY) Variance. This kind of report compares financial statistics to see how they vary from one year against the performance. In the diagram shown before, there were three sparklines that give a simple version of how this analysis can be done for comparing Income Statements of 2 years.
When management teams analyze a company's performance, they often use a type of variance report that compares financial statistics to see how they vary from one year against the performance of another, usually the current year. This type of report, called year-over-year (YoY) variance, is especially useful and insightful when comparing a time series of financial data. Analysts can quickly and clearly see changes in various business aspects with YoY analysis.
By making some adjustments, you can achieve to combine the three sparklines (PY, AC Cascade Sparkline, and the Variance Sparkline). You can show with a Cascade Sparkline the accumulated variance deviation of different elements of the Income Statement between PY and AC.
In the above report, you can see that PY Net Income is $134 M, and Actual Net Income is $214 M. To show how the PY Net ended up to the Actual Net Income, follow these steps:
- First, for illustration purposes, change the sign of the expense values to negative ones as those decrement the total value.
- Calculate the Variance between statement elements (below, we have included only the major categories of the statement, if needed, those can be drill down even further, for example, Revenue and OPEX).
- Create a Cascade Sparkline by considering the PY Net as starting Total, variance elements as negative or positive contributions, and Actual Net Income as the final Total.
- By changing the vertical parameter to FALSE and rearranging the data below, Sparkline's box would be the Y-axis and the diagram horizontal.
And, if you want to expand your analysis further, you can create diagrams for each of the categories.
As an example, here is a granulated diagram for Operating Expenses, showing the variance deviation between the Previous Year and Actual Year.
In our data-driven digital age, the decision-makers need their financial dashboards to be equipped with graphs and charts that are accurate and deliver the level of insight required to increase efficiency and stop potential threats. SpreadJS's goal is to make it easier for all the raw data to be transformed, into practical and meaningful information, effortlessly.
We give the means to add advanced spreadsheet capabilities to your JavaScript application, including amazing graphic visualizations as sparklines that would help the management have practical, easily understood, and effective information, ensuring commercial success and prosperity in the long term.
If you'd like to learn more about the amazing opportunities, SpreadJS offers, then go ahead and get the free trial.
Download the example file here and follow along.
Note: This is a .ssjson file. Please ensure your browser doesn't change the file extension on download. After downloading, import the file into the SpreadJS designer and discover all the amazing opportunities that SpreadJS offers.
Contact us to learn more at us.sales@grapecity.com and visit the SpreadJS page
Jola Hoxha