“Clarity affords focus.” - Thomas Leonard
Traditionally, most business users have had to rely heavily on pre-canned reports to make business decisions. The ever-increasing needs for custom and tailored information by the business community are forming the basis for self-service BI, providing access, autonomy and ease of use.
However, with the introduction of self-service BI, the traditional “blind spots” have been magnified around critical areas, like information security and ownership, authentication and authorization, and regulatory or legal requirements. One critical blind spot is rounding methods, which can cause confusion in self-service BI by creating irreconcilable multiple versions of truth that can have serious business and financial impact.
Rounding numbers is important, because it is done in conjunction with forming business rules to support decisions. The thresholds are typically set at high-level abstractions; for example, purchasing five percent or more of a certain product will trigger a discount. Rounding also plays a role when decisions are based on data segmentation and grouping. For example, when incentives or penalties are based on calculated metrics, such as product return rates or contracted levels, data is segmented and then grouped before the incentive or penalty rules are applied. Most such rules require rounded values on the calculated metrics. In traditional BI reports or dashboards, rounding methods are centralized and tend to hide complexities. With the autonomy and diverse use of self-service BI tools, and without knowing the impacts rounding methods cause, business users can easily cause costly mistakes.
The following are some business scenarios where rounding practices may have consequential business impacts:
- A consumer packaged goods company incentivizes retailers based on how much of the products sold are returned by customers.
- A health care provider gives incentives to the third party claims processing agency based on the percentage of claims returned or rejected by the insurance company.
- A bank rewards a collection agency based on percentage of claims resolved within one month after referral.
Let’s dive into more detail and discuss the first scenario of a BI solution for the CPG company’s business incentives programs. The company expanded into selling perishable goods that have a short shelf life. Once the product expired, the company had to manage the product’s reverse logistics of safely disposing it, and therefore product returns significantly impacted the company’s bottom line.
In order to encourage the company’s retail customers (i.e., retail chains and independent stores) to minimize product returns, a specific set of promotions were introduced for those product lines. One critical metric that supported these promotions is the product return rates, calculated as the product volume returned against volume sold. Another factor of consequence is a business rule, created for chain stores, that comes into play:
- If the chain, as a whole, has less than the threshold set for the product return rates, all the stores of the chain become eligible for the promotion;
- Otherwise, only the volume sold by the retail stores of the chain that have equal to or less than the threshold becomes eligible for the promotions.
The incentive payments are made quarterly, and administration of these programs requires BI solutions that provide accurate information on return rates by various data aggregations such as chain versus independent, by geographic territories, by states, by regions, by type of retailer contract with the company and by the product line.
While testing this incentive program, it was determined that the volume eligible for promotions aggregated at the state levels did not reconcile to the corresponding totals at the region level for a given return rate, sales quarter and product-line. To add to the confusion, though the total volume returned and sold reconciled at state and region levels, when segregated by the volume eligible and not eligible for promotions (based on the return rate thresholds), the final numbers did not reconcile.
Further analysis unearthed three different issues related to rounding, roll-up or combination of both that caused the problems:
- Poor understanding or implementation of rounding methods,
- Lack of clarity on rounding level, and
- Inaccurate calculations involving segregations and aggregations.
Poor Understanding or Implementation of Rounding Methods
A critical business requirement was missed during the requirements phase, and expected implementation of the business rules were not clarified until decision-makers were specifically asked for adoption of a rounding method -- in other words, too late.
Incentive payments were made based on the volume sold that was eligible for the promotions. Initial reports were based on the return rate calculations where the BI tool was rounding calculated percentages to two digits, using athematic rounding. It was expected that the percentages were rounded to the whole number using “the round toward zero method” that passes on the benefit of rounding to the retailers. This method of rounding, for example, makes values that are from 1.1 percent to 1.9 percent down to 1percent. As a result, this made less volume eligible for the promotion.
It is therefore important that BI professionals, as well as users, understand different rounding methods. The most common rounding method followed and taught, which many think of as the default, is the round half-up or arithmetic rounding method (e.g., 4.4 becomes 4 and 4.5 becomes 5). This technique is also used as a default in common spreadsheet programs, such as Microsoft Excel. There are several other rounding methods employed in business, which data professionals should be familiar.(A brief description with examples of different rounding methods is discussed very well in “Rounding Algorithms 101.”)
Lack of Clarity on Rounding Levels
Another issue is the lack of clarity on rounding levels, which can cause significant confusion and negative impact to the end results. Using the previous example, the following hypothetical scenario shows how rounding levels impact the incentive payments. The CPG company rewards retailers based on product return rates, by their consumers, of units sold. If the return rates are less than 2 percent of the units sold, they get an incentive of 50 cents per unit sold for a particular sales quarter. The retailers whose return rates are equal to or greater than 2 percent would not get the incentive payments.
The table (see Figure 1 below) depicts how the same number of units returned or sold can result in $0 to more than $4 million in incentive payments disbursed, based on the level to which return rate calculation percentages are rounded off when the arithmetic rounding method is used.
In looking at the data in the table, a total of 13,415,983 units was sold. When the return rate is taken out one decimal point, no incentive payment is triggered for returned units volume less than 2 percent based on business rules. However, when the return rate is taken out two decimals, the returned units volume less than 2 percent results in an incentive payment of $1,150,000; when return rate is taken to three decimals, the returned units volume less than 2 percent results in an incentive payment of $2,350, 045; and when return rate is taken to four decimals, the returned units volume less than 2 percent results in an incentive payment of $4,050,218.50.
Wrong Calculations Involving Segregations and Aggregations
Within this example, another issue discussed above was the promotion eligible volume numbers did not reconcile between the region level and the state level summary reports. The volume should have been segregated into eligible and non-eligible for promotion before the data was aggregated at the state or regional level for higher level summarization. When the reports aggregated the volume data before the return rates were calculated and then volume was segregated into eligible or not eligible volume, some of the volume was misallocated into eligible volume.
While issues with segregations and aggregations are not related to data rounding, they are mistakenly related to rounding, often referred to as rolling-up issues.
Lessons Learned and Takeaways
- Pay attention to rounding methods and policies. Be attentive to the rounding method and their impact during the requirements phase of BI projects. Make sure you have well-defined rounding standards that are documented as requirements and available to business and technical teams.
- Verify rounding errors. Pay attention to the semantic layer during verification and validation, ensuring that rounding is properly implemented and consistent.
- Verify disclaimers or assumptions. Establish effective targeted communication on rounding policies as part of disclaimers, assumptions and rules that explains available metrics and calculations.
- Understand and document rounding considerations. Have a grounded understanding of rounding mechanisms and their impact when business rules are defined; unintended financial consequences can result from rounding levels being used incorrectly.
- Provide focused training on rounding. Provide training on the various rounding policies and methods for technical and business teams.
- Be aware of the tool’s default rounding. – Be aware of rounding rules as configurable parameters for an organization’s BI and/or reporting tools validate defaults against rounding policies.
- Pay special attention to segregation before aggregation. Prevent issues with data roll-ups (i.e., reconciliation) when enabling a semantic layer. Give special attention to all the different analytical scenarios users may encounter when segregating data using business rules before aggregating.
In summary, rounding strategies or standards are driven by fair rounding practices, based on each company’s unique situation. Typically, it is assumed that appropriate rounding practices are followed before the data is presented to end users. Within self-service BI, if each user uses different methods or levels of rounding and their own methods of segregations and aggregations, users will reach various, irreconcilable versions of the truth.
Register or login for access to this item and much more
All Information Management content is archived after seven days.
Community members receive:
- All recent and archived articles
- Conference offers and updates
- A full menu of enewsletter options
- Web seminars, white papers, ebooks
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access