What is the simplest/best way to set up an Excel spreadsheet so it can manage multiple and varied "Parent-Child" relationships?
For example, a friend of mine, who has a small catering business, recently mentioned that although she loves cooking and interacting with her clients, certain aspects of her food supply management and purchasing are time-consuming and confusing — particularly knowing what amounts of individual items to purchase when she makes her grocery store runs 2-3 times per week.
Now I know that there are multiple software applications out there which are designed to manage exactly this type of situation (and I would be happy to get some suggestions re the best apps). However, I have come across this same (parent-child relationships) question before in other projects so I am very interested in learning how best to set it up in Excel.
My first inclination is to create a list of every possible food item she uses in her cooking and meal preparation. The problem is that while some of the items on the list correspond directly to items to be purchased (eg # of baked potatoes needed = # of Russet potatoes to purchase), other food items are made from a recipe of different
items on the list. And some of the latter group may also consist of combinations of yet other food items.
As an example, suppose for an event of 200 people, my friend is planning a buffet which includes the items and quantities shown in Fig. 1. (By the way, she cooks everything from scratch and, once she knows the total number of people to attend, has an uncanny ability to predict just how many servings of each item will be needed.)
Now, suppose the last item, Dessert 2, is pumpkin pie with whipped cream. If that is a single item on the food list, then breaking it down to its base ingredients looks something like this (Fig. 2):
In this screenshot, each item in Columns 1-4 is an item on the foods list but only those items in a black font belong on the shopping
list because the items in a blue font are obviously prepared from other items.
The other issue is, of course, the variable units and the conversions needed between them.
So the objective is to have one sheet tab displaying a simple list of menu options for which my friend can enter the desired number of servings, and a 2nd sheet tab which displays a printable shopping list with the amount of each base item she must either have on hand or purchase.