Guidance: Data table setup for Dashboard (Pivot Charts) Output

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,691
Office Version
  1. 365
Platform
  1. 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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Watch MrExcel Video

Forum statistics

Threads
1,114,013
Messages
5,545,494
Members
410,686
Latest member
Fer9us
Top