iPhone Programming Begins

Posted 10/30/2009 by Matthew C. Vanderbilt
Categories: Uncategorized

I’ve recently decided to learn how to program iPhone apps, mainly because there are a few applications that I would like to have and, surprisingly, can’t find in iTunes. Unfortunately, my C++/Java programming is very rusty and really not close enough to Objective-C programming to be helpful anyway, so I’ve purchased iPhone SDK Programming. With a little luck and a bit more free time, iPhone / Objective-C tips will soon be added to this blog. Stay tuned for more!

If you’re interested in the book I’ve chosen to begin with, you may review it here on Amazon.

In other news, this is the first post utilizing the trial version of Blogo; we’ll see how it goes.


Comments: Be the first to comment

Microsoft Excel VLOOKUP Function (Advanced)

Posted 10/29/2009 by Matthew C. Vanderbilt
Categories: Excel, Intermediate, Programming / Software

Previously, I wrote on the basics of utilizing the VLOOKUP function in Microsoft Excel.  This session will expand upon those basics in more of a “real-world” scenario.  In this example, a timekeeping table has been extracted to show Labor Date, Employee ID, and Hours Worked.  Using the VLOOKUP function, we’ll add a labor premium based on the start time of each employee, as well as the total pay, job title, and employee name.  Please note that this discussion assumes a certain level of comfort with the IF function.  If you’re currently hesitant with using this function, leave a comment on this blog or stay tuned or a future session where the IF statements within Microsoft Excel, Microsoft Access/SQL (IIF), and ACL are reviewed.

VLOOKUP Example
Reference File: http://www.box.net/shared/jbn8o4xi75

Worksheets

  • Summary Table – Pivot table showing total pay by employee-job title.
  • vLookup Example – Primary table showing the [Example Data] and the VLOOKUP data
  • Employee Index – Index table of employees by employee ID with the job code of each individual
  • Job Index – Index table of job titles by job code
  • Start-Time Premiums – Index table of wage premiums based on employee start time

Wage Premium (Column D in [vLookup Example])
This example, proposed by a colleague, shows a scenario where an analyst may wish to set the ABSOLUTE LOOKUP FLAG to TRUE.  A review of [Start-Time Premiums] shows that employees that start between 20:00 and 06:00 receive 120% of their base pay, employees starting between 06:00 and 12:00 or 18:00 and 20:00 receive 100% of their base pay, and employees starting between 12:00 and 18:00 receive only 98% of their base pay.  In a scenario where the ABSOLUTE LOOKUP FLAG is set to FALSE, an exact match would be required, which would lead to a significantly larger index table.  In this example, however, we are able to utilize a simple index table by setting the ABSOLUTE LOOKUP FLAG to TRUE.

Cell D3: =VLOOKUP(TIME(HOUR(A3),MINUTE(A3),0),’Start-Time Premiums’!$A$3:$B$7,2,TRUE)

The time component of the Labor Date, a Date/Time field, is extracted and looked up within the [Start-Time Premiums] index table.  For Row 3, this is 08:25, which does not exist within the index.  However, since the flag has been set to TRUE, the VLOOKUP function returns 100%, the reference for start times between 06:00 and 12:00.  For Row 8, 17:55 is the start time, for which the VLOOKUP function returns 98%, the reference for start times between 12:00 and 18:00.  So, when dealing with Date/Time indexes, where absolute matches may lead to excessively-large index files, setting the ABSOLUTE LOOKUP FLAG to TRUE may be the optimal solution.

Pay (Column E in [vLookup Example])
>Cell E3: =IF(ISERROR(VLOOKUP(B3,’Employee Index’!$A$3:$D$28,3,FALSE)),”_Unknown”,ROUND(D3*IF(C3<=8,C3,IF(C3<=12,8+(1.5*(C3-8)),8+(1.5*4)+(2*(C3-12))))*VLOOKUP(B3,’Employee Index’!$A$3:$D$28,3,FALSE),2))

There’s a lot going on in the cell, so let’s first look at what we’re trying to do.  We know when the employee started work and how many hours they worked (in this case 08:25 and 8.6 hours).  So, the VLOOKUP needs to be utilized to pull the hourly wage for the individual, but an added level of analysis must be performed to determine straight-time (1x), overtime (1.5x), and double-time (2x) hours; in this example, the employee worked his/her eight hours of straight-time plus 0.6 hours of overtime (we’re ignoring weekends and holidays in this example).  The equation must also pull the wage premium (already calculated in column D).  Finally, an error handler is added to return “_Unknown” if the employee ID does not exist within [Employee Index]; conditional formatting has already been added to colour the text red if the equation returns “_Unknown”.  The equation will now be broken down into its components:

<<A>> =IF(ISERROR(<<B>>),”_Unknown”,<<C>>)
<<B>> VLOOKUP(B3,’Employee Index’!$A$3:$D$28,4,FALSE)
<<C>> ROUND(D3*<<D>>*<<B>>,2)
<<D>> IF(C3<=8,C3,<<E>>)
<<E>> IF(C3<=12,8+(1.5*(C3-8)),8+(1.5*4)+(2*(C3-12)))

<<A>> utilizes the ISERROR() function, which returns TRUE if there’s a problem or false if the equation executes correctly.  In this case, “_Unknown” will be returned if <<B>> returns an error, while <<C>> will be returned if <<B> does not return an error (since the ABSOLUTE LOOKUP FLAG has been set to FALSE in <<B>>, an error will occur if the value being “looked up” does not exist in the dataset).

<<B>> This is the actual VLOOKUP function, which is checking the value in B3 (the Employee ID) and pulling the fourth column from ‘Employee Index’!$A$3:$D$28 (Hourly Wage) if an exact Employee ID match is found (notice that Employee ID is a text field, not a numeric field).

<<C>> This simply multiplies the wage premium (D3) by the converted number of straight-time hours worked (<<D>>) and the hourly wage returned by the VLOOKUP function (<<B>>) and rounds to the nearest penny.  Simply put, this is calculating the exact pay due to the employee for the hours worked that day.

<<D>> This IF statement checks to see if the employee worked 8 hours or less (straight-time only) and, if so, returns the number of hours worked; if the employee worked more than 8 hours, <<E>> is returned.

<<E>> Slightly more complicated, this IF statement already knows that the employee worked more than 8 hours, but checks first to see if the employee worked 12 hours or less (8 straight-time hours and an overtime component).  If this is true, <<E>> returns 8 hours (straight-time) plus 1.5 times the remaining hours (this converts the overtime hours to the straight-time equivalent).  If the employee worked more than 12 hours (8 straight-time hours, 4 overtime hours, and the rest as double-time), then <<E>> returns 8 hours (straight-time) plus 1.5*4 (6 straight-time hours equal 4 overtime hours) plus 2 times the remaining hours (this converts the double-time hours to the straight-time equivalent).

Therefore, in Row 3, $271.98 is returned as the pay for the day.  This is because Employee 2304 exists in ‘Employee Index’ and receives $30.56 per hour.  Since the employee started between 06:00 and 12:00, the wage premium is simply 100% of pay.  Finally, since the employee worked 8.6 hours, <<E>> converts this to 9.2 straight-time hours.  That means that the entire equation ends up being 100% * 8.9 * $30.56 rounded to the second decimal place, which equals $271.98.

Alternatively, in Row 9, you can see that the formula returns “_Unknown”, because employee 7205 does not exist within [Employee Index].

For further growth with Microsoft Excel formulas, including the IF statement and VLOOKUP function, manually calculate some of the other values in Column E to see if you can verify the formula’s result.

Job Title (Column F in [vLookup Example])
Cell F3: =IF(ISERROR(VLOOKUP(B3,’Employee Index’!$A$3:$D$28,3,FALSE)),”_Unknown”,IF(ISERROR(VLOOKUP(VLOOKUP(B3,’Employee Index’!$A$3:$D$28,3,FALSE),’Job Index’!$A$3:$B$7,2,FALSE)),”_Unknown”,VLOOKUP(VLOOKUP(B3,’Employee Index’!$A$3:$D$28,3,FALSE),’Job Index’!$A$3:$B$7,2,FALSE)))

Within this column, the formula needs to lookup the employee ID in [Employee Index], find the individual’s job code, and look that up within the [Job Index] table.  If either the Employee ID or Job Code is not found within the index tables, the formula needs to return “_Unknown”.  To accomplish this, two IF statements need to run the ISERROR() check and a nested VLOOKUP formula to find the Job Code must be utilized within the VLOOKUP formula that returns the Job Title.  So, the equation may be broken into the following components:

<<A>> =IF(ISERROR(<<B>>),”_Unknown”,<<C>>)
<<B>> VLOOKUP(B3,’Employee Index’!$A$3:$D$28,3,FALSE)
<<C>> IF(ISERROR(<<D>>),”_Unknown”,<<D>>)
<<D>> VLOOKUP(<<B>>,’Job Index’!$A$3:$B$7,2,FALSE)

<<A>> This is the initial function that checks to see if <<B>> (the Job Code lookup) returns an error (i.e. the employee ID does not exist within [Employee Index]).  If an error is returned, the function returns “_Unknown”; if there is no error, <<C>> is executed.

<<B>> This is the VLOOKUP that returns the Job Code from [Employee Index] by looking for the Employee ID (B3) within ‘Employee Index’!$A$3:$D$28 and returning the third column (Job Code).

<<C>> This is the second IF statement, which checks to see if the selected job code exists within [Job Index].  If the Job Code does not exist, an error is returned, and the cell will display “_Unknown”; if the Job Code does exist, <<D>> is executed to pull the Job Title.

<<D>> This is the final VLOOKUP, which pulls the Job Code returned by <<B>> and looks for it within ‘Job Index’!$A$3:$B$7.  Once found, the second column, Job Title, is returned.

Through these nested IF and VLOOKUP statements, an index embedded within a primary index table (in this case, Job Code within the [Employee Index]) may be pulled from a secondary index table ([Job Index]).  Through the use of the two IF statements as error handlers, the function will return “_Unknown” if either the employee ID or job code do not exist; as stated in the previous example, the cells contain conditional formatting to highlight “_Unknown” as red in order to be flagged for further review.

Employee Name (Column G in [vLookup Example])
Cell G3: =IF(ISERROR(VLOOKUP(B3,’Employee Index’!$A$3:$B$28,2,FALSE)),”_Unknown”,VLOOKUP(B3,’Employee Index’!$A$3:$B$28,2,FALSE))

This is one of the more basic VLOOKUP functions contained within this example.  Simply put, the Employee ID is being searched within the [Employee Index].  If it exists, the second column, Employee Name, is returned; if it does not exist, an error is returned, which the IF statement catches in order to display “_Unknown”.

Perils of ABSOLUTE LOOKUP FLAG to TRUE
Columns H through J have been added to again highlight the perils of setting the ABSOLUTE LOOKUP FLAG to TRUE.  In this example, rows 9 and 33 contain employee IDs that are not listed within [Employee Index].  When the ABSOLUTE LOOKUP FLAG is set to FALSE, the error handler is returning “_Unknown”, which, through Conditional Formatting, has been set to be highlighted in red.  Notice, however, within Columns H through J that, instead of flagging an employee that does not exist, an actual employee is being shown as receiving the pay.  This could, at best, throw off the validity of the data analysis and, at worst, fail to uncover potential fraud or inaccurate source data.  Therefore, always remember to properly set the ABSOLUTE LOOKUP FLAG when utilizing the VLOOKUP formula and, when in doubt, set it to FALSE.

Comments: Be the first to comment

Microsoft Excel VLOOKUP Function (Basic)

Posted 10/27/2009 by Matthew C. Vanderbilt
Categories: Basic, Excel, Programming / Software

The VLOOKUP function is one of the most powerful functions within Microsoft Excel for data analysis and reporting; unfortunately, it tends to be one of the lesser-known and under-utilized functions. This session will provide some basics on the use of the VLOOKUP function; a later session will provide a more “real-world” example.

Function Purpose
The VLOOKUP function is useful when an analyst has a value in one table that they need to pull into another table. For example, consider that you have a table of customer transactions, but, for indexing purposes, only the Customer ID is shown. If you have an index of Customer Names by Customer ID, you may utilize VLOOKUP to add the Customer Name to the transaction table. Although this is more of a database function (a Microsoft Access query with a left join on Customer ID would run faster), there are times when a “quick and dirty” analysis is all that is required, making the added time of creating a database unnecessary.

Components of VLOOKUP
The VLOOKUP function contains requires four variables as shown:

=VLOOKUP(<LOOKUP VALUE>,<LOOKUP TABLE> ,<COLUMN REFERENCE> ,<ABSOLUTE LOOKUP FLAG> )

  • LOOKUP VALUE is the value to be “looked up” from the alternate table; in the example above, this would be the Customer ID within the transaction table.
  • LOOKUP TABLE is the table from which a value is to be pulled; in the example above, this would be the customer index table, which would contain Customer ID, Customer Name, etc.
  • COLUMN REFERENCE is the number identifying the column within the LOOKUP TABLE from which a value is to be returned. If the Customer Index Table in our example contains Customer ID, Customer Name, etc, then the COLUMN REFERENCE would be 2.
  • ABSOLUTE LOOKUP FLAG is a Boolean (TRUE/FALSE) field that specifies whether or not an exact match is required; in general, it is recommended that an analyst always use FALSE. If the flag is set to FALSE, VLOOKUP will return an error if an exact match is not found within the index table; if the flag is set to TRUE, VLOOKUP will return the closest match.

VLOOKUP Example
Reference File: http://www.box.net/shared/1hx8gxqlq2

The reference file provides a very basic example utilizing the VLOOKUP function. Within this example, A2:B8 represents the index table; D2:E8 provides an example of the VLOOKUP function where the ABSOLUTE LOOKUP FLAG is set to TRUE; G2:H8 provides an example of the VLOOKUP function where the ABSOLUTE LOOKUP FLAG is set to FALSE; J2:K8 provides the same example as G2:H8 with the ABSOLUTE LOOKUP FLAG set to FALSE, although an error handler has been added. Note within the Index Table (A2:B8), the index “3″ has been left out; this has been done intentionally to show the differences between utilizing TRUE and FALSE with the ABSOLUTE LOOKUP FLAG.

Example #1 – VLOOKUP with TRUE
The formula utilized in E4 is =VLOOKUP(D4,$A$4:$B$8,2,TRUE), which means that the function will pull the value within the second column of A4:B8, where the first column matches D4. Because the ABSOLUTE LOOKUP FLAG has been set to TRUE, an absolute match is not required, and the function will return the closes value. For Index “1″, the function returns “This is #1″ as shown in the Index Data. However, for Index “3″, the function is returning “This is #2″, which is obviously incorrect. Because the ABSOLUTE LOOKUP FLAG has been set to TRUE, when VLOOKUP cannot find an exact match, it will assume the data is sorted and return the value prior to the location where the actual value is located. In this example, “3″ is not within the Index Data, so VLOOKUP returns data for “2″ (the closest available match).

Example #2 – VLOOKUP with FALSE
The formula utilized in H4 is =VLOOKUP(G4,$A$4:$B$8,2,FALSE), which means that the function will pull the value within the second column of A4:B8, where the first column absolutely matches G4. Notice that, in this example, H6 (the lookup of index “3″) returns “#N/A”, a Microsoft Excel error flag. This is because “3″ does not exist within Index Data and the VLOOKUP function has been told to only return absolute cell matches.

Example #3 – VLOOKUP with FALSE (Error Handler)
The IF statement and use of the Error Handler (ISERROR) will be discussed at a later date. However, this example does show how an error handler may be utilized in conjunction with the VLOOKUP function to control what is returned when absolute matches do not occur. In this example, “#BLANK#” is returned for value “3″. If a customer transaction table was being utilized (the example discussed in the beginning of this session), the return value may be “Unknown Customer” or some other value. This use of the error handler may allow for better formatted reports, but also allows for the use of conditional formatting to flag potential problems within index tables.

Formula in K4: =IF(ISERROR(VLOOKUP(J4,$A$4:$B$8,2,FALSE)),”#BLANK#”,VLOOKUP(J4,$A$4:$B$8,2,FALSE))

Keep watch for the more advanced session on VLOOKUP, where VLOOKUP is applied in a more “real-world” example: employee pay.

For more information on Microsoft functions, always review the Microsoft Support site. For VLOOKUP and HLOOKUP functions, information may be found in the article, How to Use VLOOKUP or HLOOKUP to find an exact match.

Comments: 1 Comment

Conversion of Multiple Data Ranges to Flat File

Posted 10/12/2009 by Matthew C. Vanderbilt
Categories: ACL, Basic, Programming / Software

Recently, a data index was provided for financial data that specified whether a specific transaction was “qualified” or “non-qualified”.  In the interest of confidentiality, specifics have be left out of this discussion and example files have been edited to generic data.  The financial transactions were coded in the usual method with a cost center, an account, and a work order.  The index table, however, was unique in that it contained no primary key.  Instead, the data listed ranges of qualified data: [CostCenter_From], [CostCenter_To], [WorkOrder_From], and [WorkOrder_To].  For example, any transaction that contained a Cost Center and Work Order combination that were within the ranges shown in the index was “qualified”.  During an initial review of the dataset, a work-around was created to combine the data with no direct joins, resulting in significant duplication of transactions.  From there, a boolean  field (True=1, False=0) was added to test if the transaction Cost Center and Work Order were within the index range.  After that, data was grouped by the original transaction data and the boolean field was summed as an integer (any transaction where the value was greater than 0 was “qualified”); data checks were instituted to ensure that no duplicates existed in the final table.  Although embarrassing in its simplicity, this approach worked for purposes of the initial review.  However, due to the risk of data duplication and the significant amount of processing time and network storage space required to make this work, the approach was not feasible for utilization on a continuing basis.  The index table, therefore, needed to be converted into a standard relational index.  Visual Basic for Applications (VBA) within Microsoft Access could not handle the massive amount of data involved without crashing, so the conversion was programmed via Audit Command Language (ACL) Scripting.

ACL Example Files (save to C:\ACLExample01\)
ACL Project: http://www.box.net/shared/yx6ale4u15
Original Raw Data Index: http://www.box.net/shared/8tf12lbsg6

ACL Script – Walkthrough

  1. The ACL safety routines are set to off in order to prevent ACL from stalling script execution with “are you sure” confirmations. (SET SAFETY OFF)
  2. The loop maximum is set to 0, which prevents ACL from automatically timing out if a potential infinite loop occurs.  This can be disastrous during debugging, but was the only way to execute this script, as some indexes required 10,000 loops.  (SET LOOP TO 0)
  3. In case this is not the first execution of the script, the final table is deleted (both from the navigator and the actual FIL file).
  4. The raw-data table is opened (OPEN Index_Raw)
  5. The group command is then utilized (GROUP … END).  This command tells ACL to process everything within the command on each record in the table.
  6. The variable “CostCenterFromValue” is initialized to [From_CostCenter] within the raw data.
  7. The first loop (LOOP WHILE CostCenterFromValue <= To_CostCenter) is then executed.  This will cause ACL to keep looping until the maximum value for the range is achieved.
  8. The variable “WorkOrderFromValue” is then initialized to [From_WorkOrder] within the raw data.
  9. The second loop (LOOP WHILE WorkOrderFromValue <= To_WorkOrder) is then executed.  This will cause ACL to keep looping until the maximum value for the range is achieved.
  10. CostCenterFrom and WorkOrderFrom are then extracted to what will become the temporary index table.  (EXTRACT FIELDS CostCenterFrom WorkOrderFrom TO “Index_Flat_Temp”)
  11. The Work Order variable is then incremented (WorkOrderFromValue = WorkOrderFromValue + 1)
  12. The second loop is closed (END), which causes the code to jump back to the validation at #9.
  13. The Cost Center variable is then incremented (CostCenterFromValue = CostCenterFromValue + 1)
  14. The first loop is closed (END), which causes the code to jump back to the validation at #7.
  15. Once CostCenterFromValue is greater than [To_CostCenter] within the first loop, the script will move to the close of the group (END).
  16. The raw-data table will then be closed (CLOSE Index_Raw).

At this point, Index_Flat_Temp has been created.  Within the ACL script example, it is assumed that duplicate indices are possible and that the Cost Center and Work Order values should actually be character fields.  Therefore, new [CostCenter] and [WorkOrder] character-type fields are created in the table, which is then summarized by those fields. The DEFINE FIELD and SUMMARIZE commands will be detailed in a future discussion.

Comments: Be the first to comment

Decrease of Internal Audit Staff During Recession

Posted 10/09/2009 by Matthew C. Vanderbilt
Categories: Accounting, Auditing, Independence, Responses to the News

A recent article in WebCPA, Internal Audit Staff Hit Hard by Recession, discussed a survey performed by the  Institute of Internal Auditors indicating that internal audit department staffing levels had been significantly decreased in 2008 and 2009.  Although auditors, and financial personnel in general, should in no way be exempt from the pain of budget and resource cuts, the reduction of internal audit personnel raises some concerns and highlights the need for internal audit departments to not lose focus of being a value-added business partner.

In times of economic crisis, the inherent risk of employee fraud increases significantly.  As the commodity of jobs becomes more scarce, and the supply of qualified employees increases, an individual’s concern regarding the safety of his/her continued employment at a company increases.  This stress worsens with the knowledge that companies are having to shed costs and resources in order to remain solvent and that the job market is too lean to be guaranteed finding similar employment before severance runs out.  Coupled together, these facts can result in employees that would never stray from the ethical path beginning to minimizing, or even falsify, negative results in management reports.  The role of an internal auditor to identify fraudulent activities before a company makes decisions using falsified data becomes even more important due to this increased risk.  The reduction of already thinly-staffed internal audit departments, however, reduces the ability of audit departments to perform more than a minimum assurance service, resulting in no mitigation of the increased risk.  Therefore, the reduction of internal audit staff during poor economic periods may result in increased risk going unchecked and could threaten the solvency of a company that would otherwise have weathered the troubled waters unscathed.

Internal audit departments should not, however, believe themselves to be too great a savior against corporate risk to lose sight of their role as a value-added business partner.  It is too easy for an internal audit department to focus on the Audit Plan at the exclusion of all other opportunities.  It must be remembered that an Audit Plan is simply a plan; it should not be viewed as a map through a minefield – strictly adhered to without deviation.  Rather, the Audit Plan is like the North Star when navigating the ocean – use it as your guiding light, but deviate when new opportunities present themselves.  As soon as an audit department stops responding to business-unit requests and only follows the audit plan, the stagnancy quickly results in decreased perceived benefits to a corporation, which will rightly result in decisions to decrease staff.  No communication from an internal audit department should ever read, “Due to competing priorities that our auditors have, as well as an aggressive audit schedule, we will not be able to review your request.”  With one letter, an internal audit department can quickly cease to be an internal, value-added, business partner.

Responsiveness to business-unit requests, however, does present the valid concern of maintaining independence of a process.  Attribute Standard 1100 of the Professional Practices Framework (PPF) issued by the Institute of Internal Auditors states that “[t]he internal audit activity must be independent, and internal auditors must be objective in performing their work.”  The standards go on to state that this means that the audit organization must report to the appropriate level of an organization for corporate management to have undue influence.  Additionally, an individual auditor’s independence, or perceived independence, may be impaired if (s)he audits an area over which (s)he previously had responsibilities (within a period specified in the PPF).  As one of the very first attribute standards, it is obvious to note the importance of independence and objectivity in performing an audit.  Independence, however, should not become an excuse that prevents an auditor from rendering assistance to departments in need.  Internal audit department management should not prevent auditors from providing value-added recommendations because “development of the detailed plan is Management’s responsibility, not the Auditor’s” or because of a false notion that the recommendation would impair independence.  To remain useful to an organization, and truly be a partner in the business, internal auditors must be allowed to render any value-added assistance to management.  The internal auditor cannot take control or ownership of a process, but they can still render an opinion, provide a recommendation of how to analyze an issue, or even just give the benefit of a second set of eyes on a document.  Sometimes the simplest task can provide the most value, even if it is not a specific task noted in the audit charter.  Providing that service, however, reinforces to individual employees and business units that the internal audit department is a partner in the business.

Internal audit departments are a necessary part of managing a business, but should not be perceived as simply that.  They must leverage themselves to show their ever-increasing importance during economic troubles resulting in increased corporate risk.  They must also prove themselves to be a partner in the business, willing to lend a helping hand when needed.  The audit plan is an important in navigating annual audit priorities, but it should not be viewed as the only path to travel.  Independence is one of the most important qualities for an auditor to maintain, but it should not become shackles that prevent aid from being provided to an employee requesting assistance.  Therefore, a properly leveraged and executed internal audit department should never have its resources reduced during a recession.  When this occurs, it should raise concerns as to the internal-control monitoring at a company, but it should also become an opportunity for an internal audit department to evaluate the value that it adds and make the necessary change to not become a corporate function, but truly be a partner in the operations of the organization.

Comments: Be the first to comment

Monte Carlo in Post-Downturn World

Posted 09/16/2009 by Matthew C. Vanderbilt
Categories: Responses to the News
Tags: ,

In May, Eleanor Laise of the Wall Street Journal (WSJ) published the article, Odds-On Imperfection: Monte Carlo Simulation – Financial Planning Tool Fails to Gauge Extreme Events, which was further reviewed in the September 2009 issue of Financial Advisor Magazine (FA) in the article, What Are the Odds? Certainly the point of Ms. Laise’ article is correct, that Monte Carlo simulations failed to predict the economic downturn being experienced today.  However, the report calls these simulations into question and highlights the rash conclusions that some analysts are too eagerly jumping to: that Monte Carlo is a risky predictive methodology.  This certainly could not be farther from the truth.

Although some are extremely complex, with multiple built-in contingencies and data relationships, a Monte Carlo simulation is, quite simply, a fancy random number generator.  Based on the analyst’s assumption, a probability distribution function (PDF) is developed along which results are generated.  For example, if data is modeled along a normal bell distribution, the simulation will randomly select values within that distribution, with greater emphasis on values trending toward the center.  The goal is to run these random samples enough times to obtain confidence in the predictions, but not so many that the original distribution is simply recreated.  The key risk to the Monte Carlo simulation is, therefore, the analyst’s original assumptions in developing the PDF or PDFs utilized within a given scenario.

When developing a PDF, analysts should utilize as much data as is available, with some exceptions.  Any systematic changes resulting in data fluctuations must first be removed.  For example, if an analysis of widget failure rates is being predicted and, five years ago, an inherent flaw in the factory was corrected, it would generally be inappropriate to use data prior to the correction without first standardizing that data.  So, the key to these analyses is, as always in data analysis, knowing your data.  Perhaps this is where the WSJ article should have focused its discussions – not on potentially debunking the usefulness of Monte Carlo simulations, but on stressing the importance of selecting the most accurate and representative underlying dataset to utilize within that simulation.

So, did Monte Carlo simulations fail to predict the current economic downturn?  Most assuredly.  Should this method of predicting the future be stopped to prevent similar, future errors?  Certainly not.  According to the WSJ article, “[s]ome firms are considering revising Monte Carlo models to reflect a world where big market swings happen more often.”  Increasing the risk factors within Monte Carlo simulations will certainly increase the confidence level of those simulations, but will simply result in useless data – if you always predict that something will fail, you’ll never be wrong when it does fail.  However, this will not assist individuals in planning efforts and will certainly not help prevent losses in future recessions, since the analyses will become so fundamentally useless as to be ignored.  This isn’t to say that models should not be reviewed and revised.  The ever shrinking global financial markets are becoming more interdependent every day, which changes the game – basic assumptions do need to be reset to take these variables into account.  Without further data, however, models should not be revised to predict increases in market downturns.  There was the Great Depression in the 30s, market inclines and declines over the years, a downturn in the 80s, and a dot-com bubble burst in the 90s.  It happens.  Certainly, models could be improved to better predict when these potentially cyclical events may occur, but planning for the worst will just diminish the returns of today.

Monte Carlo simulations are extremely useful tools, as long as the analyst designing them and the end user making decisions both understand all the assumptions and potential pitfalls.  The best simulation is only a reasonable estimate of the future over many time periods – it should not be the only thing considered, but it also shouldn’t be ignored, and the methodology shouldn’t become a tool of the past.

Comments: Be the first to comment