Development of a Professional E-commerce ERP & Financial Tracking System in Google Sheets (Audicstore)
Budget: $10 – $30 USD
I am looking for a Google Sheets & Apps Script expert to build a customized, automated ERP system for my e-commerce store, Audicstore, which specializes in high-fidelity audio equipment. The goal is to track every "penny" from sales, inventory, and logistics to calculate the exact Net Profit/Loss.
Core Requirements & Modules:
1. Advanced Inventory Management
Track SKUs, Product Names, Brands, and Categories.
Costing Logic: Ability to log purchases from different sources (Bulk Suppliers vs. AliExpress).
Weighted Average Cost: The system must calculate the average unit cost dynamically based on different purchase batches to ensure accurate profit calculation.
Low Stock Alerts: Automatic highlighting when stock falls below 5 units.
2. Dynamic Sales & Order Tracking
Import sales data (from Zid/Excel) and link it to the Inventory sheet.
Gateway Fee Automation: Automatically calculate fees based on the payment method:
Tabby: 6.5% of total.
Tamara: 7% of total.
ZidPay: 2.5% + 1 SAR fixed fee.
Manual Editing: The system must allow me to edit orders, weights, and cities to recalculate profits instantly. And add new Gateway payment in the future and edite current and future gatway payments
3. Complex Shipping Logic (The Shipping Engine)
I charge customers a Fixed 25 SAR for shipping. The system must calculate the Actual Cost I pay to providers:
Roadlink (Domestic): Based on City: Jeddah (14 SAR), Riyadh (15 SAR), Dammam (16 SAR), Others (19 SAR). Crucial: Add 15% VAT to these base rates.
Aramex/ZidShip (GCC): 43 SAR for the first 0.5kg + 9 SAR for every additional 0.5kg Add 15% to these base rates.
Shipping ROI: Calculate the difference between the 25 SAR collected and the actual cost paid to see the profit/loss on logistics.
4. Returns & Refund Management
Status tags: "A. Refunded" and "B. Not yet refunded".
Refund Logic: Calculate the refund to the customer as: (Item Price + 25 SAR Return Fee).
5. Automated Financial Dashboard (P&L)
Real-time summary of Total Sales, Total COGS, Total Shipping Costs, and Total Net Profit.
Visual charts for sales trends and Top 5 Selling Products (e.g., Tripowin Vivace, Zero:RED).
Accounting for 15% VAT on all domestic transactions.
Technical Skills Required:
Advanced Google Sheets Formulas (VLOOKUP, XLOOKUP, QUERY, IFS).
Google Apps Script (for automation and dynamic updates).
Experience in E-commerce financial modeling and logistics calculation.
Data Visualization (Charts/Dashboards).
Deliverables:
A fully functional, inter-connected Google Sheets file.
A brief guide on how to import new data and manage inventory batches.
EVERYTHING SHOULD BE EDITABLE AND CHANGEABLE SO I CAN UPDATE AND CHANGE EVERYTHING BASED ON MY NEEDS
Core Requirements & Modules:
1. Advanced Inventory Management
Track SKUs, Product Names, Brands, and Categories.
Costing Logic: Ability to log purchases from different sources (Bulk Suppliers vs. AliExpress).
Weighted Average Cost: The system must calculate the average unit cost dynamically based on different purchase batches to ensure accurate profit calculation.
Low Stock Alerts: Automatic highlighting when stock falls below 5 units.
2. Dynamic Sales & Order Tracking
Import sales data (from Zid/Excel) and link it to the Inventory sheet.
Gateway Fee Automation: Automatically calculate fees based on the payment method:
Tabby: 6.5% of total.
Tamara: 7% of total.
ZidPay: 2.5% + 1 SAR fixed fee.
Manual Editing: The system must allow me to edit orders, weights, and cities to recalculate profits instantly. And add new Gateway payment in the future and edite current and future gatway payments
3. Complex Shipping Logic (The Shipping Engine)
I charge customers a Fixed 25 SAR for shipping. The system must calculate the Actual Cost I pay to providers:
Roadlink (Domestic): Based on City: Jeddah (14 SAR), Riyadh (15 SAR), Dammam (16 SAR), Others (19 SAR). Crucial: Add 15% VAT to these base rates.
Aramex/ZidShip (GCC): 43 SAR for the first 0.5kg + 9 SAR for every additional 0.5kg Add 15% to these base rates.
Shipping ROI: Calculate the difference between the 25 SAR collected and the actual cost paid to see the profit/loss on logistics.
4. Returns & Refund Management
Status tags: "A. Refunded" and "B. Not yet refunded".
Refund Logic: Calculate the refund to the customer as: (Item Price + 25 SAR Return Fee).
5. Automated Financial Dashboard (P&L)
Real-time summary of Total Sales, Total COGS, Total Shipping Costs, and Total Net Profit.
Visual charts for sales trends and Top 5 Selling Products (e.g., Tripowin Vivace, Zero:RED).
Accounting for 15% VAT on all domestic transactions.
Technical Skills Required:
Advanced Google Sheets Formulas (VLOOKUP, XLOOKUP, QUERY, IFS).
Google Apps Script (for automation and dynamic updates).
Experience in E-commerce financial modeling and logistics calculation.
Data Visualization (Charts/Dashboards).
Deliverables:
A fully functional, inter-connected Google Sheets file.
A brief guide on how to import new data and manage inventory batches.
EVERYTHING SHOULD BE EDITABLE AND CHANGEABLE SO I CAN UPDATE AND CHANGE EVERYTHING BASED ON MY NEEDS