I have been tasked with creating some test cases related to financial reporting using Microsoft Excel.
I have a specific Microsoft Excel template which I have to fill in to create a good file. As in a file with no errors that we can run on our system to make sure our system reads data correctly.
For this specific template, there are a set of validation checks issued by a banking authority which we, the people entering the data, must go by.
The Template (Annex 1 (FINREP IFRS templates))
The Validation Rules https://eba.europa.eu/sites/default/documents/files/document_library//881117/EBA%20Validation%20Rules%20March%202020%20deactivation.xlsx
Those validation checks may be thousands and the Excel template has multiple worksheets.
It's worth noting that most tables are cross checked with values of other tables so all tables are connected in some way.
The Requirement
The requirement here is to develop an error-free Excel template (the one linked above) with random data that will conform to the aforementioned validation checks.
The Problem
Starting with the first table in the template, I have begun to add random values. If they are wrong (based on the validations) I alter the values so that table no longer has errors. When the first table is error-free, I go onto the second table and do the same, and so on. About 18-20 tables in, I may get errors (cross-check validations) that compare the values in table 18 for example to the values in table 2. Table 2 however is error-free so I change the value in table 18 so it agrees with table 2, but this change causes another error with table 3 or 4.
There seems to be a kind of circular dependency going on and this traps me because when I correct one thing, another thing goes wrong.
It's clear that I have approached the problem in the wrong way to begin with.
My idea was to cover the validation checks at table level, starting from the first table and going onto the next. I'm not sure what other approach to take to ensure that all tables conform to the rules. I have been told that random values are the wrong approach here but I don't have a choice. Ideally, I would have real values but as you can understand, real values come from banks and banks do not share financial information, so I'm stuck with creating an error-free file based on random values.
The Question
What method do you suggest that will help me design an error-free Excel template that conforms to all the validation rules without causing circular conflicts amongst the values in the template?
