|Home > Infor/Geac/Comshare FDC > Excel Import Utility > Create Input Workbooks|
Normally it is best to use standard FDC tools (transmission files, web workstation etc.) for data collection. However, it is often appropriate to use Excel to collect budget or forecast data. You might want to consider using Excel for data collection if:
If you decide to use Excel this utility can help you by automating the creation of input workbooks for reporting units.
This utility can create two types of input workbooks, reporting and consolidated. Normally you should use either reporting or consolidated workbooks but not both. Using both types of workbooks creates more work and exceptions can be collected using either type of workbook.
Reporting workbooks contain data for one reporting unit. They are normally used when most reporting units have their own finance function e.g. when reporting units are legal entities or standalone businesses.
The utility creates input workbooks from template workbooks by changing
the unit id in the template workbook, converting any Comshare Formula to
values and saving the template as a input workbook. Reporting template
workbooks can have as many sheets as you like. The only restriction is
that the first sheet must be called Front and the unit id for the
workbook must be on this sheet. The Front sheet is also a useful place
for other information such as month, year and instructions for completing
the workbook. A simple reporting template
TemplateReporting.xls is included in
You must unlock all data entry cells in your template. This is necessary because the utility protects input workbooks so that they cannot be changed and data can only be entered in unlocked cells. It is also helpful to colour code data entry cells. To unlock a cell:
If you want to include data from FDC in your input workbooks just insert FDC formulas into your template workbook. When the utility creates the input workbooks it will update the unit id for the workbook, calculate the workbook and convert FDC formulas to values.
This utility will intelligently create input workbooks for any branch or unit in an organisation structure. For this structure:
3000 ABC Group |-3010 ABC UK Group | |-3020 UK IT | | |-1007 UK ERP | | `-1008 UK Networks | |-3030 UK Consulting | | |-1009 UK Property Consulting | | `-1010 UK BPR Consulting | |-3040 UK Technology | | |-1011 UK eProcurement | | `-1012 UK Point of Sale | `-3050 UK Overhead | |-1001 ABC UK Ltd | |-1013 UK Marketing | |-1014 UK HR | `-1015 UK Directors |-1002 ABC Germany GmBH |-1003 ABC USA Ltd |-1004 ABC France SA |-1005 ABC Italy SA `-1006 Consol adjustments
A table of branches and workbooks created is shown below:
The process for creating input workbooks is illustrated in the following diagram.
+----------+ +----------+ +----------+ |Input |+ |FDC | |Template | |Workbooks ||+ |Database |-Formula->|Workbook |-Save As->| ||| | | | | +----------+|| +----------+ +----------+ +----------+| +----------+
To create input workbooks from a reporting template the utility:
Consolidated workbooks are similar to reporting workbooks so you should read the Reporting workbooks section first as only the differences between the two types are covered in this section. Consolidated workbook contains data for several reporting units. They would normally be used where most of the finance functions in your organisation are responsible for several reporting units e.g. when reporting units are cost/profit centres.
Consolidated template workbooks are more complicated than reporting templates and must contain the following sheets.
Front - This is similar to the Front sheet in reporting templates and would normally contain input cells for month and year. It should not have a input cell for unit id as consolidated workbooks can contain many units and the unit ids are on the unit input sheets.
Total - This is the total of all the reporting units in the workbook. In the template workbook the formulas on it should add up the BeginSum and EndSum sheets e.g. =SUM(BeginSum:EndSum!D7). The page set-up (footers etc.) on this sheet is copied to the unit sheets in the input workbook. There are normally no data input cells on this sheet.
BeginSum - This is were reporting units input their data. In consolidated workbooks all the data for a unit must be on one sheet. This means you will probably have several schedules on each unit sheet. This is different from reporting workbooks where data for a unit can be spread across many sheets. Sheets for additional units are inserted between this sheet and the next sheet.
EndSum - This sheet should be identical to the BeginSum sheet.
Lookup - This sheet is for any lookups e.g. month descriptions in the input workbooks.
A simple consolidated template
The utility will intelligently create input workbooks for any branch or unit in an organisation structure. For the above structure a table of branches selected and workbooks created is shown below:
|Units in Workbook|
||1013 1014 1015|
To create input workbooks from consolidated templates the utility:
The workbook used as a template for input workbooks.
Determines whether reporting or consolidated input workbooks are created.
The structure branch to create input workbooks for.
Character(s) to add before the unit id in input workbook filenames.
Normally this will be the schedule version for the data in the workbook.
For example for budget workbooks the prefix might be B and input
workbooks would be called
B3UUUU.xls where UUUU is the unit
Cell address for unit id in input workbooks. For reporting workbooks this cell is on the Front sheet. For consolidated workbooks this cell is on the Total and individual unit sheets.
Cell used to freeze panes.
Password used to protect input workbooks and prevent users changing them.
Creating input workbooks takes about 0.5 seconds per schedule per period. Therefore creating a workbook with two schedules for 12 periods would take about 12 seconds. However, this is only a guide and the actuals time taken is 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 is preparing its budgets using Excel and FDC. The reporting units have submitted the first draft of their budgets in Excel workbooks and these have been imported into FDC. After discussions with the reporting units the full year budgets have been changed. Group accounts now wants to send the revised budget to the reporting units for them to rephase their budgets where necessary.
To create input workbook to send to reporting unit:
Utilities.xlaand click Open.
TemplateReporting.xls: Click Browse and go to the folder you saved the Utilities archive to select
TemplateReporting.xls, click Open.
|Copyright © 1999-2008 Consolidation Consultancy Limited||Last updated: 09 Sep 2010|