How to make a Dispatch Management System in Google Sheets: Example, Workflow, Process & Formula

As someone who deeply enjoys leveraging technology to streamline business processes, I recently embarked on an exciting project. A medicine distributor approached me, needing help managing their complex dispatch operations efficiently. They were juggling numerous tasks manually, from recording payments to ensuring timely dispatches — often resulting in confusion, delays, and occasional errors.

Let me take you through this journey step-by-step, sharing insights and practical details from my own experience of creating a robust Dispatch Management System using Google Sheets.

Multiple Stages in Dispatch Process after Billing

My client’s business involved handling hundreds of orders daily.

1. Payment Status
2. Packing Status
3. Dispatch Status

Different payment types (Cash, Card, Credit, Payment Due) further complicated the process.

The Accounts Department would enter initial details manually, after which the operations team had to keep track of statuses manually. This manual tracking often led to missed steps and operational bottlenecks.

To address this, I clearly defined and structured the dispatch management workflow based on extensive discussions with the client and detailed observations of their operations:

1. Initial Bill Entry by Accounts Department: Accounts team enters key bill details including Payment Type (Cash, Card, Credit, Payment Due), Party Details, Bill Numbers, and Payment Status.
2. Payment Verification: Orders marked as “Payment Due” need payment verification. If payment is pending, the status remains at “Pending Payment”.
3. Packing Stage: Once payment is verified, items move to the packing stage. The status becomes “Pending Packing” if packing is not yet completed.
4. Dispatch Stage: After packing is completed, the next status is “Pending Dispatch”.
5. CN (Consignment Note) Confirmation: Post-dispatch, the CN confirmation is critical. Until confirmed, the status remains “Pending CN”.
6. Completion: When the CN is successfully confirmed, the status updates to “Complete”.

To clearly visualize this workflow, here’s an expanded flowchart I made using ChatGPT. I just gave the formula and I gave me this Flowchart:

Accounts enters Bill Details
            ↓
Check Payment Type
  │         ├── Cash/Card → Status: Complete
  │         ├── Credit → Check Packing
  │         │         ├── Packing incomplete → Status: Pending Packing
  │         │         ├── Packing completed → Check Dispatch
  │         │                         ├── Dispatch incomplete → Status: Pending Dispatch
  │         │                         ├── Dispatch completed → Check CN
  │         │                                           ├── CN incomplete → Status: Pending CN
  │         │                                           └── CN completed → Status: Complete
  │         └── Payment Due → Check Payment
            │                ├── Payment incomplete → Status: Pending Payment
            │                └── Payment completed → Check Packing (same flow as Credit)

Once the workflow was clear, the next crucial step was to automate it. Leveraging Google Sheets, I crafted a powerful array formula capable of dynamically updating order statuses, drastically reducing manual intervention and error.

Here is the detailed Google Sheets formula covering all conditions:

=ARRAYFORMULA(IF(H2:H="", "", 
  IF((H2:H="Cash")+(H2:H="Card"), "5. Complete",
    IF(H2:H="Credit", 
      IF(S2:S="", "2. Pending Packing", 
        IF(V2:V="", "3. Pending Dispatch", 
          IF(Z2:Z="", "4. Pending CN", "5. Complete")
        )
      ),
      IF(H2:H="Payment Due", 
        IF(P2:P="", "1. Pending Payment", 
          IF(S2:S="", "2. Pending Packing", 
            IF(V2:V="", "3. Pending Dispatch", 
              IF(Z2:Z="", "4. Pending CN", "5. Complete")
            )
          )
        ),
        ""
      )
    )
  )
))

This complete formula accurately manages every scenario based on the payment type and subsequent process stages.

What this Formula does?

Let’s illustrate clearly with scenarios for each payment type:

Cash/Card Payments: Immediately marked as “Complete” upon Accounts entry, streamlining quick transactions.

Credit Payments:

• No Packing Date yet → “Pending Packing”
• Packing complete but no Dispatch Date → “Pending Dispatch”
• Dispatched but no CN yet → “Pending CN”
• CN Confirmed → “Complete”

Payment Due:

• No Payment Date yet → “Pending Payment”
• Payment received but no Packing Date → “Pending Packing”
• Packed but no Dispatch Date → “Pending Dispatch”
• Dispatched but no CN yet → “Pending CN”
• CN Confirmed → “Complete”

Impact of Dispatch Management System

• Implementing this comprehensive automated system dramatically streamlined operations for the medicine distributor.
• It drastically reduced manual effort, eliminated errors, and significantly enhanced processing speed and accuracy.
• Most importantly, customer satisfaction soared as orders were dispatched reliably and on time.