|Home > Infor/Geac/Comshare FDC > Excel Import Utility > Import Workbooks|
This utility was developed to overcome the lack of a simple method of importing spreadsheet data such as budgets and forecasts into Comshare FDC. It allows Excel workbook data to be quickly and simply imported into a Comshare FDC database without using the general ledger import function or other inconvenient workarounds. It could also be used to import other data, which has a consistent layout and a file format that can be opened by Excel.
The utility can import data from spreadsheets into Standard (Type 1) schedules. It cannot import data into Category (Type 2) or Line Item Detail (Type 3) schedules, though this functionality could be added. The layout of the workbooks to be imported can be completely different from the layout of the FDC schedules. However, there are some restriction on the data which can be imported. These are:
This section is quite abstract and you may find it difficult to follow the first time you read it. If this happens read through it anyway as it defines important terms. Then go through the Tutorial which should make everything clear.
To import Excel workbook data into FDC the utility:
The import process is illustrated in the following diagram.
+----------+ +----------------------------+ |Data | |Mapping | |Workbook1 | |Workbook | |+--------+| |+--------+ | ||Sheet1 || ||Sheet1 | | || |--Copy-->| | | || || || | | |+--------+| |+--------+ +---------+| +---------+ | | | | |Schedules|| |FDC | | | | |--Formula-->|Sheet |--Save-->|Database | | | | | | || | | |+--------+| |+--------+ +---------+| +---------+ ||Sheet2 || ||Sheet2 | | || |--Copy-->| | | || || || | | |+--------+| |+--------+ | +----------+ +----------------------------+
Data workbooks are the workbooks you want to import to FDC. While the layout of data workbooks can be completely different from the layout of the related schedules data workbooks must conform to a few simple rules:
Layout - Data workbooks must be consistent, that is the same data must appear in the same cell in every workbook. If March revenue is in cell G8 in one workbook it must be in cell G8 in every workbook. This also applies to sheet names. If profit and loss data is on a sheet named PL in one workbook it must be on a sheet named PL in every workbook.
Units - Data workbooks can only contain data for one unit. If you have a workbook with one sheet per unit you could not import it using this utility. However, you could split it into several workbooks and import them.
Names - Data workbook names must be Long Unit IDs so the add-in knows which unit to save the data to.
Mapping workbooks translate the layout of the data workbooks to the schedule layouts. You would normally have one mapping workbook for each type of data e.g. budget, forecast etc. Mapping workbooks must contain the following sheets:
Data sheets - These are the sheets that will be copied from the data workbooks. The names of these sheets are important as the utility copies sheets with the same names as the data sheets in the mapping workbook from the data workbooks.
Schedules sheet - This sheet contains the schedule layouts and data to be saved to FDC. The data must be in exactly the same format as the schedule it is being saved to e.g. if there is a blank header line in the schedule there must be a blank line in the data. Data is transferred to this sheet from the data sheets by normal Excel formulas. When data is saved from this sheet any decimal places are saved. This is generally not desirable and you will need to round all the data on this sheet. This can be done using the Excel ROUND() function.
ImportRanges sheet - This sheet contains a range named Import. This range defines where the data is on the Schedules sheet and what period it should be saved to. The columns in the Import range are:
As mentioned above workbooks can only contain data for one unit. However, organisations frequently want to use consolidated workbooks which contain many units. This utility let you split workbooks with several sheets, one per unit, into workbooks containing one unit. Only sheets with names which are long unit ids are split.
Importing data takes about a 0.5 seconds per schedule per period. Therefore importing 10 schedules for 20 units for 12 months would take about 20 minutes. However, this is only a guide and the actual time taken will be dependant on your schedules, hardware and network.
Before you start this tutorial you should read the Theory section. You should also have created the tutorial database see FDC Utilities for details of how to do this.
ABC Group has prepared its budgets in Excel. The budgets are in fifteen
1015.xls. It now wants to import these budgets
into FDC and consolidate them. First we will review the layout
of the workbooks.
Open one of the workbooks in Excel. They should be in the temporary
folder where you saved
Utilities.zip. As you can see the
workbooks have two data sheets PL (profit and loss) and BS (balance
sheet). The layout of all the workbooks is the same but obviously the
budgets are different.
The first stage is to create a basic mapping workbook using the Create Mapping Workbook utility. The steps to do this are:
Utilities.xlaand click Open.
1001.xls: click Browse and go to the folder you saved the
1001.xls, click Open and then click Next.
Mapping.xlsas the name of the mapping workbook and click Next.
A mapping workbook will be created based on the selections you have made.
Now we need to map the data on the PL and BS sheets to the Schedules sheet. The steps to do this are:
Once you have rounded the p&l and bs you may find they are different
from the original or do not balance. You will need to calculate the
rounding errors and adjust your p&l and bs accordingly. The completed
MappingReporting.xls workbook included in
Utilities.zip shows how this can be done.
The Import range defines where the first cell of each schedule is on the Schedules sheet and what period and year it should be saved to. If you look at it now you will see that it only saves data to one month not the whole of the budget period. To save data for the whole of the budget period amend the range so it looks like the following:
Then extend the Import range so it covers all the rows you have just create:
After all the tricky configuration work now comes the easy bit. To import the data workbooks:
Mapping.xls: click Browse select
Mapping.xls, click Open and then click Next.
1015.xls: Click Browse and go to the folder you saved the
Utilities.zipto and select
1001.xls. Click Open. Select all the data workbooks and click Next.
Congratulations you should now have imported these workbooks. Why not check its worked with schedules display.
|Copyright © 1999-2008 Consolidation Consultancy Limited||Last updated: 09 Sep 2010|