MS Excel is the standard tool for financial and project budgeting modeling, is the greatest and versatile tool ever invented for business, and my respect for that. But when the business has many dimension like Cost Centers, Business Units, Products, Accounts, etc. the tables become big really big with a lot rows and columns and the excel file are big more than 20Mb, so the spreadsheets starts to fail.
This failure makes difficult the process to work with these files. Nowadays exists several excel hacks to solve some of these problems. that arose with work with huge excel files like using ranges, matrix functions, etc.
According to MS Office support page for this kind of trouble-shooting suggest the following:
- Verify/install the latest updates
- Scan Excel for Known Issues
- Start Excel in safe mode
- Investigate possible issues with COM add-ins
- Investigate Excel file details and contents
- Check whether your file is being generated by a third party
- Perform a selective startup to determine whether a program, process, or service conflicts with Excel
But sometimes, none of these hack solve the issue from the root. Today exists a new paradigm in modeling that is developed model using multi-dimensional calculation or Cube Calculation (OLAP). Software like Cubeplan, Essbase (Oracle) or Quantrix use these approach for Financial Model for Budgeting.