I have three tables in an app. I need information from two table to use in another.
Table A - needed columns:
[Sales]
Table B - Needed Colums:
[DSO]
Table C - i am using these columns.
I need to calculate:
DSO (month in sales) - Payables (same month payables are due)
Table C (So far):
Calculation (so Far - this doesnt work…)
Tried adding [Sales] from Table A - using a key (shows no data…)
I know i am not explaining it well. I just need help on this…
In plain language, explain what the calculation does, step by step.
I take the (sales) of the month of when my company is (projected) to collect the sales (from today) and minus the payables (by due dates). This will tell the company what their projected income is per month.
Step 1:
DSO - This tells the company how many days from today it NORMALLY collects (for example: 74 days) This means 74 days from today, the company normally collects the sales it did that day.
Step 2:
Payables - these are easy to find. The customers sends the company a bill (with a date).
Step 3:
I need Step 1 - Step 2 = Projected Income for that month.
Tiger:
the month of when my company is (projected) to collect the sales
How is that month determined?
Tiger:
(from today)
What does that mean? The sales from today to the projected collection month?
Tiger:
minus the payables (by due dates)
What does that mean? The payables with due dates within the projected collection period?
DSO (Days Sales Outstanding) is what i go by to tell when my company (projects) to collect the Sales they did today( meaning my company rented/sold $100,000 today - but did not collect that money. The DSO (i already have) gives the company a good average of how fast it collects that $100,000) The DSO - is already done. I did that and it works great.
Again - example. DSO = 74 days. This means, if the company takes $100,000 in sales today (credit), in 74 days it should collect that money…
Payables - is done with the invoices the customers give us (customers we buy stuff from - this is NOT same customers that rent equipment from us). On the invoice - we have a date it is due - For example:
Cust A - we owe them $100,000 due in 74 days.
So if i took both examples:
DSO (74 days) and the sales is $100,000
Payables due in 74 days and total is $100,000
Then in 74 days:
$100,000 (SALES based on DSO) - $100,000 (Payables based on customer) = $0
Eventually we want:
(Example) Projected Income:
Jan - $100,000
Feb - $150,000
March - $200,000
etc…
My problem (i think ) mostly lies in doing all of this by month. Because i am getting projected sales (DSO determines the month in the future) and align this month with the payables due month.
how does Sales Key get its value?
I refer it to Table A “April 2020 Totals”:
This was one way i tried getting [Sales / Invoices] - but didnt seem to work…
It still needs a value, a key column value in the referenced table. How does it get its value?
I put this column in Table C - to refer to Table A. SO i can then get the value of [Sales / Invoice]. So i am thinking the answer is - no value?
I tried another way of getting the [Sales / Invoices] column but it is a list. I need to be able to mathematically use the values:
I think the expression would be something like this:
IF([Today () + DSO] = [Payable due month], [Sales] - [Payable])
This is the simply version. But i tried this and it doesnt work.
Tiger:
I put this column in Table C - to refer to Table A. SO i can then get the value of [Sales / Invoice]. So i am thinking the answer is - no value?
Yep. You need a value there if you intend to use it.
Tiger:
I tried another way of getting the [Sales / Invoices] column but it is a list.
Which row’s Sales / Invoices column value do you want?
Tiger:
I think the expression would be something like this:
Expression for what?
Rows of [Sales / Invoice] - the total per the month it is projected to come in (DSO determines this)
The expression - is for the calculation i am needing.
Sales (Date determined by DSO) - Payables (date given per customer) = (Projected) Income
Tiger:
Rows of [Sales / Invoice] - the total per the month it is projected to come in (DSO determines this)
Sales / Invoice is a column. Columns occur in rows. The April 2020 Totals table presumably contains more than one row. Which of those rows do you want the Sales / Invoice column value from?
Sorry- it will be one value (I only want the “Total”) - right now it is $787,337:
The DSO right now = 74
This means the value in [Sales / Invoice] will be collected - July 3 , 2020 (74 days from today).
Right now - this is to ONLY be a monthly projection (not daily - which would be row by row)
Please engage support@appsheet.com for further help.
ok. Thanks
