Regional Sales Excel Dashboard
Regions: North America, South America, Asia, Europe, Australia
Divisions: Game, Productivity, Utility
In the Revenue by Division Section, we can see the sales of any chosen division in various regions. However, there will be an error sign if you select more than one division at once.
Function Used: Pivot table, slicer, if(), max(), min(), average(), column chart, index(), match(), conditional formatting, VBA - for auto fresh and hiding sheet
In the Revenue by Region Section, we can see the sales of various divisions in selected regions.
Function Used: Pivot table, slicer, sumifs(), if(), max(), min(), average(), combining three charts (line, columns), index(), match()
KPI Dashboard
Important Details
Regions: North America, South America, Asia, Europe, Australia
Divisions: Game, Productivity, Utility
Note: Each division has 12-15 subdivisions
KPIs: Profit, Cash, Revenue
We have data for the year 2016 which is the current year and 2015 which is the previous year (PY).
Headers
Here we choose the year, month and division. The different shapes, text boxes and conditional comments are added to make the headers dynamic.
Year: Used data validation function to make sure that input data has a text length of four; otherwise, the error message pops up.
Month: Used data validation function to form a list such that user can only choose a month from the give options
Division: Used Combo box with answers linked to calculation sheet.
Functions Used: Transpose(), Index(), Match(), Rows(), If().
KPI's
In the table below all the KPIs (Revenue, Profit, Cash) are compared against the previous year's respective value as well as against the budget.
Functions Used: Index(), Match(), If(), Conditional formatting-Data Bars, Icon sets,
Actual Profit VS Budget
We can choose any KPI and compare it against the Budget value and display top 6/ bottom 6 subdivisions based on the difference (i.e. chosen KPI and Budget)
Functions used: Form Control: List box, Option Bottom, If(), Index(), Match(), Choose(), combining three charts (line, columns)
Profit for Division/Subdivisions
We can compare the profit (actual) for the current year against the previous year's profit and budget
Functions used: Form Control: Scroll bar, If(), Index(), Match(), Choose(), line charts
