More Insight into the Dilemma of getting Data into Excel

By on January 9, 2011

In an earlier blog posting I explained the issues with using Excel to try and analyze data from a firm’s financial management system. The problem is getting the data from the system to the spreadsheet.

 There are few choices:

  • Connect Excel directly to the tables and download from the database. This is difficult since joining the right tables and using the right indexes probably requires a programmer for everything other than simple lists. For example, a client list with addresses can be done by a mid-level Excel user with a little help establishing an ODBC connection to a SQL database. Try analyzing a 5 year revenue trend by billing attorney, it’s more difficult.
  • Most standard reports can be run with an Excel spreadsheet as the output. This is fine, however, if you want to further manipulate the data, add calculations etc. all of these are lost next time you want to run or update the same report.

 So, what is the answer?  We need vendors to provide an entirely new approach to getting financial information into an Excel spreadsheet, PivotTables, charts and graphs.

 Here are the new requirements:

  • Existing spreadsheet models. Allow a user to take an existing spreadsheet(s) and insert data directly into specified rows and columns. Allow for multiple insertion points in one or s series of workbooks. CFO’s already have dozens of important spreadsheets that get manually updated each month with information from the financial management system.
  • Choose data refresh sequence. Allow a user to specify the sequence of refreshing the data that comes from the financial database. Don’t just refresh all the data automatically when opening the spreadsheet.
  • Select data components from a list. Allow a user to build a new spreadsheet, PivotTable or graph by just clicking on a choice of data elements from a list (described in English). The user
  • Automatic database attachment. The requirements above should all be available from an Excel Ribbon Bar or Task Pane with a direct connection to the all the firms databases including the data warehouse.

 In other words, make all this real simple so we don’t have to be programmers to get information into our spreadsheets.

 In my next blog post I’ll describe how new technology such as Service Oriented Architecture and Microsoft Communication Foundation can meet these requirements by exposing valuable queries from existing databases.


Comments (0)
Categories: Best Practices, Financial Management, Technology

The Challenges CFO’s Face in Using Excel for Analysis

By on January 4, 2011

Financial Management system vendors typically provide hundreds of standard reports, both in their core systems and in Business Intelligence data warehouses. Most of these reports can be modified by the end user and saved under a different name. These reports can usually be scheduled run, bursted  and electronically delivered to users via SharePoint or email.

 Law firms CFO’s, like their counterparts in corporate America have long ago chosen Microsoft Excel as their tool of choice for all forms of financial analysis.

 So what is the problem? The problem is that there are an infinite number of possible, special analyses of financial details and trends that “canned” reports can just never cover. Unfortunately it’s not easy for the typical CFO to get the data they want directly into an Excel spreadsheet. Executives can run a canned report and choose Excel as the output file type, but inevitably the user will make massive changes before the final analysis is saved.

 In theory, it should be easy to just hook-up Excel directly to a standard SQL compliant database and access all the data. Unfortunately, it’s not that easy.

The CFO just isn’t prepared to deal with:

  • Complex table structures
  • Understanding how to use primary and foreign keys
  • Choosing inner or outer joins for the logic that provides the right data
  • Setting up the data source, ODBC driver or writing queries

 So what’s the problem, it’s just TOO HARD for the average CFO.

 What can vendors do, expose your queries. I’ll discuss this in another blog posting.

Comments (0)
Categories: Best Practices, Financial Management, Practice Management, Technology

Internal Benchmarking and Business Tools – Where do you rank?

By on December 21, 2010

The term ‘benchmarking’ gets been tossed around a lot about this time of the year as firms develop their 2011 budgets. What does it mean to your firm? To most it is the process in which firms compare cost, hours recorded or the quantity of work against another firm of similar demographics or size. The gauge is somewhat the same across the board; people use surveys from providers such as Redwood Analytics or Altman & Weil to see where they rank, but how the information is used is another story.  

There are two categories of benchmarking, internal and external. Internal benchmarking is more of an individual thing. This is usually based on the use of business intelligence within the firm.  Individuals, departments and offices can view their ranking in productivity, performance to budget, value to their clients, and other criteria. Many times this analysis drive best practices as a result of fixing poor performing segments.

External benchmarking compares the firm to other firms to find out where they rank by revenue, profit, diversity, pro-bono work or other metrics. Tools such as benchmarking surveys – Lexis Firm Insight 3.0 compiles some of this information on a quarterly basis – are a great way for firms to see where they rank on a more competitive basis, on a group and individual level.

Some of the most useful information is how a firm ranks the profitability of areas of law and even clients. Understanding what to measure and how to measure the “measurables” (if that makes sense), are the most important parts of the process.

Below are a couple of tools firms a firm can use internally to utilize the valuable information already stored in their time and billing software. These tools can help measure and apply the benchmarking information:

Business Intelligence
I’ll start out by saying not all time and billing software systems come equipped with Business Intelligence, but, for the most part, the mid to up-scale systems provide this module.

 The three major components of Business Intelligence software are: data warehouse, user interface for selecting criteria and the presentation layer. Separate from the time and billing system, the data warehouse is a database designed specifically to provider fast analysis of granular data cube-type information about clients, attorneys, billings, cash receipts and expenses. Business Intelligence software allows firms to equip attorneys and other business managers, to gauge and proactively review information from the existing financial system data. This information is pushed to the end user, in a practical format where they can see it easily and compare the stats to others. This is where dashboards come in.

 Dashboards
Dashboards aid in internal financial benchmarking by providing information in a pleasant and intuitive format. The data warehouse can pull up granular information in seconds. Although it’s nice to have information such as profitability and billable hours quickly, in report format sometimes it’s just not convenient to look at. A dashboard provides the end user with an “at a glance” type gauge to see the financial metrics. If the firm provided attorneys with volumes of paper reports, the reports would have little impact, we all know this. When the firm provides attorneys with dashboards that are simple to read, easy to analyze and fast preforming, they will get used and attorneys will be in a much better position to support business decisions and strategic development, and this is only internal use.

I’ll visit external benchmarking at a later date.



Comments (0)
Categories: Best Practices, Financial Management, Productivity, The Legal Market

Financial Analysis – Surviving a Client Audit

By on August 15, 2010

It’s not a good Monday, in today’s mail is a letter from one of the firms largest clients requesting a detailed audit based on billings and payments from 2005 to 2007. The client is a public company and their outside auditors have discovered what they believe to be errors on how money was paid to the firm. No matter how wonderful the relationship with this client has been, this problem isn’t going away. The partner is already wound-up and reminding everyone who what this client means to this firm in terms of revenue and hours. Monday isn’t the worst of the days to come.

 A critical issue in the auditing of a financial system is the ability to look at “point in time” information. For example, looking at the transactions in the data base for “calculated values” like the clients beginning accounts receivable balance as of January 1, 2005, and the ending accounts receivable balance as of January 31, 2005 along with all the billing and payment adjustments that were made during that period. Most firms probably (might?)have the hard-copy printed reports for each month, but in an audit, how do you tie out the detailed transactions?

 Point in time calculated data is a snapshot of the data stored in the database in its native form and never modified thereafter. If you need the exact Client AR as of January 31, 2005, you don’t want to try and calculate it today. You don’t want the influence of any adjustments that were made after January 31, 2005.

 Firms using state of the art data warehouse technology can probably retrieve this information with little effort, for the vast majority of firms without a data warehouse, this would be either a significant challenge or impossible to provide.

Comments (0)
Categories: Best Practices, Financial Management, Technology, The Legal Market
Gallery
ist2_7730216-library-tables ist2_5516813-business-people-joining-their-hands ist2_11425109-business-woman-with-colleagues-at-the-back
Follow RainMaker