Designing an Automated Workload Allocation System for Sensus Ekonomi 2026 Using Excel, R, and Python

How data analytics and algorithmic thinking can simplify large-scale field officer allocation while maintaining balanced workloads.

Introduction

Allocating field officers for a national census is far more complex than simply dividing work equally. Each officer must receive a fair workload while satisfying operational constraints, administrative boundaries, and resource availability. When thousands of Statistical Local Units (SLS) are involved, manual allocation becomes inefficient, time-consuming, and highly prone to inconsistencies.

During my internship, I developed a simulation workflow to automate the allocation of field officers for Sensus Ekonomi 2026. Although the original data cannot be published due to confidentiality, this project demonstrates how Excel, R, and Python can be integrated into a single workflow to perform hierarchical workload allocation, workload balancing, and automated officer assignment.

The objective was not merely to distribute officers, but to ensure that every allocation remained operationally feasible while maintaining balanced workloads across households, businesses, and Statistical Local Units.

Project Background

Indonesia’s Economic Census follows a hierarchical administrative structure. Every Statistical Local Unit (SLS) belongs to a village, each village belongs to a district, and each district belongs to a regency or city. Because officer allocation must preserve this hierarchy, the allocation algorithm was designed to follow the same administrative structure.

Regency / City ↓ District ↓ Village ↓ Statistical Local Unit (SLS)

Each SLS contains different numbers of households and business establishments. Because these values vary considerably across regions, assigning one officer to every SLS would create highly unequal workloads.

Instead, workload allocation must satisfy several operational requirements simultaneously.

Project Objectives

The workflow was designed to achieve several objectives:

• Estimate the required number of census officers for each village.
• Balance workloads based on households and business establishments.
• Preserve administrative hierarchy during allocation.
• Automatically transfer excess workloads to neighboring areas when necessary.
• Produce an optimized officer allocation ready for implementation.

Data Sources

The project integrates several datasets provided in Microsoft Excel.

The datasets include:

• Estimated number of officers for each regency/city
• Number of Statistical Local Units (SLS)
• Number of households
• Number of business establishments
• Estimated Economic Census workload
• Number of field supervisors (PML)

For confidentiality reasons, the original datasets are not included in the public repository.

Technology Stack

This project combines three different analytical tools, each serving a specific purpose.

📊 Microsoft Excel

• Data preparation
• Aggregation
• Calibration
• Formula-based calculations

📈 R

• Hierarchical aggregation
• Village-level SLS calculation

🐍 Python

• Workload balancing
• Workload transfer
• Officer plotting
• Visualization

Rather than relying on a single programming language, each tool was selected based on its strengths to create an efficient end-to-end workflow.

Workflow Overview

The overall workflow consists of multiple stages.

Project Workflow

Each stage contributes to ensuring that the final officer allocation satisfies both operational constraints and workload balance.

Methodology

The steps taken to complete this project are as follows:

Step 1 — Data Preparation

The first stage focused on preparing hierarchical data from multiple Excel worksheets.

Business establishments were aggregated from the Statistical Local Unit (SLS) level into villages, while estimated officer requirements were calculated using the projected business counts for 2026.

At this stage, Excel formulas were extensively used to automate aggregation and calibration across administrative levels.

Step 2 — Estimating Officer Requirements

The required number of officers was estimated by calculating the average number of businesses that could reasonably be handled by one officer.

A calibration factor was then introduced to adjust historical business data from 2023 to projected values for 2026.

This calibration ensured that the simulation reflected future census conditions rather than historical observations.

Step 3 — Balancing Workloads

Officer allocation cannot rely solely on the number of Statistical Local Units.

Instead, workloads must also consider:

• Number of households
• Number of business establishments
• Number of SLS

Balancing these three indicators simultaneously produces a much fairer workload distribution.

Step 4 — Workload Transfer Algorithm

One of the most challenging parts of the project was handling situations where an officer exceeded the maximum allowable workload.

Whenever this limit was exceeded, the excess SLS had to be transferred to neighboring villages while preserving administrative hierarchy.

The transfer algorithm followed these rules:

• Transfer to the nearest village within the same district.
• If neighboring villages were already full, continue searching for the next nearest village.
• If an entire district reached capacity, transfer the remaining workload to the nearest district.
• Continue the process until every officer satisfied the workload constraint.

This iterative allocation algorithm was implemented in Python.

Step 5 — Automated Officer Plotting

After workload balancing was completed, officers were automatically assigned to Statistical Local Units.

Each officer received a unique identifier based on administrative codes.

The workflow also recorded whether an assigned SLS belonged to the officer’s original village or resulted from workload transfer.

This additional information greatly simplified future validation and monitoring.

Validation

Producing an allocation is only the first step.

The final result must also preserve all original census totals.

The validation process verified that the allocated data exactly matched the original dataset in terms of:

• Total Statistical Local Units
• Total households
• Total businesses
• Total number of officers

Pivot tables and automated validation scripts were used to ensure no information was lost during processing.

Result Visualization

Sample Visualization Using Histograms and Scatter Plots

To evaluate the quality of the allocation, several visualizations were produced.

The analysis included:

• Scatter plot of households versus businesses per officer
• Histogram of Statistical Local Units assigned to each officer
• Household workload deviation distribution
• Business workload deviation distribution
• Scatter plot comparing workload deviations

These visualizations provide an intuitive assessment of whether workloads are evenly distributed across officers.

Challenges

Developing this workflow involved more than implementing formulas or writing code.

Balancing multiple objectives simultaneously proved to be the biggest challenge.

Improving household balance could reduce business balance, while satisfying the SLS constraint might require transferring workloads to neighboring villages.

Several iterations of the Python allocation algorithm were required before obtaining the most balanced solution.

This experience demonstrated that many real-world analytical problems resemble optimization problems rather than traditional predictive modeling tasks.

Key Takeaways

This project reinforced several important lessons.

• Data analytics extends far beyond dashboards and predictive models.
• Real-world operational planning often requires optimization algorithms.
• Combining Excel, R, and Python can create efficient end-to-end analytical workflows.
• Validation is just as important as the allocation process itself.
• Visualization provides an effective way to evaluate allocation quality.

Conclusion

This project demonstrates how data analytics can support large-scale operational planning through automation and algorithmic thinking.

Although the repository only contains anonymized data and a simulation of the allocation process, the workflow reflects real-world challenges encountered during census preparation.

By integrating Excel, R, and Python, the workflow successfully automates hierarchical officer allocation while balancing workloads across households, businesses, and Statistical Local Units under multiple operational constraints.

I hope this project can serve as a practical example of how analytical thinking and programming can be combined to solve complex operational problems in official statistics and large-scale survey management.

📂 GitHub Repository

Explore the complete source code, notebooks, and project documentation:

https://github.com/anggiealnrn27/Simulasi-Plotting-Petugas-SE2026

🤝 Connect with Me

If you enjoyed this article or would like to discuss data analytics, statistics, or machine learning, feel free to connect with me on LinkedIn:

https://www.linkedin.com/in/anggie-alnurin-prasetya