Link to the earlier version of this document

Introduction and Motivation

Introduction to Financial Engineering or MAPL 498B (ENCE 688) is a course that I am enrolled in this spring. Jointly offered by the Engineering and Applied Mathematics departments, it is an introductory graduate course designed to expose students to investment theory, and its application to project evaluation and selection.  One of the requirements of the course includes participation in a Case Simulation where each person is a CEO of a company that owns a production line that manufactures Widgets(!). The company was founded early 1991 and I bought it in April 1995 at which time the company was not performing very well.

The production line has a constant production rate, i.e., it produces a fixed number of widgets everyday.  In normal situation, I operate the production line seven days a week.  As an owner, my objective is to make as much money as possible.  The Business Process of my production line is outlined in Figure 1.

Figure 1: Business Process of MAPL 498B Case Simulation

Because it may take a long time to justify an investment decision and we only have one semester to simulate, each day in real world corresponds to 14 days in the world of simulation.  The first day of the project corresponds to web-date 5/2/95.    When I logged into the simulation’s website on the second day of the project, I saw that the web date becomes 5/16/95 (= 5/2/95 +14 days). Every day at midnight I have access to financial data relevant to the past 14 days. In addition to this, I have access to the company’s historical (financial) data up to 5/1/95 (from 1/1/91).

The project involves continuous monitoring of the financial data so that I can make certain management decisions (such as expansion or shutting down of the production line). The only hints I have to watch the current data very closely and 'dig' through it and the historical data to try find possible patterns, trends and relationships.

Examining the Data

All data is supplied to us in the format as showing in Table 1:

Table 1: Format of data in MAPL 498

Figure 2: Examining Demand, Inventory and Balance of the Historical Data

I began by looking at the behavior of Demand, Inventory and Total Balance in the historical data set, which covered the period between Jan 1991 and April 1995. This is from the time the company was founded till the day I took over. We can clearly see that the demand fluctuated wildly but overall increased with time. Inventory sharply rose and then met the limit defined (warehouse capacity). This cost the company dearly as a lot of money was spent maintaining large inventory and discarding excess inventory when required. Total Cash Balance kept spiraling downwards into the red. This indicated that the production line should have been shut down periodically to maintain healthy inventory levels. Shutdown costs were eventually negligible compared to the loss made.

Figure 3: Examining Demand, Inventory and Balance of the 'Current' Data

I then looked at the behavior of the same triad in the current data, namely April 1995 (from the day I bought the company) to "today" (November 13th 1995). Armed with the knowledge that I have to watch inventory very closely, I was able to predict that very soon my total cash balance was going to start dropping. The initial honeymoon was over. While writing the proposal for this Application project, everything looked fine as inventory levels were normal and my overall balance was increasing. However, as I fed data into Spotfire over the next 2 weeks, I started thinking about shutting down my production line. The red box highlights the 'inversion' phenomenon where the equilibrium between inventory maintained and total amount of money (balance) is upset. The region shows where my inventory levels were high enough that the cost of maintaining them in the warehouse was higher than the profit I was making daily. From this point forward, Inventory levels were INVERSELY PROPORTIONAL to my total Balance.

This was truly a great example of information visualization as just being able to look at the change in chart I was able to arrive at a decision. Before implementing my decision, I obviously waited for a 'safe-period' just to confirm whether I could go ahead and shutdown my production line. This may have cost me a little money, but I thought better be safe than sorry! [Please see Conclusion for ultimate decision]

Figure 4: Plotting Demand and Balance against Inventory

With the above observations in hand, I thought it would be interesting to see how Demand and Balance looked when plotted against Inventory. The green box highlights the same phenomenon that the red box in Figure 3 shows, namely the reversal in proportionality at a certain point in the Demand-Balance equation.

The Challenge of Parallel Coordinates

I was fortunate to have access to Spotfire Version 5.1 that has a new feature to visualize data using something called a 'Profile Chart'. This is based on the concept of Inselburg's Parallel Coordinates, a very powerful way to view multi-dimensional data. I started playing with it to see if such a visualization could help what I was attempting to do. Included below are screenshots from this exercise. 

Figure 5: Examining Demand, Inventory and Balance of the Historical Data using Parallel Coordinates

I was unable to see too much with this. Is it because the data set is large? This could be the reason, because I was visualize a lot more when I looked at the Current Data Set which is much smaller in size. In later sections, I explain this in more detail.

Figure 6: Examining Demand, Inventory, Balance, Spot Price and Interest Rate using Parallel Coordinates (Historical data)

I began looking for 'interesting pictures' within the parallel coordinates framework. As Spot Price and Interest Rate were data that I had no previous knowledge of (like demand, I downloaded this data every midnight in the current operation of the company), I thought it might be useful to look for relationships between these categories.

Figure 7: Examining Demand, Inventory, Balance, Spot Price and Interest Rate using a tradition line chart

Is this better than Figure 6?

Figure 8: Examining Demand, Inventory and Balance of the Current Data using Parallel Coordinates

Repeating exercise (as in Figure 5). Here the 'inversion' phenomenon between Inventory and Balance (explained in detail alongside Figure 3) is astonishingly clear in the highlighted red box. The lines propagate in one direction from Balance to Inventory and a sudden reversal is seen (Crisscrossing of lines in center of the box).

Figure 9: Examining Demand, Inventory and Balance of the Current Data using Parallel Coordinates

Repeating exercise (as in Figure 6). The red box highlights the Balance-Inventory 'inversion'.

Figure 10: Examining Demand, Inventory, Balance, Spot Price and Interest Rate (Current Data) using a tradition line chart

Is this better than Figure 9? The red box is shown on the line chart too,

Parallel Coordinates is not an easy notion to grasp and while my understanding of all its nuances may be limited, I want to bring up the question: is parallel coordinates a better way to visualize such 'types' of temporal data or are plain old line charts quicker to reveal the data's secrets? (Many thanks to Matthias Mayer for agreeing to argue this out me with me). Also, it was certainly easier to visualize my smaller data set (Current Data) than the larger data set (Historical Data) which was spread over a much longer time period.

Evaluating Spotfire

(a) Spotfire Vs. Excel

I can already hear the screams that this not a fair comparison. However, the circumstances of this project makes this comparison not only fair but very important. The Case Simulation is a fairly standard assignment in Finance courses in most business schools around the world. Given an initial spreadsheet and some data, you use spreadsheet based tools to evaluate and visualize the data to arrive at decisions. While it is established that Spotfire can very easily import data and visualize it very quickly, I would like to re-emphasize this as the entire decision making process was considerably speeded up.

(b) Critique

The advantages of Spotfire are well documented. It begins with a big WOW factor as soon as you start up the application and import your data into it. Dynamic Querying allows you to excavate facts within the data very quickly. The Interface is intuitive and easy to use. Screen 'Real Estate' is well utilized by the main visualization window and the associated tools. Spotfire 5.1 has the new Profile Chart which brings the revolutionary idea of Parallel Coordinates to visualize data and also provides great export functionality to save your visualization in popular graphics formats (jpg, gif etc.).

However, when faced with a large data set (like in case of my historical data), it was much harder for me to 'visualize' the data. Sliders and legends became hard to manage and I was unable to study any patterns or trends in the crowded diagrams that were generated. Also, certain functions such as 'de-selecting' selected values in Profile charts were not at all intuitive. Once the data was displayed, I was yearning for some statistical capabilities such as regression analysis instead of going back to the Excel spreadsheet, performing the requisite calculations, formatting the new data and then importing the same back into Spotfire. 

Conclusion: So did it give me an edge?

There is absolutely no doubt that Spotfire is a great tool to visualize data. In this specific project, it made life a lot easier by generating charts and graphs 'on the fly'. I did not have (and do not have to in the future!) struggle with Excel to generate rather rudimentary looking charts while fighting with obscure control panels and difficult to manage mouse-keyboard combinations to choose data.

However, the greatest achievement of using Spotfire on this dataset was that I was able to arrive at a concrete decision to shutdown my production line for 28 days effective today (yes! Feb 28th 20001). I cannot think of a better way to measure success.

Links

Spotfire: www.spotfire.com

Course page for MAPL 498B/ENCE 688: http://www.eng.umd.edu/~chungli/ence688.htm

Other

Size of Historical Data Set: Approximately 1MB

Link to the earlier version of this document