Project: Proprietary Analysis & Reporting Online System
Objective: Develop a proprietary online system that has the capability to take initial keyed data, by individual client facilities and links to a various summaries and automatically populates other analysis, sales reports and/or presentations. All templates should have capability to eventually link from an initial upload with beverage company data. Also, all templates, reports, etc should have the same design and look.
1. Static data is populated into appropriate fields from list, i.e. - facility name, packages, brands, cost, rebates, and other data that remains constant. NOTE: Sales data is sent from Beverage Company to Consultant Company. There is some capability for our designating how that data is sorted and formatted. This will be more important of we can eventually get data access to beverage company and have them export data directly into our template. Important to note that there is a need to develop ALL TEMPLATES to have same format column order, info ,etc.( i.e.- category, brand ,package)
2. Variable data is uploaded by facility location (as many as 50) , i.e.- 12 months sales data, net price per package, rebates per package( if different from total system)
3. Facility sheets link to a total system sheet that is same information, but representing the total system and/or links to other analysis. (In large system, may be region links)
4. Key totals are linked to summary pages, individual facility data/numbers and to a TOTAL system summary.
5. Key data from summary is linked to presentation template, graphs, etc
Ongoing Analysis & Reporting
6. As ongoing work, quarterly sales reports from beverage supplier are then used to report sales to client and to invoice client for Consultant Company agreed services.
7. As a first phase, develop model that quarterly sales data from beverage company can be input into spreadsheet, by facility, linked to a total roll-up and then to a summary report/page, much like the initial analysis phase. Future phase should have capability to link into beverage company data base and automatically perform required reporting and invoicing.
8. First phase takes static spreadsheet and adds into it the variable sales data that is sent from Beverage Company and is sorted by facility, by package, etc. NOTE: Same as initial phase, there is some capability to ask Beverage Company to format their reports as we would want them.
9. Individual facility pages/tabs are rolled into total page/tab with key totals rolled into a summary page/tab by facility and then total system, with same linked to sales report and/or presentation.
10. The following documents need to be standardized and developed so as to be able to make one keying entry and customize all “predetermined” documents to client name.
• Initial Presentation; one for hospital systems and one for colleges/universities
• Letter of Understanding
• Authorization Letter Draft, (to be put on client letterhead and signed by client)
• Beverage Company Survey
• RFP Template
• Beverage Agreement Contract ( to be in clients name)
Bids will be accepted for an hourly rate and estimated number of hours. Winning bidders will be promoted to the final round of review where we will provide examples of spreadsheets.
Please provide examples of past work.
Skills: analysis, design, microsoft-sql-server