Create Input Workbooks

Contents

• Introduction
• Theory
• Tutorial

Introduction

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:

• Many of your reporting units are engaged in similar businesses and could use the same Excel business model.
• Data entry using the standard tools is difficult due to the complexity of your application or the volume of data.

If you decide to use Excel this utility can help you by automating the creation of input workbooks for reporting units.

Theory

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

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.

Templates

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 Utilities.zip.

Data entry cells

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:

1. Select the cell(s) you want to unlock.
2. From the Format menu select Cells.
3. Select the Protection tab.
4. Unselect the Locked check box.

FDC data

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.

Structures

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:

Process diagram

The process for creating input workbooks is illustrated in the following diagram.

                                            +----------+
+----------+          +----------+          |Input     |+
|FDC       |          |Template  |          |Workbooks ||+
|Database  |-Formula->|Workbook  |-Save As->|          |||
|          |          |          |          +----------+||
+----------+          +----------+           +----------+|
                                              +----------+

Process description

To create input workbooks from a reporting template the utility:

1. Checks the reporting template workbook is valid.
2. Identifies the workbooks to create for the branch selected.
3. Saves the template workbook as a input workbook.
4. Updates the unit id on the Front sheet of the input workbook.
5. Calculates the input workbook to update any Comshare FDC Excel formulas.
6. Converts FDC Excel formulas to values.
7. Protects all the sheets and the workbook with the password.
8. Saves and closes the input workbook.
9. Repeats steps 3 to 7 until all input workbooks have been created.

Consolidated workbooks

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.

Templates

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 TemplateConsolidated.xls is included in Utilities.zip.

Structures

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:

Process description

To create input workbooks from consolidated templates the utility:

1. Checks the consolidated template workbook is valid.
2. Identifies the workbooks to create for the branch selected.
3. Saves the template workbook as a input workbook.
4. Puts consolidation unit id on the Total sheet.
5. Inserts new sheets for reporting units.
6. Copies the EndSum sheet to the new sheets.
7. Deletes the BeginSum and EndSum sheets.
8. Calculates the input workbook to update FDC Excel formulas.
9. Converts FDC Excel formulas to values.
10. Copies the page set-up from the Total sheet to new sheets.
11. Freezes panes.
12. Protects all the sheets and the workbook with the password.
13. Saves and closes the input workbook.
14. Repeats steps 3 to 13 until all input workbooks have been created.

Dialog box options

Template

The workbook used as a template for input workbooks.

Input Type

Determines whether reporting or consolidated input workbooks are created.

Branch

The structure branch to create input workbooks for.

Workbook Prefix

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 id.

Unit Id Cell

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.

Freeze Panes Cell

Cell used to freeze panes.

Password / Confirm

Password used to protect input workbooks and prevent users changing them.

Performance

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.

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 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.

Creating input workbooks

To create input workbook to send to reporting unit:

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 input workbooks utility: From the Prepare menu select Create Input Workbooks.
5. Select TemplateReporting.xls: Click Browse and go to the folder you saved the Utilities archive to select TemplateReporting.xls, click Open.
6. Select Reporting from the Input Type dropdown.
7. Select 3000 ABC Group from the Branch dropdown.
8. Enter B in the Workbook Prefix box.
9. Enter C1 in the Unit Id Cell box.
10. Enter C5 in the Freeze Panes box.
11. Enter a password in the Password boxes.
12. Click OK.