Naw, XL is the correct choice because of #3. If the finance staff build a complex model in some other program, normal finance successors will not understand how to maintain it. And Access is ALWAYS the wrong tool for the job. Never go database.
Well, usually you should hire successors that are at least as competent. If you have a super-complex model, you would hire people who have the skills to handle it. And by VBA I meant XL-VBA not Access. If, like OP describes it, you have a cell filled with “page-filling” code, you might want to think about Macros and VBA-routines.
Most people in the corporate world can’t model with VBA …it complicates things for your reviewing manager.
I’ve seen complex models…using fincad.
Are you that type of modeler highway?
Try using the evaluate formula view to follow a formula step by step. This is how I understand how complex formulas are working quickly.
I go with breaking the calculations up in steps. It creates more cells, but I then datagroup them together and collapse the columns. I think performance of nested formulas vs formulas in several cells is roughly the same.
part of building models is skill with formulas. The other part is skill in presentation so someone else can follow it. And have redudant calculations to error check the complicated formulas, as things easy go wrong when no one understands how it works
Stuff like this baffles me.
Best practices for modeling #1 - Keep it simple so that others can understand it.
Anyone who can’t explain their model to others in their department is not adding much value.
Fantastic website. Bookmarked as a great resource for reviewing things I should not have forgotten!
You cannot keep it simple while increasing its flexibility and accuracy unless you VBA code everything. At which point, you need your entire team to be programmers.
So create a few easily explainable formulas in VBA and take out all the pages of code in the cells. That doesn’t require everyone else to know VBA unless the formulas need to be changed constantly.
You can also make notes in VBA code so that others that don’t code can at least understand the logic.
I consider my self to be one of these so called “Grand Wizards”. I can pretty much write any formula you need and can code in VBA. Sometime “simple” is in the eye of the coder but, I agree with a lot of what has been written above; keeping it simple for others to understand should always be a top priority. I remember a specific case where I ran into this myself. I was asked to decode a formula that someone had written (who was skilled in Excel). This was a simple case of I know a lot, but not whats required - this person wrote a paragraph style formula, using arrays and multiple if/then/and/or statements when a simple “=SUMIFS” would suffice. Not knowing what Excel can already do for you is sometimes the biggest problem. I see S2000 has already offered his help; however, I will do the same. Feel free to PM me if you want another set of eyes to help you decode the formula.
When I have a disgustingly long formula (which usually involves things like IF(), INDEX(), MATCH(), AND(), OR(), MIN(), MAX(), and occasionally OFFSET()), I generally include a comment in the cell or cell label that explains the idea. (e.g., “search for matching value in X table, using value A if flag x or y is set, otherwise value B, cap at 75%”). This at least gives someone some bread crumbs as to what the formula is trying to accomplish.
I’m not 100% in agreement on the “it’s bad modeling” argument for excel. In a typical coding environment, the coder will use indentation and line breaks to break the formula into digestible chunks that make more sense. Excel (outside of the VBA editor) doesn’t really let you do that, and it’s one of the reasons that breaking things down into subcalculations done in different cells is often a good idea (albeit not always possible or practical). It’s one of the things I least like about Excel.
An interesting issue with some modeling in excel is “at what point do I start to use named cells.” Often times it’s not clear that the model is going to be complex, so I’ll just do regular references in an early stage, but then some models evolve and really would be better implemented by calling something “clientAge” rather than “$E$6”. Ideally you’d map it out on paper first, but sometimes you are doing protyping and you’re not sure exactly what output is going to give you the best perspective, then later ends up evolving into the real thing. Aside from the purist response of “Always name input parameters,” does anyone else have a useful rule of thumb here?
Will do!
I wish the guy writing our models had your heart 
Fincad is an portfolio optimization convenience tool, AFAIK. I am more into fundamental value models, which I build entirely in R. Yes, the bread-and-butter manipulations for setting up future financial statement equalities are a royal P in the A, but no other software comes even close to R when it comes to statistics, forecasting and machine learning. I use a lasso-ridge regression to filter macro data for relevant cost & revenue drivers, for which I create custom forecasts using ARIMA-GARCH-VAR models. Then I optimize the forward looking financial statements using support vector machines and neural nets. And in the end I top it off with a nice 10M Monte Carlo simulation based on custom factor kernel density estimates, so that I end up with a searchable conditional target price distribution. Good luck doing that in XL. The beauty in modelling in code rather than in WYSIWYG-solutions is that I can run the model every day with automatically updated data (calculations take about 20 min.) and get a target estimate that takes into account the most recent data.
Great post, I agree we can’t assume this is a bad model. It’s easy to sit back and say we could have done it better, not so easy in real world conditions. Also most people in finance have never worked with truly complex models, and thus think everything can be made easy. Some jobs are just going to be complex no matter what you do, so it’s choosing the lesser evil. My goal is always to keep it simple. Which runs up against how things work in the real world; requests from many different internal (and even external) people asking for complex additions to the model over the course of years, and requests for a second model for investors with secret s**t hidden. Having learned from experience I now always map things out from the beginning assuming it will grow into a monster and by used in the department for a decade. Named cells are great “growth rate”, “tax rate assumption”, etc. And input lots of comments into cells “this is net not gross, pull the data from x report”, and “purpose of this calc is to…”. Personally I stay away from databases, macros and VBA…instead building it the long way in Excel using functions every finance person understands. In my experience macros break “oops error on line 93” and the only person in the department who can fix it is a p***k (haha, it’s always a finance-meets-IT type, and we all hate those), “yeah man I budget two weeks for that”. No, we need it working before the market opens. I also don’t put passwords on models. I’ve seen this so many times, nobody can get in, guy who knew it got fired, or is out sick. If I must I add a password which is no password (you just click enter when the question comes up to unlock it). All the analysts and managers know I do this, so they don’t need the specific password, if they don’t know I do this, they shouldn’t be in the model. 
Okay, we are nerds. 
This is nothing. Compared to the conversations over at Wilmott, this is pretty much Pee Wee League.
Nerds who still get the girls. Can’t say that about Wilmott!
I dunno bro…some of those Wilmotters are into some serious s#!t…highly paid = highly laid. Period. I mean, anonymous forums can make the most introverted doofus appear to be this extroverted rock star. You just never know about people.
Grand wizard?
Grand: highest, or very high, in rank or official dignity: Also, main or principal; chief:
Wizard: - a person who practices magic; magician or sorcerer. Also, whiz, wiz,
[wiz] (Show IPA). a person of amazing skill or accomplishment
http://dictionary.reference.com/
