Excel is commonly used as a capacity planning tool. In this consulting assignment we assisted the client in creating a live document where project start and end dates are overlaid over the available factory capacity to assess where there are bottlenecks and where they need to find more work.
Capacity Planning Report
The end result was a tool in MS Excel which could show the upcoming 15 weeks committed and potential projects, mapped against capacity (including factory downtime).
As shown below there are some initial capacity constraints after Christmas and the committed projects quickly taper off so they need to find new work.
Key features of the Capacity Tool
The key features of the resulting capacity planning tool included:
- The ability to capture both contracted and potential projects. This allowed for more detailed planning with regards start and end dates they were able to commit to.
- Differentiation by job type and project owner.
- Incorporating the original start/ end dates and allowing for changes that occur to these dates based on clients requirements.
- Allowing for a weekly update of the percentage of completion of each project which alters the capacity calculations.
- Calculating the factory usage per project and assessing how far behind or ahead each project is. As shown below each project shows how many tons are outstanding and whether they are ahead of, or behind, schedule.
- Taking into account the capacity of the factory including upcoming downtimes. As shown below, the upcoming capacity constraints are clearly shown.
- Allowing for changes in the production plan to address the capacity issues. The options allowed for in this case were:
- keep planned per week production as per original plan i.e. we cannot change our weekly production on the project.
- change the production to force it to meet the deadline i.e. speed up the average weekly production to meet the deadline (which is likely to affect capacity).
- enter a manual rate per week which is used if some projects are ahead of schedule and we need the capacity for other projects that are behind schedule.
- Graphically showing the next 15 weeks, the factory capacity and the projects utilising the factory in each week.
- Below (1) is the capacity (including Christmas downtime), (2) shows the projects affecting each week, (3) contains a bit more information on whether it is a contracted project or just a potential project.
Capacity Planning Tool Consulting
If you have a requirement for a Capacity Planning Tool in Excel, send an email to info@AuditExcel.co.za.