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

#### JackDanIce

##### Well-known Member
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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Replies
12
Views
209
Replies
0
Views
150
Replies
4
Views
359
Replies
7
Views
443
Replies
0
Views
403

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,822
Messages
5,772,469
Members
425,760
Latest member
zj042060

### 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.

### Which adblocker are you using?

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

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