This invention relates generally to multi-currency production ledgers, and more particularly to methods of converting multi-currency production ledgers to accommodate the unification of several national currencies by respective participating states into a single currency.
With the advent of a global economy and associated global competition, nations in any particular locale are joining together to form unions. These unions may exist to foster greater security to union members strategically and/or economically. An example is the creation of the Economic and Monetary Union (EMU) in Europe. The EMU is a single currency area within the European Union single market in which people, goods, services and capital move without restrictions, through, among other things, the adoption of a unified currency, namely the euro.
The rules, institutions and objectives of EMU are set down in the Maastricht Treaty. The institution responsible for the promulgation and enforcement of the rules of the EMU is the European Commission (EC). The EC Council adopts all the legislation needed for the functioning of EMU, on the basis of proposals from the EC, which is made up of various officials from the participating states, notably the legal status of the euro and the irrevocable conversion rates between the euro and participating currencies, i.e. national currencies of participating states or nations.
The xe2x80x9ceuroxe2x80x9d was introduced as the single currency of the EMU on Jan. 1, 1999 and has to date been adopted by 11 member or participating states. These are Belgium, Germany, Spain, France, Ireland, Italy, Luxemburg, the Netherlands, Austria, Portugal and Finland. The conversion rate between the respective participating nations"" currencies and the euro has been permanently fixed and so will be stable by definition. For these participating states, the euro replaces the respective participating national currencies, in July 2002 at the latest, when banknotes and coins in national currencies are replaced by banknotes and coins in euro, and the national or participating currency units of the participating member states will definitely cease to exist in all their aspects. However, during the transitional period from Jan. 1, 1999 through Dec. 31, 2001, individuals and businesses may use either the participating national currencies or the euro.
Europe""s private sector companies doing business with other participating and non-participating national companies can continue to use the participating and non-participating currencies in their bookkeeping up to Jan. 1, 2002. It will be necessary for the companies, however, to convert their ledgers at some time during the transition period. To evaluate records using participating currencies after Jan. 1, 2002, the participating ledger will have to convert entirely to the euro for all their bookkeeping. They are free to switch over to accounting and bookkeeping in euro at any convenient time during the three-year transitional period providing that this is consistent with national changeover plans.
Non-European based companies that receive payments in participating national currencies must also convert their accounting ledgers to accommodate the new single currency.
In most circumstances, the introduction of the euro will not mean replacing software and computer equipment, unless a company""s normal replacement cycle falls within the transition period. However, it does involve a sometimes painstaking examination and amendment of computer programs to ensure that all currency references take account of the euro.
In accounting nomenclature, the term xe2x80x9cgeneral ledgerxe2x80x9d or xe2x80x9cbooking ledgerxe2x80x9d is a term of art, referring to individual amounts, and the journal entry transactions behind those amounts, that comprise the assets and liabilities in a particular balance sheet, and the revenue and expenses in a corresponding income statement. In a typical manual booking ledger, there exist many types of journal entries. Such a typical manual general ledger is usually formed by creating a grid system with vertical and horizontal lines meshing together to create boxes that receive account information, names, numbers, dates, numerical values, etc. Customarily, on the left hand side of the ledger is placed account names or numbers while account values known as xe2x80x9ctransaction currency recordsxe2x80x9d, are input horizontally across the ledger from left to right. Values can be added, converted, subtracted or totaled dependent upon the type of ledger. Separate accounts can be listed vertically from the top down.
In a ledger that contains multi-currency values, a particular transaction currency record is accompanied by its equivalent value in the xe2x80x9cbooking currencyxe2x80x9d. The booking currency is the currency in which a particular business primarily deals. For example, if a company in France receives payments in various currencies, the booking currency would likely be in francs. A transaction with a U.K. company in British pounds would then be reflected in the ledger of the French company in GBP as the transaction currency record and the equivalent amount in French francs, the booking currency. This equivalent in francs is known as the xe2x80x9cBook One Equivalentxe2x80x9d. Sometimes a business may have a xe2x80x9cBook Two Equivalentxe2x80x9d if the business carries its books in more than one currency.
In addition, each account has a xe2x80x9cBook One Amountxe2x80x9d that holds the total of all activity for one account regardless of what currencies comprised that activity. Sometimes a business may have a xe2x80x9cBook Two Amountxe2x80x9d if the business carries its books in more than one currency.
At some point during the European Monetary Union transition period, the booking currency of all participating European ledgers must be converted from their respective participating currencies to euros. Since most of today""s businesses track their respective accounting using computer software designed for the task, the software must be updated to convert the value of accounts within a particular booking ledger from participating currencies to the euro. Prior to conversion, it is necessary for the booking ledger to contain values of many different entries in various participating foreign currencies. After the euro conversion, all currencies of the EMU member nations, i.e. participating currencies, need to be converted to Euros in the booking ledger, while non-member nation currencies must also remain in the ledger for consistent bookkeeping.
From a computer software standpoint the term xe2x80x9cgeneral ledgerxe2x80x9d refers to an entire set of files and record types that were designed to give the user sophisticated and wide-ranging control and reporting tools over a particular accounting ledger. The same computer programs now perform all mathematical operations on the data, calculate earnings, spending, profits, losses, and tax liability and perform a myriad of other functions necessary to allow easy access to information about the particular business.
Within the database of a particular business there exists a xe2x80x9cmaster filexe2x80x9d, which typically contains a plurality of records including amounts from a company""s general ledger. One master file record contains (1) a key identifying the record (comprised of a corporate identifier, user-defined key elements, and a currency code); (2) data fields, such as an account type, description, date opened, etc.; and, (3) amount fields, each assigned numeric field generally having 13 integers and up to 2 decimal places. Each record contains a large number of amount fields, one each for every accounting and adjustment period and period 00 for each amount class. Such a record may be represented as follows:
[CORP, USER-DEFINED KEY ELEMENTS, CURRENCY CODE, data fields, amounts fields------------------ - greater than ]
| less than - - - - - - - - master file key fields - - - - - - - - - greater than |
The term xe2x80x98corpxe2x80x99 is used as a corporate identifier to identify a particular company name. For example, for a farm equipment manufacturing company, Ajax, inc., the corp name in the United States might be AjaxUS. The corp name for the French division of the business might be AjaxFrance. A master file typically contains the records of all the general ledgers of all of the corps of a particular business. In other words, each of these xe2x80x98corpsxe2x80x99 have separate accounting ledgers, however, they can be included in one master file.
The xe2x80x98currency codexe2x80x99 is an identifier of the currency in which the record is stored. If Ajax, Inc. has a transaction record with the currency code as U.S. dollars, then the amount of the transaction record value in the amounts field is expressed in U.S. dollars.
The xe2x80x98user defined key elementsxe2x80x99 are based largely on the needs of a particular business and the types of reporting they require. Each user determines the number and the nature of the fields that form the structure of its unique identifiers for each master file record in their ledger. For example, Ajax, Inc.,firm might use ACCOUNT, CENTER, PRODUCT, and LOCATION. For another example, a bank might use ACCOUNT, BRANCH and CENTER as the user defined key elements.
Prior to the rise of packaged general ledger software in the 1970""s, manual general ledgers included a xe2x80x98Chart of Accountsxe2x80x99, which was simply the list of all valid accounts that could be used in the ledger.
For Ajax, Inc., the Chart-of-Accounts might look like this;
Where the Chart of Accounts was a list of all the valid accounts for a ledger, the xe2x80x98Chart of Valuesxe2x80x99 subdivides the Chart of Accounts into valid values for each of its component parts. Our example of a key using ACCOUNT, CENTER, and PRODUCT might contain the following valid values:
A database that holds the Chart-of-Values is still used in ledgers because of efficiency in setup. In the above example there are 64 potential master file keys (8 accounts times 4 centers times 2 products), but only 14 records are needed for set up, as discussed below, to build them as they are needed.
With the above terms and definitions discussed in mind, the conversion of multi-currency ledgers to accommodate the unification of several national currencies by respective participating states into a single currency, e.g. the euro, requires consideration of several accounting software user""s needs.
In particular, in the case of the EMU and the conversion of the participating currencies to the euro, for European based ledgers, it is necessary to provide a migration path to a euro ledger in which all ENU transaction currency records and their book one equivalents are eliminated. After Jan. 1, 1999, the old currency of the participating nations became euros. In the euro ledger, these records are unnecessary. Maintaining activity in the new euro ledger after Jan. 1, 1999, in anything but euros, is in fact, redenominating a balance, in what has been converted in euros, back into pre-EMU currency.
For non-European ledgers using EMU currencies, it is also necessary to provide a migration path to convert EMU currency activity to euros. The conversion rates for EMU currencies into any other currency are not available after the end of the transition period. As a result, ledgers that book in an xe2x80x9coutxe2x80x9d currency (for example, a USD enterprise with activity in EMU currency) will be unable to produce the required monthly currency revaluation gains/losses after the euro transition period is complete. This is an insurmountable obstacle for every non-European computer software ledger user that processes transactions in EMU currencies, regardless of whether they are converting any ledgers to euros.
Additionally, exact conversion of a participating member nation""s currency to euros can never be accomplished. An unavoidable error will occur during the conversion due to the fact that individual currencies do not divide into curos in whole numbers. Therefore, some error will occur during the conversion. At some point a rounding function must occur.
The Business and Accounting Software Developers"" Association (BASDA), sets guidelines for the development of accounting software. In BASDA""s White Paper, October 1998, xe2x80x9cThe Change Over to the Euro: Implications for UK Businessxe2x80x9d, there are standards published which must be complied with in order to be within the EC Council rules for rounding. Any advanced rounding adjustments after converting all open balances to be posted to separate accounts are mandatory. Rounding is governed by E.C. Council Regulation No. 1103/97 (Jun. 17, 1997). Article 5 of that regulation states:
Monetary amounts to be paid or accounted for when a rounding takes place after a conversion into the euro unit pursuant to Article 4 [article 4 defines triangulation rules] shall be rounded up or down to the nearest cent. Monetary amounts to be paid or accounted for which are converted into a national currency unit shall be rounded up or down to the nearest sub-unit or in the absence of sub-unit to the nearest unit, or according to national law or practice to a multiple or fraction of the sub-unit of the national currency unit. If the application of the conversion rate gives a result which is exactly half-way, the sum shall be rounded up.
For example:
if after conversion the amount is EUR 1.455, this would be rounded to EUR 1.46;
if the amount is EUR 1.457, the rounded sun is EUR 1.46;
if the amount is EUR 1.454, the rounded sum is EUR 1.45;
for conversions from the euro to a national currency, the same rounding rule applies to the lowest sub-unit of that currency, e.g. pfennig for the Deutschmark or centime for the French franc.
While the regulation is simple and straightforward, issues still arise. The E. C. White Paper xe2x80x9cPreparing Financial Information Systems for the euroxe2x80x9d, XV/7038/97, Brussels, Dec. 15, 1997, gives examples of situations where unavoidable rounding differences crop up. Rounding problems can occur when converting individual items and cumulative amounts based on the same items to euro. Take the following example:
Assume the following conversion rate: EUR 1=DEM 1.93805
Applying the conversion and rounding rules to individual items and adding up the individual outcomes does not necessarily lead to exactly the same outcome as applying the rules to the cumulative amounts. This in itself is not new, but may still lead to confusion in certain cases, for example:
Enterprise B has bought goods from enterprise A in four lots. Enterprise A has recorded the sales individually at EUR 206,393.04. However, when enterprise B pays for the goods by means of one payment it will pay xe2x80x9conlyxe2x80x9d EUR 206,393.02.
In the case of accounting software, problems could arise when the debits and the credits in one journal entry, once converted into euro, no longer add up to zero due to rounding inconsistencies. The accounting software will only allow such a journal entry to be recorded when the rounding difference is allocated to a special account in the balance sheet or profit and loss account.
This example illustrates one type of rounding error that may be encountered during ledger conversion, where the euro xe2x80x98balancexe2x80x99 for one computer software general ledger account is actually made up of a series of converted period net activity amounts.
As a result, during any conversion routine it is necessary to provide a method to correct accumulations of intra-record rounding errors (or 2nd dimension rounding). A conversion routine without intra record rounding can cause incorrect balances in the ledger after conversion. Intra record rounding entries update those balances, and must therefore meet the same stringent audit trail requirements as any other activity in the ledger. Due to the volume of rounding entries required for a ledger, it is desirable that the intra record rounding process be automated.
For example:
The simplest of users, converting three years of only one amount class for one corp, must modify and run this report 36 times to see the tens of thousand of rounding errors likely to exist in the converted ledger.
A more typical users, converting two amount classes for a dozen corps that each have two adjustment periods, must run a report over a thousand times to see rounding errors in the euro corps.
In addition, the master file records that comprise reporting subtotals can contain accumulations of rounding errors down thorough many accounting keys. Any conversion routine must also provide a method to correct intra-ledger accumulations of rounding errors or risk out of balance ledgers. In fact any conversion routine must contain such a method to be within the BASDA requirements. BASDA Requirement 1.1.3.4 for the certification of euro conversion software states:
Where applicable, a method will be available to post the rounding difference to the trial balance after conversion to make it balance.
This requirement is mandatory to meet the lowest level of product certification from BASDA. In the absence of such a method a user must determine the cause of any imbalance in condition and develop an auditable method for calculating and entering corrections.
Another need to be considered is the desire of users converting their ongoing production ledgers to a euro master file without having to develop a new program.
Another user consideration in connection with the implementation of a currency conversion routine for an old corp production ledger processing in a participating currency to an ongoing production ledger processing in euro concerns those users using a single currency ledger denominated in an EMU participating national currency. Any conversion routine for this type of user requires a migration path from the single currency ledger operating in an EMU participating national currency to a single currency ledger operating in euro.
Yet another user consideration concerns those users with multi-currency ledgers where all activity is in EMU currencies. Any conversion routine for this type of user requires a migration path from the multi-currency ledger operating with all activity in EMU participating national currencies to a single currency ledger operating in euro.
Still another user consideration is to accomplish the conversion of the production ledger in the shortest amount of time.
To function, a user must retain the ability to book transactions in participating currencies during the entire transition period since any other company which the user transacts business may delay converting to euros until the very last day of the transition period.
It is therefore an object of the present invention to provide a new and improved booking currency conversion method for automatically converting an ongoing old corp production ledger processing in a participating currency to an ongoing production ledger processing in a target currency where the old corp production ledger contains an old master file including a plurality of old master file records, each old master file record comprising a key identifying the record, data fields and amount fields.
Another object of present invention is to provide a new and improved booking currency conversion method in which conversions of the old corp production ledger are accomplished automatically in ongoing booking ledgers while providing an efficient resulting converted booking ledger.
Another object of the present invention is to provide a new and improved computerized method for converting an ongoing old corp production ledger processing in a participating currency to an ongoing production ledger processing in the new euro currency and using a computer software program to automatically accomplish the conversion.
Another object of the invention to provide a method for converting an ongoing European based production ledger, which includes a migration path to a euro ledger in which all EMU transaction currency records and their book one equivalents are eliminated.
Still another object of the invention is to provide a method for converting an ongoing non-European ledger using EMU currencies, which includes a migration path to convert EMU currency activity to euros.
Yet another object of the invention is to provide a method for converting an ongoing production ledger wherein exact conversion of a participating member nation""s currency to euros can be accomplished.
Another object of the invention is to provide a method for converting an ongoing production ledger wherein users that wish to convert their ongoing production ledgers can create a euro master file without developing their own program and without restrictions caused by charts of values that may not be synchronized with the master file at the time of the conversion.
Still another object of the invention is to provide a method for converting an ongoing production ledger using conversion software designed to convert an old corp production ledger processing in a participating currency to an ongoing production ledger processing in euro currency and wherein the user uses a single currency ledger denominated in an EMU participating national currency.
Yet another object of the invention is to provide a method for converting an ongoing production ledger using a computer software that includes a migration path from the multi-currency ledger operating with all activity in EMU participating national currencies to a single currency ledger operating in euro.
Still another object of the invention is to provide a method for converting an ongoing production ledger which accomplishes the conversion of the production ledger in the shortest amount of time.
Yet another object of the invention is to provide conversion method that creates a more efficient resulting booking ledger containing non-participating currency entries individually, and all participating currency entries represented in euro in one entry.
It is also an object of this invention to provide a conversion method that creates an efficient euro currency booking ledger without prior participating currencies remaining in the final ledger.
It also an object of this invention to provide a conversion method that creates a euro booking ledger having the ability to create reports detailing prior, current and future amounts of currency within the ledger.
Another object of this invention is to provide completely automated intra records rounding tools.
Another object of the invention is to provide tools to identify and create intra ledger rounding entries as quickly as possible.
These and other objects are attained by providing a method of converting an ongoing old corp production ledger processing in a participating currency to an ongoing production ledger processing in a target currency where the old corp production ledger contains an old master file including a plurality of old master file records, each old master file record comprising a key identifying the record, data fields and amount fields.
In accordance with this invention the ongoing old corp production ledger is converted by the steps of providing an ongoing old corp production ledger processing in a participating currency having an old master file including a plurality of old master file records, each old master file record comprising a key identifying the record, data fields and amount fields, selecting a target currency, selecting a particular time period within which conversion is accomplished, providing a target currency master file that is substantially identical to the old currency master file, such that said target currency master file contains as least one target currency corp record corresponding to each old currency master-file corp record within the old currency master-file, providing zero balances within each target currency corp records within the target master file, providing all participating transaction currency records and their book one equivalents within one old master file key that are replaced by a single target transaction record and its book one equivalent, converting all transaction currency records and their book one equivalents for the selected time period into their corresponding target currency equivalents, rounding said target currency equivalents, and populating the target currency master file corp records within the target master file with the corresponding rounded target currency equivalents.
Specifically, this invention automatically creates a converted booking ledger that contains entries representing all non-participating currencies and all participating currencies converted to the target currency. The resulting converted booking ledger created by this invention is devoid of duplicate entries representing pre-conversion values of the participating nations"" currencies for current and future year amounts. This feature frees up a large amount of physical space in a database, minimizes the amount of information required to formulate efficient accounting reports and thereby saves the user time and money.
The unnecessary EMU transaction currency records and their book equivalents can comprise up to 95% of the converted euro master-file. With large businesses that have thousands of records to book this can cause extremely large converted euro master files taking up valuable space on computer networks and creating lag time in gathering information from the database.
Failing to eliminate these unnecessary EMU transaction currency records and their book one equivalents will cause users to experience unchanged runtimes, when they were expecting dramatic reductions in processing times and cost overhead.
Another aspect of the present invention is the automatic correction of intra record accumulation of rounding errors and the automatic correction of intra ledger accumulation of rounding errors.
Yet another aspect of the present invention is a new and improved software Procedure Definition Language program known as EUROCOPY(trademark) that allows a user to create a euro master file that is devoid of duplicate entries representing pre-conversion values of the participating nations"" currencies for current and future year amounts without developing their own program and without restrictions caused by charts of values that may not be synchronized with the master file at the time of the conversion.
An existing ongoing old corp production ledger processing in a participating nation""s currency is provided. In order for any conversion routine to be executed the files must be locked so that the data cannot be modified while the conversion is executing. As a practical matter, the files containing prior year records can be converted during day-to-day production ledger processing as long as the prior year amount classes have been locked against further journal activity. However, all current and future year files cannot be locked without interfering with the day-to-day production ledger processing. As a result any conversion process executed on the current and future year ledgers must be done during a period in which no activity is conducted, such as over a weekend. That time period is known as xe2x80x98production cutoverxe2x80x99. During production cutover all old corps"" records are locked against future activity. This requires that the entire conversion process must be completed over a short period of time, namely from about 5 PM on a Friday afternoon to about 9 AM Monday morning, in order to avoid system down time.
However, the prior year, since the ledger is mostly complete for this time period, can be locked and converted at any time before production cutover. This time period is known as xe2x80x98pre-cutover preparationxe2x80x99. During the pre-cutover preparation all prior year old corps"" records are locked against further activity and the conversion process can begin. Meanwhile, the current and future year ledgers remain in day-to-day processing until the production cutover period.
During pre-cutover preparation, the ongoing old corp production ledger processing in a participating nation""s currency is copied in its entirety to a non-production or test region, while all prior year amount classes are locked against further journal activity in the production region. All current and future year amount classes in the ongoing old corp production ledger processing in a participating nation""s currency located in the production region remain active for day-to-day processing. In the test region a Production Definition Language (PDL) program known as EUROCOPY uses the master file records of the old corps to create corresponding master file records for the euro corps, and all records associated with all old corps are moved to their corresponding new euro corps. The conversion from the old corp with a particular master file key would like the following:
The book one currency is then changed from the pre conversion participating national currency to euros in the euro corps. Additionally, here the automatic generation and combination checking features are deactivated within the ledger software, allowing the EUROCOPY program to create the euro master file corresponding exactly to the old corp""s master file without regard to the Chart of Values discrepancies.
Within one master file key, the PDL program then replicates every book one, non-participating currency and non-participating currency book one equivalent record into the euro master file. The first participating currency (including euro) record encountered by the PDL program causes one euro transaction currency record and its book one equivalent to be created. The PDL ignores all other participating currency records within that master file key. However at this point in the euro master file, all amount classes contain zero amounts that will be populated by the converted amounts in a later step.
The old corp amounts of the prior year in the test region are extracted and then converted to the corresponding euro values followed by a migration of the amounts to the corresponding location in the euro master file. The location of the converted values in the euro master file depends upon their characteristics.
All non-participating currency transaction currency records and statistical accounts are copied to the maintenance transaction as is, without amount conversion.
All non-participating equivalent records and book one records in the old corp are expressed in the old booking currency and therefore need to be converted. The source amount is divided by the euro conversion rate, rounded to two (2) places, and written to the maintenance transaction.
All participating currency transaction currency equivalent records are converted by dividing the amount by the euro conversion rate, rounding to two (2) places, and adding the result to a running total referenced as EUX for a euro transaction currency book one equivalent amount. After all records have been processed for the master file key, that running total is written to the maintenance transaction for EUX.
All participating currency transaction currency records are ignored. By definition the number of transaction currency euros must equal the number of transaction currency equivalent euros because the booking currency of the corp is now euros. By using the reference EUX? in the output maintenance transaction from the previous step, both the EUX and the EUX1 records in the euro corp""s master file will be updated with the same euro amount.
The amounts posted to the euro ledger may contain incorrect values at this point due to the previously discussed accumulations of rounding errors. Known as intra-record rounding or second dimensional rounding, the year-to-date balance rounding errors are evaluated by comparing the year-to-date balances in the euro corp against year-to-date balances from the old corp records. If one or more rounding errors are found within one master file key, the report creates a master file maintenance transaction with the correcting entries.
As the above steps are executed in the test region during pre-cutover preparation for the previously locked prior year amount classes, day-to-day processing continues in the ongoing production ledger in the participating currency for the current year in the production region. When a converted euro corp with prior year amounts is complete, production cutover can occur.
At the production cutover, the ongoing production ledger locked to prior year activity is copied along side the converted euro corps with prior year amounts in the test region. All old corps in the production region are locked against further activity. Conversion of the current and future year amounts for the euro corps is executed and the euro master file is updated to include the values. Intra-record and intra-ledger rounding adjustments for current and future year amounts are created and entered to the euro corps.
The converted and rounded euro corps for all years and amount classes are migrated back to the production region. The fully converted and functional euro corps and the old corps locked against further activity remain coexisting in the ongoing production ledger which is now processing in euros. Although the old corps are locked against further activity they remain to allow for reporting and information gathering purposes.