Actual vs Forecast revenue question

Ask for Help
1

How can I calculate an actual revenue (based on events in the past) and a forecast revenue (based on events in the future) ?
Thanks for your support

2
  • Actual Revenue: would be a sum of sales, yes? Assuming that you are recording sales in a table, then you can use a rollup column to sum all the amounts.
  • Forecast Revenue: how would you calculate this if you were to do it manually?
1 Like
3

Hello Daren

actual = sum of sales up to today
forecast = sum of sales coming in the future (assuming I have a date for each event)

4

Dear Daren

Have you any tips to cover my request?
Basically I record events and split the revenue by participants
I need to report on events and revenue older then today and also report on events and revenue planned in the future
Hope my explanations are clear

Thank you

5

Can you show us a sample of how your data looks like?

6

data set

7

Please allow access to thinh@lowcode.agency. Thank you.

8

Done

9

Which table in the file should I take a look at? And which columns are you using to reference the revenue?

10

Shows table : “Date for the show”
Zicos : “Tip for the zicos” (musician revenue for a specific show)
MyBalance : dashboard for actual revenue vs forecast (sum of credit vs sum of debits)
MyDebs : store all debits made by a musician

actual revenue = sum of all the “Tip for the show” for a musician participating to a show (“Participant”) up to now
forecast = sum of all the “Tip for the show” for a musician assigned to a show (“Participant”) planned in the future (based on date for the show)

11

Hi Experts,

I guess I need to filter the result of a Rollup field based on dates… can this be done?

thanks

12

In the Zicos table, how do you know which show is the tip for or does it not matter? Do those tips for a specific musician or for all musicians and then you divide them later?

13

In Zicos, “Shows Participation ID” relate to Shows table “row ID”.
Tips are recorded for specific musicians.

14

:sob: :sob: :sob: :sob:

15

Assuming rows in Users are musicians you want to calculate the revenue for.

Step 1: In Shows, create an If-Then-Else column: “Future?”. If “Date for the Show” is after now then true, else false.

Step 2: In Zicos, create a lookup table using the “Shows Participation ID” (I would recommend renaming this to Shows ID, it’s the ID of the show and adding the word ‘participation’ in there would be confusing) back to the Shows table.

Step 3: Use a lookup column to pull the “Future?” column into the “Zicos” table.

Step 4: In the “Zicos” table, create an “Actual Revenue” column. If “Future?” is checked then 0, else return the “Tip for the Zicos” value.

Step 5: In the “Zicos” table, create a “Future Revenue” column. If “Future?” is checked then return the “Tip for the Zicos” value, else return 0.

Step 6: In the Users table, create a relation using the name of the user to the “Participant” column in the “Zicos” table (though this is not recommended, you should store the rowID of the user in the “Zicos” table and use that for the relation instead of the name, which may not be unique). Make it a multiple relation.

Step 7: Create a rollup on top of the relation, sum the “Actual Revenue” column. This will be the sum of actual revenue for the musician.

Step 8: Create a rollup on top of the relation, sum the “Future Revenue” column. This will be the sum of future revenue for the musician.

3 Likes
16

Thank you very much for the tips, I will give a try

Cheers

1 Like
17

You are my Hero !!!

:medal_military: :trophy: :medal_sports:
Thank you so much for your support and patience

Cheers

1 Like
18

So it worked! Good to hear.