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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
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
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top