• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Optimizing large number of sequential calculations

Alkuan

Alkuan

New Member
All,

I've been working on building a projected cash flow model for my company, and it's running extremely slowly. It takes 7-8 seconds to respond when i change a variable or cut/copy anything on the spreadsheet.

The basic premise of the workbook is as follows:
  • User can enter default variables which are constant across all projects
  • User can enter specific changes to these variables for some projects if desired
  • Spreadsheet checks to see if values have been changed from default
  • Cash flows are calculated on a month by month basis (50 years x 12 months Columns, 100 rows x 12 projects Rows) = ~720,000 cells
    • A lot of PMT, PPMT, If, and Sum statements and some Hlookup's.
    • Many of these cells require referencing several different variables, which may be default values or not. I believe this is the problem causing the slowdown, i'll describe my attempts to address this below.
  • After cash flows are calculated, data can be analyzed on an annual or monthly basis, IRR's, NPV's, etc.
Troubleshooting/optimization steps taken:
  • Originally I had every reference to a variable doing a vlookup for that cell against a named range (CALCS) with the form Vlookup(Project#, CALCS, Column#, False)
  • Since the Project#'s were sorted, i changed False to True, enabling binary search, which helped a bit.
  • Next I eliminated all the Vlookup's, replacing them with direct references to a smaller number of vlookups (# of vlookups ~700,000-->~700). This barely helped.
  • Finally I replaced those 700 vlookup's with Indexes, as the vlookup search term was a sequential number. This also barely helped.
My next idea is to maybe try eliminating the index searches altogether, and directly reference each cell it's appropriate reference in the CALCS table. At this point though, i'm not even sure if that will help, or if i'm barking up the wrong tree so to speak. Any feedback or thoughts would be greatly appreciated. I've uploaded an anonymized version of the workbook to Google Drive. Warning, it's a fairly large file (7mb).

Thanks in advance for your suggestions/help!

Cheers,
Alkuan
 
Thanks for the welcomes and your responses guys.

oldchippy, That article was actually part of the inspiration for the project! I made an earlier version a few months ago which made heavy use of the Offset function (oops), and i also wanted to add some new features, hence the rebuild.

SirJB7, I did take a look over the new user list but ill take another look! :). Thanks for the feedback on the likely speed i'll be able to achieve. If i really won't be able to significantly speed up the current configuration, i may take a different approach.

One option i'm considering is to calculate each project on an annual basis, with the option to select an individual project for the detailed monthly analysis. That change would reduce the number of equations by ~10x, and should speed up the workbook considerably. There will be some loss of functionality in the time phasing of the initial investments, but i guess could keep those sections as thats a much smaller subset of the spreadsheet. If I end up making the change, i'll let you know how it goes.

Cheers
 
You must log in or register to reply here.
Back
Top