Embedded Files
5g is magic for Excel's modelers. It's a methodology for creating industry specific functions that anyone can use in any workbook without special skills or extra costs. it makes models faster, smaller, more robust and even easier to create.
No Special Skills - If you know how to use Excel's functions, like SUM(), you already have the skills needed to use 5g functions.
No extra cost - If you have Office 365, there is nothing more to buy. Most 5g functions are free and Microsoft's add-in for importing 5g functions directly into Excel is also free.
Faster and smaller - Models built with dynamic arrays and 5g functions take less memory and perform better.
More robust - Using expertly crafted prebuilt, pretested functions can prevent errors by as much as 50% and when coupled with dynamic arrays, cell error risk can be reduced by more than 90%.
Easier - Assembling models from 5g functions is faster and easier than writing bespoke formulas - those complex formulas 5g functions replace.
5g was created to reduce errors and eliminate the skills gap required to model with dynamic arrays.
Reduce Errors
Spreadsheets typically have a cell error rate (CER) between 1% and 5%. That means a small model with 3,000 formula cells is likely to have between 30 and 150 errors. The only way to find those errors is through auditing. Auditors catch most errors, but not all.
Now consider the implications of reducing that small model from 3,000 formula cells to 300. With a CER between 1% and 5%, a 300 formula model would likely contain between 3 and 15 errors. That's a 90% error risk reduction and was made possible when Microsoft's released Dynamic Arrays (DAs) to the general public in 2020.
DAs have a "SPILL" range. A spill range contains formula results, but no formulas. With DAs we can write a formula, say a period counter, in one cell and have it spill to all periods. Thus, a twelve month model would have 1 formula cell for its period counter, and 11 spill results. That reduces that model row's error risk by a factor of 12 to 1 or more than 90%! But the problem with DA's is, working with them requires advanced skills.
Eliminate the Skills Gap
DAs have this incredible potential to reduce model risk, but what good is that if no one can use them? To make DA modeling accessible to everyone, we created 5g functions. 5g functions package the complex formulas needed to work with DAs in financial models into simple-to-use functions that look and feels just like Excel's functions... only these functions are specific to financial modeling! Need a depreciation schedule? There's a 5g function for that😉.
A New Approach to Modeling
The traditional approach is to write one formula for each result. A 5g model breaks that one formula one result paradigm. 5g models have one formula in period one that spills results to all other periods. 5g models can also use functions that create multi-row results, such as the common corkscrew account which typically occupies four rows. The 5g corkscrew function occupies just one cell but spills to all periods in the first row, as well as the other 3 rows. 5g functions can push that concept further to create entire schedules, such as a depreciation schedule. One formula in one cell spills all results for all assets and all periods. The 5g approach is like assembling cars from prebuilt components, like an engine or a transmission, rather than from custom crafted individual parts.
Instant Expertise
Instant Expertise
5g functions are created by industry subject matter experts with advanced Excel skills. Their expertise is inside their functions. When a junior staff member uses one of their functions, the junior staff member instantly performs, for that function, at the expert's level.
Speed to Market
Speed to Market
5g functions are much smaller and simpler to use than the formulas they replace. With only a few keystrokes, major model sections can be added. When 5g matures, models will no longer be built from formulas, rather they will be assembled much quicker from functions.
Getting 5g Functions
Getting 5g Functions
5g functions are available free on the web and for sale at Eloquens.com. To find free 5g functions google "5g Functions Excel gist" or use this site's 5g Function Libraries page.
Example 5g Function
Example 5g Function
Below is the 5g function, Depreciateλ(). This example shows how 5g works with dynamic arrays to solve real world modeling problems in seconds.
Can I see an example model built with 5g?
Can I see an example model built with 5g?
The video at right features Depreciateλ in a completed 3-way financial model that is made of 100% dynamic arrays with 5g functions.
The model is simple but too valuable to give away. It is bundled with the 5g Financial Starter Pack which is available on Eloquens.com. The Financial Starter Pack is a workbook containing a collection of 5g functions with documentation and working examples for each function.
Summary
Summary
5g functions make creating models with dynamic arrays easy. They require no special skills to use and they work in any workbook.
If you are one of the technical elite, this site provides the information you need to create your own 5g functions. Share your expertise with coworkers to raise their performance. Publish your creations to the world to help others. Or sell your masterpieces on Eloquens.com.
Page updated
Google Sites
Report abuse