Financial Simulation Modeling in Excel

INCONSISTENT DATA FORMATS

Inconsistent data formatting is not an issue with the data itself, but with how different sets of data are presented. The most common form of discrepancies comes in ordering data and in data frequency. Historical pricing data is typically recorded daily; however, some sources of data display the most recent trade date on top, while other sources list the information in an ascending-date format. Other data fields may be produced or provided on a weekly, monthly, or yearly basis. When aggregating source data to set up a model, typically a number of different sources of data are necessary, and organizing the data, even when all the data sets are of high quality, can be difficult. Most formatting issues can be handled by using sorting functions in Excel and by recording macros to implement them automatically. In the Model Builders for this chapter, we will cover techniques to deal with some common data-quality issues.

Dividends and Splits

Many companies increase the number of equity shares they have outstanding through either secondary issuances, stock dividends, or share splits. All of these will influence the value of the shares and need to be handled differently depending on the type of model that is being implemented. Splits are the most simple. In a stock split, shares are exchanged in a defined ratio that does not change the economic position of the shareholder. So, for example, a holder of 10 shares of a $5 stock, after a 2-for-1 stock split, holds ...