From ‘Spreadsheet Hell’ to “Spreadsheet Heaven”

Written By: Nasir Rizvi CA, CPA Founder of Kootio Inc.

Even among people selling business analytics (BA), business intelligence (BI), and financial planning and analysis (FP&A) software — jobs traditionally performed through Excel — you will probably still hear the singing of Excel’s praises!

In a 2011 study, accounting and tax advisory WeiserMazars found that 87% of the CFOs they surveyed relied heavily on Excel spreadsheets in their financial close, FP&A activities, as well as for budgeting and reporting.  Having said that, only recently has Excel’s full power been unleashed to provide an all-encompassing reporting and business intelligence tool.

The missing link that allows Excel to act essentially as a front end to SQL with all the functionality of Reporting Services and more, is now available.  Now, Excel can automate complex reports and complicated business models, out of reach by BI applications, yet still perform much of the analysis functionality provided by BI applications.  Before moving forward it is important to explain the distinction between reporting and BI.  A question posed on a forum the other day asked “Which is a better reporting tool, Tableau or Qlickview?” That’s a trick question in my mind.  They are both excellent analysis tools capable of slicing and dicing data, but neither is a reporting tool.  Reporting goes beyond just slicing and dicing data and includes building the most detailed business models, account reconciliations, prescribed form, and ad-hoc reports. Reporting is the presentation layer that precedes analysis and consumes much more time and resources.  BI applications have always been developed for analysis and have never catered to reports.

As a former comptroller, I spent 80% of my time producing reports, and about 20% of my time performing analysis.  That’s why I often wonder why companies spend willingly on BI solutions, but pay no attention to reporting tools.  In any case, no BI application that I have worked with can produce bank reconciliations, amortization waterfalls, cash flow projections, or in general, helped with the month end close.  This is why most BI applications have ‘Export to Excel’ functionality – because they don’t have the finesse needed to get the job done.

Conversely, many of the functions offered in specialized BI applications can be performed in Excel.  I have worked a lot with Excel and see it becoming a business intelligence tool for the power end-user; a front end for SQL and SSAS while maintaining spreadsheet functionality.  For example, Excel pivot tables and charts along with new slicer functionality already provide much of the functionality most organizations need in terms of analysis.  For data mining and business intelligence, the Data Mining add-in for SQL server provides mind-blowing functionality, and it’s free! However, one ingredient inexplicably missing from Excel’s functionality is graph drill down.  Once Excel becomes equipped with it, I don’t see why anyone would want to invest in software, consulting, and end-user training to essentially replicate Excel’s built in functionality. Especially given the ubiquity of Excel and the vast amount of end user hours already invested in it.

There has been an explosion of applications stating that they are Excel-based reporting applications.  However, at the end of the day, what they have managed to do is remove the ‘Export to Excel’ function from what would otherwise be a typical analysis application.  These applications have basically re-packaged current Excel functionality.  The perceived benefit received from these applications is that they offer real-time reporting capabilities.  Your Excel workbook is directly connected to data, allowing it to be refreshed as the underlying data changes.  That is great functionality.  However, it’s been around since Excel 2003.  The funny thing is most people I have trained in Excel reporting did not know that Excel can directly connect any workbook to multiple databases, including SQL, SQL Analysis Services, Oracle, and many others right out of the box.  Microsoft has simply not done a good enough job in educating it’s users on this functionality.

We have all heard the expression “spreadsheet hell”, a condition characterized by symptoms like having many versions of the same spreadsheet and having spreadsheets that should corroborate each other and don’t.  “Spreadsheet hell” is the main reason companies move away from Excel and pursue other solution for their analysis and reporting needs.  However, by tethering all organizational reports to a common data source, “spreadsheet hell” can be completely avoided.  However, for Excel to really become and full capacity reporting, analysis, and BI tool, one key hurdle needed to be overcome.

All Excel based BI and reporting tools, including Excel itself can only retrieve data from data sources into pivot tables (column and row type format) or other similar variations.  Pivot tables are cross tabulations very useful for analysis.  I use them all the time when I want to see sales by sales person, or labor by project, or total by account.  But they are not effective in producing and automating those account reconciliations, cash flow projections, and other complex business models organizations absolutely need.  Producing these types of reports requires more than cross tabulations and a more sophisticated report canvas than a grid.

The solution is to provide in-cell querying capabilities where each individual cell can contain its own individual query to the data-source.  For example, imagine being confronted with a tax return all in Excel.  Neither a BI application nor current Excel functionality could automate it.  However, if each individual cell could store its own individual query to a data source, the tax return could be automated using a combination of in-cell queries and Excel formulas acting in unison.

Qbica for Microsoft Excel creates the missing link, allowing automation of even the most complex reports and business models by the end user.  Qbica has essentially turned the tables on data.  Now the data must conform to the report rather than the report conforming to the data.  For example, start with a report template pulled directly from the web and populate it cell by cell while maintaining a perpetual connection to the data.  Add in some slicers, and the same report becomes completely dynamic - part report, part dashboard. In the past, data would be dumped into Excel, somehow transformed into something that classified itself as a report, and re-done to accommodate changes.  This is creating a fundamental shift in how Excel will be utilized in the future. Kootio’s technology is a great achievement in terms of closing a technology gap that exists between where data is stored and where it is needed – in Excel.

Unfortunately, “Finance is slow to adapt,” says Sanjay Sehgal, global enterprise performance management practice leader at The Hackett Group. “But young kids — who’ve had iPhones and iPads — are coming into financial organizations and perceiving them as IT museums. Young kids don’t want to be hand jamming 18-page spreadsheets. Organizations that get this right, and get this right quickly, will win in the market and the talent within the marketplace.”

Qbica Benefits Summary:

Non-technical End-Users can create any report, analysis or complicated Business model on the fly.

Easy to use, perfect for the end-user– no data-dumping, complicated formulas or scripting language needed – only simple click, drag, drop functions

Easy to implement – 1-2 hours to implement the reporting and BI tool that covers 100% of your reporting and analytic needs.

Affordable – priced so that everyone can own Qbica

Scalable – can be used by any size company, from any industry or vertical.

Taking a Closer Look at DSO

Days Sales Outstanding [DSO] is a measure of the average length of time your company takes to convert credit sales to cash. There is no magic number separating good DSO from bad DSO. To gauge the general health of your receivables, start by comparing your companies DSO with established industry benchmarks, but don't stop there. DSO is much more than simply a cash flow measure. Scrutinizing this ratio reveals much more than just how fast you're collecting. It also says a lot about the quality of your revenue and discipline of your business practices.

If you are struggling with a high DSO, you are struggling with a combination of one or more of the following: (1) Invoicing, (2) Collection efficiency, (3) Extended invoice terms, (4) Sales linearity.

Let's examine the ramifications of each.

Invoicing - Producing bad invoices screws up your inventory, unnecessarily creates extra accounting work, and above all, slows down your cash collections. Creating bad invoices results in inaccurate inventory numbers, causing you to either sell things you do not have in stock (but think you do), or not sell items that you think you do not have in stock (but actually do). To eventually fix an inaccurate invoice, you need credit memos, debit memos, conversations, applications, and when you‟ve had it, a full account reconciliation. Not pleasant. How many days have we already lost trying to collect on this inaccurate invoice? To make matters worse, how many customers actually phone to tell you about inaccuracies? How many pay an inaccurate invoice? The key is to nail the process and proactively review invoices. One way is to call your customers well before the due date to ensure invoice accuracy. This also serves to build relationships with your customers.

Collection Efficiency - It is simply the ratio of cash collected divided by total cash collectible. It measures the effectiveness of your collectors and dunning process independently of other factors. Apart from negatively affecting working capital, continued slow collections ages your receivables, and requires you to book larger reserves that impact your bottom line. Not good. To increase collections efficiency, work on the following: (1) Strengthen relationships with your customers (2) Implement a pre-defined dunning timetable (3) Promptly escalate issues (4) Meticulously document all collection calls (5) Take appropriate legal measures early.

Extended Terms - For sales people, granting extended terms is like throwing in the extra free knife to close the deal. However, this may really be indicating that extra incentives are needed to sell your products. The explicit cost of capital associated with extending terms is real and measurable; no biggie. Far more considerable, most analysts agree, is that increasing credit terms is a leading indicator of declining future sales revenue. Yikes! Remember also, average invoice terms are your DSO floor.

Sales Linearity - Having a disproportionate amount of sales occur near the end of the reporting period says your customers can wait to buy what you sell. For organizations using a sales channels model, this means you are likely 'stuffing' sales partners to satisfy top line revenue expectations. Consequently, margins (and sales people) get squeezed as customers and distributors wait for deals near quarter end. You have unwittingly lowered market expectations, and your high DSO is beginning to affect your stock price! In most companies struggling with DSO, linearity is often the biggest culprit.

To effectively manage DSO, analyze each component to see which cog needs grease. All too often, people concentrate all their effort on dunning their customers, completely ignoring the other factors. Also, design effective hand-offs between processes. For example, how will collections document and report found invoice errors back to billing? Lowering your DSO requires a balanced and collaborative approach. Here's an example of what I mean.

I once worked for a company who had a terribly high DSO. As the comptroller, I was charged with the responsibility to drastically lower it. For the president of the company, hiring a collection manager was the simple and obvious solution. However, additional headcount simply did not address the root cause of our problem. His 'strategy' had a low probability of being effective because it only addressed 25% of the problem, at best. My pitch, which attempted to convince him of a four pronged approach, was agreed to reluctantly. Here‟s how it shook out.

My first step was to understand our business processes, from sales to collection. I got involved with the invoicing process to get an idea of when and to whom our sales force was extending credit beyond normal terms. I wanted to understand our sales process to find out why 70% of our sales occurred in the last month of the quarter. Finally, I made myself a collector to understand why customers did not pay their invoices on time.

I decided to phone a list of customers that had invoices drastically overdue to see if I could strong arm the money out of them. To my amazement, I found that 8 out of the 10 customers I contacted had a total of 16 billing issues with the unpaid invoices. To make matters worse, they had already had the same conversations with our collections department. So, part of the problem was that our invoicing team produced erroneous invoices, and the only feedback mechanism to let them know this was through e-mails that were being largely ignored. A disciplined approach was needed. Creating a ticketing database that enabled our collectors to immediate communicate invoicing errors to billing was the solution. After agreeing upon SLA's, we saw a drastic reduction in invoicing errors. Collecting cash is much easier when you stop sending inaccurate invoices. We saw our DSO decreased considerably, but not to an acceptable point. We still needed to address the other factors.

By in large, I found out our collectors were some of the best. Not since watching 'Bent Nose' Larry in action was I so impressed with cash collectors. As impressive as his methods were, my focus was much more on results. I used the 'collection efficiency ratio' to measure performance. Our collection efficiency was definitely not the issue. We were collecting over 95% of amounts due in the quarter. In one quarter, we were over 100%. That meant we collected amounts not yet due! Not bad. What also helped was goal setting. You can't really tell your collectors to ensure they hit the DSO target. Instead try calculating the desired collections needed to hit your DSO target. Remember to consider AR balances you brought in from the prior quarter, use current quarter expected credit sales, and back into the cash collections target.

Things seemed to be getting better, except our DSO. Our invoicing issues were solved, our collection efficiency was high, but our DSO was still too high? What was being neglected was our sales linearity. Our linearity came in consistently at around 33%. That is, we sold only 33% of our product in the first 2 months of the quarter. With standard 30 day terms, this meant that we had no chance to collect two-thirds of our credit sales before quarter end. Even at 100% collection efficiency, and no extended terms, the lowest we could get DSO down to was 60 days. It was obvious that sales linearity was the most disruptive element in our DSO reduction initiate. The most important thing now was to effectively communicate this to our sales organization. Many slide decks and meetings later, our sales organization was finally convinced they needed to address sales linearity, if not only to address DSO, but also to address margin gouging, and the strain put on operations to ship the majority of our product through a very tight window. We implemented a program of sticks and carrots for our channel partners to change their back-ended buying habits. It didn't happen overnight, but we eventually got traction.

Ah, a job well done… Not quite. I learned salesmen adapt very well to perceived obstacles. To combat this crazy notion of buying and paying for goods relatively evenly over time, they simply began extending terms. I imagine the pitch went something like this, "Just submit the order before the last month of the quarter and I'll extend the terms so payment isn't due until the next quarter anyway. Hey, you'll get your linearity rebate, and you keep your cash. Who loves you baby?" Bottom line, keep a constant eye on extended terms by developing a reporting mechanism to track them, and have them approved by someone outside the sales organization. Extended Invoice terms beyond 30 days all but disappeared once our CFO began signing off on all requests.

The old adage, 'Cash is King' still holds true. All too often companies get side tracked with a different objective than making money, giving way to top line revenue objectives that attempt to create shareholder value. Just remember, if you can't collect on those top line revenues, your share price will eventually suffer.

Apart from building solid DSO processes, here are some other recommendations.

1. Use DSO as a Key Performance Indicator. Perhaps take it one step further and create a dashboard that displays the performance of each DSO factor separately.
2. Know the factors negatively impacting your DSO and be prepared to discuss the factors with your analyst community.
3. Set goals or SLAs and attach incentives and disincentives to each factor.
4. Create cross functional teams. Managing DSO touches finance, sales, and operations.
5. Remember, DSO is like steering a ship. Positive measures taken today take several quarters to see.

Visit www.cubexcel.com to see how your organization can take advatage of our software that enables users to populate dashboards like the 'DSO' in a matter of minutes.

Email sales@cubexcel.com or call 571.246.1560 to inquire about our products and services.

CubeXcel: Overview of the Financial Close, Consolidation & Reporting Process

Financial Close, Consolidation, and Reporting:

Some words by Deloitte Consulting LLP

Today’s finance organizations face multiple priorities that include the oversight of financial transactions, management of enterprise performance, attestation of financial reporting, and timely close and consolidation of financial data. As they grapple with these issues, Chief Financial Officers (CFOs) are always seeking ways to increase the efficiency and timeliness of their financial close and compliance processes. However, merely improving the speed of the financial close process is not enough. There is a competing demand for improved financial governance and increased transparency and reliability of data. Finance organizations need to proactively manage the challenges of data quality and prepare for new regulatory requirements to avoid creating a “perfect storm” for their financial close and consolidation processes.

Over the last decade, the financial reporting landscape has seen significant change. Finance executives face mounting pressure to increase the accuracy of financial reporting while decreasing turnaround time. Costs are being highly scrutinized as the longest recession in U.S. history continues. Regulatory agencies have introduced a host of new standards and accounting rules changing materiality thresholds, requiring detailed schedules, and new disclosures for public filings. To complicate matters, many finance organizations are being asked to do more with less, as headcounts are reduced in response to economic pressures.

Examples of the challenges faced by finance organizations today include:

• Typical public filings have grown from 10-15 pages to up to 75. Regulators and agencies continue to require more detailed explanations of public filings.

• Regulators have shortened the timeframe for filing requirements while increasing the demand for detail. Quarterly submissions are due within 40 days (down from 45) and annual filings due within 75 days (down from 90).

• The convergence of accounting principles generally accepted in the United States of America (U.S. GAAP), IFRS, along with XBRL requirements requires new definitions for financial statements. Compliance has taken on a life of its own — requiring more time and expense for technical accounting and financial reporting.

• Faced with reduced staffs and high burnout, employee turnover continues to be very high as many CFOs have determined the stress and risks outweigh the benefits of the role.

Finance leaders find themselves squeezed between meeting public demands, attempting to ease the burden on overworked staff, and meeting the technical requirement of regulators. With limited resources, how can finance organizations deliver accurate, useful, and timely data to an increasing number of stakeholders? What effective practices do organizations follow to streamline processes while meeting regulatory obligations and increasing transparency?

Avoid the domino effect

The financial close is a set of sequential steps requiring alignment and a clear direction across the organization. Each step in the process has dependencies on others, and delays result in a domino effect, pushing each subsequent activity back and resulting in more manual efforts and decreased transparency. Organizations that are able to monitor and react during the close cycle can reduce the impact of a breakdown in the process.

The figure below illustrates the typical financial close process within the context of other finance operations:

Plotting the course: Phase I — Ledger close

Most organizations are faced with a similar set of challenges during the ledger close phase. While some accounting processes vary between industries, the underlying themes remain the same.

Challenge	Why?
·          Nonintegrated systems slow the flow of data required to close sub ledgers and business units	·          Manual adjustments are typically required to finalize data Submission is impacted due to limited ability to extract a                   complete data set without time-consuming manual intervention
·          No close related accounting activity is performed during the critical ledger close period	·          Corporate ledger closing requirements have not been formally defined resulting in site-specific processes ·          Status quo has evolved over time resulting in unclear roles and responsibilities
·          No capabilities to track performance to identify bottlenecks or recurring issues	·          Routine occurrences are known only at the source and do not get resolved ·          Environments are reactive when issues arise because expectations are not communicated

Effective finance organizations address these issues by establishing a ledger close governance framework. Central components of this governance include:

• Policies and procedures — establishing rules and defining requirements for accounting activities can lead to standardized processes and help mitigate the risk of accounting errors. Creating policy and procedure manuals is a good way to help achieve knowledge transfer.

• Roles and responsibilities — defining tasks and dependencies when ambiguities exist between functional areas can help clarify key activities and decision points. This can help increase the efficiency of a close process by reducing duplication of efforts.

• Ledger close calendar — developing a close calendar can provide finance with the ability to identify dependent sources of information for key activities and track progress against milestones. Additionally, assigning ownership to individual tasks can help improve status reporting and accountability.

• Ledger close scorecard — defining and tracking key close metrics can enable organizations to perform post-mortem evaluations to more easily identify improvement opportunities and facilitate target setting for future initiatives.

If you don’t have a plan, you typically won’t reach your destination on time. An effective ledger close governance framework should provide the tools to develop the plan, communicate it globally, and reinforce the roles and responsibilities that lead to greater accountability. Additionally, it should help encourage a proactive environment where more issues can be solved.

Create pieces to one puzzle not many: Phase II — Consolidation

The close and consolidation of financial books is the process that corporations use on a monthly or quarterly basis to reconcile, translate, eliminate, consolidate, and report financial information. Each company has their own recipe of closing and consolidating their financials with varying degree of efficiency and standardization.

What if:

• Business unit source ledgers had been absorbed over time and account structures have not been aligned?

• Transaction data required manual intervention in order to provide reporting and analysis?

• The U.S. dollar was not the currency supported in field ledgers?

• Segment and legal entity structures differ?

These are some common questions raised by stakeholders about the challenges of transforming transactional data into high-quality information. Often, the answers leave finance leaders in a position of having to explain why it takes so long to produce “multiple versions of the truth.” This can result in increased risk, more time required to reconcile data, and less time available to analyze it.

Challenge	Resulting issue
·          No commonality in account structure	·          Inconsistent account use ·          Inaccurate financial statements
·          Multiple currency translations	·          Different translation rates ·          Unpredictable currency translation implications
·          Unresolved Intercompany eliminations	·          Manual corrections ·          Extended reconciliation efforts
·          Lack of visibility into legal entity or sub consolidations	·          Offline tax reporting ·          Different rollups = different totals
·          No established hierarchy structure for accounts or entities	·          Use of multiple spreadsheets for consolidation ·          Inability to adapt to Securities and Exchange Commission (SEC)/Agency requirements

Unlike the ledger close phase, consolidation does not have a simple blueprint that provides financial executives with a framework for improvement. However, there are guidelines that effective finance organizations follow to make all the pieces fit together better:

• Define commonality — defining a common chart of accounts is typically the first step toward aligning local ledger data. Requiring field ledgers to map to a common set of data elements speeds consolidation, can improve accuracy, and help establish the skeleton of financial statements and supportive schedules.

• Create flexibility — creating flexible hierarchy structures can enable the consolidation tool to serve as the central repository for financial data. Establishing a multidimensional, multi-scenario solution can provide financial reporting a single platform to develop financial statements and schedules to help meet accounting and compliance requirements.

• Combine process and technology — before implementing technology, process owners and the technical design team need to collaborate on business requirements to better understand the current state. Combined efforts can result in a solution that will help reduce manual data entry, improve controls, and move the organization toward standardization.

The overall goal of the consolidation process is to collect and transform data into financial statements. Leveraging the principles listed above will help finance leaders in their efforts to provide tools and data to their growing number of stakeholders who rely on accurate and timely financial results to evaluate and measure performance, devise business strategy, and meet regulatory requirements.

Communicate with stakeholders through reporting: Phase III — Reporting

Financial reporting standards are under constant modification. The investment community and regulators continue to require more organized and systematic exchanges of financial information. This impacts both how and when financial data is distributed and communicated. In recent years, organizational and regulatory changes have increased both the level of effort and technology required to meet an ever increasing demand for more standardized data, quicker access to financial data, and visibility into financial reports and disclosures.

Reporting was once considered an outcome of the consolidation process; however, effective finance organizations now drive design of financial consolidation and reporting technology solutions by first defining the financial data detail requirements of compliance reports and schedules. Given today’s landscape, reporting requirements must be fully understood and taken into consideration when designing policies and procedures for all aspects of the financial close process.

What is being asked of financial reports and disclosure schedules?

Requirement	Challenge
·          Standardization and consistency in data	·          Multiple financial statement hierarchy definitions ·          Time consuming manual efforts to prepare useful results
·          Flexibility and adaptability	·          Inability to add or modify financial data structures ·          High cost to update and maintain systems
·          Transparency and visibility	·          Lack of access to financial statement details due to offline combinations ·          Reliance on Excel spreadsheets and manual processes
·          Repurpose financial data	·          Multiple versions of the truth ·          Disparate and nonintegrated reporting
·          Global applicability	·          Different regions, different requirements ·          No universal or common unit for rollup and reporting

Financial results have never been more scrutinized.

Quality results need to be provided timely, accurately, and to multiple stakeholders in regulatory agencies and the investment community. Effective finance organizations consider reporting the most critical component of the financial close, and therefore let reporting requirements drive the design of the entire process.

Visit www.cubexcel.com to find out how CubeXcel's 'Next Generation' Reporting and Analysis Software can help your company.

Business Intelligence; Reporting and Analysis: CubeXcel...What we do and why we do it

Business Intelligence; Reporting and Analysis: CubeXcel...What we do and why we do it: "The industry is plagued with applications that are overly complicated and not user friendly. These applications simply do not addres..."

CubeXcel...What we do and why we do it

The industry is plagued with applications that are overly complicated and not user friendly.  These applications simply do not address key problems facing finance departments on a day to day basis.  Financial Reporting Management and Business Analytics should be easy but can be out of reach of those with even the best financial management skills. Financial reporting applications should not require a team of IT experts to implement a solution, and should be readily available to all users in the organization.  CubeXcel has designed a system to simplify how reporting is done.  We stripped away the unnecessary complications associated with financial reporting software and designed the first program based on the principal “for accountants by accountants.”  CubeXcel is a 'next generation' reporting and analysis tool that can be used 'today' and represents an evolution in the Financial Reporting Management and Business Analytics industry.

Visit our Website today at http://www.cubexcel.com/