Dynamic Resource Allocation Model

Project Overview

This project is an Excel-based dynamic resource allocation model designed to distribute a fixed reward pool across participants using a transparent and configurable scoring framework.

The model combines two key factors:

• participation level
• support need

These factors are converted into weighted coefficients, combined into a final priority score, and then used to allocate rewards proportionally.

---

Business Problem

When a limited pool of resources must be distributed fairly, equal allocation is often inefficient because it ignores differences in contribution and support needs.

This model solves that problem by introducing a rule-based scoring system that:

• rewards consistent participation
• increases support weight for lower-benchmark participants
• limits distortion from extreme outliers
• keeps the allocation logic transparent and adjustable

---

Model Logic

1. Activity Coefficient

Measures participant activity during the evaluation period.

Formula:

ActivityCoef = Days Active / Max Days

---

2. Support Coefficient

Measures support need relative to a target benchmark.

Formula:

GearCoef = MAX(0, MIN(1, (Target Benchmark - Current Level) / Support Range))

This coefficient is normalized and clamped to the 0–1 range:

• participants at or above the benchmark receive 0 additional support weight
• participants significantly below the benchmark are capped at 1
• values inside the range scale proportionally

This makes the model more robust and prevents extreme outliers from distorting allocations.

---

3. Priority Score

Combines participation and support need using configurable weights.

Formula:

Priority Score = ActivityCoef × Activity Weight + GearCoef × Support Weight

Participants with zero activity are excluded from allocation:

• if ActivityCoef = 0, then Priority Score = 0

---

4. Reward Allocation

The reward pool is distributed proportionally based on each participant’s final priority score.

Formula:

Reward = Priority Score / SUM(Priority Score) × Reward Pool

---

Parameters

The model is driven by a dedicated Parameters sheet, which allows the scoring logic to be adjusted without changing formulas.

Main parameters:

• Reward Pool — total resources available for allocation
• Activity Weight — weight assigned to participation
• Support Weight — weight assigned to support need
• Max Days — maximum number of active days in the evaluation period
• Target Benchmark — reference level used to calculate support need
• Support Range — gap from the benchmark at which the support coefficient reaches its maximum value of 1

---

File Structure

The workbook contains three sheets:

• Parameters — configurable model inputs
• Online_Sample — sample participant input data
• Allocation_Model — calculation logic for coefficients, priority scores, and final reward allocation

---

Why This Model Matters

This project demonstrates how a limited resource pool can be distributed fairly using a parameter-driven weighted scoring model.

The design emphasizes:

• explainability
• fairness
• flexibility
• outlier-safe scoring
• proportional allocation

Although the original inspiration came from reward distribution, the framework can be applied more broadly to other allocation problems.

---

Transferable Use Cases

This type of model can be adapted for:

• team bonus allocation
• training budget distribution
• mentorship prioritization
• internal support resource planning
• project funding allocation

---

Skills Demonstrated

• Excel modeling
• weighted scoring design
• parameter-driven logic
• normalization and coefficient capping
• proportional allocation
• explainable decision modeling

---

Key Takeaway

This project is not just a spreadsheet calculation. It is a structured decision model that translates contribution and support need into a transparent allocation framework that can be reused across different business scenarios.