5

I work for an organization with a yearly budget that is an Excel spreadsheet consisting of about 25 large sheets that are linked to one another. There is a big summary sheet that draws it's numbers from sheets that have more detailed accounts (and projections) of revenue and expenses, which in turn take numbers from even more detailed sheets.

It has gotten to the point where editing the spreadsheet is slow, corruption is not uncommon. and it is unclear whether data is always correctly linked. Last year I found one revenue stream to be unaccounted for.

Is there an alternative software for detailed budgeting that handles large files better? Someone suggested Access and while I could see it being used that way, it's not really written to be used for budgets in the way that Excel is. Is there any other budget software out there?

Or is this size of a file ok for Excel and it's the computer that needs more memory/virtual memory/faster CPU etc? (I don't know what our business manager's computer is like, but it's decent though not a cutting edge machine.)

8 Answers 8

6

Couple of thoughts:

(1) Is there a particular reason why the budgeting is not incorporated into your existing ERP?

If you are using SAP or a similar accounting package then these usually have some kind of a built-in forecasting functionality. The added benefit would be that actual vs. budget could be run pretty easily and you could reconcile to the general ledger (i.e. missing revenue streams).

(2) Is the current spreadsheet as efficient as it could be?

For instance, do all the sheets really need to be constantly linked? Once the data is in there could you not just have the raw data as "paste values" with the summary sheet linking to them.

You could also disable automatic calculation which would increase performance massively. This article on MSDN discusses it (among other options).

Do you really need all the intermediate tabs? If you re-formatted the data (i.e. replicating a DB) then could you just have one tab of raw data and the summary tab pivoting this instead?

(3) External datasource

Failing both of the above, you could have the raw data stored in a database (such as Access) and then run external queries on it from MS Excel to import and manipulate. Office.com has a brief article on connecting to external data sources and this one discusses the different sources available:

Microsoft SQL Server™ OLAP Services (OLAP provider)
Microsoft Office Access
dBASE
Microsoft FoxPro
Microsoft Office Excel
Oracle
Paradox
SQL Server
Text file databases
Third-party providers
3

There are software specifically for budgets (Sage and Quicken spring to mind initially) however Excel 2010 is available in a 64bit Edition that can utilize more memory. Your problems are likely relating to the fact that the file is larger than the amount of memory in your computer and it's having to use the virtual memory.

I would recommend as a first course of action upgrading the memory to greater than the size of the file (the margin of how much more is up to you). The downside to that is you may then also need to upgrade your Operating System to a 64bit version so there are hidden costs there too.

3
  • 2
    I feel that Excel really isn't designed for this complexity. It is great for doing analysis of information, but not that great for the actual day to day tracking. May 19, 2011 at 21:28
  • 1
    I agree that it is a very broadly designed package and there are better out there, it really depends on the OP's budget, the best course of action might be a full software replacement and training in its use. May 19, 2011 at 21:37
  • Thanks for the suggestion on memory. We are cash strapped at the moment, so while I asked about alternative programs, getting a large software program and training people on it might be outside our budget.
    – chrisfs
    May 20, 2011 at 18:42
3

Although you are using excel to create a budget, it isn't budgeting software exactly. You really ought to look at software that does accounting, and there are quite a few. Peachtree and QuickBooks are both relatively inexpensive, and ought to handle most of what you desc ribe. There are single and multi-user solutions depending upon how you compile the content.

3

Windows or Linux:

Gnumeric is my choice. It is fast and will open xls, txt and many other formats.

It can also be compiled on osx.

2

It looks to me like the problem is not one associated with the choice of software, but more a process issue. The problem with a single large spreadsheet is that it gets so complex and interactive that it becomes impossible to sanity check the results or intermediate stages. It is this that is causing the problems like items not being picked up. My suggestion would be that the process for deriving and monitoring the budget needs to be looked at again and broken down unto smaller self supporting units. These units can then be more readily checked at different stages.

Whilst more complex software may help improve the management of the interactions between the different budgets, it still does not guarantee that human error is picked up and by breaking down the budget into sub-budgets it will be less prone to human error.

I appreciate this answer does not respond to the specific question about software for detailed budgeting but you really should at least take a look at the process to seee if it can be improved before jumping straight onto another piece of software because it may just make the problem worse rather than better.

2

You may need to check if your spreadsheets are designed for efficiency.

Some of the items below may seem insignificant when you're dealing with just one spreadsheet, but considering the fact that you have 25 Excel files containing years of data, spreadsheet design matters.

  • Check your formulas and references. Instead of referencing A5:A56000, try A:A or use dynamic named ranges.

  • Use INDEX/MATCH instead of VLOOKUP if you're dealing with more than 2 columns of data. Another common mistake is repeating VLOOKUPs as in the ff. example:

    Imagine if this formula was placed in a total of 1000 cells in each file, and if MyRange was a 50x100-cell range.

=IF(ISNA(VLOOKUP(100,MyRange,2,False)),"",VLOOKUP(100,MyRange,2,False))

  • Make sure your sheets don't have blank rows and columns that Excel is counting toward the file size. Check your scroll bars. If they're small or if DRAGGING them all the way to the end takes you to row 1048576 or column XFD, transfer your data to a new sheet. Do not highlight all cells then copy-paste, just transfer the non-empty cells.

  • Minimize external references if you can. Sometimes manually copy-pasting raw data from other spreadsheets is more efficient, especially since you mentioned you're losing data integrity due to file corruptions and improper referencing.

  • If you can solve the problem using pivot tables or revising your layout, use those instead of array formulas.

More best practices and tips for optimizing spreadsheets: http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm

2

You could give OpenOffice/LibreOffice a try. It has often shown to be more stable than MS-Office. Since it is free, you will lose just some time in the worst case.

1

Quite honestly, you've completely exceeded the intended usage scenario for Excel. You are pretending that Excel is a database, and you're running into the problems of doing that. Excel does not guarantee that data flows correctly. Excel does not validate your data such that you know it is genuine. Excel does not guarantee consistency of data across multiple sheets. Excel does not allow multi-user read-write access to the data, and does not allow you to centralize processing so that you can design a computer which will generate reports in a timely manner.

The correct course of action is to normalize your data, construct a database for OLAP, and then use reporting software (Cognos, SQL Reporting Services, Crystal Reports combined with SQL VIEWs) to extract the data into useful information. If you must use Excel to munge the data in a read-only format and generate charts, Excel can connect to an SQL database to do that without sacrificing the benefits of storing your data in an RDBMS.

What you're doing here is as silly as trying to use Word's tables for calculations of numeric data instead of Excel. You're using the wrong tool. The fix is to use the right tool.

1
  • the size described is quite within Excel's "comfort zone" - though I do agree, it's not being used for what it was designed to do
    – warren
    Jul 13, 2011 at 1:02

Not the answer you're looking for? Browse other questions tagged .