Читать книгу Xero For Dummies - Heather Smith - Страница 79

After you’ve exported your data, and if necessary converted it to a CSV file (refer to the preceding section for some general information on these processes), you’re ready to prepare your data for importing.

Here are some useful techniques for preparing data in Excel for importing into Xero:

 Convert dollar values to numbers — dollars values aren’t recognised.

 Enter dates in day/month/year format. Users of the US version of Xero enter dates in month/day/year format.

 Import amounts as a single column — when data is split between deposits and withdrawals, for example, the columns need to be merged and the withdrawals recognised as a negative number, by prefixing them with a negative symbol.

 Remove consumer tax from values (if required). Most information extracted doesn’t include consumer tax — so this hopefully won’t be an issue for you — but may be required if you’re converting from MYOB.

Here’s how you can convert amounts to a single column, using the example of combining deposit and withdrawal columns:

1 Insert an additional column in the Excel spreadsheet, beside the Withdrawal column, and label it Amount.The new column is shown in Figure 3-5.FIGURE 3-5: Combining data into a single cell.

2 Combine the Deposit and Withdrawal column by entering a formula in the Amount column to subtract Withdrawals from Deposits.For example, using the spreadsheet shown in Figure 3-5, you could enter the formula ‘=+C2-D2’ in cell E2, combining data into a single cell. The withdrawal in this example is now represented by a negative number.

Here’s how to calculate the tax exclusive value from the tax inclusive value:

1 Calculate the consumer tax Exclusive Amount by inserting a column beside the Inclusive Amount column and using the formula Inclusive Amount/(Consumer Tax+100)%.Figure 3-6 shows an example of this calculation, including the formula required based on the Excel cells involved. In this example:Inclusive Amount: $500Consumer tax: 10%Exclusive Amount: 500/1.1 = 454.54FIGURE 3-6: Calculating consumer tax Exclusive Amount.

2 Copy the formula to the end of the data list by clicking the small black box at the bottom right of the cell and dragging it down alongside the existing data. Don’t apply the Exclusive Amount calculation where the Selling Price is already exclusive of consumer tax, the consumer tax is 0, or you’re importing bank statement lines.