Despite the horrors of Hurricanes Eta and Iota, and the disastrous effects people are still facing months after the events, people came together to support each other, even more amazing because the effect of the pandemic on Honduras’ economy has been incredibly damaging. I was also thankful to see my churches in Houston reach out to see how they could help.
I knew it would be a lot of work to buy food for emergency relief, but what I didn’t realize was how much time I would spend comparing prices. Post-hurricane price gouging has been a real problem. I decided on three different stores that would 1. provide receipts, 2. let me see prices online, and 3. have the option to purchase online and pick up from the store.
I realized pretty quickly that I needed a system to keep track of prices. The different measurement systems and item sizes were making my head spin. I also wanted to be able to keep track of the product itself using a link, and separate out my final decisions by store to make a shopping list.
I made a messy version in Google Sheets that did the trick, but I spent a little bit of time making a cleaner version yesterday because I know I will be needing it in the near future. It has made comparing prices so much simpler. I also made a product entry form so that, as I’m shopping online, I can add the products one by one and then decide which one I want at the end.
Here is the version I am using now: the Bulk Purchase Calculator, with the Product Entry form. You can make edits by making a copy and play around with it.
Calculator Details
The first step is to use the Product Entry form to add product entries to the list. It is important to gather as much product data as possible before starting the selection process (see Improvement Ideas for why). New items must be added using the form, or by making the Entries sheet unhidden and manually adding new items there. New items cannot be added to any other sheets.
Once you are satisfied with the product entry list, you can select from the list of products to add them to the final Bulk Purchase Calculator list (if new products are added using the form, you will have to review which items are checked because the checked items will have shifted). These can be added or removed by clicking the check box next to each product. The products are sorted by name and then by price per unit, with the cheapest product coming first. It also shows the brand, the product link page, and other details to help with the decision. This is important because some products might be slightly cheaper, but are much lower in quality. Rice was one of these products: for just a few lempiras more, the quality of the rice improved drastically.
The next tab has the selected items. Here is where we make the decisions: how many families are we purchasing for? What is our budget? How many of each item are we giving to the family? These values are changeable, and you can play around with the quantities to get as close to the budget as possible. It also shows the number of extra units: if purchasing an item in a set, there might be extras.
The Shopping List sheet tab uses a simple pivot table to display the items organized by store, with the total sum of money needed for each store. This sheet is not meant to be edited, as explained in the instructions at the top of the page. It includes all the relevant information needed for anyone to be able to make the purchases.
There are a few hidden sheet tabs, including the raw form entries page and a Price Per Unit calculation page that takes the price and divides it both by the number of items in that set and the measurement of each item.
Improvement Ideas
Ways to improve this calculator would be to have an automatic measurement converter to ensure that we are comparing prices based on the same measurement system: ounces to grams, for example. Right now I just have to keep track of that in my head as I am making the product entry or notice what the measurement types are in the Product Comparison chart.
Another way to improve would be to be able to maintain the selected product list even if I add new products to the list of possibilites later on. Currently, because of the way the formulas work, if I add a new entry, I have to go back and double check that the correct items are selected (they probably won’t be selected because the rows will shift but the check boxes will not). I decided to do it this way because otherwise new entries would override my checkboxes and calculation formulas in order to insert a new row, which is more of a pain to fix than just double checking that the correct products are selected.
The ultimate goal would be to have an app that checks my selected stores automatically for the type of product I want and pulls the information from the product page automatically, but the websites here have issues like different spacing in the text (300 g vs. 300g, for example), different languages, different spellings (so many ways to spell “spaghetti” in Spanish), etc. This might be a task better fit for websites that are somewhat more standardized in their presentation of the product. It was fun to make an OOP spike of what this could look like, though. (The OOP version of this would get rid of many of my issues I had before.)