Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Tutorial: Import data from TAS Books into Excel - Part 2. by David Carter

by
22nd Nov 2007
Save content
Have you found this content useful? Use the button above to save it to your profile.

In the first of this two part series David Carter showed how to get nominal transactions out of TAS Books and into Excel via Excel’s Data – Import External Data command. In this second tutorial he shows how to use an Excel pivot table to analyse the data and (if you want it) to provide job costing analysis

In part one we exported nominal transaction data from TAS into Excel. We used Excel’s Import External data command for this rather than File-Open. This has two key benefits.

Benefit 1: reports are automatically updated via Refresh
The benefit of using Import External Data is that we can create a permanent daybook in Excel. When new invoices are entered in TAS, all we need to do is to re-run the TAS report. Then press the Refresh button in Excel, and the nominal daybook in Excel will be automatically updated.

Benefit 2: TAS data can be deployed to anyone around the organisation
In addition, this daybook can be placed on anyone’s desktop, as long as their machine has “line of sight” to the server where you saved the TAS export file. And since it's an Excel report, they don’t need to have TAS on their own machine or to know anything about it.

So, if you have managers anywhere in the organisation who need access to TAS data, this is the way to provide it. Not via a printed report, but by exporting data out of TAS, then importing it into Excel via Import External Data and letting them view it in Excel on their own desktop.

Sales Reporting in TAS as well
Note that you can also export sales transaction data out of TAS, (Sales - Customer Reports – Export Invoice Details). So you can use this technique to provide managers with sales analysis information as well.

1. Create the pivot table
[I’m assuming you have done pivot tables before. If you need a refresher, practise on the first couple of Five Minute Pivot Table tips .]

In the first tutorial, you created a file in Excel called TASimport.xls.

Start up Excel. Open TASimport.xls.

Click onto the data. From the main menu at the top of the screen, select :

Data - Pivot Table and Chart Report
[These instructions are based on Excel 2003. Excel 2007: Insert – Pivot table.]

Step 1: Next Step 2. Next

Step 3: Click on Layout. The COLUMN-ROW-DATA box appears. To the right are all the column headings of your spreadsheet.

From the list, drag and drop into the boxes as follows:

COLUMN…. FinPerName
ROW………. NLGroup Description, NL Description,
PAGE……… .Department
DATA…….….Amount

[When dropped into the DATA area, Amount should say “Sum of Amount”. If it says “Count of Amount”, double click on it and change Count to Sum]

OK. Finish. The Pivot Table is created in a new worksheet.

You see the Nominal account headings down the left, with month names across the top – a standard P&L layout.

2. Sort the rows so that Income comes first
The sort order of the pivot table on my data is alphabetical – i.e. Cost of Sales, Expenses, Income

You can use drag and drop to move Income from the bottom to the top.

First, you must collapse the pivot table, so that there are only three lines Cost of Sales, Expenses, Income.

Then use drag and drop to move Income from the bottom row to the top (it’s bit fiddly – you have to get the mouse pointer into a white arrow before it will work).

Once you've sorted into Income-Cost of Sales-Expense order, expand the pivot table again to show the Nominal Descriptions.

3. Profit and Loss by Job
At present, Department is set up as a Page field in B1. Probably it shows only 100.

If you implement the Job No field as I’ve explained in the appendix below, you can put Job No where Department is now and apply the down arrow filter in B1 to see create P&L reports on any job.

4 Add a new transaction in TAS
The real benefit of using Import External Data comes when new transactions are added to TAS. All you have to do is to press the Refresh button in Excel, and they are added automatically to the TASimport workbook and to the pivot table. Therefore:

Add a new transaction (i.e. invoice, payment etc) to TAS Books.

In TAS, go to Nominal - Reporting – Export Nominal Transactions.

The Remember feature at bottom right is green, meaning that the options are the same as last time, so simply run the report and save it once more as Ntran01.csv (i.e. overwriting the existing version).

Close down TAS. Go into Excel.

5. Refresh the data in Excel
Open TASimport.xls.

Right click anywhere on the data. At the bottom of the menu, click on Refresh Data.

The Refresh Data box appears. OK.

The Import Text file appears, with NTran01.csv as the default file name.

Click on Import. The Excel file is refreshed.

Click ZA on J10 in the Posting No column. The new transactions appear at the top.

Now go into the pivot table. Right click for menu: Refresh Data.

The pivot table is also updated with the new transactions.

END OF SESSION, or try out Job Costing in the Appendix below

APPENDIX – If you want to add Job Costing to TAS.
Job costing in TAS is limited. You can apply a Tag Code, but only to an entire invoice. There is also a facility to analyse to “Projects”, but this seems to be something entirely separate from nominal analysis.

Effective job costing is a very simple thing – wherever you analyse an amount into a nominal ledger code box, there should be another box next to it where you can type in a Job Code as well.

You can use Excel to create this additional Job Code box in TAS.

1. Change TAS Configuration
As TAS is set up, you can only enter one description for the whole invoice.

For serious costing you need to be able to enter a separate description on each analysis line. To do this, you have to change the TAS configuration.

From main menu: Central – General Company Information - TAS Books Configuration.

Choose Purchase Ledger.

In Purchase Journal Entry, the default reads:

Use Nominal Account Description for each Journal line (Default):

Change this to:

Use Own Description for each journal line.

[You will have to change this in each transaction type – purchase ledger, sales ledger, cashbook etc]

2. Enter the job number to the right of the description
Whenever you enter a purchase invoice into TAS, this new setting will allow you to enter a separate description on each line.

Suppose you use job codes which are four letters long (e.g 1234 etc).

When you enter the description on each line, add the job code at the end. So, if the description is “Courier charges for June “, change it to “Courier Charges for June - 1234”.

3. Create a new JobNo field in Excel
In TASimport.xls, go to the first blank column on the right, (column W on my version).

In W1, add a new column heading: JobNo

In column W2, type the formula =RIGHT(F2,4). (i.e. use the right hand 4 digits in the Description field, column F.)

Copy this down to all rows.

Now right click, select Data Properties. At the bottom, tick: Fill Down formulas with new transactions.

Whenever you refresh the data, you will find that this formula is applied to all new transactions and the job number will be generated in column W.

4. Using the new JobNo field for reporting
You can use the Job No field to analyse costs in two ways:

In the list of transactions, use Data – Autofilter.

In the pivot table, set up JobNo as a Page field. You can then produce individual P&L’s for individual jobs by selecting from the drop down box in B1.

Tags:

Replies (1)

Please login or register to join the discussion.

avatar
By pedropnr
28th Nov 2007 19:55

Problems with consolidating departments
Very usefull article on importing tas values to excel.However,there does not seem to be a facility to import all the values for a range of nominal accounts if you are using departments as well as nominal accounts.For example,we split P & L nominals into 5 sub categories using the department codes-we use 100/101/etc.We have to do this to cater for Cis & non Cis suppliers as the Tas cis system is not designed to cater for nominal codes in the P & L being capable of taking both cis & non cis supplies.
Any ideas on how to import data to incorporate consolidates departments?

Thanks (0)