How hard is modelling with dynamic arrays?
What if you could cut financial model errors in half and build models faster?
Creating 100% dynamic array 3 statement financial models can be relatively quick and easy with 5g functions. More importantly, dynamic arrays can cut formula cells (and potential failure points) by 90%. On top of that, 5g functions reduce errors by 50%.
How important is error reduction to you?
If you are not familiar with 5g functions, 5g stands for fifth-generation modeling. These are prebuilt, pretested formulas that work natively in Excel, no VBA required. They look and feel like regular Excel functions, complete with inline help. Anyone can import and use them for free.
Watch the video below as I convert a full 3-statement financial model to a 100% dynamic array version, in just 10 minutes. The results look and function identically, but the new model has 90% fewer formulas and inconsistent-formula errors are impossible.
90% fewer formula cells means 90% fewer potential failure points.
After converting the model, we will add functionality where one input switch can instantly convert the model from monthly, to quarterly, to annually. Try doing that with traditional methods!
After watching the video, let me know in the comments if this looks too complicated for you. Let me know what you don't understand or are uncomfortable with. We are here to make 5g better and easier so financial models can have fewer errors and perform faster. Concerns from you will help us do that.
Several companies are already using 5g models and more are joining every month. Do you want to be a 5g model provider? Explore free 5g function libraries and tutorials at 5gModelling.com.
Senior Financial Analyst | I Help Companies Succeed in Project Finance & M&A through Expert Financial Models | $1.6 M Funding & $300 M+ Deal Closed
1dCraig Hatmaker I was very reluctant to shift my models to dynamic arrays, but now have started making the ones other than Project Finance Models. It's really great what 5g modelling and dynamic array is doing with FP&A modelling, having models which are updating in real time connecting with power query. Now you don't need to wait 1 month after a quater close to see what should be the decision on how the company actual numbers and the dynamics are changing. This also reduces model audit time significantly.
Financial modelling: training courses in the classroom and live online, lead instructor, self-paced learning, financial model build, risk management and review
1dThis is a helpful video showing a well-structured cell-based model being converted into a well-structured array-based model. I can imagine this being part of a QC process by which the results of the array-based model are compared with the cell-based original and differences highlighted for further investigation. No question that the number of unique formulas have been reduced and therefore possible points of failure have been reduced (from the fact that the concept of "copy consistently across the columns" is now redundant). I'll buy the M/Q/Y functionality -- although I suspect others may value that a lot more than I do. "Let me know what you don't understand or are uncomfortable with": 1. I'd appreciate a definition of a "non-technical modeller" (mentioned in the narration); and 2. More guidance on how you have chosen whether to run with (i) a DA or (ii) a 5g LAMBDA for the 30 per cent of formulas that are deemed "more complex".
Helping Finance Teams Go Home Early with Agentic AI + Automation | Microsoft MVP | Curiosity Catalyst | #TheGamingLearning
1dBuilding the initial lambdas requires some mastery, but the housekeeping part becomes very simple. Just yesterday, I was creating a depreciation schedule at my work using dynamic arrays. I made a few mistakes in the lambdas, but all I had to do was open my AFE and edit the lambda. That's it. I've used that one formula in three different places, and all of them updated in a second. If it had been a scalar model, I would have had to change each formula, copy and paste them, and take care not to mess anything up. This experience comes from my internship, where five students were updating one workbook. Everyone had their own way of writing formulas, and when it came time to organise the workbook, it was a nightmare for me.