Time & Money: Financial modeling prototype


#1

Time & Money

I am setting out to build a financial modeling platform in Coda that would make Coda not only competitive with Excel, but superior to Excel, for building and using financial models. I call it Time & Money. I know it sounds ambitious, but isn’t Coda itself pretty ambitious?

So far, I have created a relatively simple prototype that demonstrates how a financial modeling platform in Coda might work. It demonstrates:

  1. That time series data, such as monthly cash flows, can be addressed in Coda.
  2. How in Coda, cash flows can be generated by rules, rather than by copying numbers and formulas across columns and down rows as in Excel.
  3. Some huge advantages of a rules-based approach to generating a financial model:
    • The user can easily modify major model parameters, such as the project schedule, that would require structural changes to an Excel model.
    • Changes can be made to model parameters definitionally, such as to increase all expenses by 5%. To do something like this in Excel would require what a financial modeler would likely call “brain damage."
    • Errors in copying cells across and down, which are hard to eliminate in Excel, become nonexistent.

To get a copy of the prototype: 1) click the link below, 2) click “Open with Coda” at the top of the screen, and 3) click on the down arrow next to the “Time & Money Prototype” title to select “Copy doc.”

–> Time & Money Prototype

Time & Money grew out of a project I started a few years ago, to be built on top of Excel. I had run a real estate development company that relied heavily on cash flow modeling, and I was continuously frustrated by how seemingly small changes to the model (“hey, what if you put a 5% annual growth rate on sales prices?”) required hours to implement. What’s worse, we kept finding little errors in programming cells, particularly as models became more complex, that had huge effects on the projected bottom line. Holding a 30-page printout of a model that we were going to use to make a major financial decision, I would joke (with gallows humor), “so we are going to bet the whole project on this packet of paper?”

Although I set out to build Time & Money years ago to solve some of these major financial modeling problems, I realized that doing it on Excel would be an immense project. I got bogged down, then got a job, and I set Time & Money aside. Now Coda provides a platform that provides an easily programmed relational database engine at its foundation—a far better base for building Time & Money—and I am at it again.

This prototype is the first step. From here, I plan to:

  1. Generalize Time & Money to make it a platform for building cash flow models of all kinds;
  2. Create Time & Money templates for specialized uses with which I am familiar;
  3. Add features, such as handling debt and equity financing structures, to increase the usefulness of Time & Money; and, eventually
  4. Add features that make it useful for other types of financial modeling (beyond cash flow modeling).

I don’t know yet how Time & Money should be monetized. That may depend largely on what tools Coda might provide in the future for distributing and monetizing Coda docs/apps/templates.

I would love any feedback on the concept and on the prototype.


Formula for loan payments (PMT), debt service coverage ratio
#2

This looks beautiful! I will have a deeper look later today, but I already love the clean structure and the use of colors to emphasize the setions.

One interesting part, I was not aware of: when you use the slide-controls in section Summary, it calculates during the slide process along all the steps, instead of just calculating when you “drop” the control on the aspired position. Not sure if bug or feature of Coda :thinking:


#3

Thank you, @Daniel_Stieber! I would love to hear more comments once you are able to take a deeper dive.

As for the feature/bug question, I think there are two competing features that make it looks like there’s a bug. First, the recalculation on the fly, as the slider is slid, is actually a very cool feature. It actually recalcs pretty fast, and it allows you to instantly see the effect of a change on the bottom line numbers. Second, the bar chart animates the bars growing out of the base whenever the chart is changed. The problem is that the animation takes a second to complete, so as you slide a slider, the animation keeps restarting but does not fully finish until you stop moving the slider. The result is a herky-jerky action on the chart that looks like a bug. IMHO, it would be better if Coda nixed the animation on the chart so that as the slider is moved, the bars on the chart would instantly move to their new positions in response to moving the slider, creating a kind of animation effect that the user is controlling with the slider.

I have added a suggestion to Coda about this situation.


#4

@Todd_Olson this is awesome! You’re onto something pretty powerful. I love the concept of rule-based financial models, rather than the “battleship”, cell-based Excel models that the entire financial world is currently run on (which are very prone to errors).

Excited to see what other financial models you build in Coda!

Justin


#5

Thank you, @justin! You understand the problem I’m trying to solve. Makes me :grinning:.


#6

Big fan of the concepts you talk about in your post (e.g. rules-based vs. formulas) @Todd_Olson. On the Summary section, perhaps you could try using the Filter() formula instead of Switch() to get the summary values from the “Cash Flow model” table. This way you can shorten the formula in this table, and the formula would filter by whatever is in the Type column (e.g. Revenues, Expenses, Net Cash Flow, etc.) This would also make the summary table more extensible as you add new “Types” to this table.


#7

Thank you for the encouragement and formula suggestion, @Al_Chen_Coda!. Much appreciated. I’m relatively new to Coda’s formula language, and I just started discovering the power of the Filter() function. As I work to generalize Time & Money, I can see that it will be very useful.