Multiplying 3 tables to build Profit and Loss Statement
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
Solved! Go to Solution.
- Labels:
- Datasets
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
