Skip to main content

I would like to recreate a transaction sheet and P&L sheet from Google Sheets into Airtable. In Sheets, there is a "Transactions" sheet with all of the company's expenses. The relevant columns for the P&L sheet are data, amount, category 1, subcategory 1, subcategory  2, and subcategory 3. On the "P&L" sheet, which tracks the transactions by categories and month, I am using SUMIFS equations to get the weekly and monthly totals for each category, as well as the total expenses for each month. For example, this is a snippet of the table. It is continuous for the whole year. 

I would like to recreate this in Airtable. Right now, I have a "Transactions" table in Airtable. The first field in the date, then a field for the category (Employee, SAAS, contract, office), then a field for the first subcategory (USA salary, PH Salary, Intern, Travel, Sales & marketing, etc.) and then another field for the second subcategory (data research, website, PA, etc). How can I create a P&L sheet that breaks the transactions down by weekly, monthly, and categories, and two subcategories? 

I think you're going to need another table for this, and you'll create a rollup field with a "SUM(values)" in it to get the data

To set the data up, you're going to need to create one record per piece of data you want to see.  So for example, assuming you want to see:
1. Weekly, Employee, PH Salary, QA
2. Monthly, Employee, PH Salary, QA

In this new table you'd need two records, each representing:
1. [Week number] [Year], Employee, PH Salary, QA
2. [Month number] [Year], Employee, PH Salary, QA

And you'd need to link transactions that fit those categories to the appropriate records in the new table

For example, if you have a transaction that is for Employee, PH Salary, QA for today, 13 July 2023, you'd link it to:
1. Week 28, 2023, Employee, PH Salary, QA
2. July 2023, Employee, PH Salary, QA

This data structure would allow you to create rollups that would sum the data like you want

It sounds complicated, but it's fairly easy to set up and can be fully automated; once everything is set up the only thing you'd need to do is create new transactions

Reply


Terms & Conditions