Vendor Report Creation

Conversation
1

Is there a way to add vendor name and then run a report by vendor and amount paid. I like to run report by month, Quarter, Year etc.

Some of the benefits of this report:

  1. Negotating with vendor about pricing based on the amount spent.
  2. Analyzing where money is going the most
  3. Eliminating or reducing few vendors, where possible, will improve cashflow
    Thanks!
2

You have at least a couple options. One is to install the tags manager: Docs: Tags Report for Transaction Tagging
Where you can tag each transaction by the vendor and then use that report to see how much was spent each period

If the vendor as they pulling in a consistent manner in the transactions column you can give each vendor a specific category via AutoCat and use the Docs: Category Tracker
to use that to pull in the details and view by time and category as well. if the transaction details are not congruent each time you can also use AutoCat to help get the naming consistent along with the category.

1 Like
3

Hello @Coach , Great suggestion. I can see how many people could take advantage of a community templates that does this. One thing that I like about using Tiller is it is flexible to allow me to add additional columns to my Tiller Transactions sheet. You can create a dropdown for those cells that reference a list or insert a vendor sheet that the dropdown populates from.

You can create your custom template possibly with a QUERY function

=QUERY(Transactions!A:Z, “SELECT Col1, SUM(Col2) WHERE Col1 IS NOT NULL GROUP BY Col1 ORDER BY SUM(Col2) DESC LABEL Col1 ‘Vendor’, SUM(Col2) ‘Total Spent’”)

You would just need to replace Col1 with the actual column letter for your “Vendor” column (e.g., F) and Col2 with the letter for your “Amount” column (e.g., E).

NotebookLM Suggestion

Based on the sources provided, Tiller is a highly customizable tool that can be adapted to track and report on vendor spending as you’ve described. While there isn’t a built-in “Vendor Report” out of the box, you can achieve your goal by leveraging the flexibility of the Tiller spreadsheet and some of its powerful features.

Here’s a breakdown of how you can add vendor names and run reports, along with the benefits you mentioned:

Adding a Vendor/Merchant Name

The sources indicate a few ways to ensure you have a clean “vendor” or “merchant” name to report on:

  • Use the Merchant Name Column: You can add a column titled Merchant Name to your Transactions sheet. Tiller will then automatically fill this column with a normalized merchant name, which is helpful when descriptions for the same vendor vary (e.g., “Amazon.comsx4056tw3" and "Amzn Mktp Ushi79n7i23” would both be simplified to “Amazon”). This provides a consistent name for reporting.
  • Use AutoCat to Clean Up Descriptions: For even more control, you can use AutoCat, an automation tool, to create rules that not only categorize transactions but also clean up the Description column. For example, you can set a rule that if a transaction’s description contains “Starbucks store 1234,” it should be overridden to simply say “Starbucks”. This gives you a clean vendor name in the Description column itself.

Running Reports by Vendor and Amount

Once you have a consistent vendor name (either in the Description or Merchant Name column), you can generate reports for different time periods using pivot tables. This directly addresses your request to analyze spending by month, quarter, and year.

Here is a step-by-step guide to creating these reports:

  1. Add Necessary Time Columns: Your Transactions sheet already has Month and Week columns. To report by year or quarter, you’ll first need to add a Year column to your Transactions sheet. You can do this by inserting a new column and using a specific formula provided in the sources for Google Sheets or Excel.
  2. Create a Pivot Table: Pivot tables are powerful tools for summarizing transaction data.
    • Select all the data in your Transactions sheet.
    • From the Insert menu, choose “Pivot table”.
    • Configure the pivot table editor:
      • Rows: Drag and drop the Description or Merchant Name column here. This will list each of your vendors.
      • Columns: Drag the Year, Month, or other time-based column here. Excel may automatically group dates by Year, Quarter, and Month, which you can expand or collapse.
      • Values: Drag the Amount column here and ensure it is set to summarize by SUM.
      • Filters: You can add filters to narrow down the report, for example, to only show specific years or vendors.

This setup will create a detailed report showing the total amount paid to each vendor, broken down by the time period you selected. You can create separate pivot tables for monthly, quarterly, and yearly analysis.

How This Addresses Your Stated Benefits

The reporting capabilities you can build in Tiller directly support the business goals you outlined:

  • Negotiating with Vendors: The pivot table report will show you the total amount spent with each vendor over any period. This data is exactly what you need to negotiate pricing based on your spending volume.
  • Analyzing Spending: The report provides a clear, summarized view of where your money is going. You can sort the report to see your top vendors by total amount spent, helping you instantly analyze where your money is going the most.
  • Improving Cash Flow: By identifying high-spend vendors or areas with multiple similar vendors, you can make informed decisions to eliminate or reduce spending, which will improve your cash flow.

In summary, by adding a consistent merchant/vendor name to your transactions and using pivot tables, you can create the exact vendor spending reports you need to achieve your financial analysis and business negotiation goals.

-Alice
Tiller Evangelist

Bluesky, Instagram, Facebook, LinkedIn