Site Map (Search) page
page menu
page content
Data Perception Consulting
home
discuss
design
develop
deploy
document
deliver
support
warning
size:
A-
A
A+
align:
←
↔
Functional Specification — Business Purpose
Typical business purpose document.
1. Business Objectives 1.1. Manage the distribution, collection, collation, return and aggregation of Sector Managers' campaign forecasts and actuals. 1.2. Enable an automated system managed from a central Microsoft Access 97 database which distributes CSV-formatted data to each of 55 sector managers as MS-mail, E-mail or (as comms failure contingency) 1.44Mb diskette-based data. 1.3. Collect completed and returned data; import into Microsoft Access system. 1.4. Convert existing archived macro-based Microsoft Excel 5 spreadsheets into VBA-based Microsoft Excel 97 workbooks. 1.5. Import / export data between Microsoft Access system and Mainframe system in suitable agreed format (CSV or fixed width). 2. Business Overview 2.1. The business map is as follows: Regions (4 large and 2 small) are divided into Sectors, in turn divided into Teams each allocated a representative. Large Teams are further divided into postcode sector-based Territories. 3. Production Information 3.1. The business derives its production information from (on average) 14 Campaigns operated each business year, April to March, grouped into 4 business quarters: Quarter 1 Initiative 1 - 4 Quarter 2 Initiative 5 - 7 Quarter 3 Initiative 8 - 10 Quarter 4 Initiative 11 - 14. Quarter 1 generating the greater revenue; Quarter 2 the lesser. 3.2. Each campaign approximates to 19 Target-plan days (roughly 4 weeks) through which time product orders generated at Area level are processed at Wescot. 4. Current Performance Monitoring System 4.1. Performance monitoring is forecast-driven at Team level, as follows: A target statement representing percentage increases in annual Team Growth, Totals Sales, Units and a yearly forecast template (on 3 diskettes) are distributed to each Divisional Manager early in the 4th Quarter (with quarterly updates thereafter) by the Finance / Market Planning department. The template comprises: * Additions Additions to the Team Representatives total * Removals Removals from the Team Representatives total * Removal Rate Previous Team Growth against current Campaign * Team Team Representatives total * Strength Units expressed as percentage against Team Growth * Units Total Units * Average Clients Clients over Units * Client Spend Average Unit over average Client * Average Unit Sales over Average Units * Sales Total Sales * Sales Contribution Quarterly percentage of Annual (100%) * Total Clients Total Clients 4.2. Each of the categories above comprises an identical columnar structure: 1996 Actuals, 1997 Actuals, 1998 Actuals, 1999 Actuals, 2000 Actuals 2001 Forecast, 2001 Forecast v 2000 Actuals, 2001 Actuals, 2001 Actuals v Forecast 4.3. The columns displayed will vary at 3 points during the year: * New Year forecast Issued during the 2nd Quarter * Current Year forecast Issued during the 3rd Quarter * Action Plan forecast Issued during the 3rd Quarter 4.4. The row structure (count of) is determined by the number of Campaigns, broken and subtotalled at respective quarters, with 1st Half Year, 3rd Quarter Year-to-Date, 2nd Half Year and Annual totals. 4.5. Updated Actuals are dispersed to Sector Managers at the close of each Campaign, with, if necessary, the appropriate template. 4.6. Sector Managers update Forecast / Action Plan details and return the same diskettes to Market Planning. 4.7. The following 10 Columns may be updated by Sector Managers: * Additions 2000 Forecast, 2000 Actuals * Removals 2000 Forecast, 2000 Actuals * Strength 2000 Forecast, 2000 Actuals * Average Clients 2000 Forecast, 2000 Actuals * Client Spend 2000 Forecast, 2000 Actuals 4.8. The 1996 Actuals column doubles as the Action Plan input but is overwritten progressively by Actuals at the close of each Campaign; the Action Plan figure is discarded. 4.9. Rows are locked-off progressively at the close of each Campaign once the Actuals have been processed. Current Data Structures 5. Export to Sector Managers 5.1. Data to populate the template, prior to release to Sector Managers, is extracted from the mainframe as 18 columns in the following CSV format: * Region, Sector, Team, Current Year and Campaign - unique composite key * Additions, Removals, Team, Strength, Clients, Units, Sales - Actuals / Action Plan data * Additions, Removals, Team, Strength, Clients, Units, Sales - Forecast data 5.2. Data is held as fixed-width, signed where necessary, zero prepended. 6. Import from Sector Managers 6.1. Data to the mainframe is uploaded in the following CSV format: Action Plan / Forecast Identifier, Current Year and Campaign, Average Clients, Additions, Removals, Strength Percentage, Average Unit Percentage. 6.2. Data is held as fixed-width, signed where necessary, zero prepended. 7. Output 7.1. Printed output reflects the contents of the categories Additions through Total Clients spread across 5 pages, either as Team reports or Sector, Regional, National totals reports. 7.2. A further report summarises Contributions details at Sector level comprising: 7.2.1. Unit Forecast & Actuals by current quarter and year and previous 4 years 7.2.2. Sales Forecast & Actuals by current quarter and year and previous 4 years 7.2.3. Current year Team Forecast by start and quarter 7.2.4. Current year Units Forecast by quarter and full year as a percentage of Contribution 7.2.5. Current year Sales Forecast by quarter and full year as a percentage of Contribution 7.2.6. Current year Team Growth Forecast by quarter and full year 7.2.7. Current year Units Forecast against previous 4 years as a percentage of Contribution 7.2.8. Current year Sales Increase Forecast as a percentage against previous 4 years 7.2.9. Current year Units Increase Forecast as a percentage against previous 4 years. Revised System 8. Revised Performance Monitoring System 8.1. Performance monitoring is forecast-driven at Team level, as follows: A target statement representing percentage increases in annual Team Growth and Totals Sales and a yearly forecast template are distributed by E-mail to each Sector Manager early in the 4th Quarter by the Finance / Market Planning department. The template comprises: * Additions Additions to the Team Representatives total * Removals Removals from the Team Representatives total * Team Team Representatives total * Team Growth Calculated from Additions - Removals * Strength Units expressed as a percentage against Team Growth * Units Total Units * Average Clients Clients over Units * Client Spend Average Unit over average Client * Average Unit Sales over Average Units * Sales Total Sales * Total Clients Total Clients 8.2. Each of the categories above comprises an identical columnar structure: Current Year-4 Actuals, Current Year-3 Actuals, Current Year-2 Actuals, Current Year-1 Actuals, Current Year Forecast, Current Year Forecast v Current Year-1 Actuals, Current Year Action Plan, Current Year Actuals, Current Year Actuals v Forecast. Units, Average Units, Sales will display an additional column - Percentage Growth over Last Year Growth as Current Forecast over Last Year Actuals. 8.3. The columns displayed will vary at 3 points during the year: * New Year forecast Issued during the 4th Quarter * Current Year forecast Issued during the 1st Quarter * Action Plan forecast Issued during the 1st Quarter 8.4. The row structure (count of) is determined by the number of Campaigns, broken and subtotalled at respective quarters, with 1st Half Year, 3rd Quarter Year-to-Date, 2nd Half Year and Annual totals. 8.5. Updated Actuals are dispersed to Sector Managers at the close of each Campaign, with, if necessary, the appropriate template. 8.6. Sector Managers update Forecast / Action Plan details and E-mail the data to Market Planning. 8.7. The following 10 Columns may be updated by Sector Managers: * Additions Current Forecast, Current Action Plan * Removals Current Forecast, Current Action Plan * Units Current Forecast, Current Action Plan * Average Clients Current Forecast, Current Action Plan * Client Spend Current Forecast, Current Action Plan 8.8. The Current Year Action Plan column is placed to the left of Current Actuals, is not overwritten, and is saved to the Microsoft Access 97 system along with other data. 8.9. Rows are locked-off progressively at the close of each Campaign once the Actuals have been processed. Revised Data Structures 9. Export to Sector Managers 9.1. Data to populate the template, prior to release to Sector Managers, is first extracted from the mainframe as 18 columns in the following CSV format: * Region, Sector, Team, Current Year and Campaign - unique composite key * Additions, Removals, Team, Strength, Clients, Units, Sales - Actuals / Action Plan data * Additions, Removals, Team, Strength, Clients, Units, Sales - Forecast data Data is held as fixed-width, signed where necessary, zero prepended. 9.2. The mainframe CSV is loaded into the Microsoft Access 97 database where it is converted and held as Additions, Removals, Team, Units, Sales, Clients identified by a composite key comprising Country, Team, Year, Campaign. Mainframe percentages are converted to values to simplify and accelerate processing. 9.3. The CSV files sent to Sector Managers will also contain additional information: * Sheet Title * Column Show / Hide flag * Cell locking flag 10. Import from Sector Managers 10.1. Data to the Microsoft Access 97 database is exported in the following CSV format: Additions, Removals, Team, Units, Sales. 10.2. This is then converted into a format suitable for uploading to the mainframe in the following format: Action Plan / Forecast Identifier, Current Year and Campaign, Average Clients, Additions, Removals, Strength Percentage, Average Unit Percentage. 10.3. Data is held as fixed-width, signed where necessary, zero prepended. 11. Output 11.1. Printed output reflects the contents of the categories Additions through Total Clients spread across 5 pages, either as Team reports or Sector, Regional, National totals reports. 11.2. A second report summarises Contributions details at Sector level comprising: 11.2.1. Units Forecast & Actuals by current quarter and year and previous 4 years 11.2.2. Sales Forecast & Actuals by current quarter and year and previous 4 years 11.2.3. Current year Units Forecast by quarter and full year as a percentage of Contribution 11.2.4. Current year Sales Forecast by quarter and full year as a percentage of Contribution 11.2.5. Current year Team Growth Forecast by quarter and full year 11.2.6. Current year Units Forecast against previous 4 years as a percentage of Contribution 11.2.7. Current year Sales Increase Forecast as a percentage against previous 4 years 11.2.8. Current year Units Increase Forecast as a percentage against previous 4 years 11.2.9. Contribution of Sales as Quarterly Percentage of Annual (100%). 11.3. An additional report comprising year-on-year volume and percentage increase for Team, Units and Sales at Team level spanning the current and 2 previous years with average growth and Sector comparison growth. 11.4. Additional graphing comprising: 11.4.1. Sales expressed as a percentage of next year Forecast against current year Actuals 11.4.2. Units expressed as a percentage of next year Forecast against current year Actuals 11.4.3. Team Growth by Campaign. 11.4.4. Sales contribution as a percentage by Campaign 11.4.5. Units contribution as a percentage by Campaign. The Sector average will be displayed on all graphs. System Database 1.2 Access 97 Database 12.1. The Access 97 database will be the primary repository for data transfer to and from STMs' Forecasting workbooks. It will also act as a conduit for data transfer to / from the mainframe. 12.2. Workbook functionality will be governed directly by Access, i.e. titles, rows, columns, cells will be manipulated by a set of options from a control panel, these parameters being stored within the CSV file prior to dispersal to Sector Managers. 12.3. The database will contain all information from archive and current years and will contain all future generated data (with a roll-over period of 10 years). 12.4. Action Plan details will be stored once the system goes live and maintained to conclusion of the current year; 1 years current data will be stored. 12.5. Additional data will be imported form the mainframe as a CSV file detailing changes to Regions, Sectors, Teams - the Team Map. This will be imported on an as-needs basis and checked for prior to the workbook CSV dispersal procedure, identified by date stamp. A log of Team Map restructure imports will be maintained. 12.6. The process governing restructuring requires detailed discussion to ensure data integrity, especially when Teams are split / merged and Forecast, Action Plan data is reapportioned. It may be that Sector Managers are required to re-enter data. To be discussed. 12.7. The workbook CSV will contain the following data, some will be hidden within workbooks upon workbook release template (New Year, Current, Action Plan), some hidden permanently but used for Contributions and graphing purposes: * Cur Yr-4 Actuals Hidden * Cur Yr-3 Actuals Hidden * Cur Yr-2 Actuals Chosen-format# * Cur Yr-1 Actuals Visible * Cur Yr Forecast Visible * Cur Yr Forecast v Cur Yr-1 Actuals Visible when appropriate * Cur Yr Action Plan Visible when appropriate * Cur Yr Actuals Visible when appropriate * Cur Yr Actuals v Forecast Visible when appropriate * Cur Yr Forecast v Cur Yr-1 Actuals % Visible when appropriate# # See Sector Managers' Workbooks - Section 13 Sector Managers' Workbooks 13. Workbook Layout 13.1. The existing functionality will be maintained; however, specific columns will be shown or hidden upon Workbook template release. 13.2. All Team sheets will be maintained in one Workbook; tabs at the screen bottom will display available Teams and reflect the current Team in use. To select a different Team select the appropriate tab. 13.3. A graphing facility, as detailed in section 11.3 above will exist. This will be triggered upon menu selection and will display a list of graphs to display and / or print. 13.4. To assist with entry of data in the New Year template an option to Auto-fill the Workbook will exist. This will populate all worksheets with values approximating to the average increase in productivity required (the target percentage increase), as issued by Market Planning. It must be noted that this facility is an aid to forecasting and not the recommendation of Market Planning; Sector Managers will be responsible for adjusting data appropriately. 13.5. At all times the screen will display 3 cells containing the target Sales value, the current value as entered in the Sales totals row of all Teams, and the difference between them. For example, a target value of 100,000 would be visible on-screen. If the Manager selected to Auto-fill (section 13.4), the current value would be (roughly) 100,000. Modification to Sales in any Team sheet would be reflected on-screen immediately.
« back
Site Map (Search)
