Enhance look and functionality of Excel template
Budget: $15 – $25 USD
SCOPE
2 Excel files (Input file + Consolidation file)
CONTEXT
Input files are used to collect individual inputs by completing the questionnaire to identify potential waste in in-store processes.
Upon completion, inputs are reconciled in Consolidation file with dynamic and comprehensive visualization.
REQUIREMENTS (more detailed explanations in further sections below)
1. Enhance look and feel of the files – we want it to look as a professional tool with a “sharp” design
2. Enable countries to input Translations (see initial example translation tab, should be linked to questionnaire)
2. Create Consolidation file linked to Individual input files based on max 8 separate store files
3. Visualization – we need dynamic and intuitive charts
4. Functionality enhancements – locking cells; adding calculated columns
PROCESS EXPLANATION (how it will work)
Input file
Distributed among multiple participants (Stores). Each Store needs to fill in the following data:
• Worksheet “Store input” – numerical fields in Column B with the comment “input country”
• Worksheet “Questionnaire” – columns G to O
o Frequency (column G) – choosing frequency from drop-down list (daily/weekly/monthly/quarterly/yearly) except cells with locked value “per time”
o Task freq (Column H) – indicating how many tomes the task is repeated per frequency in column G (e.g. 3 times weekly) except cells with locked value “1” corresponding to “per time” frequency in column G
o Avg. Task (Column I) – number of minutes per task
o Who does the task (Columns J, K, L) – choose “X” value where relevant from drop-down list
o Potential waste (Column M) – choose Y or N values from drop-down list
o Pot. Gain (Column N) – if Y value chosen in Column M, indicate minutes of potential gain if the waste is eliminated
o Comments (Column O) – explain thoughts / provide feedback
Columns A and B should be used for further consolidation (linking Store and Task numbers).
Columns P and Q are used for calculation of weekly duration of each task (to be used in Visualization).
Consolidation file
It should compile Questionnaire inputs from multiple completed Input files and visualize consolidated results.
DETAILED REQUIREMENTS
Input file
1. Worksheet “Questionnaire”
• Lock the range A1:F70 for editing
• Lock columns P and Q for editing
• Make impossible insertion of new lines between lines 1 and 70 (keep the option of filling in new lines as of line 71)
• Lock all cells with “per time” value in Column G and “1” value in Column H
• Add column R with calculation of weekly waste (min) using the same logic as in Column Q (using task frequency and potential gain from Column N)
• Keep conditional formatting principle in Columns G to O (design can be enhanced) i.e. cell has a colour when input is required
2. Worksheet “Visualization”
• Pie chart based on the updated Questionnaire reflecting average duration of tasks (with possibility to easily change task hierarchy based on pivot fields and/or filters)
• Chart that shows potential gain vs task duration (e.g. if Task X takes 60 min per week and potential gain is 15 minutes – etc. for all tasks) – please propose the optimal format
Consolidation file
• Create the file with consolidated Questionnaire; link it to individual files through Store ID and Task ID (Columns A and B in Questionnaire worksheet)
• Consolidated Questionnaire should reflect all inputs from Stores to allow comparison of results (adding more columns vs Input file by replicating Columns G to Q for every Store, limiting number of Stores to 10)
• Adding calculated columns in the end to have average task duration and potential gain for all stores together
• Visualization:
o Two charts with similar logic as in Input file (combined results for task durations and potential gains)
o Bar chart per task group to see comparison between Stores (where which task takes more or less time)
o Table showing output per store per line item and variance
2 Excel files (Input file + Consolidation file)
CONTEXT
Input files are used to collect individual inputs by completing the questionnaire to identify potential waste in in-store processes.
Upon completion, inputs are reconciled in Consolidation file with dynamic and comprehensive visualization.
REQUIREMENTS (more detailed explanations in further sections below)
1. Enhance look and feel of the files – we want it to look as a professional tool with a “sharp” design
2. Enable countries to input Translations (see initial example translation tab, should be linked to questionnaire)
2. Create Consolidation file linked to Individual input files based on max 8 separate store files
3. Visualization – we need dynamic and intuitive charts
4. Functionality enhancements – locking cells; adding calculated columns
PROCESS EXPLANATION (how it will work)
Input file
Distributed among multiple participants (Stores). Each Store needs to fill in the following data:
• Worksheet “Store input” – numerical fields in Column B with the comment “input country”
• Worksheet “Questionnaire” – columns G to O
o Frequency (column G) – choosing frequency from drop-down list (daily/weekly/monthly/quarterly/yearly) except cells with locked value “per time”
o Task freq (Column H) – indicating how many tomes the task is repeated per frequency in column G (e.g. 3 times weekly) except cells with locked value “1” corresponding to “per time” frequency in column G
o Avg. Task (Column I) – number of minutes per task
o Who does the task (Columns J, K, L) – choose “X” value where relevant from drop-down list
o Potential waste (Column M) – choose Y or N values from drop-down list
o Pot. Gain (Column N) – if Y value chosen in Column M, indicate minutes of potential gain if the waste is eliminated
o Comments (Column O) – explain thoughts / provide feedback
Columns A and B should be used for further consolidation (linking Store and Task numbers).
Columns P and Q are used for calculation of weekly duration of each task (to be used in Visualization).
Consolidation file
It should compile Questionnaire inputs from multiple completed Input files and visualize consolidated results.
DETAILED REQUIREMENTS
Input file
1. Worksheet “Questionnaire”
• Lock the range A1:F70 for editing
• Lock columns P and Q for editing
• Make impossible insertion of new lines between lines 1 and 70 (keep the option of filling in new lines as of line 71)
• Lock all cells with “per time” value in Column G and “1” value in Column H
• Add column R with calculation of weekly waste (min) using the same logic as in Column Q (using task frequency and potential gain from Column N)
• Keep conditional formatting principle in Columns G to O (design can be enhanced) i.e. cell has a colour when input is required
2. Worksheet “Visualization”
• Pie chart based on the updated Questionnaire reflecting average duration of tasks (with possibility to easily change task hierarchy based on pivot fields and/or filters)
• Chart that shows potential gain vs task duration (e.g. if Task X takes 60 min per week and potential gain is 15 minutes – etc. for all tasks) – please propose the optimal format
Consolidation file
• Create the file with consolidated Questionnaire; link it to individual files through Store ID and Task ID (Columns A and B in Questionnaire worksheet)
• Consolidated Questionnaire should reflect all inputs from Stores to allow comparison of results (adding more columns vs Input file by replicating Columns G to Q for every Store, limiting number of Stores to 10)
• Adding calculated columns in the end to have average task duration and potential gain for all stores together
• Visualization:
o Two charts with similar logic as in Input file (combined results for task durations and potential gains)
o Bar chart per task group to see comparison between Stores (where which task takes more or less time)
o Table showing output per store per line item and variance