I really love my spreadsheet. It started back when we were planning The Bibbulmun; the spreadsheet, not my love of it. It was developed a great deal for The Australian Alps Walking Track, and is developing even further for The Heysen, possibly mainly because of my Keto restrictions; and now, I've finally I've cleaned up the shopping list pages (which I've been wanting to do for years).

Creation of Shopping Lists

As detailed in Our Menu Development / Allocation Process the first steps are to:

1. Create a basic menu
2. Allocate menu items across the itinerary
3. Repeat until kilojoules and nutrition levels are getting close to our requirements

Once it's at that point, the shopping list pages can be polished; I say “polished”, because before tonight there was a lot of manual adjustment that needed to be done to get the pages correct and printing nicely. Tonight I've made it more dynamic so that changes anywhere in the cascading sheets are reflected nicely on the shopping lists.

Our Shopping Lists

A quick summary of how this works:

1. Food Items List

Food items are added to the FoodItems sheet, detailing:

• nutrition,
• package weight,
• serve weight,
• cost.

2. Meal Definitions

Food items are used in meal definitions:

• Breakfast,
• Snacks,
• Lunch,
• Dinner,
• Food Drops.

3. Meal Allocation

Meal definitions or individual food items are distributed across the Itinerary. I got a little lazy with some of the allocation and made it a calculation where possible.

4. Shopping List Items

The 'used amounts' are aggregated onto Shopping List Items, where the supplier is added. I guess the actual Supplier could change at the time of purchase depending on their current costings.

5. Shopping Lists

Finally, the shopping list sheet groups the aggregated amounts from Shopping List Items into their Supplier Groups.

It really is wonderful to see the calculations cascade through the spreadsheet…

Some Current Totals

Shopping List Summary

Item	Number
Shopping Items Types	101
Suppliers	15
Total Kilograms	118

Now that everything is plugged in and working in the shopping lists (i.e. the calculations and aggregations), the Food Drop Packing List needs to be checked to make sure all of the food is included in 'packed units'. The two lists should be two different views of the same data:

1. Shopping List: Total amounts grouped by Supplier (shop);
2. Food Drop Packing List: Packets grouped by Food Drop (bucket).

For example:

• Breakfast is composed of muesli and milk powder.
• Muesli and milk powder will appear on the Shopping List as two items, possibly in different supplier groups,
• The Food Drop Packing List it will display a single item, because the two components will be mixed together and bagged into single day bags.

The development of the menu, adding variety and attempting to reduce cost where possible, can continue including the following which is still in progress:

• The longevity test of our home-made bars,
• I'm working through some home nut butter (fat bombs) recipes,
• I'm developing my keto friendly dehydrated soup recipe

Our Resources

The Friends of The Heysen Trail have a huge amount of resources available, which we have obviously availed ourselves of quite liberally. I'll be updating this as required…
Read more...