Data extract then Automated Excel CFSI Dashboard with automations and word/outlook template outputs Reports
Budget: $250 – $750 USD
Project title
Automated Excel CFSI Dashboard and word/outlook template outputs
Objective
Build Excel workbook that runs the entire CFSI pipeline. Data entry, tracking, reporting, and a one page dashboard. Excel and Word stay in sync both ways. Confirmed cases create email drafts and Word packs without manual copy paste. All time stamps use Abu Dhabi time.
Scope of work
1. Data model and fields
• Master table Cases with these fields like below
Case No
PO No
Class
Item Description
Qty
Item Code or Part Number
Manufacturer
Supplier
Country of Origin
Identified Date
Started Date
Completed Date
Status
Result Type
SLA Target Days
Days Open
Workdays Open
Priority
Risk Rating
Case Owner
Department
AR Ref
NCR Ref
OSDD Ref
BCE Needed Yes or No
BCE Status
Reporting Needed Yes or No
Reporting Status
FANR Report Flag
EPRI Share Flag
Evidence Folder Path
Primary Image Path
Email To
Email CC
Free Text Summary
Key Notes
• Data dictionary with allowed values and validation rules for each controlled field
• UAE workdays and holidays list for NETWORKDAYS
2. Input and automation
• Protected input form for guided entry with drop downs and validation
• One click Add Case writes to the Cases table and time stamps the change
• Automatic numbering with newest first
• Duration and workdays auto calculated from dates
• Overdue logic using SLA Target Days with clear flags
• Full audit trail
User
Field changed
Old value
New value
Timestamp in Abu Dhabi time
3. Excel to Word and Word to Excel sync
• Three Word templates with content controls bound to case fields
Result email template for confirmed CFSI
Basic Cause Evaluation draft
CFSI Reporting Form
• One click Generate from Excel creates all three Word files for the selected case
• Central image handling
Store images in one folder per case
Excel saves the path
Word pulls images for that case into the report
• Two way updates
Update in Excel reflects in Word on refresh
Update controlled fields in Word content controls flows back to Excel on refresh
No free text in Word should overwrite protected calculated fields
4. Alerts and outputs
• SLA breach alert by Outlook and by Teams
One click test button to prove alerts work
• One click Refresh rebuilds Power Query, measures, pivots, and charts
• One click Export prints the dashboard to A4 landscape PDF
• Version number and last refresh timestamp on dashboard and on exported PDF
5. Dashboard one page
• KPIs at top
Total Cases
Open Cases
Closed Cases
Average Days to Resolve
Median Days to Resolve
Running Resolution Rate month to month
• Charts
Open vs Closed
Result Type mix
Cases by Class
Resolution trend month to month
Ageing heatmap with buckets 0 to 7, 8 to 14, 15 to 30, 31 to 60, 61 plus
• Table
Top overdue open cases with Case No, Supplier, Identified, Days Open, Owner
• Print fits on one A4 landscape page with clean margins
Technology
• Excel 365 for Windows
• VBA for buttons, audit trail, Word integration, Outlook and Teams alerts
• Power Query for load and reshaping
• Power Pivot or standard pivots and measures based on volume
• No external links without approval
• No volatile formulas that harm performance
Security and performance
• Lock logic sheets
• Leave input form and Cases table unlocked for data entry
• File size target under a practical limit for email sharing
• No hardcoded user paths
• All time zones set to Asia Dubai
Deliverables
• Unlocked master workbook xlsx with documented VBA modules and queries
• Three clean Word templates with content controls bound to named fields
• A short screen recording that shows
Add five cases
Refresh dashboard
Generate one email draft, one BCE, one CFSI report with images
Trigger Outlook and Teams alerts
• Written hand off notes
How to add a new field safely
How to change visuals
How to add holidays
How to add a new image field
Acceptance tests
• Add five new cases via the form. All KPIs and charts update without edits
• Change Status and Completed Date for one case. Resolution KPIs update correctly
• Trigger an SLA breach. Outlook and Teams messages arrive with the right case info
• Generate the three Word outputs for a confirmed case. Fields and images are correct
• Update a controlled field in Word then refresh. Excel reflects the change
• Print the dashboard. It fits on one A4 page with correct scaling
Milestones and pricing
• M1 Design and sign off on data dictionary and templates. Fixed price
• M2 Build engine and form. Fixed price
• M3 Reports, alerts, and images. Fixed price
• M4 Dashboard and performance tuning. Fixed price
• M5 Test, hand off, and training video. Fixed price
What you provide to start
• Sample data
• ENEC color palette and fonts
• Email template text for confirmed CFSI
• BCE and CFSI report content structure
• UAE holidays list
What to include in your bid
• One screenshot of a similar automated tracker or dashboard
• A short note on your approach to two way Excel and Word sync with content controls
• A simple timeline with the five milestones
I want the files generated and the excel to be visually looking good and easy to use
Deliverables:
• Automated Excel file (.xlsx).
• Formulas unlocked and documented.
• Quick guide (1 page) explaining data entry and dashboard updates.
1- we will create a template for identification of CFSI. So we can control what’s received in the identification. I’m creating a template for the identification phase to have the critical data there. Photos are usually shared from the identifier either by email or in the AR system itself. Identifiers drop down is usually ( Quality Receipt Inspection or Standard Receipt Inspection or Maintenance or Engineering) rarely it is others like reported by external agency. So if we choose others, could we specify in the excel or that would be hard?
2- Through what’s received in the identification in word or pdf, would the main excel be able to extract the information automatically ? If not, then we manually copy paste the data from the identifier to the main excel.
3- the main excel will be used to input the critical data and from that data, the next step will follow, beginning the investigation by sending out emails one to supplier and the other to the manufacturer and the template is ready to share of that initial contact email , to be drafted and of course each email is unique because it has its own set of questions however, most of that email can be automated by the template.
4- closing the investigation by either confirming it as cfsi or not cfsi. Or not cfsi but substandard. So three results. Also if confirmed ( it can either be counterfeit or fraudulent ) then some dropdown could show ( intentional, not intentional supplier oversight, other please specify) not sure in excel if the option “other please specify” is possible or not.
5- after closure, dependent on the results, if not cfsi, only identifier and buyer is notified. If not cfsi but substandard, identifier and buyer and VRM are notified for actions like final warning letter. If confirmed cfsi, then an email goes out to several functions who we have their emails to show the results to them.
All those emails templates are ready and sent previously.
Confirmed CFSIs must be emailed to external agencies quarterly like epri and possibly others soon. Can use a similar email template there as well.
Power points are issued monthly and has information about open, closed cfsi cases. Powerpoint can be shared.
If say case number 8 was created , would it be possible to automatically create a share folder with the name of that case, and photos can be added there along with all related outputs of that case? For easier reference in the future?
Cfsi case form is available to share; it is subject to some changes as it is in draft phase now. We want to get rid of the monthly reporting
Kpis and graphs can be automated as well. With well put visualization.
Data is available in the word shared, need to be extracted, the new cases after May can be sent to you to be added as well into the main excel.
Do we need to be experienced with vba and macros? Or just have them enabled and let the codes you will create do the work?
Also, will it have restrictions if the codes are added from your computer? Since we have a lot of restrictions? I know we can share the file to my email but everything need to be traced back to my name even the codes, is that easy?
Kpi and graphs the ones we created are really nice to know; would be nice to have them into two different pages, one with most important data and the other with nice information to have.
Subject to add more data like timeliness of cases closure and other
Oh yes and an email to the supplier informing them about the result of the cfsi as well. For all results, supplier must be informed. Template is ready for that as well
Along with the information submitted previously, I think this covers it for now.
I hope I didn’t leave out anything else
The project can have several revisions to fine-tune and add the missing functionalities. If you agree, we can discuss the milestones. I hope we can agree on a good price as this is the first project of many. I’m becoming head of that function and the cfsi is just 20% of the program.
The only concern I have is how to add photos into each case when we will enter data to the excel, will it create a share folder for each case automatically and we can add photos there? And manually add photos into the automatic drafts emails and reports? Or it is possible to add into excel for each case and it will be automatically add the photos to the reports and emails
We need a solution to this
Not a big issue, if other functionality and automation were done properly, we will lift a huge admin burden
Automated Excel CFSI Dashboard and word/outlook template outputs
Objective
Build Excel workbook that runs the entire CFSI pipeline. Data entry, tracking, reporting, and a one page dashboard. Excel and Word stay in sync both ways. Confirmed cases create email drafts and Word packs without manual copy paste. All time stamps use Abu Dhabi time.
Scope of work
1. Data model and fields
• Master table Cases with these fields like below
Case No
PO No
Class
Item Description
Qty
Item Code or Part Number
Manufacturer
Supplier
Country of Origin
Identified Date
Started Date
Completed Date
Status
Result Type
SLA Target Days
Days Open
Workdays Open
Priority
Risk Rating
Case Owner
Department
AR Ref
NCR Ref
OSDD Ref
BCE Needed Yes or No
BCE Status
Reporting Needed Yes or No
Reporting Status
FANR Report Flag
EPRI Share Flag
Evidence Folder Path
Primary Image Path
Email To
Email CC
Free Text Summary
Key Notes
• Data dictionary with allowed values and validation rules for each controlled field
• UAE workdays and holidays list for NETWORKDAYS
2. Input and automation
• Protected input form for guided entry with drop downs and validation
• One click Add Case writes to the Cases table and time stamps the change
• Automatic numbering with newest first
• Duration and workdays auto calculated from dates
• Overdue logic using SLA Target Days with clear flags
• Full audit trail
User
Field changed
Old value
New value
Timestamp in Abu Dhabi time
3. Excel to Word and Word to Excel sync
• Three Word templates with content controls bound to case fields
Result email template for confirmed CFSI
Basic Cause Evaluation draft
CFSI Reporting Form
• One click Generate from Excel creates all three Word files for the selected case
• Central image handling
Store images in one folder per case
Excel saves the path
Word pulls images for that case into the report
• Two way updates
Update in Excel reflects in Word on refresh
Update controlled fields in Word content controls flows back to Excel on refresh
No free text in Word should overwrite protected calculated fields
4. Alerts and outputs
• SLA breach alert by Outlook and by Teams
One click test button to prove alerts work
• One click Refresh rebuilds Power Query, measures, pivots, and charts
• One click Export prints the dashboard to A4 landscape PDF
• Version number and last refresh timestamp on dashboard and on exported PDF
5. Dashboard one page
• KPIs at top
Total Cases
Open Cases
Closed Cases
Average Days to Resolve
Median Days to Resolve
Running Resolution Rate month to month
• Charts
Open vs Closed
Result Type mix
Cases by Class
Resolution trend month to month
Ageing heatmap with buckets 0 to 7, 8 to 14, 15 to 30, 31 to 60, 61 plus
• Table
Top overdue open cases with Case No, Supplier, Identified, Days Open, Owner
• Print fits on one A4 landscape page with clean margins
Technology
• Excel 365 for Windows
• VBA for buttons, audit trail, Word integration, Outlook and Teams alerts
• Power Query for load and reshaping
• Power Pivot or standard pivots and measures based on volume
• No external links without approval
• No volatile formulas that harm performance
Security and performance
• Lock logic sheets
• Leave input form and Cases table unlocked for data entry
• File size target under a practical limit for email sharing
• No hardcoded user paths
• All time zones set to Asia Dubai
Deliverables
• Unlocked master workbook xlsx with documented VBA modules and queries
• Three clean Word templates with content controls bound to named fields
• A short screen recording that shows
Add five cases
Refresh dashboard
Generate one email draft, one BCE, one CFSI report with images
Trigger Outlook and Teams alerts
• Written hand off notes
How to add a new field safely
How to change visuals
How to add holidays
How to add a new image field
Acceptance tests
• Add five new cases via the form. All KPIs and charts update without edits
• Change Status and Completed Date for one case. Resolution KPIs update correctly
• Trigger an SLA breach. Outlook and Teams messages arrive with the right case info
• Generate the three Word outputs for a confirmed case. Fields and images are correct
• Update a controlled field in Word then refresh. Excel reflects the change
• Print the dashboard. It fits on one A4 page with correct scaling
Milestones and pricing
• M1 Design and sign off on data dictionary and templates. Fixed price
• M2 Build engine and form. Fixed price
• M3 Reports, alerts, and images. Fixed price
• M4 Dashboard and performance tuning. Fixed price
• M5 Test, hand off, and training video. Fixed price
What you provide to start
• Sample data
• ENEC color palette and fonts
• Email template text for confirmed CFSI
• BCE and CFSI report content structure
• UAE holidays list
What to include in your bid
• One screenshot of a similar automated tracker or dashboard
• A short note on your approach to two way Excel and Word sync with content controls
• A simple timeline with the five milestones
I want the files generated and the excel to be visually looking good and easy to use
Deliverables:
• Automated Excel file (.xlsx).
• Formulas unlocked and documented.
• Quick guide (1 page) explaining data entry and dashboard updates.
1- we will create a template for identification of CFSI. So we can control what’s received in the identification. I’m creating a template for the identification phase to have the critical data there. Photos are usually shared from the identifier either by email or in the AR system itself. Identifiers drop down is usually ( Quality Receipt Inspection or Standard Receipt Inspection or Maintenance or Engineering) rarely it is others like reported by external agency. So if we choose others, could we specify in the excel or that would be hard?
2- Through what’s received in the identification in word or pdf, would the main excel be able to extract the information automatically ? If not, then we manually copy paste the data from the identifier to the main excel.
3- the main excel will be used to input the critical data and from that data, the next step will follow, beginning the investigation by sending out emails one to supplier and the other to the manufacturer and the template is ready to share of that initial contact email , to be drafted and of course each email is unique because it has its own set of questions however, most of that email can be automated by the template.
4- closing the investigation by either confirming it as cfsi or not cfsi. Or not cfsi but substandard. So three results. Also if confirmed ( it can either be counterfeit or fraudulent ) then some dropdown could show ( intentional, not intentional supplier oversight, other please specify) not sure in excel if the option “other please specify” is possible or not.
5- after closure, dependent on the results, if not cfsi, only identifier and buyer is notified. If not cfsi but substandard, identifier and buyer and VRM are notified for actions like final warning letter. If confirmed cfsi, then an email goes out to several functions who we have their emails to show the results to them.
All those emails templates are ready and sent previously.
Confirmed CFSIs must be emailed to external agencies quarterly like epri and possibly others soon. Can use a similar email template there as well.
Power points are issued monthly and has information about open, closed cfsi cases. Powerpoint can be shared.
If say case number 8 was created , would it be possible to automatically create a share folder with the name of that case, and photos can be added there along with all related outputs of that case? For easier reference in the future?
Cfsi case form is available to share; it is subject to some changes as it is in draft phase now. We want to get rid of the monthly reporting
Kpis and graphs can be automated as well. With well put visualization.
Data is available in the word shared, need to be extracted, the new cases after May can be sent to you to be added as well into the main excel.
Do we need to be experienced with vba and macros? Or just have them enabled and let the codes you will create do the work?
Also, will it have restrictions if the codes are added from your computer? Since we have a lot of restrictions? I know we can share the file to my email but everything need to be traced back to my name even the codes, is that easy?
Kpi and graphs the ones we created are really nice to know; would be nice to have them into two different pages, one with most important data and the other with nice information to have.
Subject to add more data like timeliness of cases closure and other
Oh yes and an email to the supplier informing them about the result of the cfsi as well. For all results, supplier must be informed. Template is ready for that as well
Along with the information submitted previously, I think this covers it for now.
I hope I didn’t leave out anything else
The project can have several revisions to fine-tune and add the missing functionalities. If you agree, we can discuss the milestones. I hope we can agree on a good price as this is the first project of many. I’m becoming head of that function and the cfsi is just 20% of the program.
The only concern I have is how to add photos into each case when we will enter data to the excel, will it create a share folder for each case automatically and we can add photos there? And manually add photos into the automatic drafts emails and reports? Or it is possible to add into excel for each case and it will be automatically add the photos to the reports and emails
We need a solution to this
Not a big issue, if other functionality and automation were done properly, we will lift a huge admin burden