Grocery List Cost Estimator

Click to See Full Template

Version
5'732 Downloads
38 KB File Size
January 4, 2022 Updated
0 Number of comments
★★★★★
★★★★★
Rating

Download this template for free
Get support for this template

table of content

How much should you spend on groceries? The Center for Nutrition Policy and Promotion frequently publishes information about the cost of food for the average American. Their reports include four different price levels ranging from thrifty to liberal. For a family of four, the monthly average cost of food ranged from $567.80 on the thrifty plan to $1296.30 on the liberal one.

Another personal finance study found that American households with two people spend an average of $7203 each year on food. This number includes purchasing groceries and eating restaurant meals.

So, there isn’t one “right” number to spend on food for your family. However, this is an item on every household’s budget that can be reduced.

To accomplish this goal, people often use coupons, buy bulk food, and create meal plans. But those tactics come with problems. Coupons and food bought in bulk often expire. And meal planning is not only time consuming in itself, but can also lead to waste - those vegetables bought from the farmer’s market on Saturday afternoon may not be so fresh on Friday evening. If you want another strategy to help you reduce food costs, try using a grocery list cost calculator.

This tool will help you create a grocery list and find the best value for each item at the stores you visit. Then you will always know which grocery store (or stores) you need to stop at. You can also see how much you are going to spend, so you create a reasonable grocery budget for your household.

If you’re ready to create your own grocery list cost estimator, this customizable Excel template can help. The download includes three different worksheets that help you calculate the cost per unit for each of the items you purchase regularly:

Cost Summary Worksheet – Create your personalized shopping list and automatically see which of your stores has the best deal.
Grocery Cost Table – Enter pricing information for the groceries you purchase at the stores you shop at.
Setup Worksheet – Configure the template to meet your grocery list needs. Create your stores, brands, units, and categories list. Then make a master shopping list of the groceries you want to purchase.

These three worksheets are designed to work in unison. You need the information from the setup worksheet to make the formulas work on the others. Together, they will help you save money and find good deals at the grocery store.

Preparing Grocery List Cost Estimator

The first thing you need to do is prepare your spreadsheet. This will help reduce the amount of typing you need to do in the next step. When you open the template, navigate to the Setup worksheet.

Setup

The setup worksheet looks like this:

Grocery List Cost Estimator Setup

The goal of this worksheet is to enter in several pieces of information about your grocery-shopping habits.

Item Name

Now that you have your categories in place, you can create your master shopping list. In the Item Name section on the left side of the template, go ahead and enter the grocery items you purchase regularly.

When you enter an item, take a moment to assign a category. You do this by clicking on the dropdown menu and selecting the appropriate category, like this:

Once again, there is a prepopulated list of common grocery list items to help speed up this process. Use this list as a starting point and update it to reflect what you purchase. You can delete ingredients you never eat and add the ones you do.

Try to list the items and pantry staples you purchase frequently, but remember you can always come back and add more. So, if you forget something, it’s okay.

Brand

Now you need to update the Brands List to reflect the brands of food that you purchase. Your list can include generic brand names like Great Value, or brand names like Trader Joe’s.

To add a brand, simply click on the cell containing Brand 1. Then enter your first brand. Move onto Brand 2 and update that one.

If you don’t use all 20 cells, go ahead and delete the rest. That way they don’t clutter up your grocery list cost estimator.

Unit

When you’re done entering the brands you purchase, take time to review the Unit List. These reflect the way that you purchase items on your list. For instance, you might buy fresh fruits and vegetables by the pound and pick up a bag of breakfast cereal. This means you’d want lbs. and bags on your list.

Many common units are prepopulated for your convenience, but you should double check that everything you need is entered.

Store

Next, update the Store List to include the grocery stores you visit. Simply delete the prepopulated numbered “Marts” and replace each with a store where you shop. Think about all the stores you have loyalty cards with, and make sure to add these.

If there are any generic marts left over, go ahead and delete them and shift the cells up.

At this point, you have finished entering the information you need for the Setup Worksheet. It’s time to move onto the Grocery List.

Grocery Cost Table

This worksheet is the most time-intensive part of creating your grocery list cost estimator. Before you can figure out how much you need to spend at the grocery store, you must gather data to complete your grocery cost table, and to enter grocery prices.

Don’t feel that you have to fill in every single price right now. Get started with what you know and then bring some paper along to the store with you to record some information. If you write down several prices each time you shop for food, you can add those numbers to your worksheet over time.

You can also use grocery shopping apps from each of the stores you visit to gather this information. This is easier, since you can do it from the comfort of your home.

The Grocery Cost Table is the middle worksheet on the Grocery List Cost Estimator Template.

Here’s what it looks like when you open it:

First, you will need to enter the items you want to purchase. If you copy and paste your list of “Item Names” from the Setup Worksheet, you can get this part accomplished quickly. To do this, you can clear the contents of the Item column. Highlight each item on the list, right click, and select “Clear Contents.” Notice that the category list also disappeared. That’s because when you enter the item name, it will automatically populate the category based on what you assigned it on the previous worksheet.

Next, head over to the Setup Worksheet again. Then highlight each entry in the Item column and hit copy. Then go back to the Grocery Cost Worksheet. Click on the first cell in the Item column, and right click. Select the first paste option:

Now you will have your entire list entered, and the template will automatically fill in the correct category name for each item.

It’s time to begin entering price information. Start with one item on your shopping list. Think of all the stores where you purchase this item. Think of which brands you use.

For instance, if you purchase two different brands of laundry detergent at one of two stores, you will need to have four instances of laundry detergent to your list. You can scroll down to the bottom and use the extra lines there to make this simple.

If you right click and add a new row, you will not enter the formulas. So, your spreadsheet will not work correctly. Instead, scroll down and use those blank rows.

There’s plenty of space for you here. So, for each laundry detergent you might purchase, enter in the brand, quantity, unit, store, and price. The brand, unit, and store each have drop down menus to help expedite that data entry.

Once you have this information, the price per unit will automatically calculate. Then you can move onto the next item. Remember to select a different store or brand.

If you aren’t sure on the size, go ahead and just enter a quantity of 1, and the unit as bottle or box or another more generic term. Then you can enter the price.

Since the price in the blue is what shows up on the Grocery Cost Summary page, you may decide to enter everything as a single quantity. Then you can easily see your total costs.

Again, this step will take time to initially set up. But, once you have this information you will be able to quickly identify the best value for each item, across multiple stores. Then the primary grocery shopper in your home will be able to score all the good deals.

Use Your Grocery Cost Estimator

Congratulations, you’ve completed the tedious part of creating a grocery shopping list budget.

Now it’s time to kick back and let Excel do some comparison shopping for you.

Open the Grocery Cost Summary worksheet. You will see two tables. One is labeled, “Price Based on Lowest Price.” The other is, “Price Based on Similar Store.”

If you want to get the best deal on the items you need, even if it means stopping at multiple stores, use the first table. If you want to streamline your shopping trip and only hit one store, use the second one.

For either list, enter the item that you want to purchase. Then you will see which store has the lowest price per unit. The price per unit will show in the total. That’s the blue column on the Grocery Cost Table list. To have it show the total price instead of price per unit, you can change the quantity on the Grocery Cost Table List to 1. Then it won’t divide the price per unit, and you will see the total lowest price.

To use the bottom table, select a store from the drop-down list. If you have price information available for the items at that particular store, you can see them. This is helpful to see which brand is the best value at the store where you are heading.

This template is a great tool to budget for groceries, saving money, and finding the best deals to keep your grocery bills under control.