Let's say you are implementing SAP submodule Asset Accounting (FI-AA) and you need to convert the asset master data from a legacy system. If you have large volumes of data, you would use a BAPI, which is SAP's preferred solution, a Computer Aided Test Tool (CATT), or a Legacy System Migration Workbench (LSMW). Since most people tend to use these methods as a matter of course, a fourth method is virtually unknown.

If you have a small set of assets, the tool Transfer Legacy Data to Asset Accounting using Microsoft Excel can be preferable, because it is fairly easy to use and reliable. Once your data is in the right format and you have run a test and eliminated any errors, the actual upload is quick and painless. It has a physical limitation of 5,000 lines in the Excel file, and, since each asset typically uses five lines, it is therefore most suitable for datasets up to 1,000 lines. It is quick and slick, and it tells you where it encounters problems, so you can correct your input and run it again. You have to put some effort into the file format but you do not have to write a CATT script or do any other development.

The following steps will help you load and map smaller amounts of legacy data quickly and easily:

Step 1. Assemble and Reformat Legacy Asset Data

As with so many other things, good preparation is the key to success with this transfer tool. Download asset data from your legacy system into Excel or by manual entry of the assets into an Excel spreadsheet. Many legacy systems download directly into an Excel format or into a flat text file that Excel can read. If your legacy system does not, you may be stuck with manual entry. The data must then be changed into a form that the SAP tool can read.

This is most the time-consuming part of the process, as it requires a layout of five or more rows per asset record. I have illustrated this in a spreadsheet (Figure 1). The highlighted area at the top of the spreadsheet is the Header, and it must be in the first line to properly map the file into SAP. Below the header, I have included two asset records to illustrate how the data has to be entered.

Figure 1

Use the Excel format Standard for the entire worksheet.

If you have cells for which you have no information, enter a forward slash (/). This tells R/3 not to expect an entry. Formatting of dates requires special attention. During entry into R/3, there is a setting for date options, which I explain later. In Excel, use one of the following date formats so that R/3 can interpret the dates correctly: American (MM/DD/YY or MM/DD/YYYY), ISO (YYYY-MM-DD), European (DD.MM.YY or DD.MM.YYYY), or SAP (YYYYMMDD).

The fields are organized in Record Types:

• Record Type 0 is reserved for the legacy asset number. If you have a problem with the upload, R/3 uses this number to tell you where the problem is. Even if you don't have a legacy asset number, it is recommended to include a value, since any errors made during upload are identified using this number.
• Record Type 1 is for header data and general data, such as asset class, company code, or description. This follows the Asset Master Record tabs in R/3. In Figures 2 and 3, I illustrate this using transaction AS01 (Create asset). Your asset class should have eight characters. If it does not, add leading zeroes.
• Record Type 2 is for the time-dependent data, allocations, origin, and net-worth tax data.
• Record Type 3 is for depreciation area data, including the asset values. Always put the depreciation area identifier for your book depreciation first (01 in my illustration and in most SAP systems since this is what SAP recommends you use). Use another line for any other depreciation areas you have, such as 30 for group depreciation. Note that you may also enter the values here, using a minus sign behind the depreciation numbers. You do not need trailing zeroes, so if there are no decimal places, no decimal separator is required either. SAP will interpret the numbers correctly.
• Record Type 4 is for current year transactions such as additions or retirements.

Figure 2

Enter Asset class and Company code

Figure 3

Enter a description

Tip!
If you have leading zeroes (as, for example, I have with Depreciation Area 01), format the cell first as Text. When all your data is in, select the entire worksheet and set the format to Standard and the alignment to left. This allows you to enter leading zeroes. If you try entering them into a cell formatted as Standard from the start, they disappear, and 01 becomes 1.

The Identification feature is an important field in Record Type 1. Populate it with the Asset Takeover Date, formatted as described above. It does not correspond to a field in the Asset Master Record but has to be mapped to a field in SAP used to identify the data set.

The asset master record in SAP has many fields to fill in. Some are mandatory, such as asset and a capitalization date and at least one depreciation area, and some are optional, according to how you've configured FI-AA. For example, most companies assign each asset to a cost center, and tax and insurance information is often included, for US GAAP reporting or other purposes. All assets must be assigned to asset classes, such as Plant & Machinery or Motor Vehicles. In the definitions of these asset classes are account determinations as well, which steer the accounting entries for FI-AA in FI, creating the links between the submodule and the G/L.

If you have data for which a field is not correctly defined, it does not appear in R/3 and you are not able to map it. Conversely, you may decide that there is a field you do not want to map, so you may simply leave it out of the mapping. Mapping a field and subsequently deleting or moving it should be avoided, as it may cause errors. If this occurs, you may have to delete and recreate your field assignments.

Leave a blank row between asset records, so that SAP knows where one ends and another begins. Save your file with a name that will allow you to find it again easily.

Step 2. Transfer and Map

This is the fun part. In the IMG, follow menu path Asset Accounting>Asset Data Transfer>Legacy Data Transfer in Asset Accounting using Microsoft Excel. You can also jump directly to this by using transaction AS100.

Select your input file (in the example, ASSET DATA.xls) and click on Start. Before you start mapping, it is important to select Settings from the menu and set your date format. Skipping this step could cause unnecessary errors, which will take time to correct.

Map the fields from the header of your Excel worksheet (on the left side) to the fields in the Asset Master Record in R/3 (right side), as shown in Figure 4. Note that R/3 only shows those fields from your file that you have defined in your header with the correct record types.

Figure 4

Map the data

Select a field in each window and click on Assign. The fields drop down into the bottom window. Proceed carefully, as changing the assignments once you have saved may cause errors. Before you save, you can use the Undo assignment icon at the bottom of the screen to break a link and redo it.

Note in the example that I have assigned Legacy Asset Number to the field Asset type name. The order in which you do the mapping does not matter, but I find it easiest to work methodically down the left window and through the tabs on the right side (Figure 5).

Figure 5

Mapping using the drop-down list of tabs

Step 3. Save the Field Assignment

When you have finished mapping, click on the save icon or press Ctrl+S. Give your field assignment a name. You can reuse field assignments afterward, allowing uploads of legacy assets to be done in small batches or the use of one field assignment for several company codes.

Step 4. View the Field Assignment and Test

When you arrive at the screen shown in Figure 6, you might be left wondering what to do next, as this screen is a bit lacking in information. Clicking on the Field assignments button allows you to see the field assignment that you have just created. Select it and use the green arrow to arrive at the screen shown in Figure 7. Now you can and should do a test run by clicking the Test run button, as shown in Figure 8.

Figure 6

Click on the Field assignments button to view

Figure 7

Select field assignment

Figure 8

Test run resulting in errors

As you can see in Figure 8, I launched a test run and got errors. Life would be boring if everything worked right the first time, wouldn't it? Seriously, I deliberately provoked these errors to show that SAP tells you that you have them and what they are. In this example, the Company Code does not exist. If you have multiple errors, you can export them to an Excel file (see Figure 9) where you can do further analysis, by grouping them together, sorting, and filtering.

Figure 9

Exporting errors

When you have eliminated all the errors and have a successful test run, click on the Assets button (Figure 10). Your assets will be created complete with their values and will be ready to use.

Figure 10

A successful test run

As I said at the beginning, this method is quick, slick, and very effective. When the final step is complete, your asset master records are set up in SAP exactly as you want them, in the correct asset classes together with the acquisition and production costs and accumulated depreciation for the relevant depreciation areas. You are ready to start using the assets for your day-to-day bookkeeping. You can simulate and post depreciation for subsequent periods, post additional acquisition costs, retire or transfer assets, and carry out any standard bookkeeping tasks involving assets.

Roy Brookes

Roy Brookes is a senior consultant in SAP FI/CO and a qualified accountant and business consultant. He has worked for many years in accounting, finance, and business consulting. He is an SAP expert and has designed and implemented FI and CO solutions at the European and country level during the European rollouts of SAP R/3 in a number of multinational companies. He has also worked in the US and Turkey.

You may contact the author at Roy.Brookes@Hamburg.de.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.