JackDanIce
Well-known Member
- Joined
- Feb 3, 2010
- Messages
- 9,922
- Office Version
- 365
- Platform
- Windows
Hi,
Looking for guidance to create a Dashboard for quanties of stock, cashflow and other KPIs. I have table of data where:
Column A = Date (ordered, earliest to latest)
Column B = "In" or "Out" (if it's stock received or stock dispatched)
Column C = Product code (e.g. 1, 2, 3, 4, 5)
Column D = Quantity (>0)
Column E = Unit Price of Stock Out
Column F = Unit Price of Stock In (calculated value pasted to cell)
Column G = Cash Out (calculated total amount if cash is spent against stock received for that row)
Column H = Cash In (calculated total amount of cash received against stock dispatched for that row)
Dashboard to show pivot charts for information such as:
Most products sold for given time period
Margin per product line (absolute amount or %) for give time period
Monthly PnL
I understand each pivot chart in the dashboard needs it's own own pivot table for User interactivity (slicers, timelines)
Do I need separate tables for stock movement vs cash movement or with current table add Column I as another formula column with:
=SUMIFS(quantity column, [product type],[Date>=x],[Date<=y])
Using a fixed start row with variable end row ($A$1:$A200) (data set up as Excel table; formulas auto-drag down when new data is added - not worried about end row)
Specifically for margin, data table record entries:
1st day/1st Month Buy 100 product A, cost $100
10th day/1st Month Sell 30 product A, receive $600
20th day/1st Month Sell 20 product A, receive $500
End of month, dashboard charts to show:
Available quantities product A[50: 100-30-20]
Total spend product A[$100: SUMIF(A, "IN", Cost column)]
Total sales product A[50: SUMIF(A, "OUT", Quantity column)]
Total revenue product A[$700: SUMIF(A, "OUT", Cost column)]
Average sale price product A[$22: ($600 + $500)/50]
Average margin $ product A[$12: $22 - $10]
Average margin % product A[120%]
(+repeat for other products, separate charts for expenses)
Links and examples found so far, data tables only track 1 metric e.g. units sold and associated data, e.g. product name, salesperson, area, but this doesn't fit my setup as my table tracks 2 metrics, change in stock and change in cash.
Anyone built a dashboard similar to this or advise any helpful changes to current set up?
TIA,
Jack
Looking for guidance to create a Dashboard for quanties of stock, cashflow and other KPIs. I have table of data where:
Column A = Date (ordered, earliest to latest)
Column B = "In" or "Out" (if it's stock received or stock dispatched)
Column C = Product code (e.g. 1, 2, 3, 4, 5)
Column D = Quantity (>0)
Column E = Unit Price of Stock Out
Column F = Unit Price of Stock In (calculated value pasted to cell)
Column G = Cash Out (calculated total amount if cash is spent against stock received for that row)
Column H = Cash In (calculated total amount of cash received against stock dispatched for that row)
Dashboard to show pivot charts for information such as:
Most products sold for given time period
Margin per product line (absolute amount or %) for give time period
Monthly PnL
I understand each pivot chart in the dashboard needs it's own own pivot table for User interactivity (slicers, timelines)
Do I need separate tables for stock movement vs cash movement or with current table add Column I as another formula column with:
=SUMIFS(quantity column, [product type],[Date>=x],[Date<=y])
Using a fixed start row with variable end row ($A$1:$A200) (data set up as Excel table; formulas auto-drag down when new data is added - not worried about end row)
Specifically for margin, data table record entries:
1st day/1st Month Buy 100 product A, cost $100
10th day/1st Month Sell 30 product A, receive $600
20th day/1st Month Sell 20 product A, receive $500
End of month, dashboard charts to show:
Available quantities product A[50: 100-30-20]
Total spend product A[$100: SUMIF(A, "IN", Cost column)]
Total sales product A[50: SUMIF(A, "OUT", Quantity column)]
Total revenue product A[$700: SUMIF(A, "OUT", Cost column)]
Average sale price product A[$22: ($600 + $500)/50]
Average margin $ product A[$12: $22 - $10]
Average margin % product A[120%]
(+repeat for other products, separate charts for expenses)
Links and examples found so far, data tables only track 1 metric e.g. units sold and associated data, e.g. product name, salesperson, area, but this doesn't fit my setup as my table tracks 2 metrics, change in stock and change in cash.
Anyone built a dashboard similar to this or advise any helpful changes to current set up?
TIA,
Jack
