When I lived in New York we would have our food delivered monthly. However we found we were throwing a lot of food away as it rotted in the fridge. Eating out and eating ‘unplanned’ meals was the primary cause of this and we become increasingly annoying with the food waste.
My solution was better planning via excel (of course)! The idea being to compile a list of meals, with the exact ingredients listed for each meal. We could then use this list to order the monthly shopping and know we had exactly (and only) what we needed. Frozen meals and gaps were adding into the system to account for eating out or being too lazy to cook a proper meal that day.
Food Planner.xlsm
This is pretty simple compared to the excel stuff I do at work, but unlike that it’s completely shareable and gives a slight glimpse into what I do day-to-day.
In the workbook are several sheets, to allow end-to-end food planning and shopping list creation. Ingredients, contains a list of everything needed for each meal. Menu allows you plan the week, fortnight, or monthly meals. Shopping list shows you what to need (either to buy or check you have in the cupboards. Finally Unique Meals gives you a quick way to check what you can cook.
Ingredients
Contains a complete list of each meal and the ingredients needed to cook it. A level of standardization is required here, specifically not mixing measurements within each food type.
As the list was created when living in the US it’s only partially metric, and care must be taken when converting amounts, for example a US Pint is 473ml but a UK Pint is 568lm. Differences also apply to tablespoons and other non-metric measurements. I would complete the conversion to metric but many of the US measurements are more practical for cooking, measuring flour by a cup or salt by a teaspoon is quicker than using grams).
To handle ingredients in packets I have added a serves field. This allows the workbook to calculate how many packets are required when cooking for different numbers of people without having to use only a portion of a packet (there is some fuzzy logic here so that the system will stretch a serves 4 packet to 5 people rather than require an extra packet for just one additional person.
This is also where you set the number of people you are cooking for. Note the default excel formatting for ‘input’ has been used to keep the sheet as familiar as possible (I also use Excel 2007 – 2010 colour scheme as I prefer it)
Menu
There are 3 menu sheets, for 7 day, 14 day and 28 day food plans. Each menu contains entries for main course and dessert, dinner and lunch. Items can be typed in or copy & pasted (recommended) from the Unique Meals sheet.
Meals in red are not listed in the ingredients sheet and should be added (uses conditional formatting ISERROR / MATCH formula).
Unique Meals
A pivot table listing each meal in ingredients with a button and macro to refresh the table. Used to review what’s in the sheet and for building the menu via copy & paste.
Shopping List
The whole point of the workbook. A set of pivot tables (one for each menu sheet) listing the ingredients and amount for the Menu. The list will only provide whole numbers for each ingredient (so it’s important to use grams not kilograms) and will apply the following logic to give a practical value.
- Where an ingredient is a packet, only the whole amount will be used, for example when buying jelly or a tin of chickpeas. This is on a per meal basis, so if the same meal is features twice each time will use the whole amount (half open tins not kept in the fridge).
- Allow a meal to be ‘stretched’ cooking for 5 people where the meal serves 4. Note this is only an issue with items serving more than 1 person.
- Prevent meals being over stretched, the opposite of the above.
To accomplish the above there is a set of hidden fields in the ingredients sheet.
Meal count in menu
First the number of times a meal appears in a menu is totaled using a simple countif.
=COUNTIF(Menu7[[Main meal]:[Lunch Dessert]],[@Meal])
Amount to buy
Next the amount of the item is calculated, rounded to allow for ‘stretch’
- If the meal count is 0 then all ingredients for that meal will be zero.
- If the meal count not 0 but the rounded value (to nearest integer) for the item is zero (less that 0.5) the value will be set to 1, to ensure that ingredient is available.
- If the above is not true then the amount will be the number of people cooking for divided by the number of people the item serves, multiplied with the number of times that meal appears in the menu.
=IF([@[Meal count (7)]]=0,0,IF(ROUND(CookingFor/[@Serves],0)=0,1,ROUND(CookingFor/[@Serves],0)))*[@[Meal count (7)]]*[@Amount]
All of the above is then used to populate the pivot table to provide the shopping list.
Possible improvements
There are a few things I would like to do to improve the sheet, either to make it more efficient or to lean and implement new skills.
Better management of items where the requirement is less than 0.5 for a meal
Where very small amounts of an item are required for a meal it might make more sense to allow fractions, especially when the item is required for more than one meal. This would prevent 2 instances of .05 of an item multiplying to 2, doubling what would actually be needed. It should be noted at this stage that the actual amount to add when cooking is defined by the recipe not the food planner, so that even if the workbook lists 1 of an item where the true value is 0.5 then 0.5 will be the amount added to the pot.
Replacing formulas and pivot tables with VBA
This would be a more elegant solution than having fields with formulas and then pivot tables connected to them. Not really needed but it would by fun to code and have practical implications for other sheets. Having talked to excel developers it seems that VBA is the preferred option as it is less resource hungry and much harder to introduce and calculation error (very import in a business environment). VBA has some really good debugging options as well which make it nice to work with, and it’s less likely to be messed up by users.
Download the workbook here: Food Plan
