DPMX, Excel and the database: How to have your cake and eat it too


Q. Should your portfolio system be spreadsheet-based and open or database-powered and robust?
A. Yes! DPMX Flexibility and Robustness

In an earlier post I introduced DPMX, and said a few words about the importance of data management and the value of transparent modelling environments like Excel and DPL. Here I'm returning to the topic to drill down on the subject of spreadsheets and databases, which are close friends in our milieu.

Traditionally, portfolio management has involved a wrenching tradeoff between ad-hoc approaches that are flexible but messy and "systems" that are reliable but slow to change. The architecture of DPMX is designed to strike a vastly more advantageous compromise between those two extremes, bringing a better combination of flexibility and robustness than was previously possible.

Spreadsheets are great, but, ...

Many analysts doing project and portfolio management have a love hate relationship with Excel. On the one hand, it's a familiar environment, a work-a-day tool that's seldom optimal but almost always gets the job done. When the boss says "can you just do a quick off-to-the-side calculation and tell me _____" that quickie is an Excel spreadsheet. Just the same, all that flexibility and convenience comes with a lot of drawbacks. Is the latest sales forecast in EMFIIIg_CF_adjQ2_(Edited JMS)_MktShare Graph.xlsx? Or rather, is it in the copy of that file in your email, your group share drive, your hard drive, or somebody else's hard drive?

All the challenges of evaluating projects in spreadsheets increase exponentially when a portfolio is involved. Are all of those 103 project spreadsheets using consistently calculated metrics? The new VP Finance insists on tax loss carry forward, that'll be easy, right? Just make the change in 103 documents.

Why not bring out the "Big Iron"?

It's tempting to respond to the chaos and inefficiency of ad-hoc spreadsheet analysis with a database-powered portfolio management system built to a set of static requirements. Such a system typically has a large number of data fields and business rules to produce the necessary results. They can be very time consuming to build and test, but they typically offer a high level of security, robustness and reliability. Whether such a system is on a modern or legacy platform is immaterial, it's basically a big ship, efficient in fulfilling unchanging requirements but very hard to turn.

The trouble with the heavy system approach is that business needs often move more quickly than change orders can be written. What happens when that VP Finance asks the question and she needs the answer faster than the big system can be reprogrammed? Well, somebody dumps the data, puts it in a spreadsheet, and does an off-to-the-side calc! How reliable is that process? What's the point of that heavy system if critical analysis is done somewhere else?

The DPMX managed spreadsheet approach

DPMX steps around the Excel/system tradeoff by using Excel for cash flow calculations and for data entry, but not for data management/storage. A DPMX Excel template is a real spreadsheet: you can open one up, look at the formulas, enter data, and see how the results change. The difference comes when that file is checked back into the DPMX intranet portal: the actual project data is stored in a database, timestamped, and linked to the user who changed it. For a particular project you can see who has changed what when, and examine historical versions. "How did these ancient sales numbers end up in here?" You can answer that question easily.

In the real world of portfolio analysis, rules tend to change while the game is being played, and DPMX helps you roll with it. Changes to calculations, outputs and even new metrics are user-level changes -- no programming required.

The whole package

I haven't said a word about analytics, but as you would expect, DPMX works seamlessly with DPL Portfolio. DPL class leading decision tree analytics. The convenience and openness of Excel. The robustness and security of a database-powered intranet system. That's the DPMX system.

Tags: