Import Workbooks

Contents

• Introduction
• Theory
• Tutorial

Introduction

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:

• the layout of the workbooks to be imported must be consistent, that is the same data must appear in the same cell in every workbook;
• each workbook can only contains data for one unit; and
• the names of the workbook filenames must be Long Unit IDs.

Theory

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.

Process description

To import Excel workbook data into FDC the utility:

1. Opens the mapping workbook you selected (mapping workbooks are discussed below).
2. Opens the data workbook you selected (data workbooks are discussed below).
3. Copies the data sheets from the data workbook to the mapping workbook.
4. Calculates the mapping workbook so the Excel formulas in it transfers data from the data sheets to the Schedules sheet.
5. Gets the unit to save the data to from the name of the data workbook.
6. Saves the data on the Schedules sheet in the mapping workbook to the FDC database.
7. Closes the first data workbook.
8. Repeats steps 3 to 7 until all the workbooks you selected have been imported.

Process diagram

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

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

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:

1. Schedule - the schedule data is to be saved to.
2. Period - the period data is to be saved to.
3. Year - the year data is to be saved to.
4. First Line - the A1 style address of the first line of the schedule on the Schedules sheet.

Split Workbooks

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.

Performance

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.

Tutorial

Preparation

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.

Scenario

ABC Group has prepared its budgets in Excel. The budgets are in fifteen Excel workbooks 1001.xls to 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.

Creating a mapping workbook

The first stage is to create a basic mapping workbook using the Create Mapping Workbook utility. The steps to do this are:

1. Start FDC Control Site: From the Start menu select Programs > Comshare > FDC > FDC Control Site.
2. Login to the ABC Database. The user id is COMSHARE and the password is FDC.
3. Open the Utilities add-in: From the File menu select Open, select Utilities.xla and click Open.
4. Start the Create Mapping Workbook utility: From the Prepare menu select Create Mapping Workbook.
5. Select 1001.xls: click Browse and go to the folder you saved the Utilities.zip.Select 1001.xls, click Open and then click Next.
6. Select sheets PL and BS and click Next.
7. Select schedules B1299 and B2299 and click Next.
8. Select period 1 and year 2001 and click Next.
9. Enter Mapping.xls as the name of the mapping workbook and click Next.

A mapping workbook will be created based on the selections you have made.

Creating mappings

Now we need to map the data on the PL and BS sheets to the Schedules sheet. The steps to do this are:

1. Open the mapping workbook you just created.
2. Select the Schedules sheet.
3. Select the value for Turnover product 1 on schedule B1299 (cell C3).
4. Link the value for Turnover product 1 on the PL sheet (cell C6): press =, click on the PL sheet, click on cell C6 and press Enter.
5. Round the result to zero decimal places. Edit the formula so it reads =ROUND(PL!C6,0).
6. Repeat until all the lines of B1299 and B2299 have been mapped.
7. Copy the column C to D.
8. Change the profit and loss formula to add column C so the values are year to data.
9. Copy column D to the remaining months
10. Save the workbook.

Roundings

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.

Updating the Import range

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:

1. From the Insert menu select Name > Define.
2. Select the Import name.
3. Amend the Refers to text box to be =ImportRanges!$A$1:$D$24. One way to do this is to select the contents of the Refers to box and then highlighting the range with the mouse.
4. Click OK.
5. Save the workbook.

Importing data

After all the tricky configuration work now comes the easy bit. To import the data workbooks:

1. Start the Import workbooks utility: from the Prepare menu select Import Workbooks.
2. Select Mapping.xls: click Browse select Mapping.xls, click Open and then click Next.
3. Select 1001.xls to 1015.xls: Click Browse and go to the folder you saved the Utilities.zip to 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.