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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Replies
2
Views
364
Replies
0
Views
172
Replies
0
Views
450
Replies
0
Views
173
Replies
4
Views
311

1,195,671
Messages
6,011,077
Members
441,581
Latest member
rp4717

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.

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