When Traditional Models Fail

Errors creeping undetected into our models... omissions like mirages we thought were there but weren't... bad assumptions from cracked crystal balls causing catastrophic losses... these are the stuff of modeling nightmares.

I should never have read EuSpRIG's collection of spreadsheet horror stories and I probably shouldn't subscribe to Patrick O'Beirne EuSpRIG emails that periodically deliver a fresh modeling disaster to my inbox. Yesterday's email struck a nerve. It opened (from The press):

"An Alpine Energy error that led to overcharging customers millions of dollars..."

How did this happen?

“The spreadsheet modelling allowed some historical company assets to depreciate to below zero,”

Mistakes like this shouldn't happen. Had the model been reviewed, the mistake probably would have been caught. It is easy to spot, that is, if we don't assume that the modeler might not add formulas to stop the depreciation at the end of the asset's life. No matter how well thought out our standards are, no matter how simple our formulas be, catastrophic mistakes like this can, and do happen. But what if we could make mistakes like this impossible?

Can we make mistakes like this impossible?

Yes! Using the 5g modeling methodology, we have made this particular mistake impossible. 5g methodology provides guidelines for creating functions from 100% native Excel formulas, specifically designed for use by others. To that end, the 5g methodology ensures functions can be used by anyone of any skill level and can be easily imported into any workbook. 5g functions are self documenting, include inline help and version control, and are thoroughly tested before publishing. To learn more about the 5g standard, visit 5gModelling.com. It is all free.

The 5g function that makes this mistake impossible is Depreciateλ(). Depreciateλ() can create an entire depreciation schedule for any number of assets with each having its own acquisition costs, in service date, salvage value, and depreciation method. One formula, in one cell, does it all and the possibility of depreciating below zero is impossible.

What else can 5g do?

Making the impossible, possible

5g can provide solutions not possible with traditional formulas. An example is PMTAλ(). PMTAλ() is like Excel's PMT() function in that it calculates equal monthly debt payments but for commercial accrual methods: Actual/360, Actual/365, or Actual/Actual. I have only seen this calculation performed in Excel using Solver or VBA. Now, we can perform this calculation using native Excel.

Solving Circularity

5g functions have solved circularity in things like revolving credit (think credit card debt) that rely on average debt balances. In this situation, to know how much to borrow, we must first know the current period's opening debt. But we must also know the current period's closing debt so we can average them. Closing debt includes the revolving credit amount which includes the closing debt... and so on. This is a circular equation. To complicate matters further, to know how much cash we have to apply to the revolver, we must know the current period's opening cash position which includes average interest gained from investing cash which can be impacted by the revolver. This is circularity within circularity. 5g has solved this problem without VBA, without 'circuit breakers', and without enabling iterative calculations.

Size, speed, flexibility

Dynamic arrays make models faster, smaller, and incredibly flexible. They can make model timelines expand or contract without having to change any model formulas. They can make adding more customers, accounts, items, locations, etc. effortless. But if we want to use dynamic arrays in financial models, we need help from functions like 5g functions.

With all these advantages, why doesn't everyone use 5g?

5g is new and new things always take time to accept. This is largely due to people not knowing what 5g is or the value it adds. Articles like this help educate them on that. Getting educated requires time and effort and many people simply would rather not spend the energy when what they have is working. But if everyone else moves forward with 5g, won't the skeptics be left behind? This fear of being left behind can lead some skeptics to find ways to discredit new ideas so the old ways prevail. Here are some of the objections I have heard.

Are 5g functions too complex?

Traditional modeling standards encourages us to break large problems, like a monthly declining balance depreciation schedule, into smaller formulas. Each formula should be simple. It is important to note here that such schedules can require hundreds of formulas; thus, the calculation block, as a whole, is complex.

Depreciateλ() is just one very long, complex Excel formula but if we examine Depreciateλ()'s formula we see smaller formulas organized into LET() steps. LET() allows us to structure complex formulas into much smaller and simpler formulas, just like traditional models, with the difference being traditional formulas place those simple formulas in cells and 5g places them in LET() steps.

Another difference between cell based formulas and LET() step formulas is 5g LET() steps are self documenting which brings us to one of the other skeptics' concerns: Transparency.

Are 5g functions transparent?

"Transparency" and "Black Box" are two terms used by skeptics as reasons to avoid 5g functions but they use those terms inappropriately. In fact, 5g functions are more transparent than traditional formulas and are the opposite of black boxes.

Transparency in spreadsheets can be measured (click to see definition) and based on that measure, 5g functions are measurably more transparent because they provide context in the formula instead of outside the formula.

'Context' converts data into information. The number 12 is data. Add context, like 'Months' and we have information. '12 months' is much more meaningful than the number 12 by itself.

Formula 'context' is often provided by labels. To explain by example, let us use the traditional formula: = J11 - J12. To get that formula's context we must find that formula's label which is usually placed near the front of the formula's row. Let us assume the label is "Book Value." Each reference must also have a label. Let us assume J11's label is "Opening Balance" and J12's is 'Depreciation'. With these labels, we can understand what the formula does. Without these labels, the formula is 'opaque'.

In our traditional method, these labels are outside the formula. In 5g methodology, labels are inside the formula and look like this:

BookValue, OpeningAmount - Depreciation,

With 5g, we do not have to go looking for the labels and that makes 5g functions measurably more transparent.

Are 5g functions black boxes?

The skeptic means two things by this term. They imply 5g source is not visible and not understandability. Neither is correct.

Source visibility - In the traditional context, we click a cell and the formula shows in the formula bar. Note! We must do this for each formula in the calculation block to understand the whole which is tedious. In the 5g context we use the Advanced Formula Environment (AFE). Getting to the AFE requires a couple of extra mouse clicks, but once there ALL of the calculation block's formulas are present, formatted and documented, the convenience of which far out weighs the inconvenience of a couple of mouse clicks. Thus, 5g source is in a different location. 'Different' is not the same as 'hidden'.

Source understandability - One contributor to understandability is transparency which has already been addressed. A second contributor are the Excel functions themselves. If a 5g function uses INDEX() and the reviewer does not understand INDEX, to them the formula is not understandable, but that is their fault, not the function's. Ignorance can be overcome with education.

Other concerns

Skeptics also voice these objections to 5g functions.

1. Clients won't accept them - Some won't. Some have. At least one of the world's largest accounting firms use 5g models as well as several investment firms and modeling consultancies. The list is growing. Those with modern Excel skills can play in this new market as well as the old.

2. LAMBDA skills are rare - True, for now. If you want to create functions, you will need a little education. But if all you want to do is use 5g functions, you can, right now. No special skills required nor any knowledge of LAMBDA what-so-ever.

3. Inspecting formulas is the only way to know they work - False. Inspecting a formula is an inferior method to testing functions. For example, we can easily use traditional methods to test PMTAλ() to confirm that: no matter what APR we use; no matter what loan term we use; no matter how much we borrow, the monthly payment returned by PMTAλ() will result in a zero balance at the end of the term.

4. We can't use modern Excel in traditional models - False. We can use traditional cell references to include dynamic array cells in formulas. In some cases, I recommend doing that. As an example, there is no reason not to use a dynamic timeline. If we want to refer to one of the timeline's values, we can using traditional cell references. Easy.

Hopefully you see that most of the skeptics concerns are unfounded and others are inconsequential compared to 5g's value proposition.

Closing

Do you have a spreadsheet horror story? Share it with us in the comments below!

Do 5g functions concern you? Share your concerns in the comments and let's discuss.

If we are serious about eradicating errors, we need to know about 5g. If we are looking for ways to make models smaller and faster, we need to consider 5g. If you want to know more, visit 5gModelling.com.