Ten-Minute Talks: Under the Hood

Last week, I was relaxing at one of my favorite spots on campus when a friend approached me and asked for my Excel expertise.

Now, to be fair, I’m hardly an expert in Excel. There’s still a host of things I’m not at all comfortable with. But in this case, my friend needed to construct a histogram with multiple data series (essentially plotting two histograms on the same graph). It didn’t sound too hard, and I had homework and responsibilities to shirk, so I agreed to help. My first intuition was to just use the histogram wizard. My friend had already tried that, became incredibly frustrated, and had been furiously Googling to find an alternative method. In doing so, she was dragged through the weeds of the Analysis Toolpak add-in, searching for a wizard that she could use to make her histogram.

At this point, it occurred to me to abandon the search for a built-in solution and just make the histogram manually. After all, histograms are really just a specific kind of bar graph. You just create some ranges, count the number of datapoint between those ranges, and plot them as a bar graph. It really became some “COUNTIF” formulas.

This thought process was not only where I differed with my friend, but also where I differ from my younger self. Too often, we’re afraid to think about the underlying process in a software tool. We view them as black boxes, where we put in the right kind of information and it yields the right result. But doing this limits us to the functionality of the software we have available to us. When it comes to computational methods, I’ve often found it to be more effective and efficient to just create the tools I’m searching for.

Here’s another example. Last year, we had to use Monte Carlo simulation for a homework assignment in my Engineering Economics class. The goal was to simulate three different kinds of investments (T-Bills, Bonds, and Stocks) over 30 years, where year was drawn from a normal distribution for the return.

As a loyal (ish) Mac, user, I quickly ran into an issue: I couldn’t use @RISK on the Mac OS, and I was having some licensing issues on the boot camped partition of my computer (which runs Windows).

Now, I’m not here to claim that @RISK is a software that’s not worth the cost. We used the software on other, far more complicated problems that I couldn’t have solve easily myself.

But this problem really wasn’t that complicated, and Monte Carlo simulation is a fairly simple premise if you understand it. If you can generate a random number, you can simulate a random variable. This was made easier by the distributions we were given, which were all normal distributions, with a convenient inverse function built into Excel. With that, I created a spreadsheet that I could customize to my needs, while testing various strategies far quicker than I could have in @RISK.

All of this has already come in handy a few times. The first was on the final for that class, where we were asked to perform a simplified simulation by hand. Building the spreadsheet forced me to clamp down on the method, and it was easy to apply that understanding to hand calculations. This year, in my quest to make the perfect March Madness bracket, I again used simulation. But this time, it was in Python. Because I knew the method, I didn’t even need to find a library to do it for me; I just programmed it myself.

The common denominator in the histogram example and simulation example was my willingness to step down to a lower computational level, where the methods were applied directly. And understanding some methods has come in handy at other times. Recalling the bisection method for numerically finding the solution to an equation helped me construct my “bins” for the March Madness simulation histograms. And while it certainly takes longer to do things yourself, you get more control. That control clearly manifests itself in the way you solve the problem, and the tweaks that you can make. But it also gives you control over your timeline; if you only rely on tools built by others, then you’re more or less screwed if you find out that tool doesn’t exist the day (or night) before a deadline.