Advanced Google Sheets and Excel Automation and Real-Time Spreadsheet Integration

Project Specification: Advanced Google Sheets & Excel Automation for Sandton Taxi Cabs (Pty) Ltd
Project Objectives:
Sandton Taxi Cabs requires a freelancer with demonstrable expertise in VBA, Macros, Pivot Tables, XLOOKUP, Google Apps Script, and advanced spreadsheet visualization to:
1. Automate data entry and reporting processes
2. Extract, clean, and structure raw data for business analysis
3. Build real-time dashboards and reports from a central Google Sheets spreadsheet
4. Improve accuracy, speed, and consistency of operational reports
5. Facilitate automated quote, invoice, and client report generation
6. Enhance data access and collaboration through synchronized and self-updating reports

Company Overview:
Company: Sandton Taxi Cabs (Pty) Ltd
Location: Johannesburg, South Africa
Industry: Transport & Mobility Services
Primary Data Environment: Google Sheets with multiple data sources and Excel exports

Project Timeline:
Start Date: 1 August 2025
Completion Date: 30 October 2025 (90-day delivery period)
Project Duration: 3 months (renewable based on performance and outcomes)

Required Skills & Technologies:
Advanced Google Sheets (functions, data validation, named ranges)
Google Apps Script (custom scripts for automation and data synchronization)
Excel VBA & Macros (especially for legacy systems and conversion to Google Sheets)
Pivot Tables, XLOOKUP, INDEX/MATCH, ARRAYFORMULA
Conditional Formatting (for visual cues and alerting)
Experience in data cleaning, standardization, and dynamic data structuring
Proficiency in data visualization (charts, pictograms, graphs, scorecards, dashboards)

Input Source:
Central Google Sheet Spreadsheet containing all operational data, including:
Driver trip records
Sales and payments
Vehicle assignments
Client data
Consultant performance
Invoice history

Project Scope:
Data Preparation & Cleaning
Eliminate duplicates and invalid entries
Standardize formats (dates, currencies, names, etc.)
Validate entries using dropdowns, regex, formulas

Automated Reports and Dashboards
1. Sales Reports
Daily, Weekly, Monthly, Quarterly, Yearly
Revenue per Driver / Vehicle / Consultant / Client / Region

2. Client Dashboards
Individual sheets generated for each client
Real-time summaries, trip history, total spend, invoices

3. Performance Reports
Driver productivity
Consultant efficiency
Vehicle usage & ROI

4. Profitability Analysis
Gross & Net Income
VAT calculations
Expense integration (standardized template to be provided)

5. Quotes & Invoices
Automated invoice generation per trip, client, period
Dynamic quote generator with editable pricing structure

6. Payment Method Trends
Analysis of payment methods (Card, Cash, EFT, App-based)
Segmented by client, consultant, time frame

7. Marketing Support Reports
Sales trend by location, season, client type
Client retention, campaign response tracking

Automation Requirements:
Link all dashboards and reports to the original source Google Sheet for real-time updates
Scripts to:
Sync data across sheets
Trigger email alerts on defined criteria
Auto-generate and send PDFs of client reports/invoices
Track changes and log updates

Deliverables:
1. Fully automated Google Sheets workbook with:
All reports and dashboards
Predefined templates (quotes, invoices)
2. Google Apps Script functions for:
Data sync
Report generation
Real-time updates
3. User Guide (short manual on how to run/maintain the system)
4. Final handover with 1-hour walkthrough/training session (optional)

Expected Benefits:
Time Saved: 10+ hours/week on manual reporting
Accuracy Improved: <1% error rate on calculations
Decision-Making Enhanced: Reliable dashboards and trend visuals
Scalability Enabled: Framework supports higher data volumes and new clients
Revenue Visibility: Clear real-time tracking of income streams
Team Collaboration: Shared access to live reports & tracking

Freelancer Requirements:
Proven portfolio of similar automation/reporting projects
4.8+ rating on Freelancer.com
20+ completed projects (preferably with transport/logistics or SME clients)
Must provide samples or video demo of relevant work
Excellent communicator; able to provide weekly progress updates
Budget & Payment:
Hourly Rate: USD \$10–\$50/hour (negotiable)
Payment Milestones:
1. Initial Setup & Data Cleaning – 20%
2. First Batch of Reports & Dashboards – 30%
3. Integration of Automation Scripts – 30%
4. Final Testing, Handover & Training – 20%