We were recently engaged by a Fortune 500 company to create a set of charts (commonly known as a dashboard) to help them monitor their sales potential in the region. Before this, they were reviewing the sales forecast for each country separately and have to refer to many spreadsheets (all numbers) for the regional and global accounts. As too much time was spent calculating the sales for these accounts manually and therefore, they decided to approach us to help them solve the problem.
Presenting the numbers visually in charts is always a good option as charts are easier to read and understand than interpreting rows and rows of numbers. Drawing simple charts is a piece of cake for the client and they could have easily completed it all by themselves. The main problem in this project is however in the consolidation of the numbers in the spreadsheet for drawing the charts. If done manually, my clients would require a few days to manually update the charts. So we recommended using dynamic formulas like INDEX, OFFSET, MATCH and SUMPRODUCT for the calculations. Based on these formulas and other functions in Excel, a dynamic dashboard was created. Everything ran automatically like a program. All they had to do was updating the data worksheet and the Excel formulas would take over to update the charts. Other than automatically updating the charts,
- Users have the flexibility to add new values in the setup worksheet which would appear in the dropdown list straight away;
- When data from a new category is introduced into the data worksheets (e.g. new market segment called education) , the charts would intelligently to pick up the new categories automatically;
- The charts always present five years of data. You can choose which year to start simply by selecting the year in the dropdown list and the chart will automatically present the five years of data starting with the selected year.
You can now download and experience the interactivity of this dashboard (sanitised version) by compeleting the form below: