CFSI Investigation Log Automation and Dashboard And Reports
Budget: $50 – $200 USD
Project title
CFSI Investigation Log Automation and Dashboard
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
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
Does users who will work on this file in the company must have macros enabled?
I want the files generated and the excel to be visually looking good and easy to use
CFSI Investigation Log Automation and Dashboard
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
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
Does users who will work on this file in the company must have macros enabled?
I want the files generated and the excel to be visually looking good and easy to use