I’ve got a long term storage business and my base has a table that stores the following:
- Customer Name - Text
- Arrival Date - Date
- Departure Date - Date - Date they left and therefore STOP getting charged
- Setup Revenue - Currency - They get a one-time setup cost
- Cost - Currency - I get charged a daily rate
- Rate - Currency - The daily rate I charge the customer
I’m trying to create a summary table that shows my profitability on a monthly basis.
How do you recommend I accomplish this in Airtable?
If you have a table that combines all transactions (accounts payable and accounts receivable), you can view the total in summary function for the field that contains the dollar amount. On a computer the summary function appears at the bottom of the screen. On a mobile device, you have to pull down to see the summary function.
You can then group transactions by month (on the computer) and see the result of the summary function for each group. You will need a formula field to extract just the month and year so that you can group by month.
On the other hand, this only works with historic data and probably duplicates information that you should be able to get from your accounting software.
If you want projected profitability, things get more complicated.
Although this situation seems simple at first, it could easily get quite complicated, depending on several factors.
- Do you charge per day or per month? (e.g. does February cost the same as January)
- If you charge per month, do you pro-rate for partial months for the first and last months?
- Do you know the departure date in advance?
- Does your accounting use an accrual basis where you defer the income from the setup charge over the course of several months to smooth out spikes in income when new customers start?
Depending on the answers to the above questions, your situation might be too complex to answer in the context of these forums.
This is a tough problem. In a traditional web application we’d separate the data from the reports. I’d use a date range as “inputs” for a given report. It’s hard for me to wrap my head around making everything a new “column” and storing calculated data in a database instead of using a logic-engine to do it.
Thanks for the help and I think you’ve got me going down the right path. I’d love help so please let me know if you think of anything else or if you’d like more details.
We might have some mis-communication here. I am not suggesting that you store calculated data. Every column, even a text based column, can have its own summary function. Having a summary function is different from creating a new formula field, although both use formulas.
If you are looking for a date range as “inputs” you can filter the data base on the date range, and the summary functions will aggregate the data based on only the records viewed.
If you have a paid Pro plan, you can also generate printable reports.
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.