Multi-Cell Array Modelling
Post date: Oct 26, 2016 2:13:56 PM
Peter Bartholomew’s work is pioneering. He views XL’s grid differently and from his engineering perspective creates models that go against several fervently held financial modelling beliefs. He uses array formulas and names. His work has no cell references at all. His work would be a mere curiosity if it weren’t for the fact that it is measurably the least risky approach to modeling I have ever seen.
Style
Here is what Peter’s work looks like.
I am an architect by education. Architecture studies focus on aesthetics. From an aesthetic perspective, this model is visually superior. He eliminates unnecessary ugly grid lines. He carefully selects fonts and coordinates his color pallet which is both pleasing and functional. The colors readily reveal his work’s structure segregating labels from values.
While style is paramount for architects, substance is paramount for Financial Modelers.
Substance
Fewer Formulas
When we look at Peter’s example six rectangular blocks capture our attention. Each has four lines. If this were a traditional Financial Model we would expect each line to have a different formula. Not here. Each BLOCK has a different formula. If we exclude the time line block, there are just six distinct formulas in this model. In a traditional financial model we would expect 24 or more distinct formulas for those same lines. Fewer distinct formulas means fewer failure points. Fewer distinct formulas means fewer formulas to test and verify. Fewer tests mean fewer failure points. On this one measure Peter’s work is far less risky than traditional methods.
Inconsistency Impossibility
When we click on a block’s cell we see the formula is part of a multi-cell array. Try changing it. It is impossible to make a formula in a multi-cell array differ from any other formula in the array. As an auditor we can skip checking for inconsistent formulas. The risk of inconsistent formulas simply cannot exist in Peter’s multi-cell arrays.
Mistype Mistake
We also see that Peter’s formulas have no cell references. He uses names instead. Cell references can be mistyped and Excel will be blissfully unaware of the mistake. Mistype a name and Excel complains bitterly. This risk of ‘fat fingered formulas’ is far less in Peter’s work.
Total Transparency
Names have another benefit; they make formulas measurably more transparent. Transparency is important for auditors to understand and validate a work. I measure formula transparency as the number of steps required to find a formula parameter’s value and label. If the label is missing, the parameter is opaque.
To find a formula’s parameter values and labels I prefer to click in the formula bar which highlights and color codes parameter cells. One step is all it takes. This reveals parameter cell values and is the same for cell references and names but finding labels differs.
To find a cell reference’s label we must look immediately left or above the parameter. Sometimes they are easy to find. Sometimes they are impossible to find because the modeler neglected to add them. It is impossible to neglect labeling a named reference’s value because the name IS its label. There are no steps required to find a named reference’s label. Names are ALWAYS more transparent than cell references and can NEVER be opaque.
Intro to Multi-Cell Array Formula Modelling
To demonstrate Peter’s methods on a more modest level I created this small model (below). The model has four products. Each product has a cost, price, and production quantity. Management expects labor to find one percent costs savings each month. Management expects sales to increase prices .25% each month. If production quantities stay the same, what is the net profit at the end of six months?
Formats
Gray cells are formulas. Teal cells are labels. White cells are inputs. Red outlines indicate which cells each reference number refers to.
Step by Step
I entered the products’ names and initial values into a table named tblInitialValues. I love tables and for this they are perfect. Since this is an XL table, XL automatically creates names, known as Structured References for each column.
I named the area in this red rectangle Period then I entered the first multi-cell array formula (MCAF). To create the MCAF I first selected Period’s cells then typed this formula in the formula bar:
=COLUMN()-MIN(COLUMN(Period))
To complete the formula I pressed CTRL-SHIFT-ENTER (all at the same time) to make this an array formula which now looks like this:
{=COLUMN()-MIN(COLUMN(Period))}
This put the same formula in all selected cells and bound them as an MCAF (multi-cell array formula).
I named this area Period_Start then entered the second MCAF (multi-cell array formula):
{=EOMONTH(Model_Start, Period - 1) + 1}
I named this range Unit_Costs then entered this MCAF:
{=tblInitialValues[Costs] * (1 - Cost_Reduction) ^ (Period)}
NOTE: tblInitialValues[Costs] is a structured reference created automatically back in step 1
Some may recognize the formula as a simplified compounded interest formula P = C (1 + r/n) nt. When the rate is already expressed as a per period rate the formula simplifies to P = C (1 + r) t.
I named this range Product_Prices then entered this MCAF:
{ =tblInitialValues[Prices] * (1 + Price_Increase) ^ (Period)}
I used the same compound interest formula to compound price increases each month.
I named this range Production_Cost then entered this MCAF:
{ =Unit_Costs * tblInitialValues[Quantity]}
I named this range Sales_Revenue then entered this MCAF:
{ =Product_Prices * tblInitialValues[Quantity]}
I named this range Net_Revenue then entered this MCAF:
{ =Sales_Revenue - Production_Costs}
This is a simple formula:
=SUM(Net_Revenue)
Though this model occupies 237 cells it contains merely eleven distinct formulas.Unlike distinct cell reference formulas for which relative cell references take on different values, each array has only one formula which is displayed in every cell. There is no possibility of misidentifying them as distinct or not. Eight of these formulas perform calculations. Three merely label values (Monetary units, date formats and product names). If we increased the number of periods and added more products it would still contain eleven distinct formulas. Each formula is highly readable and self-documenting. Each formula block is impervious to inconsistencies.
Peter’s multi-cell array modeling is both elegant and robust. His work is different and has tremendous merit. I am very grateful he shares his methods with us.
Peter Bartholomew
Peter is a former Senior Fellow at QinetiQ and was internationally known for his research in the area of the optimal design of aeronautics structures, having published over 80 papers. He served as a Visiting Professor attached to the College of Aeronautics at Cranfield University. After retiring from QinetiQ, Peter continued to work on a consultancy basis for major clients including the European Commission, Airbus and the ESA.
Newer interests included Simulation Data Management, an area in which he has presented a number of keynote lectures, and the use of ubiquitous spreadsheet technology as a platform for building models. Peter has strong interests in graphic design, which he fully exploits though his innovative use of Excel to capture and convey information to the user. He has developed a unique approach to the development of workbook based solutions which seeks to reduce the ad hoc nature of traditional spreadsheet modelling.
Examples
My modest example is merely an introduction to Peter’s work. Peter’s models leverage more than just multi-cell array formulas. They also leverage defined names in unique ways and pushes the legal naming convention envelope (See Illegal Excel Name Characters). I encourage readers to examine his models and learn from them as I have.
My Example Model
https://www.dropbox.com/s/r6lqxtmb8947wpn/PB%20Example.xlsx?dl=0
Peter’s Models
https://www.dropbox.com/s/djpgun5bn5yby75/PB%20Loan%20Calculator%20model.xlsm?dl=0