Numbers are Spilling TEA!
As the old saying goes “Actions SPEAK louder than words”, that sentiment reigns true for numbers as well. Numerical data, when properly analyzed, can literally speak to us; thereby giving insight to such things as: untruths, past events, current occurrences, and future outcomes.
This Blog explicitly details how the use of statistical strategies – with the assistance of data analytic tools in Excel – can significantly reduce the amount of time and effort needed to quickly access a narrative from a wide range of data that is, typically, readily available to us. As Accountants and Auditors, the information gained from these analyses can help us to make more informed decisions about the organizations we work for. Coincidentally, as a twist, these tactics can also be applied by ordinary individuals looking for insights that will help us to divert from the hassle, heartbreak, and headache we could potentially experience in everyday living. The answers we seek are right in front of us, we just have to let the Numbers spill the TEA!
Untruths (Look for Anomalies/Outliers)
TEA: “Oooh, something isn’t right.”
The best way to locate Anomalies or Outliers is by finding the Mean, Median, and Mode of a particular numerical dataset. If these statistical values show clear variations (the values are not relatively the same), then you can infer that the presence of anomalies/outliers exist. Anomalies and/or Outliers can indicate that you have information within your selected dataset that deviates from the norm or expectation. These deviations can be the result of mistakes (unintentional), events outside the usual, and/or defalcations (manipulated/false information).
In the event you are feeling insightful, you can attempt to pinpoint the anomalies/outliers by calculating the standard deviation of the dataset. Any amount(s) – within the dataset – that are outside of two (2) standard deviations of the mean (or median) are potentially your anomalies/outliers. Once identified, set aside those amounts from the dataset and reconfigure your mean, median, mode to see if you have successfully located all anomalies/outliers.
If you are feeling really investigative, further evaluate the support for those anomaly/outlier amounts to see what details make them so different than the normal. The statistics are telling you that something is not of the norm, further evaluation of the anomalies/outliers will tell you what exactly is not adding up.
For Accountants/Auditors it could mean that some accounts are misstated, for regular Joe/Joanne it could mean that someone is misleading you.
Helpful Tips: (1) When calculating the Median make sure you organize your selected numerical data in ascending or descending order. (2) The longer the period of your data range the better, six months to 1 year of data at the minimum.
Past Events (Look for Trends/Patterns)
TEA: “Hmmm, that’s an interesting connection.”
One of the easiest techniques to locate trends or patterns is through the use of correlation analysis. This type of analysis looks to see how a particular variable within a dataset impacts another variable within the same dataset. For example, the larger the budget for a particular project and/or expenditure the longer the procurement/approval process. Any results that show correlations that are peculiar can give indications to such things as: preferential treatment (bias), circumvention of approval controls (management override), and/or schemes to deceive (kickbacks, bribes, extortion, forgery, plagiarism).
Data Analytic tools such as Excel and Tableau make it easy for users to identify any correlations among numerical data in any given dataset. Here’s How:
Shows how Excel and Tableau chart Correlations.
Helpful Tip: To include qualitative data in your correlation analysis, just change this data to a numerical variable. For instance, if I wanted to see how days of the week correlated with miles driven, I could turn Monday to 1, Tuesday to 2, Wednesday to 3, etc. and then run my correlation analysis to see if miles driven correlates with any particular day of the week.
For Accountants/Auditors it could mean that on check write or pay days a considerable amount of miles are driven to cash checks, but bank is across the street from office location. Potentially employees are colluding to embezzle company funds by writing checks to themselves or someone they know.
For the average Melinda/Danny it could mean that spouse is storing away a secret nest egg in an account (undisclosed to you) in an attempt to potentially leave the relationship.
Current Occurrences (Look for Significant Variances)
TEA: “Uhhh, you do know this is considerably more than last year.”
Performing a side-by-side comparison of prior period (Month, Quarter, or Year) and current period figures is a great way to identify significant variances (changes). Significant is, however, a subjective term. This means that you – as the evaluator – must first identify what you consider as a substantial change, prior to performing your analysis. Any substantial variances from prior to current periods could indicate such things as: non-conformance with applicable standards, non-compliance with mandated rules or regulations, non-adherence to legal requirements, mistakes, and/or intentional attempts to inflate/deflate numerical balances. The bigger question – you as the evaluator must ask – is why is there such a significant change and can the facts supporting the change be reasonably justified.
Excel: Simply place you prior period data in one column (A) and then your current period data in the next column (B). In the subsequent column (C) enter a formula that will subtract the prior period amount from the current period amount and then divide the difference by the prior period amount. This will calculate the percent change (increase or decrease) from period to period. Example: (Current Period – Prior Period)/Prior Period; (B2-A2)/A2. Copy the formula down to subsequent rows containing classifications of data in which you wish to know the percentage change.
Based on your preliminary analysis of what is considered a significant change, begin to evaluate any percentages that are above that predetermined threshold. (i.e., 5% change in any significant accounts such as: revenue and accounts receivable).
Helpful Tip: As a rule of thumb any increase/decrease that causes you to have a questioning mind, warrants a closer evaluation. For example, if you ever experience any enormous variances such as 400%, please investigate further.
For Accountants/Auditors substantial increases/decreases from prior to current periods – in such significant accounts as revenue – could mean such things as fictious recording of sales, overstatement of profits to falsely show increased earnings, and/or improper recognition of revenue.
For the average Parker/Alice it could mean possible attempts to defraud banks in order to obtain lines of credit or loans.
Future Outcomes (Look for Performance Indicators)
TEA: “Heyyy, I bet you I know what’s going to happen next.”
Ratio analysis is a great way to gauge future performance of a company or evaluate an individual’s financial dexterity. Ratios take the measurement of any given financial component(s) – for a particular period – and compares it with other financial component(s) – of the same period – to determine such things as financial health over time. For instance, amounts such as: total debt or net income, can be compared to total equity or sales to compute a metric that can be used to determine profitability or financial distress.
The Altman Z-Score is a financial indicator that predicts the probability of a company going into some sort of financial distress, whether it be bankruptcy or insolvency, within the next two years. Here’s How:
Altman uses a metric of 3.0 to predict whether a company is expressing or moving towards financial distress. If the discriminant Z-Score is above 3.0, this means that the company is in a safe zone. If the Z-Score is below 3.0, this means that the company is expressing some type of financial distress. Note: The greater the Z-Score is above 3.0 the less likely the company is to experience financial hardship. The lower the Z-Score is below 3.0 the higher the probability that financial hardship will occur.
For Accountants/Auditors this can be useful information needed in making informing conclusions regarding company sustainability and when making informed decisions regarding future strategic directions of the organization. Also, this information can be used as a preliminary indicator in determining the reason(s) why this company is suddenly experiencing financial collapse. Are the individuals – in positions of power – conducting acts that are not in the best benefit of the business (i.e., misuse of company funds to support lavish lifestyles, making aggressive decisions based on personal gain/bonuses).
For the average Rose/Edwin this can be useful information when determining which companies to invest their savings into – in an attempt to strengthen their investment portfolio – or help them to determine which companies they need to remove from their investments – to prevent impending losses of life savings.
Helpful Tip: When computing financial ratios, you should take into account that your analysis may be skewed if the amounts used to compute the ratios include non-recurring items. Try to configure financial amounts to exclude these non-recurring items, especially when determining future projections of performance.
Example: If by chance you won $750,000 in a lottery one year, that is income that – more than likely – will not occur in future years. Therefore, you should exclude this amount from Income when calculating financial ratios such as Debt-to-Equity (Income), which is commonly used by Banks when determining who to issue money (long-term debt) to based on their projections on your ability to repay debt as it becomes due in the future.
✨Free Bonuses✨
“KEYNOTE: Numbers do SPEAK! If anytime in your life you frequently see the same number (integer) – repeatedly in your dreams, on a sign/billboard, and/or it keeps popping up on your screen. It will be best beneficial if you take the time to Google the meaning of the number. Why? Because for that particular period (moment) in your life, there is an important message that is attempting to be communicated to you. The number(s) are trying to tell you something, Listen!”