How Should I Do My UAT? (1): Program / Spreadsheet / Model

If you are working in the actuarial department of an insurance company, it may not be uncommon to you involving in various testing exercises, which are known as User Acceptance Testing ("UAT") in general. Some of us may think that UAT is only applicable to the policy administration system implementation project (Oops... Do I remind on the non-stop follow-ups from your Project Manager?), but in fact UAT is also needed for the programs, spreadsheet templates or actuarial model we use to perform various actuarial studies - whether they are developed internally or by external consultant. It is particularly important for those tools which we are going to use to perform regular studies.

In my view, there is no one right answer for the approach to be used in performing UAT on program / spreadsheet / model - most importantly, the approach that you adopt should allow the required testing to be carried out in a structured manner, and it should not create too much "processes" that doesn't help to improve quality of testing (e.g. filling in too many forms or having too many sign-offs). In case you are struggling in finding a suitable approach for your UAT exercises, perhaps the following proposed method can provide some ideas to you.

STEP 1: Prepare Test Script
To ensure your UAT to carried out in a systematic manner, the first thing you need to do is preparing a test script. A test script consists of (1) test scenario; (2) expected results; (3) actual results (which you will fill in after carrying the required testing). Below is a sample format of a test script for a FoxPro program:

A test script serves as a structured guide to the testers, so that they have a clear idea what areas to be tested and how they should carry out the required testing, in proper order. Without any test script, the testers will only test on what they manage to think of at they are doing the testing (it will be worse if they do the testing after office hour with tired minds), like a kid running helter skelter in the street. Apart from overlooking important areas to be tested, the errors / required modifications reported to the developer will not be in a proper order - which will increase time & efforts to modify the tested tools as well as redo the required testing.

STEP 2: Prepare Test Data
Although it is good to use the actual policy data to perform UAT, sometimes the available data are unsuitable for testing, especially at the initial stage where you would like to detect any errors in formulas. Alternatively, you can use a simulated set of data with a specific pattern to do your testing, such as illustrated in the following diagram (a spreadsheet template for loss ratio study):

As shown in the above diagram, you can easily detect the formula error for Plan D, which by right the claim incurred should be 1,300 and the calculated loss ratio should be 10.00%. If you use the actual data directly, you may not be able to detect such formula error and may lead to incorrect conclusion for Plan D.

Of course, before you sign-off the program / spreadsheet / model, it is recommended to do at least one testing using actual data for reasonableness checking.

STEP 3: Perform Testing
Before you start doing testing, you may want to sort the test scenarios in an order that will allow you to carry out testing in a more efficient way. In case you find out some test scenarios left out when preparing the test scripts, you may add the test scenarios into your test script. Same for additional testing you carry out - especially the findings are meaningful.

Similarly, in case you find out any test scenarios are no longer applicable, you may want to cancel the test scenarios. We should be flexible enough in handling various conditions arising during testing.

STEP 4: Observe Outcomes
After completing each testing, you need to update the "Actual Results" column based on the outcomes you observe and decide the testing is "passed" or "failed". If the UAT exercise is complex, you may consider using an issue log in managing errors you report to the developer (IMPORTANT: Developer and Tester should NOT be the same person) or modifications you request the developer to do.

Some Remarks...
The above proposed approach is not new to actuarial people, since it uses the concept of "actual vs. expected" - which we are always doing in "monitoring the results" stage of the actuarial control cycle. However, in term of how comprehensive a test script should be, it is important to note that we should avoid having million dollar solutions to ten dollar problems - we cannot test everything or check everything, if it is a simple program, some simple testing will do (which justifies the time & effort spent).

Also, we need to take note that a program / spreadsheet / model will not be perfect after doing ONE session of UAT. Please allow the tools to improve over time, but enhancements should not be done frequently (unless there are errors to be fixed) - otherwise, you will end up being busy all the time by doing testing.