Can Coda do that? Cost forecast by an average value

1

I am new to Coda and am getting desperate with a task:

I would like to preview my expenses by taking an average of all the individual expenses from previous months (e.g. salary transfers).
My data base looks like this: I have a table for each accounting month from January to April with categories (e.g. personnel) and below that the costs (e.g. salary transfers) and the amounts.
What kind of formula do I need to add up all the salary transfers from January to April and take an average of them? I would then need to apply this formula to all costs (about 20). Is it possible to display all this in a new table?

I am grateful for any tips!
Best regards
Patricia

2

hi @Patricia_Da_Costa , welcome!
yes this is possible as far as I can see. Would you be able to share a dummy doc with the structure you have in mind. That makes it easier to help you solving this puzzle, cheers, Christiaan

3

Hi @Christiaan_Huizer! Thx for your answer. This is how my tables look like and I have one table per month.

Bildschirmfoto 2021-06-03 um 15.59.24
Bildschirmfoto 2021-06-03 um 15.59.241782×716 18 KB

Thx for your help!

4

Thx for the clarification. did you consider a column with the months and columns with the type of costs . In that logic you have one table and at the end you have a column using Average()

If for any reason you are stuck to this logic I would suggest to :

  1. create a table Expenses 2021
  2. create in this table the columns you have in your month tables
  • category Taxes
  • category HRM
  • months
  • amounts

and you apply a filter to only bring in the costs related to the column like Taxes
.
all together, this will become messy and hard to maintain (every month you have to go back to the year table to add a new month with filter in each column). A maybe practical alternative is to put a button on each row and every time you added a value, you click on the button to bring these values to the year table. If you use AddOrModifyRows() this could work. Test it with one and the Year Table, if it works, use it on the others. In the Year Table you get all the data with one extra column ‘the month’. Once you have the data in one table, you can start calculating.

Long story short, the set up of your tables is crucial.

hope it helps a bit.

5

Thank you very much! This is very helpful.

You said: “you apply a filter to only bring in the costs related to the column like Taxes” – is it also possible to apply a filter to bring in the individual costs like “trade tax”, “salary transfers” and “wage tax” or just the whole column “taxes”? How do the formula for the column using Average() look like if I want to know the average costs for salary transfers?

6

yep @Patricia_Da_Costa , the moment you have a column in your month table with individual costs like trade tax or salary transfers, you can reference to it.

I would suggest to have a look about how filters work, anyway it looks a bit crowed this way and this is only for three months.

image
image2115×1655 188 KB

image
image2906×1348 320 KB

list([Expenses JAN 2021].Filter(Detail.Contains(thisRow.Detail)).amount,[Expenses FEB 2021].Filter(Detail.Contains(thisRow.Detail)).amount, [Expenses MARCH 2021].Filter(Detail.Contains(thisRow.Detail)).amount).WithName(Months, months.Sum() / Months.Count())

enjoy!

ps I updated the formula, an error appeared in the outcome, this last version is correct but in my opinion a bit complicated as well

1 Like