Production Planner in Excel
The Challenge
A daycare catering company was preparing meals for multiple nurseries, each with its own weekly order of regular meals and children with specific dietary restrictions — allergies, intolerances, and other exclusions.
Each week, every nursery submitted the number of regular meals needed plus a list of children with dietary needs and their attendance schedule. For every restricted child, on every day they attended, every component of every meal had to be checked against an ingredient database to find a safe substitute. A single lunch might have four or five components (main, side, starch, dessert), each requiring individual analysis.
This was done manually — cross-referencing children's restrictions against recipe ingredients, finding appropriate substitutes, then producing separate reports for the kitchen (what to cook and how much) and for each nursery (which children get which substitutes). The company rotated through three different weekly menus, each with its own ingredient database.
The process was time-consuming, repetitive, and safety-critical — a mistake in matching a child's allergy to a recipe ingredient could have serious consequences.
The Solution
We built a comprehensive production planning system in Excel with VBA that automated the entire weekly workflow:
Client Data Collection
Each nursery has its own data entry form where they submit weekly meal counts, children's names, attendance days, and dietary exclusions. Children's details persist week to week — only attendance and meal counts need updating.
Ingredient Database
Every recipe component is mapped against all known allergens and dietary exclusions. The database includes the primary recipe plus multiple substitute options, each with their own ingredient profiles.
Automated Substitute Matching
For each restricted child, on each day they attend, the system tests every meal component against their specific exclusions. When a conflict is found, it automatically steps through available substitutes until a safe option is identified.
Production & Delivery Reports
The system produces kitchen production reports (recipe quantities including substitute portions), nursery delivery reports (which children get which substitutes), diet advice sheets, and flags any unresolved sensitivities for manual attention.
The tool supports three rotating weekly menus, multiple meal types (lunch and tea), and handles the full complexity of multi-component meals where a child might be able to eat some components as normal but need substitutes for others — automatically adjusting production counts accordingly.
We supported and evolved this tool with the client over several years, adding features as the business grew — new nurseries, new menu options, invoicing integration, and reporting refinements.
The Results
3
rotating menus managed
7+ years
in active use
Safety
systematic allergy checking
- ✓ Hours of weekly manual cross-referencing replaced by automated processing
- ✓ Systematic, repeatable allergy checking — every child, every component, every day
- ✓ Unresolved sensitivities flagged automatically rather than discovered in the kitchen
- ✓ Kitchen gets exact production quantities including substitutes — less waste, fewer surprises
- ✓ Each nursery receives a clear report of which children get which meals and substitutes
- ✓ New nurseries and menu changes accommodated without rebuilding the system
Technology
Have a similar challenge?
If you have a complex, manual process that needs to be reliable, repeatable, and fast — we can probably automate it. Let's talk.
Get in Touch