Before we dig into Modern Excel, can you please help me out? What, exactly, is “Modern Excel”? I can’t find a standard definition on the Microsoft site, or anywhere else!
What Is Modern Excel?
On the weekend, I watched a video meetup, “Modern Excel … a Replacement for the Spreadsheet?”
The video was interesting, and I’ve embedded it below, along with the full transcript, so you can watch (or read) it too.
There are lots of online references to Modern Excel, but I’ve never seen a clear-cut description. Maybe you’ve found one.
- Does Modern Excel start with a specific version of Excel?
- Is it any Excel version with dynamic arrays?
- Is there a different definition?
- Or is Modern Excel indefinable?
Excel Vs Jenga™ Game
Anyway, near the start of the video, the presenter, Peter Bartholomew, compared traditional spreadsheets to the wobbly stack of blocks that you build in the Jenga™ game.
Here’s a picture of our copy of the game. We’ll probably play next Sunday, after out Thanksgiving dinner!
And here’s a link to the game on Amazon, in case you want to play too!
Video Meetup Notes
The video, that you can see a bit further down the page, is a recorded live stream virtual meetup.
- Title: Modern Excel … a Replacement for the Spreadsheet?
- Date: The live stream virtual meeting was held on October 1, 2025.
- Host: Excel MVP, Danielle Fairhurst, from Plum Solutions. On her website, go to the Virtual Meetings page, to see the list of upcoming meetings, and watch the recordings from her past meetups.You can find her on LinkedIn.
- Speaker: Spreadsheet expert, Peter Bartholomew. You can find him on LinkedIn.
My Notes & Details
While watching the video, there were a few acronyms, names, and words in the presentation that I had to look up. I wanted to verify spellings and meanings, while proofreading the transcript.
I’ve put my notes here, in case some of these items are unfamiliar to you too.
EuSpRIG: In the Virtual Meeting summary, it mentions that Peter presented a paper at the EuSpRIG 2025 conference.
- EuSpRIG =
European Spreadsheet Risk Interest Group - If you visit the EuSpRIG site, be sure to read some of the spreadsheet horror stories!
FAST: At the 4:26 mark, Peter mentions the FAST standards.
- The FAST standard is a set of rules for designing spreadsheets that are:
- Flexible, Appropriate, Structured and Transparent.
- You can read or download the full guide on the main page of the FAST Standard Organisation (FSO) site.
AFE:
At the 7:52 mark, Peter mentions the AFE (Advanced Formula Environment).
- AFE is a feature in the Excel Labs add-in for Excel.
- It lets you write, edit, and reuse formulas.
- You can get Excel Labs in the Microsoft AppSource
5G Components (Five G): At the 23:26 mark, Peter mentions Excel MVP Craig Hatmaker’s 5G components.
- Learn more, and get the free templates, on Craig’s site, Beyond Excel (BXL)
Carolina Lago: At the 27:28 mark, Peter mentions Carolina Lago, from Tactic Financial
- See Carolina’s work on her Tactic Financial site
Silcrow: At the 45:25 mark, Danielle asks about the silcrow. It’s a section sign, §, that looks like a double “S”.
- Read more about section marks in this Wikipedia article.
- You might be familiar with the pilcrow, ¶, which is a paragraph mark, that looks like a backwards capital “P”.
Video: Modern Excel … a Replacement for the Spreadsheet?
Here’s the video, and the full transcript (slightly edited) is below the video.
- Do you use any of the Modern Excel tools that Peter showed?
- Are you ready to move to this type of spreadsheet construction?
Video Summary (by Danielle Fairhurst)
- The introduction of Dynamic Arrays has fundamentally altered how spreadsheets are built!
- Watch to hear from Peter Bartholomew to explore how ad-hoc end-user practices are giving way to structured, programming-like approaches, thanks to new capabilities like LET variables, LAMBDA functions, recursion, and advanced array handling. The talk will highlight how these innovations enable the creation of robust, algorithm-driven spreadsheet solutions, moving far beyond traditional tips and tricks.
- This presentation draws on insights from Peter’s recent paper delivered at the EuSpRIG conference, where he explored how embracing “array thinking” and the new Excel programming paradigm can reduce risks and unlock unprecedented flexibility for spreadsheet developers and modellers.
Video Transcript
Here is the full transcript, which was auto generated by YouTube, and slightly revised by me (for capitalization, spelling, etc.).
If you spot any errors in the transcript,
please let me know!
Note: I adjusted some really long lines of text, so a few of the transcript time stamps might be off by a second or two.
‘——————–
‘—–TRANSCRIPT START—–
Virtual Meeting – Start
0:00 Well we’ve got uh Dallas, South Africa, Namibia, Brisbane, Australia,
0:07 Abu Dhabi, Egypt, Nigeria, all over the place.
0:09 Welcome, welcome. My name is Danielle Stein Fairhurst. I’m based in
0:14 Sydney, Australia. I’m a financial modeling specialist and I love talking
0:18 about Excel and financial modeling and I love running
0:21 these virtual meetups where I invite special guests uh relating uh
0:27 that have something to say about the world of Excel and financial modeling
0:33 and I’m very excited to welcome Peter Bartholomew uh here is he’s joining us
0:39 from the UK today so morning his time, evening for me. Peter Bartholomew is by
0:44 his own an admission not a financial modeler but his contributions to modern
0:50 Excel means that his work is of a lot of interest to financial modelers. He’s got
0:56 a lot of uh he talks to a lot of financial modelers in particular with the
0:59 new dynamic arrays and when LET and
1:02 LAMBDA came out and uh he’s been doing a lot of work in the development
1:07 and it means that his work is very relevant to the financial modeling
1:10 community and I’m sure he’ll uh tell us a lot more about that.
1:14 So, I’ve known Peter for many, many years. Uh, and we finally got to meet in
1:17 person in London earlier this year, and I’ve been wanting to have him speak to us
1:24 for quite some time, and I’m really happy that he’s agreed to do it. So, welcome,
1:30 Peter. I’m going to let you take it away.
Peter Bartholomew Presentation Starts
1:34 Okay. Thank you, Danielle. That’s uh that’s yeah, that was
1:38 a a good session, wasn’t it, in London? Yeah, that’s uh the uh
1:44 that was well that’s I I have been campaigning
1:49 for better arrays uh to Microsoft oh at least before uh 1915 that’s 1915
1:59 no not 2015 we’ll make that and uh was overjoyed in 2018
2:06 when I actually got, oh I bet, something that was not just what I
2:12 wanted but but better. So and what I have found is that it changes everything
2:21 to the extent that when I’m asking answering questions on fora
2:27 a common follow up is where do I put the code? The original post poster hasn’t
2:35 even recognized the solution as being Excel. Now that leads me to think that
2:41 possibly something’s changed. So uh um let’s see whether I can uh ah
2:50 down page down should work. Yes.
2:55 So what I’m going to do is try and show you
2:59 why I believe that LET and LAMBDA don’t just enhance traditional methods,
3:05 they actually offer a completely different approach
3:11 within the same package. Um they do share some common features.
3:17 Both are based on the grid and they share a common library of functions.
3:24 Beyond that, not so much. That’s pretty much everything beyond that can be
3:30 different. Um I sometimes think that if the traditional spread spreadsheet
3:38 were a building set, it would be piling up Jenga blocks. They stack to the
3:42 point where the whole edifice becomes ever more wobb wobbly
3:51 sort of built on its own inconsistencies
3:56 and eventually the whole lot comes crashing down
4:01 that uh and I have seen some very unstructured workbooks that uh
4:06 it’s a wonder they ever hold together and come to that only about uh 10%
4:13 of spreadsheets at the most are actually correct and do hold together.
4:20 So uh um say that the standards might help things
4:26 like FAST give you enforce a very regular pattern
4:31 and if I’m trying to uh refactor a an
4:37 analysis starting on somewhere the something that’s quite well defined does
4:43 help. So, but they’re still built on the same
4:48 shaky foundations.
4:51 Now, we’ve got this the new shiny thing
4:56 in town of LET and LAMBDA. And there is a temptation
5:02 to see this as the clever bits, the bits for advanced users that uh the the bit
5:10 that goes as the the pinnacle of creation balanced on top of your existing model
5:17 and I believe that is fundamentally flawed and a wrong way of doing it.
5:25 The LAMBDA is not the crowning glory of an ivory tower. It is something far more
5:34 basic than that. So it’s a more structured setup in a way. You have far
5:41 less flexibility than you do with the traditional spreadsheets,
5:47 but it’s more solid. And what you can
5:52 then do is build from the ground up using LETs, LAMBDAs, and so on.
6:00 Um the the rules of construction are simply different but what you build can do
6:07 solve the same problems and in a better way. So
6:13 if I if I compare that’s the traditional and modern
6:21 um now I would hold all my variables as LET variables.
6:27 They’re somewhere in memory. I don’t know where they are.
6:30 I don’t care where they are. I reference them by name. On a
6:34 traditional spreadsheet, they would be visible in cells and they would be
6:40 referenced by the location of the cell. Um, I believe I can get more meaning but
6:47 with the name. The formula that traditionally there’s the illusion that
6:54 the formula is in the cell. It’s not. There’s no such thing as the grid. It is
6:59 just uh an illusion, an image, graphic image built up. Um
7:04 You are already blowing my mind, Peter, already. What are we eight minutes in
7:09 and I’m like, okay, it’s just what I thought was everything
7:16 I thought was true, you’re telling me is no longer the case.
7:19 Yeah. Yeah. It’s just an illusion. And
7:22 the originators the spreadsheet knew that they were expert programmers
7:29 writing for non-programmers and creating an illusion. So uh it’s uh that
7:38 now the formulas are also in the as the alternating LET parameters where you
7:46 that uh they clearly define the parameter and to view them it’s best in
7:52 the AFE because the name manager is dreadful but uh
7:59 um the way you handle arrays that uh traditionally
8:05 they are just collections of scalar cells flying in close formation.
8:11 Mhm. That they address to one by one by using the concept of relative referencing.
8:18 If I drag a formula down one cell then the cell everything I references goes down
8:24 by one cell. It’s an absolutely awful way of doing it. But uh it it works for
8:31 non-programmers. It’s um that now it is the array that is the
8:39 object that it has elements. Fine. It’s well its properties are defined in terms
8:45 of elements but the array is the object. You address it by name. I named the
8:51 anchor cell and then put hash to get a dynamic array and that that’s actually a
8:57 range on your sheet. um the user intent
9:04 that’s from a traditional spreadsheet it’s just implicit. You know that if the
9:10 spreadsheet is put together by a financial modeler and it’s offered to
9:15 other financial modelers they can they
9:18 have a shared background experience and can actually see what’s going on. that
9:25 um there’s no no statement of what’s being done at every any stage. Whereas
9:32 now with a LAMBDA function, the function like any function, the built-in
9:38 functions, they should tell you what the process step is, what you’re doing to
9:44 get the new results, that you’re sorting things, you’re filtering them, you’re
9:49 doing something. And it’s that intent that I would uh seek
9:57 The precedence. What what is it that’s being processed?
10:00 You’re doing something, but to what? Well, it’s you click on a
10:05 precedence tree in or use control some square brackets.
10:13 I’m was never very good at the keyboard shortcuts. Um, but now
10:19 you have an explicit argument list in in parentheses following the array, the
10:25 function name. So it’s all there what you’re doing and what you’re doing to
10:30 it, what you’re doing, what you’re doing it to is there explicitly.
10:38 Um so oh that’s it’s built on the concept of
10:45 multi-dimensional arrays. Just embrace it and don’t look back because if you
10:51 try and build in what you already know into the new you’ll get yourself into a
10:58 mess. You’ll keep on thinking oh I could just do like that like and then
11:02 what you do won’t be dynamic. it won’t work with the rest.
11:06 It’s very we we know this, but it’s very hard to do in practice
11:09 because we often have these models
11:14 that have already been built and we’re sort of adapting them and
11:16 I’ve lost sound.
11:18 Oh, you can’t hear me,
11:20 aren’t you’re there again.
11:21 I just said that it’s it’s quite difficult to do
11:24 that in practice because it’s it you know you you inherit these legacy models
11:31 and they’ve already got older traditional formulas there and it’s not
11:38 really practical to replace everything with a dynamic
11:40 It is well that’s I haven’t used the cell reference
11:43 since 2015 when FAST told me I shouldn’t use names
11:51 And I thought, you’re not right for that for that moment. I banned cell
11:59 references from my workbooks.
12:02 I remember you telling me this a long time ago that you never ever
12:05 use cell references. And I was like, what? How can how can you do that?
12:08 And of course, it makes sense once
12:12 you’ve got the dynamic arrays working.
12:15 Yeah. Oh, it was really painful with cse
12:17 arrays and uh defined names but yeah it’s possible
Examples
12:24 right so let’s try and give of just a couple few examples of things that I
12:30 mean when I say life is different and the the the
12:37 algorithms that I implement are now or I implement that’s uh that’s not jump to
12:45 conclusions, um, use array manipulation
12:51 as a as an integral part of how you do how I do things. Take a I’m trying to
12:58 start with a simple problem of uh monthly reports for a various items
13:07 and somebody says okay I want quarterly I’m not I don’t need that
13:10 level of granularity monthly, quarterly will do me fine. Mhm.
Array Shaping
13:14 How do you do it? Well, there there are a number of ways of doing it, but the
13:19 one that I want to illustrate uses array shaping. So, what I want is the same
13:27 figures by quarters. So, that’s sums if
13:32 I can see it down the corner, but should be the one 194 there. And uh so
13:41 what a I have done let’s see if I can get this to work
13:48 is and I will also open that up
13:54 is I first counted that there are four quarters that I’ve got to to reproduce
14:01 and I I’ve then just rounded it up so it’s
14:07 the December that was missing I’ve so I’ve got a round number of quarters.
14:12 That’s just admin. To go to a single column, the the target is to wrap it. So, I’ve
14:20 wrapped the figures across the uh sheet in threes. So, it’s January, February,
14:27 March, April, May, June. And by rearranging the array, the data, I’ve
14:34 now got a simple calculation that all I’ve got to do is sum each sum
14:41 by row and I’ve got the quarter values. So
14:48 that’s summed by row. Mhm. This this quantity by quarter is by row
14:54 arranged by quarter sum. And uh that’s pretty almost the job done
15:02 except that I want to wrap it back under the byproduct.
15:08 And so that’s just another wrap. And then uh if this is working data
15:16 as part of a calculation, it stays as it is. But if it’s for user consumption,
15:21 if it’s part of presentation, then I need to pretty it up with
15:26 possibly a total row and the and a heading row. And dynamic arrays are very
15:34 nice at changing the output formats and uh give you quite a lot of flexibility
15:40 whereas to do this by cell, cell by cell would be quite awkward really.
15:47 M um okay that so that’s so that’s array shaping
Accumulation and Stacking
15:49 Next I want to
15:55 do to show problems that can be solved by um accumulation
16:02 and uh stacking so
16:08 a tax return that this Dennards quoted this at one time those are the
16:16 thresholds to some US tax bands for 2022, I think. And those are the
16:24 associated rates. And what I want to do is given a taxable
16:31 income, I want possibly just to produce
16:36 the amount of tax, but also the possibility
16:42 of a full breakdown in which you look at each band, how much tax is payable, is
16:50 chargeable in that band, and what the tax is. So that that’s that’s the
16:57 challenge. Um you can see my formula is tax calc,
17:03 the thresholds for the US system rates
17:07 and that I want a full print out. If I delete that.
17:13 Don’t know what yes only gets. So I better type true
17:18 again.
17:23 uh and go to so that that’s the uh the
17:29 exercise and
17:34 what I do is these are the the lower the the lower
17:40 thresholds I want to get the upper bounds the limits and
17:45 so I drop the zero at the bottom
17:50 and then the thing drops down. But I’m sneaky. I put the income in, stacked into
17:58 the loop. So the income gets sorted in the correct place.
18:05 The next step is to look at the bands, which is the upper limit minus the threshold.
18:08 Oh, I suppose I ought to be showing you
18:12 a little bit about what’s going on. So let me go back. Those are the upper
18:21 bounds. That is the the 140 is the is the amount you see up there. And uh now
18:31 that is the entire bands, the entire second band, part of the third band
18:39 because it’s limited at the top by the actual taxable income.
18:47 The next step is to multiply it by the tax rate to get the tax due.
18:55 And then I have the uh the the breakdown
19:01 at which I I stack all of the things together. I calculate the total
19:08 and I return a pretty little table with the
19:15 tax due at the bottom and all all of the breakdown.
19:19 I could if I wanted and if I’ve got uh
19:23 if I if I’m the accountant and I’ve got a lot of 150 of these
19:28 things I could just put the incomes in with the client name and just bash out
19:36 the tax as single statements so that I’ve got a summary one but uh
19:42 okay so that’s an example of that
19:45 that’s great there’s a there’s a Paul Clark has just popped another example
19:49 into the chat which is a big long uh sort of code uh which is a similar kind
19:56 of approach but you know more using using formulas um with a LAMBDA formula
20:04 uh personally I just go with the uh close MATCH XLOOKUP which is a a much
20:09 simpler way of doing it but um yeah as always there’s about there’s multiple
20:14 ways of achieving the same result.
20:17 Yeah. Yeah. quite that
20:19 Uh pity I can’t see the uh the discussion that uh
20:26 Oh, that’s okay. I’ll send it to you later.
20:29 Okay, that’d be great.
FIFO Example
20:31 The other one that I didn’t know whether to skip or include
20:35 it depends on how much financial modeling I wanted to do
20:38 today or you wanted me to do. Interesting. Was a FIFO f first in first out
20:46 calculation. which I have a set of inputs, dates which aren’t particularly used but
20:53 could be useful for working out the time that stock is held and the warehouse
20:59 costs and so on. um the volume in the price in and then the customers the the
21:06 the outputs dates volumes and the the one possible goal is for any
21:16 given output batch you you work out the input costs
21:21 um I’ve also done this uh for iron ore
21:27 so that you for any given customer batch you know what the iron the uh content is
21:35 so it’s the same calculation and uh I accumulate the inputs so those
21:44 essentially are serial numbers that act as a flag something happened you ran out
21:51 of a of a supply at that point um I do the same with the outputs that
21:58 you fulfilled an order. At this point, I stack the two and sort them. So now I’ve
22:06 got um divisions with uh each one is a
22:12 tranche. I can work out how many items are in the the tranche, where it came
22:18 from, and where it went to. And that’s is the basis for you know their number
22:25 of possible calculations. So that’s the table that I build up
22:33 and uh so that those are the volumes of each tranche. Those are the customers it
22:40 goes to. That’s where it came from. That’s how much it cost. And uh
22:49 the formula is th this is done as a a Craig Hatmaker 5G component.
22:55 So if I if I want to get something to a
23:01 deliverable standard that’s uh Craig’s work is amazing. It’s what happens when
23:06 you let a professional an IT professional into a spreadsheet and it
23:12 comes out as something looking totally different in concept. Um,
23:18 so that so you know I feed some ideas into
23:26 Craig’s 5G components. He exploits them and occasionally I step
23:33 up to the mark and up my game and produce something that is transferable,
23:38 usable, readable and documents with change control and all the rest of it.
23:45 Um yeah it’s it’s quite a different concept from the normal spreadsheet
23:50 approaches but uh the the bit I’m going to show is uh I I do all the calculation
23:58 won’t take you through that but it’s the same sort of thing
24:02 it is the stacking and sorting and then I’ve got a range of
24:05 options the default table or I can group by to get it group by the customer and
24:14 you you look at the total cost of the inputs. I can pivot them. Let’s go and
24:21 have a look at this. Um that’s the list that’s grouped by by
24:27 customer. And uh if you look that say customer three is the
24:34 28 and 58. Do I see that? Customer 3 28 and 58.
24:41 Now it looks Yes.
24:42 Hang on. Hang on. What are these? What are these? What are these buttons here?
24:45 Is this a slicer with a table?
24:47 Uh, it’s Yes, it’s a one cell pivot table,
24:57 right? It’s one one cell because I’ve selecting one
25:02 item and I’ve got rid of it’s only the row
25:08 headings. I don’t have any values and uh I don’t have any headings or
25:14 buttons or anything else. It’s just one value that sits in a cell
25:19 and that controls the um the uh the switch statement which
25:27 controls what you see here. Nice.
25:30 So, and they they’re they’re nice looking little controls, aren’t they? Uh
25:35 so um that that’s the sort of the basic but if you want to see it’s sp sort of
25:42 arranged so you can actually see which batches went came from where and
25:46 went where you can do a cross tab. Now I wouldn’t recommend that
25:52 if you once you know this is six and four inputs outputs.
25:57 If you’re talking of uh 15,000s and you you’re thinking of a 15,000 by
26:05 15,000 array with just a little straggle of numbers working down diagonal, I
26:11 don’t think that’s much use for anyone that uh I wouldn’t be greatly
26:15 attracted to that. But for small models and seeing
26:19 what’s going on, it’s it’s it’s nice and it’s sort of you can
26:26 actually see um I’ve also a version which tells you the volumes as well as
26:32 the costs. So uh right that was the sort of end of first part.
26:40 How am I doing on time? I’ve got a little bit
26:45 good but uh are there any questions or or comments? Any? No.
26:50 Any any rotten fruits being thrown in? Yeah. Yeah. No, no, that’s great.
26:54 Um, no, just just lots of comments, but um no particular questions.
26:57 So, if people have got particular questions that they’d like Peter to address, just pop
27:03 it into the Q&A. Um, if it’s just a comment, um, just pop it into the chat.
27:06 So, um, I will let you know if questions come through, Peter.
27:10 Okay. Now I go and see what I can find another. Isn’t that I’ve got to find
27:17 another spreadsheet.
27:19 You’re going to go to the other the other file now.
27:23 Yeah. Right.
27:25 Pop that into the chat as well so people can
27:28 Okay. And um I’ve also it was actually Carolina Lago
27:34 that produced a very simple financial model for me that I could work
27:40 with and uh because I I know it’s bread and butter for
27:46 everybody else but I find them massively complicated to understand.
27:53 And really it’s because I don’t I was expecting to see a calculation and what
27:58 I’m actually looking at in the um the model is not a calculation. It’s a
28:05 presentation you know of information. It’s a story in numbers. And uh whereas
28:12 I expected well what what’s the question? What is the answer and what is
28:18 the quickest route from the question to the answer? You know that uh and
28:24 probably the question is um given these flows these um assumptions
28:33 what is the financial state of my company in 8 years time? You know so you
28:38 want uh to know whether you’re in year by year whether you’re
28:43 in credit or debt and once as you know that
28:47 you have the information to present it how you like and this is how financial
28:53 models appear to like it. So, uh but it really does did confuse the hell out of
28:58 me. Um, so we started off with assumptions and
29:05 Carolina put a a an exponential uh geometric series for the uh revenue
29:14 and costs which peaked and that throws the company into debt for a while into
29:21 revolver debt uh called down. Um
29:26 uh the thing is I don’t know what these numbers mean. Are they just assumed
29:33 flows assumed constants over the period and then magically on January the 1st
29:39 they change to a different thing or is there an implied continuous flow and
29:48 you know it’s and according to how you model it you then you’ll implement
29:55 slightly different solution algorithms and you’ll get slightly different
29:59 results, and different accuracy.
30:02 So uh anyway the the model I try to analyze it. The uh green were taken from
30:12 I I took each of these um schedules and uh statements as if it were a function.
30:21 I looked at what the inputs were in green. I looked at what the outputs were
30:28 in um in yellow. And I went down and found, oh dear, I haven’t got a clue
30:33 what the interest is, and neither will I know until I finish the model.
30:36 So I put a a break in there. So I either do revolver interest
30:44 That whole circularity issue where you’ve got your opening, closing,
30:48 and you can’t calculate your interest until you know what your cash flow is,
30:51 and you can’t calculate your cash flow until you know what your interest is.
30:56 Is that what we’re attacking here?
31:00 Yep. That uh Came stripped the problem
31:04 right back and said, “Right, you solve that.” So uh um it turns out that the
31:09 problem is actually comes down to here of beginning sorry further down
31:17 is yeah yes into the uh debts schedule and uh things that you
31:25 don’t know down here um I analyzed it. Oh because I’m going
31:30 to put everything in LET statements I need names. I use defined names here but
31:36 ultimately they move across as LET variable names as well. So it’s the
31:43 first cell of is the long-term liabilities
31:52 which apparently were nil which is very good but um the uh and then put a hash
31:58 after it and you get the the whole row. So that was the way that I started the
32:06 refactoring. Um I to see what was going on, I wrote out all of the formula
32:15 there and I put the names across the top and I looked to see whether the names
32:23 occurred in the formula. And if they did, then I put a one out.
32:27 And these are this is the grid that you get.
32:30 And you’d see that earnings before tax depends on revenue and costs
32:35 and you know those and that’s fine but it also depends on revolver interest
32:41 and you don’t know that because that calc gets calculated much later.
32:47 So that that was what I used to try and get a grip on the problem and try and find
32:55 what I was facing. There’s a little revolver in there that is looks self-contained.
33:02 Um, so if I go back to the model and I
33:10 switch out the uh the break from what I’ve told you, you should be
33:18 expecting the whole lot to crash and uh fail to evaluate. But it doesn’t.
33:24 And the reason it doesn’t is I’ve cheated that uh if you come down
33:32 to beginning balance closing balance
33:39 moments where am I think I’m in the right place um
33:47 the opening cash or debt is not calculated in this model at all
33:54 it’s calculated on the next sheet. So this one won’t go circular because
33:58 I’ve got I’ve given the given it the answer was for
34:02 completely separate calculation. Um, what I did with Carolina’s model was
34:10 I combined the cash and the revolver debt into one variable so that positive
34:17 values of cash, negative are are the revolver debt. And I did that just to
34:24 make the calculations easier. Um, so it’s as if it were one account
34:30 with a a credit facility on it. So
34:37 my calculation is actually here. It’s a SCAN over the revenue minus costs
34:45 and a LAMBDA function which works out cash and revolver.
34:51 The function doesn’t looks
34:56 really it’s not much to it. But then I ask for a problem without much to it
35:02 for you maybe. Um I need to know opening values which are zeros flows which are
35:09 revenue minus costs. The interest is the
35:15 revolver the rate revolver rates times the negative part
35:21 of the combined cash debt figure to the financial state
35:28 earnings before tax. You take the flow and you subtract the interest. That
35:34 gives you the earnings before tax. The tax is the positive parts is related
35:40 to the positive parts of the earnings before tax
35:45 times the tax rate. And then the closing balance is opening plus the
35:52 earnings minus the tax and return the closing. And that’s that
36:00 that is the spreadsheet expressed in a different way in which it sort of just
36:07 go straight for the closing balance. Um the advantage here the horror was
36:15 that the whole band model has to go into one formula and uh you know how on earth
36:23 one does that? Is it’s not quite as bad it’s bad but it’s not quite as bad
36:29 as that because this function only has to deal with one time period
36:37 at a time and SCAN and we’ll run it across. The other thing about it
36:44 that’s a saving grace is I’m only looking for the closing balance. I’m just looking
36:50 for one quantity. Uh everything else that I calculate along the way I discard.
36:57 So there there is some simplicity in this. But uh
37:03 if what I do once as I’ve got oh yeah
37:09 then opening balance is the closing balances which is the
37:14 formula you’ve just seen brought forward one period uh by pushing um the initial
37:21 revolver balance in at the front. And so if I know closing balances, I know
37:28 opening balances. If I know all the opening balances, then I can go back to
37:34 this model and calculate the whole lot in terms of the opening balances, which
37:41 is what is done here. And why I’ve got numbers because down there I’ve brought
37:48 in the negative part of the uh revolver
37:53 which is the debt. And once as I’ve got that that goes up to the top as um
38:03 the as as interest by multiplying by the
38:08 rates that you’re paying. So the whole model then holds together
38:14 given that I’ve got the calculation on another sheet.
38:18 Um I ought to be finished. I I’ll give myself can I have five minutes
38:24 just to uh just throw another couple of grenades into the goldfish ponds?
38:30 Absolutely. Absolutely. Throw away. Grenade away.
38:34 Um so I then went to make the calculation a bit more complicated.
38:40 There’s you know if something if something is complicated already there’s
38:46 no harm in making it really complicated. uh the the problems of circularity
38:55 is that occur when these you see models in which the you have an average of
39:02 beginning and closing balances but you don’t know the closing balance until you
39:07 you know the closing balance depends on the average and
39:10 the average on the closing balance and you get yourself into a real mess.
39:13 You invoke circularity, it converges to something. It converges
39:20 to something more accurate. Maybe if it converges, I’ve seen problems which go
39:26 on for a hundred steps and then just meander away from the solution because
39:32 the uh you know the the um convergence
39:37 isn’t being run at that step. Um the the chaos theory is based on formulas
39:45 like this. So you don’t necessarily get decent behavior.
39:48 But there is an alternative which is predictor corrector approaches.
39:55 So the interest was the revolver rate uh times the negative part of the cash
40:01 date. And but that is only a first guess. The earnings before tax flow
40:09 minus interest is again first guess. No more than that. I’m not going to put it
40:15 on my sheet. I’m not going to overwrite it. I that it is just a first guess.
40:20 It’s a different quantity. Okay. The average is the opening balance plus
40:27 half the earnings. Mhm. And that’s again it’s just an estimate
40:32 but it improves the uh the calculation. So I recalculate interest as the revol
40:41 as the with a revolver rate the negative part this time of the average balance
40:48 over the period and uh then hence the earnings but
40:53 before tax gets recalculated the I haven’t included the t the tax in
41:01 this averaging process I rightly or wrongly assume that the tax is payable
41:07 at the end and the fact that you’re t pay you’re building up a tax commitment
41:15 doesn’t make your your financial state worse. You’re not drawing on more
41:20 revolvered debt because at the end of the year you’ll have to pay tax. So I
41:26 didn’t want to merge the tax back in. So I I just left that till later. that
41:32 gives the closing balance and again that uh return the closing balance gives
41:41 that’s the calculation but with just a different LAMBDA function
41:46 the same as the one before I’ve got the uh estimates
41:51 as well as the refined thing if I just output the variables
41:57 from there and then I tidied it up And I was sort
42:03 of kind and I I separated out what is cash, what is revolver debt. You see
42:09 them switch as you as you go into debt and those periods.
42:15 Um similarly with the closing. So and I
42:20 chucked away the estimates. So that that would be a solution.
42:27 And you you can see the figures sort of going from closing back to opening.
42:33 Can I just ask about how this differs to the uh the one that Craig Hatmaker
42:40 showed us uh a little while ago? I think the um you know it does a similar thing,
42:45 but I think he said the the way that it calculates is slightly different. Is that right?
42:51 Yeah. that uh I need to go back and have a further look at uh what Craig has done
42:57 that uh in fact I’m working with work with Craig that know him well that uh
43:04 similar it’s a similar kind of a similar kind of thing
43:07 yeah some of my methods have feed feed into his components and more a case that I
43:12 ought to learn and do more with my work to put it in the form of his components
43:20 it’s great to see the my understanding yeah the collaboration is good for for
43:24 the industry as a whole.
43:28 My understanding is what Craig did with the the circularity problem was to emulate
43:32 what’s the uh the um built-in uh circularity does of just repeating the
43:40 calculation and he put that into a recursion rather
43:45 than uh um an Excel iteration
43:50 and uh did so many loops around um by keeping on averaging the beginning
44:00 flow and the ending flow over a period and taking the average that that is a
44:06 relationship which works exactly if the flows are quadratic.
44:13 Typically this type of equation has exponentials as its solution not quadratics.
44:19 So you’re putting a lot of effort into converging to something that isn’t the
44:26 solution. It’s a it’s it’s a good estimate. It’s an improved estimate,
44:32 but it isn’t the solution. So uh I I
44:35 just I think u Diarmuid Early about two years ago. I’ve only just come across
44:41 it, but did quote uh a workbook that does the same calculation as I’ve done
44:49 essentially, but you you just see the estimate and then the second revised
44:55 estimate appearing on the spreadsheet. So, it’s worksheet formulae rather
45:02 than uh inlet LAMBDA. So, wow. Right. Yeah.
45:08 Hi, can I just if if anyone else has got any I don’t think we have any
45:111 outstanding questions right now. So, if anyone has
45:14 any, please get them in. Um, I just wanted to ask about that symbol which
45:19 I’m not really I haven’t seen used that often. You’ve got that uh I can see it
45:22 just there in front of the nil. It’s like the double S. I think it’s called
45:25 a silcrow. Um, is that like a like a placeholder or why do you use that symbol?
45:32 Where where are you? Uh where will uh
45:35 So I can see it over on Oh, I can’t see your rows.
45:39 Um it’s like the double S. Uh like the the the sort of Latin character.
45:46 Oh, yeah. Yeah, that’s it. The the I think it’s called the silcrow there.
45:51 That one. Yeah, that’s the one. Yeah. Yeah. that
45:56 basically um BBA is not tolerant of it but
46:02 spreadsheet formulas you can um insert symbols.
46:08 Mhm. And uh although they tell you that names must just be Latin letters and
46:17 numbers then uh it’s not really true that if you
46:22 are Greek then uh all of these things are part of your natural language. So,
46:29 uh, you can pick quite a lot out of the symbols. And what I did was to pick
46:36 somewhere the section markers, the double S to indicate series assumptions
46:45 there, that’s it as series assumptions. And I picked Oh,
46:52 dogs are going berserk, um a currency marker
46:57 as constant assumptions just so that I
47:05 would know what’s the uh you know understand that the names I
47:11 know immediately that’s a series assumption
47:20 yeah so it’s like a marker, is that a commonly used technique?
47:20 it just suited me that the other things that I’ve got uh backspace
47:29 lambda. Yep. And that turns into a Greek lambda.
47:35 That that’s just the um uh oh they call it in words there’s the uh correction
47:44 sort of uh spell that does spell ch checking and correction.
47:48 you put in symbols like that and because
47:53 um Excel annoyingly thinks that that is a cell reference
48:01 or possibly let’s do something a little bit more significant
48:06 tax 2020
48:13 this year it Excel will think that’s a cell and
48:18 everybody knows it’s an amount of tax you’re going to be paying next year.
48:25 And I find bits like that frustrating.
48:28 I want x1 as a variable. So what I tend to do is x underscore one.
48:36 Mhm. And it turns it into a subscript. Oh, nice. So uh so as I I managed to get
48:46 over the fact that uh Excel has squandered
48:52 um how many variable names it’s the uh you know it’s hundreds of thousands oh I
48:59 suppose this number of cells isn’t it it’s 16,000 rows times uh million
49:09 you know columns and So, it’s an awful lot of names that it has squandered
49:13 on meaningless on, in on imaginary cells.
49:16 Yeah. Yeah, that’s true. But now, um, we’ve got a question from John.
49:23 Um, he says, “Is frozen?” Oh, you can Can you hear me?
49:31 So, we’ve got a question from John and he’s saying,
49:34 “Can everyone else hear me? Hear me?” Uh, I can hear you. Okay, you can hear me.
49:37 All right. Uh so he says is it practical so this concept of creating this
49:43 uh you know the circularity is it practicable when a model has multiple
49:49 circularities for example tax interest debt sculpting it seems that the
49:54 complexity of such a big LAMBDA formula would be exponential with circularities
50:00 and secondly how flexible are these so how easy it is it
50:04 to add new elements into the big formula
50:07 formula such as adding tax circularity to an existing formula.
50:13 So you would be able to add more circularities, wouldn’t you?
50:21 It’s yeah where you add them might uh might vary slightly but uh something
50:29 like um uh what they call senior debt
50:35 that that’s got its own little circularity as you integrate along
50:39 and it’s self-contained and it just comes in
50:42 as the result comes in as a number that that would be
50:47 quite easy to do. Mhm. Um the
50:54 you’re you’re you’re working with formulas like
51:00 I’m I’ve lost my mouse. There it is. It’s on the wrong screen. Um like this.
51:06 I I had actually made a mistake and uh had to confess to Danielle this this
51:13 morning that uh the average I put in as the opening plus earnings over two and
51:23 I’ of course I’d missed out another opening. It’s the opening
51:29 over two plus the closing. So, uh, that’s the opening
51:33 plus the average earnings before tax. And
51:39 it’s reasonably straightforward to edit these formula. If you’ve if it’s
51:46 meaningful, you can follow through. I believe they should be a lot more
51:51 modular. Um so that uh
51:56 you know each sort of business decision or law ought to be
52:04 captured in its own uh LAMBDA function. So this itself ought to be built up from
52:11 LAMBDA functions. Um and you would be trying to construct the
52:18 things so that it is uh editable so that
52:24 you can take one of these to your client and say is that your company procedure. This is
52:30 what I’ve implemented. I think it’s what you described to me.
52:33 Take that little LAMBDA function away. Test it. See
52:37 whether it is what you want or I’ll do it with you. Um so there is a level of
52:47 comprehension and communication that’s going on which is not involved in the
52:53 numbers. It’s involved in the formula and the the numbers just follow the
52:58 logic. So uh but you’ve got to have a client that understands the logic.
53:05 It’s uh so yeah and I think that the the challenge with this is always having the client
53:13 client understand and be able to to follow your logic. Um because we are so used to
53:20 using Excel and the grid uh and being able to trace back every single cell and
53:26 this is just such a different way of working. Um I think it’s just going to
53:29 take a while for people to get used to it, for sure.
53:33 Yeah. Yeah. Agreed.
53:40 Okay. Well, we are just about to time. Uh so were there any was there anything
53:40 else you wanted to sort of leave us with, Peter?
53:47 Um that is a very good question. I did have a PowerPoint,
53:54 but uh let’s have a um what happens when the OP doesn’t
54:00 recognize the code? Another issue. How do you teach it? Do you leave LET LAMBDA
54:05 to the end? And I say no, they’re foundational elements.
54:08 You you they’re the first things you do. You don’t even
54:11 need to teach A1 notation. That’s this is where you start.
54:17 um should modern Excel be regarded as cutting edge enhancements or is it
54:23 better to of a spreadsheet or is it its replacement within the same code?
54:29 It’s as different as if you had Python but uh you know it’s doesn’t have Python’s
54:37 libraries but uh Oh, I just shouldn’t put that up, should I?
54:41 It’s different enough. I didn’t say
54:44 good riddance, honest, but so uh
54:51 right on that if anyone does have queries or
54:58 or followups then uh I am accessible and I hope to be
55:05 working further with uh say Andrew Craig and so on that some work and No, what I
55:14 would like to see is to get some core steering data into a model and then it
55:21 shouldn’t be necessary to test whether your balance is balance because the
55:28 that’s mere output and the the functions that create the uh the um
55:37 flows should automatically put in the both entries of the double entry. It
55:45 shouldn’t be up to the user. They shouldn’t be able to make a mistake of that sort.
55:50 But, uh, that’s that is hazy futures. That’s it’s not my territory. And, uh, yeah.
55:56 Yeah. Yeah. I think we’ve got a got a bit of a got a bit to go before that’s going to
56:02 be the reality. But, wow, you have certainly certainly given us a lot to think about.
56:08 So, thank you so much, Peter. That was um absolutely uh mindblowing which I uh
56:16 I knew it would be. So any conversation with you is always uh uh an absolute uh
56:22 is I know it’s always going to be absolutely fascinating and challenging
56:25 and and really make you think. So thank you so much Peter.
56:30 Um I will uh get the files and the recording out to everyone
56:33 who registered um as well as the links that Peter mentioned. So, thank you so
56:40 much everyone for your contributions and uh I’ll see you again soon.
56:45 Thanks everyone. Bye.
‘—–TRANSCRIPT END—–
‘——————————————————-
Do Your Excel Files Collapse Like Jenga™ Blocks?
My somewhat mean version: Microsoft is reverting to its annoying monopolistic defaults. For example; pushing you toward saving on their cloud platform, annoying lawyer defined warnings for automated cloud updates in one’s spreadsheet. I’ll be firing Microsoft products from my life upon retirement at the end of this year.
Thanks, Daniel! And I don’t want those intrusions into my spreadsheets either. I turn off all the cloud-saving defaults, but some people are stuck with them. All the best for your upcoming retirement, and I look forward to seeing what you do next!