Data Collection, Analysis & Reporting

Data comprising numbers (with leading zeros) and amounts in five categories received from various sources needs to be collated and stored.? Sources provide the information by faxed sheets each numbered.? The data entry routine must be quick and simple storing the numbers by category and souce number and sheet number.? The data entry form must return the sub-total per source number and ? sheet number? on a running basis for data validation purposes.? Each source has a discount structure that must be applied to the data from a master sheet containing all the various discounts. Once all the data has been received it must be collated into master sheets for each category and also overall.? In the master sheet the data will be manipulated to limit the maximum amounts allowable from a user defined figure and a report produced of the numbers and amounts by category exceeding the limits. ## Deliverables The most important consideration is speed of data entry.? Therefore the form used to enter the data will use the / * - + and Enter key from the number keypad to select the number category.? The numbers are either three digits or two digits and both may have leading zeros which need to be retained.? The data form should display the running sub-total for the source or agent and the sheet number once entered into the data entry form.? Upon entry the source/agent discount should be applied to derive the gross amount, net amount and discount amount and should also record a payout multiplier which is obtained from a master agent discount structure sheet containing details for all agents/sources.? The source/agent worksheets should sort the numbers by category.? After data entry completion the agent/source sheets should be compiled into a master sheet sorted within categories by number to give totals for each number in each category.? There will be several formulas applied to the data which will be supplied.? Finally the master sheet will compute a best fit scenario and generate a report of all numbers and amounts over a calculated or user input amount. The 5 categories are named 3 Digit High, 3 Digit Combi, 2 Digit Double, 2 Digit High, 2 Digit Low.? The 3 Digit Combi type when entered will generate six separate entries for the six possible number combinations from the 3 digits each with 1/6th of the total amount for that entry.? The 2 Digit Double will allow either one entry in either the 2 Digit High or 2 Digit Low category amount entry box placing an equal amount in the 2 Digit High and 2 Digit Low fields OR will allow two separate amounts to be entered in the respective 2 Digit High & 2 Digit Low category boxes storing the appropriate amounts in each category field.? A sample entry sheet received from the various sources/agents is attached