Tips

Spreadsheet Grocery Budget Management

Updated on 2021-06-11

The process of using a spreadsheet to manage my grocery budget began in 2018 when I put together a LibreOffice Calc spreadsheet workbook (file of multiple spreadsheets), titled price–history, to track prices of purchased items. I wanted to keep track of prices so that I could recognize price increases, both obvious or phantom; e.g. quantity reductions for the same price. I mainly use receipts from my shopping trips to populate the spreadsheet, but I sometimes go from memory and I’ve also taken pictures of shelf prices using my smartphone.

Up until about two and a half years ago, I didn’t worry much about staying within my grocery budget. I did have a budget and I tracked what I spent on groceries, but I frequently went over budget. With no income and having gone through all of my retirement money, my grocery budget became much smaller when I began relying on the generosity of my wife.

Initially, I put together a rough LibreOffice Calc spreadsheet, naming it grocery–shopping–list, as an aid in budgeting my monthly grocery purchases. However, too many times I didn’t make the time to use the spreadsheet and it was cumbersome to use for purchases from multiple stores. After going over budget on a monthly basis for most of 2020, I decided things needed to change. So I made time to put together a more sophisticated grocery shopping list spreadsheet workbook that better enables me to stay within my monthly grocery budget.

I currently shop at three different stores for my grocery needs; Walmart, Wegmans, and Whole Foods Market. Therefore, my grocery shopping list spreadsheet workbook contains a separate spreadsheet for each of the aforementioned stores and a spreadsheet to cover planned purchases at other stores, physical and online.

Product pricing is obtained from my price history spreadsheet workbook. When price changes occur, I record the change in the price history spreadsheet workbook. I use the most current product price when planning my monthly and future grocery purchases.

I have a summary spreadsheet that combines all of the data from the separate store spreadsheets using formulas and range names. My monthly grocery budget available balance is recorded on the summary spreadsheet.

I have a sales tax spreadsheet that has the sales tax rates for taxable items in my state. The rates are used via range names in formulas in the seperate store spreadsheets to generate totals for purchases.

After using the more sophisticated spreadsheet workbook for awhile, I decided to add a spreadsheet for future purchases and a spreadsheet for items that I purchase on a monthly recurring basis. I copy and paste the data from the monthly purchase spreadsheet into the corresponding store spreadsheets.

I highlighted spreadsheet cells containing formulas in yellow as a reminder not to enter anything into the cells. Entering data into a cell containing a formula would wipe out the formula.

As I spend money, I adjust the available balance on the summary spreadsheet, obtaining the balance from my grocery money set aside account in GnuCash.

Changes can easily and quickly be made since I’m using spreadsheet technology. If I decide I want to add one or more items, I can quickly and easily add the item(s) to the applicable store spreadsheet. If the summary spreadsheet shows that I may overrun my monthly grocery budget, I can move items to the future purchases spreadsheet.

When I’m ready to go shopping at one of the stores at which I regularly shop, I go old school and handwrite a shopping list, writing down the items to purchase from the applicable store spreadsheet. When I get back from shopping, I remove those items that I have purchased from the applicable store spreadsheet.

Since creating and using the more sophisticated spreadsheet workbook, I have not exceeded my monthly grocery budget. In fact, there have been some months that have ended with a surplus.

Hopefully before the year is over, I’d like to learn SQLite to see if the database format can be used as a replacement for my purchase price history spreadsheet. If SQLite can be used to create a price history database, then I should be able to use the PortoDB app on my smartphone and always have pricing information with me whenever I go shopping.

You can download the purchase price spreadsheet workbook and the grocery shopping list spreadsheet workbook here, as a 7-Zip (7z) compressed file. The compressed file contains spreadsheet workbooks in both OpenDocument spreadsheet format (.ods) and Microsoft Excel format (.xlsx). You should also download and decompress the GPG signature for the compressed file to verify that it has not been tampered with. You will also need the Shared Bits public key and verification software.

I suggest making copies of the spreadsheet workbook files, renaming the copies for your use. Making copies will allow you to refer back to the originals in case you accidently erase something or change a formula that ends up not working correctly. You’ll also need to change the some of the tab names and some of the range names in the grocery shopping list spreadsheet workbook to reflect the stores at which you shop.

Feel free to leave your question or comment in the Comment box at the bottom of the page.

2021-006

Leave a Reply

Your email address will not be published. Required fields are marked *

These Ten Things Will Happen Next as the Conspirators Tighten the Screw
This is default text for notification bar