Design Guide

Contents

Introduction

This document does not attempt to cover every aspect of implementing a FDC system. Instead it aims to supplement the information provided in Comshare documentation with advice based on my experience. It therefore assumes you have a basic knowledge of FDC.

Version of FDC

The advice in this guide applies to FDC version 5.0. Generally the same advice will apply to different versions of FDC but the optimum configuration and design for other versions will inevitably be slightly different. I would be pleased to offer advice on implementing and maintaining any version of FDC or Comshare Budget.

Networks

FDC version 5.0 will run on any network that allows network drives to be accessed on client PCs. This includes Windows for Workgroups, Windows NT and Netware. It would even be possible to run FDC using the Direct Cable Connection program supplied with Windows 95 but this could be very slow.

I recommend using Windows NT. The main advantage of Windows NT is that FDC programs can be run on the FDC server. The benefits of this are:

Simplicity

Simplicity is the key to designing successful FDC systems. FDC is a very flexible package but it is not strongly structured which makes it very easy to create unnecessarily complex systems. As you read this guide I will sometimes advise you avoid using a FDC feature e.g. Categories and Key Number Generation. This is because these feature increase the complexity of your system and their use is not always necessary. Avoiding them results in systems that are much easier to use and maintain. Remember your FDC system may be very widely used in your organisation and the easier it is to use, the easier it will be to support.

A good rule of thumb is to stop and reassess your design if you are finding it difficult to understand or to get part of it to work. There is generally a simple way to meet most business requirements using Comshare FDC.

Databases

Generally FDC systems should only have one live database. Users developing two systems that share little data, e.g. management and statutory accounts, sometimes develop two completely separate systems. This should generally be avoided for the following reasons:

Structure Your System

Structuring your FDC system wherever possible should make it easier to understand and use. Schedules Numbers are a good example of the kinds of structures I use when I are implementing systems. However, when you are designing structures and naming conventions remember to leave room for future expansion.

Comshare Budget

Comshare Budget is based on FDC with some additional programs and functionality to address budgeting requirements. Therefore most of the advice in this guide applies to Comshare Budget. We would be please to provide advice on implementing Comshare Budget.

General Application Setup

Most of the settings in this folder can be left at the default set when FDC is installed. Settings worth discussing in detail are:

Transmit in Base Currency

I prefer to leave this as the default (unchecked) so data is transmitted in local currency. You may not need local currency values but it will be much easier if you have them in your head office system as you will have control over the exchange rates used and be able to change them if necessary.

Decimal Precision Exchange Rate

I prefer to change this to eight to provide greater accuracy. Initially exchange rates to eight decimal places may not appear necessary, as they are not provided to this accuracy. However, FDC holds exchange rates as reciprocals. The problems this can cause are best illustrated by a pre Euro Italian Lira (ITL) example. A ITL/Base currency rate of 2,125, would be held as 0.000470 in FDC. However, this would mean the base currency values calculated by FDC could be different from manual calculations: 200,000,000 / 2,125 = 94,118 but 200,000,000 x .000470 = 94,000. These types of differences are minimised if exchange rates are stored to eight decimal places. To avoid confusion I recommend calculating you own reciprocals to eight decimal places and using these for manual calculation.

Schedule Types

I like to use the following basic schedule types

B - Budget
F - Forecast
M - Management Actuals
S - Statutory Actuals

I avoid using A for actuals as most systems will have separate schedules for statutory and management actuals.

Organisation Structures

Generally, setting up organisation structures present few problems and I have only two tips.

Multiple Structures

Some businesses need to consolidate units in more than one way, for example by product and by region. This can be easily achieved in Comshare FDC by creating two consolidation structures. You just need to make sure you use different consolidation units in the different structures.

Adjustment Units

By adjustment unit I mean a unit used to input consolidation and other journals. I recommend that the minimum numbers of adjustment units be used, as additional units will increase consolidation times, see Consolidations for more information. I find one consolidation unit per structure meets most business needs.

I advise against having an adjustment unit for every subsidiary in a structure. This triples the number of units, as each company must have an actual, adjustment and total unit. It also triples the consolidation time. This structure is generally not necessary as adjustments can be made directly to subsidiaries using journals.

Currencies

Currency Codes

I use SWIFT currency codes as a standard. The SWIFT codes for common currencies are listed here.

Rate Codes

I recommend deleting any rates codes you do not use to make your system as simple as possible. I also suggest you delete the following Rate Codes that are created when FDC is installed.

K Brought Forward Prior Month

This rate code brings forward balances from the previous month. I recommend using only rate codes that pull forward year-end balances. This is because FDC stores year to date values and works best with brought forward balances from year-ends. If you need to see monthly or quarterly movements these can be calculated on reports.

M Historical Fixed Rate

Initially this appears to be a very useful rate code that could be used to translate the opening balances from your existing system. However, existing systems often have a different exchange rate for every line and as Rate Codes apply the same rate to every line, this rate code usually cannot be use for translating opening balances or history.

Schedules General

General

Schedules are probably the most important and complex part of any FDC implementation. Poor schedule design can lead to very poor applications. I have therefore devoted two sections to them. This section, Schedules General, gives general guidance on schedule design and Schedules Examples gives guidance on some common design issues.

If you have not developed a FDC system before, plan to completely rework the design of your schedules several times. This gives you the chance to refine their design as you gain a greater understanding of FDC. As with most other computer systems, it is difficult and time consuming to change FDC once data has been loaded and it has been rolled out to users. It is therefore worth making every effort to get your system right first time. See my Project Management Guide for tips on managing the development of your FDC system.

Existing Layouts

Closely basing the design of schedules on existing layouts can cause problems. If the existing system is paper based, most pages will have about 60 lines on them. Translating these directly into FDC may not be efficient as schedules can have up to 400 lines. Analysis of Totals explains how two schedules can be combined. Similarly, converting an existing computer system may lead to a few very long schedules when it would be better to split data into different schedules.

Schedule Trees

The objective of schedule trees is to show the links between schedules. A schedule tree for a very simple FDC system is shown below.

M2199 Balance Sheet
  |--M2010 Fixed Assets
  `--M1199 Profit and Loss Account

Key
-- Schedule Crosscheck
== Key Number
++ Formula

In this example M2199 is the top schedule and pulls information from M2010 and M1199, using schedule crosschecks. Some data flow rules I try to apply when designing schedules are described below using schedule trees.

Number of Levels

Generally, the more levels in a schedule tree the more difficult the resulting system is to understand. Whenever possible I try to reduce the number of levels. For example, the following tree has three layers.

M2199 Balance Sheet
  `--M1199 Profit and Loss Account
       `--M1010 Revenue Analysis

This could be reduced to two levels by adding the revenue analysis information on M1010 to the profit and loss schedule.

Avoid Circular Reference

Circular references can cause the same kinds of problems in FDC as they do in spreadsheets and should be avoided. Schedules can have cross-checks to themselves but the kind of data-flow shown below should be avoided.

M4199 Capital Employed Statistics
  `--M2199 Balance Sheet
       `--M4199 Capital Employed Statistics

Schedules Numbers

First Digit

I use the first digit of the schedule number to indicate the type of schedules. The digits I use are:

V1xxx - Profit and Loss
V2xxx - Balance Sheet
V3xxx - Cashflow
V4xxx - Statistics

This leaves plenty of room, 5xxx, 6xxx,7xxx etc. to extend the system later.

Identical Schedules Layouts

Schedules with the same layout but different versions should have the same number. For example, the p&l schedules in a management accounting system could be numbered as follows:

B1199 - Profit and Loss (Budget)
F1199 - Profit and Loss (Forecast)
M1199 - Profit and Loss (Actuals)

Consolidation Order

I number schedules in the order they should be consolidated as in the following schedule tree:

M3199 Cashflow
  `-- M3010 Cashflow Workings
M2199 Balance Sheet
  |--M2010 Fixed Asset Analysis
  `--M1199 Profit and Loss Account
       `--M1010 Revenue Analysis

This should be consolidated in the following order:

M1010 Revenue Analysis
M1199 Profit and Loss Account
M2010 Fixed Asset Analysis
M2199 Balance Sheet
M3010 Cashflow Workings
M3199 Cashflow

This is the same as the order of the schedule numbers.

Schedule Length

There are no hard and fast rules about schedule length. As a rough guide I would try to combine schedules with around 30 lines and consider splitting schedules with more than 200 lines. The main considerations determining schedule length are:

Duplicate Data

Avoid holding the same data twice as this will increase the possibility of inconsistent data and lengthen consolidation times.

Blank Lines

Schedules should not have blank lines as these take up the same space in FDC as data lines and will increase consolidation times.

Header Lines

Header lines are like blank lines as they take up the same space as data lines and will increase consolidation times. However, while a good application can have on blank lines header lines are important for structuring schedules and should not be completely omitted instead they should be used sparingly.

Total Lines

It will make your system much easier to use if you can quickly identify the total lines on schedules. The easiest way to do this is to put normal lines in Proper Case and total lines in UPPER CASE as FDC does not store any format information. For example:

1010 Sales Revenue
1020 Leasing Revenue
1030 Rental Revenue
1040 TOTAL REVENUE

Sign Conventions

My preference is for all lines to be debits. I think this makes systems easier to use and understand as positive numbers are always debits and negative numbers are always credits. The disadvantage of this approach is that it can be confusing for non-accountants as revenue will be negative when schedules are viewed directly. This is only a significant problem if non-finance staff have direct access to schedules e.g. through ExecuView. Otherwise the sign convention can easily be changed in reports.

Whatever approach you use, I suggest making the minimum number of changes of sign on schedules. For example, a balance sheet schedule should probably have only one change. All the lines in the asset section, even those which are normally credits should be debits, and vice versa. As a minimum, all data input lines which add up to a total should have the same sign.

Line Numbers

If you have an existing numbering system, use this in your new application but remember to leave room for expansion. If your existing lines are numbered 1, 2, 3 etc. number your FDC lines 10, 20, 30 etc. Although FDC does not require it your system will be easier to use if your lines are in order as you will instantly be able to tell if the line you are looking for is above or below the line you are looking at.

If you do not have an existing numbering system, I suggest starting at 1010 and leaving gaps of 10.

Key Number Generation

The main design guidance for Key Number Generations is to avoid using them if at all possible They increase the complexity of FDC systems and their use can usually be avoided. Key Number Generations are a very useful feature but they are not necessary for the vast majority of application. Key numbers are commonly used to calculate Total Schedules but these are generally not desirable, see Fixed Asset Schedules for a practical example.

Categories

As with Key Number Generation the main design guidance for Categories is to avoid using them wherever possible as they increase complexity. Categories are used to avoid having many standard schedules with the same layout. However, I find having many identical standard schedules is often the best solution. I would generally only consider using categories if a system required more than 20 identical schedules See Product Profitability for a practical example.

Schedules Examples

The following examples illustrate how some of the above guidance is applied in reality.

Analysis of Totals

FDC often replaces paper or spreadsheet systems that have layouts similar to that shown below.

Revenue
Cost of Sales
Gross Profit

Revenue Analysis:
Product 1
Product 2
Product 3
Total Revenue

This layout could also be used for the FDC schedule with a cross-check between Revenue and Total Revenue. However, I find that combining the two sections often makes schedules easier to use. The two sections can be combined as shown below.

1010 Product 1
1020 Product 2
1030 Product 3
1100 TOTAL REVENUE
1110 Cost of Sales
1120 GROSS PROFIT

Note how line numbers have been left between 1030 Product 3 and 1100 TOTAL REVENUE for future expansion.

Cash Flows

I usually calculate cashflows using schedule formulas. One problem with this is that lines calculated with schedule formulas cannot be totalled using schedule downfoots. The solutions to this problem is to set up a working schedule for values calculated using schedule formulas. Then transfer these to the final Cashflow schedule using schedule cross-checks. The totals on this schedule can then be calculated using down foots.

Fixed Asset Schedules

Fixed assets schedules are often implemented as follows:

S2199 Balance Sheet
  `--S2090 Total Fixed Assets
       |==S2010 Land and Buildings
       |==S2020 Plant and Machinery
       `==S2030 Fixtures and Fittings
Key
-- Schedule Crosscheck
== Key Number

In this design S2090 Total Fixed Assets is calculated from the individual fixed asset schedules using Key Numbering and the total fixed assets net book value is transferred to the tangible fixed assets line on the balance sheet using a schedule crosscheck. This has following disadvantages:

I prefer to implement fixed asset schedules as shown below:

S2199 Balance Sheet
  |--S2010 Land and Buildings
  |--S2020 Plant and Machinery
  `--S2030 Fixtures and Fittings

In this design lines for the individual fixed asset types are added to the balance sheet and the net book values from the individual schedules are transferred by schedule crosschecks. You can calculate Total Fixed Assets on spreadsheet reports and if you do not want to display values for Land and Buildings etc on your balance sheet you can miss these off spreadsheet reports.

If you require total schedules in your system, they should be memo schedules and separate from your main schedule tree as shown below.

S2199 Balance Sheet
  |--S2010 Land and Buildings
  |--S2020 Plant and Machinery
  `--S2030 Fixtures and Fittings

S2090 Total Fixed Assets
  |==S2010 Land and Buildings
  |==S2020 Plant and Machinery
  `==S2030 Fixtures and Fittings

Product Profitability

Analysis of product profitability often requires a schedule similar to the one below to be collected for many products.

1010 Revenue
1020 Cost of Sales
1030 GROSS PROFIT
1040 Distribution Expense
1050 CONTRIBUTION

This can be done in FDC using Categories but this is very complex. An alternative is to have a standard schedule for each product or to repeat the product profitability lines on one schedule. This can be done by repeating the lines on a schedule or by copying the schedule. Total revenue etc. can then be calculated as shown below.

1010 Product 1 Revenue
1020 Product 2 Revenue
1030 Product 3 Revenue
1090 TOTAL REVENUE
1110 Product 1 COS
1120 Product 2 COS
1130 Product 3 COS
1190 TOTAL COS
1200 GROSS PROFIT
1210 Product 1 Distribution
1220 Product 2 Distribution
1230 Product 3 Distribution
1290 TOTAL DISTRIDUTION
1300 TOTAL CONTRIBUTION

This does lead to data duplication but I feel it usually produces a better system. However, if you need to collect information for more than about 20 products categories may be the best solution.

Performance

I have seen FDC applications where budget consolidations take 18 hours and restating history takes days. However, these excessive times are generally due to poor design and if the advice in this guide were followed these times can be significantly reduced.

FDC's poor performance is mainly due to its use of traditional database technology that is not optimised for the intensive number crunching needed for consolidations. However, the use of traditional database technology does allow text and very large amounts of data to be stored which is not always possible with other technologies that have superior number crunching abilities. I have attempted to summarise the main factors affecting the performance of the key process in FDC below.

Consolidations

The factors that most influence the time taken for consolidations are:

Number of Units

An application with 100 units will take roughly twice as long to consolidate as an application with 50 units. My main tip to reduce the number of units is to eliminate unnecessary Adjustment Units.

Number of Lines

An application with 400 lines will take roughly twice as long to consolidate as an application with 200 lines. To reduce the number of lines in your application avoid Duplicate Data and Blank Lines.

Number of Schedules

The effect of the number of schedules on consolidation time is more difficult to quantify. If two applications both have 200 lines but one has four while the other has eight schedules the application with eight schedules will usually take longer to consolidate. Halving the number of schedules will not reduce the consolidation time by 50 percent. However, reducing the number of schedules generally does make FDC applications easier to understand and maintain.

Key Number Generations

The time taken will be proportional to the number of schedules and lines being added. So applying the advice for reducing consolidation times will also reduce Key Number Generation times. However, the best way to reduce Key Number Generation times is to avoid using them completely.

Formula Generation

The time taken will be proportional to the number of formula. So try to reduce the number of formulas. Many schedule formulas can be replace by calculations in Excel Reports which are easier to setup and maintain.

Reports

The spreadsheet reporting in FDC is one of its best features. This section makes specific reference to Microsoft Excel but the general principles apply to other spreadsheets.

Workbooks

I prefer to have one report per workbook rather than multiple sheets and reports in one workbook. This is because a single report will only take about 30 seconds to calculate but a workbook with several reports could take five minutes. The disadvantage is that all your reports cannot easily be printed at once. To overcome this problem I usually develop a simple print macro that prints all reports automatically.

Structure

The structure I use for reports is shown by the following example.

A       B                C                D       E       F
   1    Unit    5012
   2    Month   12
   3    Year    98
   4    Sched                                       M1199   B1199
   5                    Car Dealers Ltd (5012)
   6                    Profit and Loss Account
   7                                                Actual  Budget   B/(W)
   8
   9    1010            Vehicles Sales             363,673 354,971   8,702
   10   1020            Maintenance Sales          176,349 134,987  41,362
   11   1030            Forecourt Sales             87,654  86,164   1,490
   12
   13   1040            TOTAL SALES                627,676 576,122  51,554

You will notice that all the workings, unit, month, year, schedules etc., are clearly displayed on the spreadsheet. To avoid printing these I would set the print area to be C5..F13. In Excel this is set on the Sheet tab of the File | Page Setup dialog box. I prefer this approach to hiding columns and rows as it saves continually having to hide and display columns and rows when developing reports and when tracing the source of numbers.

Footers

I always put the filename, report version, page reference, time and date in the footer for my reports. The time and date are particularly useful as if you end up with multiple copies of a report on your desk you can easily identify the most recent version.

Customisation

Consultants will often recommend customising your FDC system. The aim of this is generally to increase the time taken to implement your system and their billable hours. It will probably also create a system you will not be able to maintain thereby creating still further revenue opportunities for the consultant. Customisation should be avoided wherever possible. You should use the minimum possible macros and/or Visual Basic in your FDC system.It is usually helpful to provide a few macros to run common processes such as consolidations and crosschecks, but try very hard to avoid anything more complicated. After all, you did not buy a software package in order to spend time and money customising it.

Maintenance

FDC software

Comshare is very good at ensuring that different versions of FDC are compatible and that different versions can be used at different sites. It is therefore tempting to install the latest version at head office. However, I have found this can cause problems as the different versions will probably not behave identically, and what works at one site may not work at others. I therefore recommend that all sites use the same version and that upgrades are planned and controlled.

Upgrades

When you upgrade you system make upgrades cumulative. This means your version 2 upgrade should also require all the changes made in version 1. This means that if an upgrade is not successful it will automatically be corrected in the next upgrade and the multiple upgrades will not need to be applied.

For simple systems upgrades can be performed manually, but for more complex systems it is generally best to automate the upgrade process. This can be done using a Visual Basic program. A major benefit of automating upgrades is that it helps ensure all the FDC installation in your group are consistent which greatly simplifies supporting remote sites.

Versions Numbers

Use version numbers wherever possible. Your system will probably need to be upgraded and version numbers make it easy to tell if upgrades have been successful. I usually put version numbers in the following places:

Local Schedules

Local schedules should be avoided if possible as they increase complexity and make rolling out head office upgrades more complex.