Hello and best wishes for a very happy new year to you! I have been very focused on project work since the start of the pandemic and have fallen out of touch with many of you fine people. I do hope we can connect again in 2023, and as part of that effort I figured I’d start with this newsletter.

It’s been over a year since the last newsletter, and life here feels very different now. As expected, Eleanor and I are now empty-nesters. I’ve had kids at home for 27 years, so this is a big change! Taking advantage of our newfound flexibility, next month we’re planning to be in Taos, New Mexico--close to the ski hill. There are still a few dependencies, but hopefully it works out.... My early-starting schedule should let my daily exercise be a few hours on skis. But if it’s a power day, well, we might need to change that meeting time.

Friends & Family Spreadsheet Help Offer

I regularly get calls from folks who are stuck on some Excel problem. They range from very basic to quite advanced, and I enjoy helping out. I’d like to extend that offer to you, since you subscribe to the newsletter. I like hearing from people randomly and I find the issues to be like a little puzzle I get to solve before getting back to work.

Linear Algebra and Excel

As I see the end of full-time work perhaps on the five-year horizon I’ve started imagining a second career. Don’t get me wrong--I still love what I do, but I can also imagine not doing it all day every day. Ideally I’d like to keep modeling for decades to come and add some new stuff into the mix. While this is still a vague plan, I’m looking at doing modeling for scientific teams. As part of that I started studying math again during the pandemic, starting with all humility with Algebra 2. It turns out, what a surprise, that I really enjoy math--the rigor, the beauty of how it fits together, having it eventually click, and so on.

In 2021 I finished Linear Algebra. I had taken it during my freshman year of college, and I don’t recall enjoying it. This time through I saw the elegance and the brilliance. It’s a very simple and compelling framework with all kinds of applications.

I quickly found a few uses for the linear algebra mindset and I want to share two of them.

Dot Products, SUMPRODUCT, and NPV
The first concerned itself with dot products. (Recall there are two ways to multiply two vectors together, dot products and cross products.) This time through the course I quickly took a liking to dot products because they are a simplified version of the SUMPRODUCT function, and I just love using Sumproduct…I’ve talked about it before many times. In fact, I use it so much that I once caught myself wondering if I’d be willing to cut off a finger to be able to keep using it!

Since the very beginning of my modeling with Marakon in 1988 I’ve been calculating NPVs in Excel. Of course, Excel has a built-in NPV function which works quite well, and XNPV works when date coding of columns gets more complex. Through 2000 I used to discount the cash flows myself, period by period, dividing each value by (1+Ke)^n. Eventually I grew to trust the NPV function, but even this past week I found myself doing it by manually again just so I could be sure how it treats the first period.

The dot product innovation was to treat NPV as the SUMPRODUCT of reciprocals, i.e. a stream of 1/(1+Ke)^n. I then “dot-producted” these against cash flow streams, allowing me to easily calculate NPV in a consistent way and without constantly referencing the Ke input or other date codes. In practice, it means that instead of just calculating a single NPV after all cash flows have been consolidated, now I can calculate the NPV of individual line items (revenue or expense components, for example). This lets me better hone in on the value drivers inside of models.

Matrix Vector Multiplication and Network Usage
The second application involved matrix vector multiplication, which is a major focus of linear algebra tool kits.

For background, many of you know that I’ve long specialized in the economics of telecom networks, and more recently I’ve developed a new sub-speciality in submarine fiber optic cable systems, i.e. the subsea cables which carry traffic between continents.

In most businesses, revenue is price times quantity, and investors want an easy way to think about the assumption behind the quantity sold in our forecasts. One easy way to do that is to consider how much of the system capacity is being utilized, since that’s a percentage from 0% to 100%. In a basic “network” with a single connection from point A to point B this is easy to measure for a given period of time. But some of these systems get complex: there can be a few dozen physical segments (PS) and a great many different route products (RP) which can be sold. Of course, each route consists of a known list of physical segments. While I could forecast demand by RP I needed to translate that into demand by PS to ensure that no part of the network was overtaxed.

And this leads us to the linear algebra part of this story. With that new toolkit in mind I could see that this was a basic linear transformation, a mapping from a domain (the m-dimensional vector of route products) to a range (the n-dimensional space of physical segments). This is MVM, matrix-vector multiplication, where you multiply a vector times a matrix to get a new output vector. The matrix itself was easy to assemble, as the rows are physical segments and there is one column for each route product. The entries are either a zero or a one: a one if that physical segment is needed to connect up that route.

Satisfyingly, this all coded up quickly and worked like a charm! As usual with my Excel tricks, my clients were happy with the functionality and I was a bit tickled at the elegance under the hood making it happen.