I'm hoping you can help me determine if what my company desires to do is possible with your software. We're a multi-facility and agency healthcare company with approximately 550 staff members and annual revenue of 38-40 million dollars. Right now we utilize 8 company files for our many companies. We're at the point now where we need additional reporting and analysis capabilities. Even though we're often told that QB is too limited for a company of our size, the mid-market ERPs which I've looked at seem bloated with unnecessary modules (CRM, HR/Payroll, etc) and don't do anything particularly special for our industry. We have a lot of specialized software for our business lines (nursing home, assisted living, home care, etc) that handle census, A/R, etc, and what we really need is just a good accounting software that has a back-end that's compatible with good reporting and analytic tools like Tableau, Excel, etc. (Much of the analysis we do includes information found in the specialized software packages as well as the accounting information so an ERP with extensive built-in reporting capabilities isn't that much of a draw; I'd rather use Excel, etc and have more control). Also, migrating to an entirely new system is a hassle, not to mention a financial commitment, that I'd rather not deal with if I don't have to.
I was for some time unable to access the Financial Summary subjects in the Excel add-in so I've spent a lot of time with the GL Detail. In terms of information that I want to summarize and put together, the GL Detail seems to have what I need. For example, I want to create reports that have information from the different statements - e.g. a P&L where some adjustments are shown reflecting both non-cash expenses (depreciation) and cash items not on the P&L (e.g. principal payments). The problem is, because it includes every transaction, Excel can't keep up with the simple calculated items (I get a 'There are too many records to complete this operation' error). I've tried filtering some data using MS Query but even if I just use data from this year only, I receive the same error messages. I've even tried generating a GL Detail list and then creating a PT from that, but I still get the same error messages. There are 401,080 rows - of which 171,253 are from this year - along with 10-15 columns, which I didn't think was a lot but it doesn't seem to be working.
I started thinking that I didn't need every transaction and could get away with just the totals - basically what I was seeing with the PT created from the GL Detail, except without the infinite drill-down possibilities. So, I was excited when I finally got the Financial Summary subject to load. Unfortunately, because the P&L, BS, etc are all different subjects, I'm still unable to perform the analysis that I need. I need to have access to all different accounts in order for this to work. Having to use 'Periods' set in the QQube Configuration Tool is also unsatisfying (and another reason why I much preferred the GL Detail approach), but I imagine that's a QB limitation.
Am I taking the wrong approach? Is my assumption that Excel can't handle Pivot Table calculations with that many records incorrect? I feel like we're a small company and Excel should be able to handle it, but I'm not sure why else I'm getting those error messages. Is there something else that I'm missing?
Thanks in advance.
Adam, our CEO Mr. Vigeant will contact you off line to discuss further but there are few things we can lay down for you:
We have one client who has 45 fairly large Enterprise Files in QQube - and they have all of the detail loaded. It takes about 45 minutes just to refresh.
They use power pivot tables because of the volume of data. Using 64 Bit Excel would also exponentially speedup your analysis.
I am not sure of the calculations you are using, but if they are array based (e.g. vlookup, etc.) then yes they are incredibly memory intensive. Pivot tables are much better for analyzing data, and much in the General Ledger Detail was designed for that (linked transactions, dimensions, etc.)
We have summary for P&L, Balance Sheet, and Trial Balance. For most people having the three years summary is sufficient and if you need to produce financials with both P&L and Balance Sheet items, yes it is kludgy. You could, using pivot tables, and Excel custom grouping - use TB (We have three year summary with balance, activity, and even budget) to get what you want.
If you want to use MSQuery to filter out G/L by date, you should have no problem, however you won't be able to get a current balance sheet.
What we have learned over the years, is that people love Excel, but when it comes to two things: reporting and dynamic data, Excel falls very short of the mark. Applications like Crystal Reports can run circles around Excel. And Dynamic data comes in as a contiguous block -e.g. you can't insert columns, rows, because the data is tied together; so now you get into creating manual queries, which is the ONE thing we try to steer people away from - it is too cumbersome.
What you are trying to do with half a million rows of data should not be an issue if you have enough horsepower e.g. an I3,5,7 processor and minimum of 3 Gig memory. Again 64 bit Excel screams with several millions of rows - it is night and day.
We will have Mr. Vigeant call you to see what types of analysis you are trying to arrive at, and see what can be done.
Choose a location