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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Watch MrExcel Video

Forum statistics

Threads
1,114,021
Messages
5,545,539
Members
410,690
Latest member
navneetr
Top