Advanced Excel-Based Capacity Planning Model
Budget: ₹600 – ₹1,500 INR
Create an advanced Excel-based Capacity Planning Model for manufacturing machine scheduling with the following sheets, formulas, and dashboards.
1. Machine Master Sheet
Create a sheet named Machine_Master with columns:
Machine ID
Machine Name
Machine Type
Available Hours per Day
Shift Count
Efficiency %
Planned Downtime Hours
Actual Utilization %
Capacity per Hour
Daily Capacity
Weekly Capacity
Status (Available / Maintenance / Breakdown)
Formula: Daily Capacity = Available Hours × Capacity per Hour × Efficiency %
---
2. SKU Master Sheet
Create a sheet named SKU_Master with columns:
SKU Code
SKU Description
Product Family
Batch Size
Customer Priority
Order Quantity
Due Date
Priority Rank
Standard Lead Time
FG Release Required Date
Priority formula: Sort by:
1. Customer Priority
2. Due Date
3. Priority Rank
---
3. Routing Sheet
Create sheet Routing_Master
Columns:
SKU Code
Operation Sequence
Operation Name
Machine ID
Setup Time (min)
Run Time per Unit (min)
Transfer Time (min)
Queue Time (min)
Yield %
Example: SKU001 → Op10 → Machine M01
SKU001 → Op20 → Machine M03
SKU001 → Op30 → Machine M07
---
4. Lead Time Calculation Sheet
Create sheet Lead_Time_Calc
Columns:
SKU Code
Batch Size
Total Setup Time
Total Run Time
Total Queue Time
Total Transfer Time
Total Manufacturing Lead Time
Planned Start Date
Planned End Date
Formula: Run Time = Batch Size × Run Time per Unit Total Lead Time = Setup + Run + Queue + Transfer
---
5. Machine Utilization Sheet
Create sheet Machine_Utilization
Columns:
Machine ID
Available Capacity Hours
Assigned Load Hours
Utilization %
Idle Hours
Overload Hours
Formula: Utilization % = Assigned Load / Available Capacity
Conditional formatting:
Green < 75%
Yellow 75–90%
Red > 90%
---
6. Machine Queue Scheduling Sheet
Create sheet Machine_Queue
Columns:
Machine ID
Sequence Number
SKU Code
Batch Number
Previous Batch End Time
Start Date Time
End Date Time
Processing Hours
Priority
Status
Rules:
Each machine can run only one batch at a time
Next batch starts only after previous batch ends
Sequence should follow:
1. Priority
2. Due date
3. Machine availability
No overlapping batches on same machine
If SKU has multiple operations, next operation starts only after previous operation completed
Automatically calculate machine-wise queue
Formula logic: Start Time = MAX(previous batch end time, previous operation end time) End Time = Start Time + Processing Time
---
7. FG Release Planning Sheet
Create sheet FG_Release
Columns:
SKU Code
Batch Number
Last Operation Machine
Final Operation End Date
QA Hold Time
Packing Time
FG Release Date
Delay vs Due Date
Formula: FG Release Date = Final Operation End + QA + Packing
---
8. Dashboard Sheet
Create visual dashboard named Capacity_Dashboard
Include:
KPI Cards
Total Machines
Total Capacity Hours
Used Capacity Hours
Capacity Utilization %
Total Orders
Delayed Orders
On-Time Orders
Bottleneck Machine
Charts
1. Machine Utilization Bar Chart
2. SKU Order Status Pie Chart
3. Daily Capacity vs Load Trend
4. Bottleneck Machine Chart
5. FG Release Timeline
6. Priority Order Tracker
Filters / Slicers
Date
Machine
Product Family
SKU
Priority
---
9. Advanced Features
Include:
Auto Bottleneck Detection
Identify machine with: Highest utilization % or longest queue
Capacity Alerts
Show:
"OVERLOADED"
"UNDERUTILIZED"
"DELAY RISK"
What-if Analysis
Allow user input:
Additional machine
Extra shift
Efficiency improvement
Then recalculate:
Capacity
Utilization
FG release dates
---
10. Excel Automation Logic
Use formulas:
XLOOKUP
INDEX MATCH
SUMIFS
COUNTIFS
MINIFS
MAXIFS
IFERROR
WORKDAY
NETWORKDAYS
Conditional Formatting
Dynamic Charts
Pivot Tables
VBA optional for auto scheduling
---
Final Output Required
The model should automatically show:
Which SKU batch runs on which machine
Start date/time
End date/time
Machine sequence
Capacity loading
Utilization %
Bottleneck machines
Final FG release date
Delay against customer due date
---
Model Objective
Build a professional manufacturing planning model that helps:
Capacity planning
Production scheduling
Machine loading
Delivery planning
Bottleneck identification
Management reporting
1. Machine Master Sheet
Create a sheet named Machine_Master with columns:
Machine ID
Machine Name
Machine Type
Available Hours per Day
Shift Count
Efficiency %
Planned Downtime Hours
Actual Utilization %
Capacity per Hour
Daily Capacity
Weekly Capacity
Status (Available / Maintenance / Breakdown)
Formula: Daily Capacity = Available Hours × Capacity per Hour × Efficiency %
---
2. SKU Master Sheet
Create a sheet named SKU_Master with columns:
SKU Code
SKU Description
Product Family
Batch Size
Customer Priority
Order Quantity
Due Date
Priority Rank
Standard Lead Time
FG Release Required Date
Priority formula: Sort by:
1. Customer Priority
2. Due Date
3. Priority Rank
---
3. Routing Sheet
Create sheet Routing_Master
Columns:
SKU Code
Operation Sequence
Operation Name
Machine ID
Setup Time (min)
Run Time per Unit (min)
Transfer Time (min)
Queue Time (min)
Yield %
Example: SKU001 → Op10 → Machine M01
SKU001 → Op20 → Machine M03
SKU001 → Op30 → Machine M07
---
4. Lead Time Calculation Sheet
Create sheet Lead_Time_Calc
Columns:
SKU Code
Batch Size
Total Setup Time
Total Run Time
Total Queue Time
Total Transfer Time
Total Manufacturing Lead Time
Planned Start Date
Planned End Date
Formula: Run Time = Batch Size × Run Time per Unit Total Lead Time = Setup + Run + Queue + Transfer
---
5. Machine Utilization Sheet
Create sheet Machine_Utilization
Columns:
Machine ID
Available Capacity Hours
Assigned Load Hours
Utilization %
Idle Hours
Overload Hours
Formula: Utilization % = Assigned Load / Available Capacity
Conditional formatting:
Green < 75%
Yellow 75–90%
Red > 90%
---
6. Machine Queue Scheduling Sheet
Create sheet Machine_Queue
Columns:
Machine ID
Sequence Number
SKU Code
Batch Number
Previous Batch End Time
Start Date Time
End Date Time
Processing Hours
Priority
Status
Rules:
Each machine can run only one batch at a time
Next batch starts only after previous batch ends
Sequence should follow:
1. Priority
2. Due date
3. Machine availability
No overlapping batches on same machine
If SKU has multiple operations, next operation starts only after previous operation completed
Automatically calculate machine-wise queue
Formula logic: Start Time = MAX(previous batch end time, previous operation end time) End Time = Start Time + Processing Time
---
7. FG Release Planning Sheet
Create sheet FG_Release
Columns:
SKU Code
Batch Number
Last Operation Machine
Final Operation End Date
QA Hold Time
Packing Time
FG Release Date
Delay vs Due Date
Formula: FG Release Date = Final Operation End + QA + Packing
---
8. Dashboard Sheet
Create visual dashboard named Capacity_Dashboard
Include:
KPI Cards
Total Machines
Total Capacity Hours
Used Capacity Hours
Capacity Utilization %
Total Orders
Delayed Orders
On-Time Orders
Bottleneck Machine
Charts
1. Machine Utilization Bar Chart
2. SKU Order Status Pie Chart
3. Daily Capacity vs Load Trend
4. Bottleneck Machine Chart
5. FG Release Timeline
6. Priority Order Tracker
Filters / Slicers
Date
Machine
Product Family
SKU
Priority
---
9. Advanced Features
Include:
Auto Bottleneck Detection
Identify machine with: Highest utilization % or longest queue
Capacity Alerts
Show:
"OVERLOADED"
"UNDERUTILIZED"
"DELAY RISK"
What-if Analysis
Allow user input:
Additional machine
Extra shift
Efficiency improvement
Then recalculate:
Capacity
Utilization
FG release dates
---
10. Excel Automation Logic
Use formulas:
XLOOKUP
INDEX MATCH
SUMIFS
COUNTIFS
MINIFS
MAXIFS
IFERROR
WORKDAY
NETWORKDAYS
Conditional Formatting
Dynamic Charts
Pivot Tables
VBA optional for auto scheduling
---
Final Output Required
The model should automatically show:
Which SKU batch runs on which machine
Start date/time
End date/time
Machine sequence
Capacity loading
Utilization %
Bottleneck machines
Final FG release date
Delay against customer due date
---
Model Objective
Build a professional manufacturing planning model that helps:
Capacity planning
Production scheduling
Machine loading
Delivery planning
Bottleneck identification
Management reporting