ManagementBook

This is a sample Dashboard based on Bicycle Sales data which is included with the product installation. It connects to a simple local cube file - BicycleSales.cub.

User Selections: • User selections are through two slicers, on Month and PoS. The grids in the workbook select the slicer directly; the formulae are based on the cells which the slicer updates. o Time Slicer: updates $D$2 o PoS Slicer: updates $G$2 Dashboard Worksheet: • KPI table (B5:G13) o Consists of an XLCubed Grid (B5:D13)  Grid header area is hidden in grid properties – appearance  Time and PoS are in the header area, and select the respective slicers  Sparkline is based on selected month plus previous 11 members o and XLCubed cube and chart formulae (E5:G13)  Variance % column uses an XL3Link() to allow the user to jump to detail on the selected KPI  Bullet Graph in column G, built through an in-cell chart formula, is based on the Target YTD%

• Region Table (B18:G24) o Consists of an XLCubed grid (B18:F24), and and an in-cell chart formula (G18) o Time and PoS are in the header area, and select the respective slicers

• Top 10 Regions / Products o Xl3RankLookup and ValueRankLookup formulae o Product also uses XL3Link() to jump to detail

• Product Groups & HeatMap o Built from XLCubed formula based data on hidden sheets Variance Analysis Sheet: • Built using one XLCubed grid, with embedded in-cell charting o It is restricted by the Pos and Time slicers o For Key Figures, it uses VarianceAnalysis!D3 , which is populated by the XL3Link formulae on the KPI table within the Dashboard sheet. o Contains an additional XL3Link in column E, contained within a grid calculation, to allow jump to detail on the selected product.

Time Series Chart Sheet: This contains a detail table and chart of value and target for the selected period, product, PoS, and the Key figure passed in by the xl3Linkformulae in ‘Variance Analysis’!$E$9: $E$29. Cell $O$8 is based on the selected month, and C:N use the XL3MemberNavigate() formula to choose the previous month.=, overall delivering a rolling 12 months.

• • Note that this could also be achieved in a grid, by driving the date hierarchy from $C$5 and choosing the ‘previous 11’