Comprehensive Excel-based Commercial Operating System

Project Description (for an Excel Expert)

Overall Objective

The objective is to build a single Excel file that functions as a commercial operating system: task management, customer orders, status tracking, communication logging, and time-based planning, all fully interconnected and controlled through a central dashboard.

This is not a simple tracker, but a mini-CRM combined with Order Management and Project Planning, built entirely in Excel.

⸻

Logical Structure of the File

Central Dashboard (Dashboard sheet)

The dashboard is the main entry point and control center of the system.

Main functions:
• Real-time KPIs:
• Total, open, and closed tasks
• Paid versus unpaid orders
• Orders in production, ready, and shipped
• Global filters:
• Customer
• Producer
• Payment status
• Production status
• Shipping status
• Time period (order date / delivery date)
• Quick navigation:
• Click to task details
• Click to customer details
• Click to producer details

From a technical perspective: pivot tables, slicers, and structured formulas, with no manual data duplication.

⸻

Tasks / Orders Sheet (core of the system)

This is the core of the project.

Each row represents one task, corresponding to one customer order.

Main fields:
• Task ID (primary key)
• Customer (lookup)
• Producer (lookup)
• Product / description
• Order value
• Payment status (enumerated)
• Production status (enumerated)
• Shipping status (enumerated)
• Order date
• Production start date
• Expected completion date
• Shipping date
• Priority
• Operational notes

This sheet feeds the entire system, including the dashboard, calendar view, Gantt view, customers, and producers.

⸻

Conversation Management (relational log)

Conversations should not be scattered across multiple places.

A dedicated Conversations sheet is structured as a relational database.

Fields:
• Conversation ID
• Date
• Type (email, call, WhatsApp, meeting)
• Contact (customer or producer)
• Linked Task ID
• Conversation text or summary
• Next action (optional)

This allows viewing all conversations related to a specific task, as well as filtering conversations by customer or producer.

⸻

Customers Sheet (CRM)

A dedicated sheet exclusively for customers.

Fields:
• Customer ID
• Company name
• Country
• Email
• Phone
• Payment terms
• Notes

Dynamic views include linked orders, order status, and conversation history.

The customer is treated as a relational entity, not just a static contact record.

⸻

Producers / Vendors Sheet

This sheet mirrors the Customers sheet from the supplier perspective.

Fields:
• Producer ID
• Company name
• Contact person
• Average production lead time
• Country
• Technical notes

Key functions include viewing all linked tasks, filtering conversations related to the producer, and analyzing delays and operational bottlenecks.

⸻

Calendar View (time-based)

Tasks must be visible by date, not only as a list.

The calendar view is based on:
• Order date
• Production dates
• Shipping dates
• Critical deadlines

This view supports workload management and weekly or monthly planning.

⸻

Gantt View (project management)

Each task is also represented as a time-based project.

Gantt phases per task include:
• Payment phase
• Production phase
• Shipping phase

This enables identification of overlapping activities, detection of bottlenecks, and analysis of structural delays.

⸻

Key Concept to Emphasize

The goal is to move away from disconnected sheets and build a relational Excel model with normalized tables, dynamic dashboards, and time-based views such as calendar and Gantt charts, without data duplication.

⸻

Implicit Excel Technologies
• Structured tables
• Power Query (optional)
• Advanced pivot tables
• Synchronized slicers
• Indexes and lookups
• Advanced conditional formatting