Multiplying 3 tables to build Profit and Loss Statement

[anamik72]

I am a beginner and could use your help to build a workflow. Thank you so much in advance.

I am given following three input tables:

Table 1 - Amount by Customer by State by Month

Table 1 - Amount Table
Cust ID	State	Jan	Feb	Mar
2	GA	10	15	25
2	CA	20	25	35
2	NY	30	35	45
4	CA	25	30	40
4	GA	15	20	30

Table 2 - Weights table. This table need to multiply with Table 1 (joining by State)

Table 2 - Weight Table
	State	Jan	Feb	Mar
	GA	0.80	0.70	0.50
	CA	0.70	0.60	0.40
	NY	0.60	0.50	0.30

Table 3 - Rates table (for price and cost)

Table 3 - Rates Table
Cust ID	Revenue	Cost
1	10.0	3.0
2	6.0	2.0
3	8.0	1.0
4	7.0	5.0
5	4.0	5.0

Here is the output table:

Intermediate step - multiple table 1 and table 2

Output by multiplying Table 1 and 2 (Join by State)
Cust ID	State	Jan	Feb	Mar
2	GA	8.00	10.50	12.50
2	CA	14.00	15.00	14.00
2	NY	18.00	17.50	13.50
4	CA	17.50	18.00	16.00
4	GA	12.00	14.00	15.00

Here is final output table by applying rates:

Final Output Needed
Line	Cust ID	State	Jan	Feb	Mar
Revenue	2	GA	48.00	63.00	75.00
Revenue	2	CA	84.00	90.00	84.00
Revenue	2	NY	108.00	105.00	81.00
Revenue	4	CA	122.50	126.00	112.00
Revenue	4	GA	84.00	98.00	105.00
Cost	2	GA	16.00	21.00	25.00
Cost	2	CA	28.00	30.00	28.00
Cost	2	NY	36.00	35.00	27.00
Cost	4	CA	87.50	90.00	80.00
Cost	4	GA	60.00	70.00	75.00
Profit	2	GA	32.00	42.00	50.00
Profit	2	CA	56.00	60.00	56.00
Profit	2	NY	72.00	70.00	54.00
Profit	4	CA	35.00	36.00	32.00
Profit	4	GA	24.00	28.00	30.00

[grossal]

Hi @anamik72,

I think I got it:

Output: 

Values seem to be right from what I checked. It's not sorted to exactly match your table yet.

What happens:

1) I Shift all the columns (besides Key-Columns) to rows for easier Join

2) I calculate the Profit right at the beginning with an additional column

3) Join everything based on key + month 

4) Calculate everything (only one formula needed)

5) Shift everything back

6) Order the columns

7) Sort by Line

Workflow attached. Let me know what you think.

Best

Alex

[JosephSerpis · Accepted solution]

Hi @anamik72 I mocked up a workflow and tried to design so it's dynamic as possible so can handle more months and will sort automatically in order. Let me know what you think?

 

[anamik72]

Thank you so much. This took care of it. And I found it very easy to follow. I can now scale it for entire dataset. Much appreciated.

[anamik72]

Thank you Alex. Yes, I follow your logic and it takes care of my issue. Thanks again for helping me out. And for being so prompt.