Excel export / import guidelines for apportionment information (1120)

Alerts and notices

Consider the following guidelines when modifying the Microsoft Excel workbook data you want to import into UltraTax CS.

  • Always start by exporting apportionment information into Excel. UltraTax CS assumes the initial Excel workbook template was generated via the process of exporting the apportionment information spreadsheet from UltraTax CS.
  • Always save the Excel workbook before importing the data into UltraTax CS. This ensures all recent changes will be imported into UltraTax CS.
  • For the purpose of importing data into UltraTax CS from Excel, UltraTax CS assumes you have not modified the order of the Excel worksheets in the workbook. The worksheets are imported into UltraTax/1120 in the order in which they were initially exported to Excel, which is as follows:
    1. Apport Info Property
    2. Apport Info Payroll
    3. Apport Info Sales
  • You cannot insert or delete rows. Do not add or delete any column heading rows and do not add or delete any factor components. You can only add rows below the columns' total rows (that is, the Property Amount, Payroll Amount, and Sales Amount rows). Any other rows you add will not be imported into UltraTax CS.
  • You can export apportionment information from one UltraTax/1120 client to an Excel workbook and subsequently import the information back into a different UltraTax/1120 client. You can also export apportionment information from one UltraTax/1065 client to an Excel workbook and subsequently import the information back into a different UltraTax/1065 client. However, you cannot export and import apportionment information between UltraTax/1120 and UltraTax/1065 clients.
  • Information from the apportionment tabs is not exported or imported.
  • Year-end property amounts for depreciable property are not imported when using detailed asset data-entry to classify assets for apportionment. For more information, see Using asset detail for apportionment.
  • Statement dialog detail is not exported or imported. When importing apportionment data from Excel, total amounts are imported into the UltraTax/1120 Apportionment Information window. You can then open the statement dialogs and update the statement detail or you can import the statement detail from Excel or the Windows clipboard.

    Note: If the color of the Statement dialog Statement dialog button button is purple, then the amount imported from Excel does not equal the amount within the statement dialog. Open the statement dialog and update the detail.

  • Consolidated UltraTax/1120 states will not be exported from UltraTax CS or imported from Excel. Changes to the apportionment information must be made in the member returns.
  • Column shading, column totals, and the cross-footing of state columns into the Everywhere column mimics the shading, footing, and cross-footing behavior in the Apportionment Information window for states, cities, and other returns exported to Excel. For more information, see Apportionment spreadsheet. If you add a column to Excel, the column will have no shading and no column totals, and the column will not cross foot into the Everywhere column.
  • Data from the Everywhere column in Excel is not imported into UltraTax CS. Components that are overridden in the Everywhere column in the UltraTax/1120 Apportionment spreadsheet will still be overridden after you import data from Excel. To delete the overrides, choose Edit > Overrides > Delete Override.
  • When you export information from UltraTax CS to Excel, all data entered in the UltraTax/1120 Apportionment spreadsheet is exported to Excel. If the Excel workbook already exists, the workbook will be overwritten with the data from the UltraTax/1120 Apportionment spreadsheet.
  • When you import apportionment information from the Excel workbook back into UltraTax CS, all data already entered in the UltraTax/1120 Apportionment spreadsheet is overwritten with the information from the Excel workbook.

    Note: UltraTax/1120 does not overwrite year-end property amounts for depreciable property when detailed asset data-entry is used to classify assets for apportionment.

  • The first time you export apportionment information into an Excel workbook, the states attached to the client's return are listed in alphabetical order in the Excel workbook. If there are city or other returns attached to the client's return, these returns are listed after that state in the workbook.
  • You can insert and delete state, city, and other return columns in the Excel workbook. (See the next bullet item for information on the naming conventions for column headings.) Although you can add these columns in Excel, we recommend that you add the state, city, and other returns to the client's return in UltraTax CS before you export the apportionment information to Excel.
  • If you add columns for state, city, or other returns in Excel, the column headings must be completed in a specific manner in order to enable you to import the data back into UltraTax CS. If the column headings are not added correctly, the information cannot be imported back into UltraTax CS. Follow these guides for adding state columns.
    • To add a state column, enter the state's name on row three of the column heading. Do not use the state's postal code.
    • To add a city or other return column, enter the state's postal code in row two of the column heading. Then, enter the name of the city or other return exactly as the information appears in the Apportionment spreadsheet, in row three of the column heading.
  • If the column headings in Excel do not exactly match the column headings in the Apportionment spreadsheet, then the data from these columns will not be imported into UltraTax CS. Instead, UltraTax CS will display the Apportionment Import Error Messages dialog, which identifies the columns that were not imported.
  • If a state's column heading in Excel is spelled correctly but the state return is not attached to the client's return in UltraTax CS, the amounts from the state column in Excel will be accumulated into the Other column in the UltraTax/1120 Apportionment spreadsheet.
  • If a city or other return's column heading in Excel is spelled correctly but the city or other return is not attached to the client's return in UltraTax CS, the amounts will not be imported. Instead, UltraTax CS will display the Apportionment Import Error Messages dialog, which identifies the column(s) that were not imported.

Note: If you are not going to import the Excel file back into UltraTax CS, you can make modifications to the file in accordance with Microsoft's Excel guidelines.

Related topic: Excel Export / Import utility for apportionment information overview (1120)