Functional Specification

Typical functional specification document.
There will be no radical changes to the 'look and feel' of the application, rather much of the functionality will be delivered 'behind the scenes'. All System Enhancements and Low Priority Features (see below) will be activated unobtrusively by Toolbar or Menu Bar options (excepting point 16, Target Variance display, which can be activated by button).

The requirements detailed below are ordered by functional priority, observing timescale constraints.

Primary Requirements

These are deemed vital to the new system and will be completed within the delivery deadline:
31 October, 2000

1. Transfer all existing functionality from Microsoft Excel 5 into Excel 97

All existing functionality will be migrated to Microsoft Excel 97, including workbook backup/restore routines, data import/export routines. All macros will be converted to VBA procedures.

Secondary Requirements

These are deemed considerable enhancements to the existing system and will be implemented once the primary requirements have been met. It is hoped these will be completed within the delivery deadline.

2. Consolidate all historical information

All existing information from Microsoft Excel worksheets will be imported into the Microsoft Access database.

3. Build a relational database (in Microsoft Access 97)

This will hold details of Team Map and Team data which will disperse Sector data to STMs. It will:

* Store all historical Microsoft Excel data
* Import Team Map data from a mainframe CSV (text) file
* Import Team data from a mainframe CSV (text) file
* Export Sector, Team-based data into the appropriate DSM (E-mail) directory
* Copy (as required) the necessary Excel template into the appropriate STM (E-mail) directory
* Automate diskette-based distribution and retrieval of data as a current and future contingency against E-mail failure.
* Import STM returns, translate these, then export a CSV file for mainframe upload
* OfferSector, Regional and National reporting identical to STM Area level output.

4. Create an E-mail facility

The ability to E-mail data to and from STM laptop computers from the central Microsoft Access database (storage) directory will exist. This feature is  on third-party implementation and hardware procurement logistics.

5. Create multiple Excel templates

Eight templates will be created:

* Four Templates will lock off successive quarter Forecasts, once these have been agreed, and will not display Action Plan columns
* Four Templates will lock off successive quarter Forecasts, once these have been agreed, and will display Action Plan columns

These will be distributed, as necessary, by the Microsoft Access database.

6. Change columns to reflect 01 Forecast / Action Plan / Actuals

* Remove Removal Rate and Contribution of Sales indicators
* Insert Staff Growth indicators
* Accommodate (in the sequence following) 1996 Actuals, 1997 Actuals, 1998 Actuals, 1999 Actuals, 2000 Actuals, 2001 Forecast, 2001 Forecast v 2000 Actuals, 2001 Forecast v Actuals Percentage Growth, 2001 Action Plan, 2001 Actuals, 2001 Actuals v 2001 Forecast columns in all indicators.

Indicators will be displayed in the following sequence:
Sheet 1:	Additions, Removals, Team Growth
Sheet 2:	Team, Strength, Units
Sheet 3:	Average Clients, Client Spend, Average Unit
Sheet 4:	Sales, Total Clients

7. Add Percentage Increase per Campaign column for Sales, Units, Average Unit

8. Merge Chicago / Boston Sectors

This will be accomplished by a mainframe procedure. All Team data will reflect changes.

9. Add Redland 2 and Reduce Redland Sectors

This will be accomplished by a mainframe procedure. All Team data will reflect changes.

10. Add Western Region and Restructure 

This will be accomplished by a mainframe procedure. All Team data will reflect changes.

Points 8, 9 and 10, above, will be the responsibility of mainframe staff. Any changes to the existing mainframe import and export csv data formats after 14 October, 2000 will require reprogramming the Microsoft Access database and Excel templates and will impact on the delivery schedules set out herein.

11. Add separate Action Plan Column - visibility determined by template

Action Plans are not always required. Workbook templates will be released with Action Plan columns visible only as required.

12. Add Forecast locking facility

Once Sector forecasts have been agreed subsequent template releases will lock the relevant forecast period(s), inhibiting retrospective adjustment by STMs.

13. Add facility to view all Forecast columns side-by-side

This feature will permit viewing of 5 Forecast input columns together with 3 Forecast locked columns on a single sheet.

14. Add year-on-year comparisons sheets for Team, Unit, Sales by Team

These sheets will display 3 years history, the average increase, current year growth derived from Teamlevel Forecast; Units and Sales sheets will display additional percentage indicators with all columns except average increase.

15. Add auto-save facility

Auto-save will operate every 20 minutes. This facility will be Menu Bar activated as a toggle. The default setting will be enabled.

System Enhancements

These are deemed useful enhancements and will be implemented once the secondary requirements have been met. These will be completed after functional testing on the system and may fall outside the delivery deadline.

16. Add Target Variance display

This feature will permit viewing of current Sector totals against Sales Planning targets, illustrating the Target, Current Value, Balance and Balance Percentage for Team, Units, Sales.

It will be a pop-up window activated by button, Toolbar or Menu Bar.

17. Add Graphing facility

Five line graphs are required:

* Sales expressed as a percentage of next year Forecast against current year Actuals
* Units expressed as a percentage of next year Forecast against current year Actuals
* Team growth by Campaign
* Sales contribution as a percentage by Campaign
* Units contribution as a percentage by Campaign.

The Sector average will be displayed on all graphs.
These will be selected from a pop-up form activated by Menu Bar option. Each graph will display a maximum of 5 Areas plus the Sector Average.
 
18. Add Global Forecast Residual Dispersal facility

This feature will permit automatic apportionment of Sector target shortfall by adjusting Team, Sales or Units Forecasting across all Teams; locked forecasts will not be updated.

Low Priority Features

These are deemed useful features and will be implemented once the system enhancements have been met. These will be completed after functional testing on the system and may fall outside the delivery deadline and contract period.

19. Rearrange Contributions Sheets

The sequence of Current Year Contributions Sheets indicators will be ordered more logically:

* Team Forecast Growth by Campaign
* Units Forecast / Units Actuals
* Forecast Units % Contribution,
* Forecast Units % Contribution v last 4 years
* Forecast Units Increase v last 4 years
* Sales Forecast / Units Actuals
* Forecast Sales % Contribution,
* Forecast Sales % Contribution v last 4 years
* Forecast Sales Increase v last 4 years

20. Add data cascade (Forecast auto-populate) facility

This feature enables STMs to incorporate automatically the percentage increase recommended by Marketing & Planning in New Year Forecasts. The increase will be based on prior year Actuals - where unprocessed, Forecasts - and applied at Campaign level across all Teams.
« back