'How to' question about a Dashboard

[halesowenmum]

Hi

I'm looking for a 'thoughts on how to go about it' conversation - there's no spreadsheet to attach at this point in time; I need to figure out which Excel features/functionality it would be best to use in order to 1. provide spreadshee/spreadsheets where users can enter their updated figures which will then feed into furnishing a dashboard. This dashboard will be used to track and review progress, status, trends, all split by the organisation structures of 5 massive Divisions, split into many pretty large Sub-divisions, Departments and countless teams. The organisation has over 5k core staff (add +10k more if you include staff in one further area the organisation who are also likely to also be involved). They won't all be inputting data, but this is to give you an idea of the scale.

We need sheets where they can input their figures, which data will ultimately feed into a consolidated dashboard that works whether it's right at the very top of the organisation (Chief Exec/Board) or an individual Team. I cannot yet figure out what this will need to be structured like in terms of spreadsheets to be used by folks to submit their updates - as in how many sheets/situated where - don't want too many so it's impossible to manage / control + potentially a nightmare to consolidate into a dashboard, but not too few that I'm worrying about co-authoring conflicts / unsaved updates (which would not go down well at all).

The data being submitted is actually pretty simple - it's an entry such as a number of days / hours, or a 'I feel 3 out of 5 happy today' or a £ or a time value. There will be a need to calculate (certainly for financial data) results like '% of savings realised so far' and the need to see trends / results over time. I foresee the data being updated weekly. In terms of people formally reviewing it, I'd see that weekly at the Working Group level, and monthly at different formal governing bodies. However, the data needs to be live if possible, so people can dip in and out and see how a particular area is doing (their own area or someone elses).

'Relationships'

• There could be (say) 15 high level goals an individual Team's workstream efforts are going to be working towards
• There might be, say, four different Workstreams in play that feed into those goals in various ways

The following concerns are flitting round my mind currently in terms of provision of updates:

• Limitations on how many people can concurrently edit a spreadsheet without risk of conflicts/unsaved entries
• Concern about someone inadvertently updating in the wrong row/cells
• The need to capture good quality data.

In light of the above, more thoughts on the how's:

• User forms to limit access to the correct location in the sheet and possibly ability to capture and audit trail of who updated what?
• MS Loop as a possible way to input updated data (sited in a related Teams site)?
• A MS Form as a possible way to input updated data (sited in a related Teams site)?
• Likely need to utilise Power Query
• Possible use of Slicers.

Please do let me know your initial thoughts and advice, if you've not nodded off during reading this, well done (if you did read all the way, I hope you enjoyed a cuppa during). Many thanks.

 

[etaf]

are you sure excel is the tool for this - i would think you will spending a lot of time chasing people to update and get all sorts of reasons why they could not update the spreadsheet or they did update and its not in there
or a lot of missed/wrong data entries which will in turn cause issues
I spent a lot of time back in the 90's getting people off of spreadsheets and we had 100's of countries , regions etc - nothing but trouble
and at the reviews , manly excuses like - data is wrong, those numbers are not correct , missing data
i suspect you need some form of database and then front end the results into a dashboard

i feel you are opening up to a huge can of worms

even something like access built correctly with a form to fill in may be a better route

last role was using a spreadsheet across a UK only based company with just 1000 employees and 7 departments and getting each dept to update the sheet - so 7 people and also review reasons why KPI and goals not meet , ready for presentation to the board monthly - was a full time job - with 7 individuals assigned to source the data and complete a spreadsheet
was a nightmare to be honest ......... even when driven hard be the CEO and directors

anyway - just my thoughts - having worked in large global coroprations all my crereer and looking at management information, performance improvement , with spreadsheet for board down to units within countries

I dont have a solution because i retired in 2016 , and the available tools for this sort of thing has come on significantly

 

[halesowenmum]

are you sure excel is the tool for this - i would think you will spending a lot of time chasing people to update and get all sorts of reasons why they could not update the spreadsheet or they did update and its not in there
or a lot of missed/wrong data entries which will in turn cause issues
I spent a lot of time back in the 90's getting people off of spreadsheets and we had 100's of countries , regions etc - nothing but trouble
and at the reviews , manly excuses like - data is wrong, those numbers are not correct , missing data
i suspect you need some form of database and then front end the results into a dashboard

i feel you are opening up to a huge can of worms

even something like access built correctly with a form to fill in may be a better route

last role was using a spreadsheet across a UK only based company with just 1000 employees and 7 departments and getting each dept to update the sheet - so 7 people and also review reasons why KPI and goals not meet , ready for presentation to the board monthly - was a full time job - with 7 individuals assigned to source the data and complete a spreadsheet
was a nightmare to be honest ......... even when driven hard be the CEO and directors

anyway - just my thoughts - having worked in large global coroprations all my crereer and looking at management information, performance improvement , with spreadsheet for board down to units within countries

I dont have a solution because i retired in 2016 , and the available tools for this sort of thing has come on significantly

Hi etaf, this did make me laugh (but in a good way!). I feel EXACTLY as you do, that an Access database (they have Microsoft already so it's free), with a nice interface, would be a much better idea. But I got real pushback from a colleague on this, hence turning to Excel and trying to think of a way that could work. I had suggested Access specifically because of the size and complexity and not to mention, the longevity - the supporting IT implementation will run for almost another 2 years, and this piece of associated work, enabled by it, will of course be ongoing.

I'm glad I wasn't way off the mark with my original thinking - fills me with horror looking at Excel so I think I'll have to push back and suggest Access again, and maybe do better at communicating what a horror show it would be in Excel!! I'm led to believe there's no budget for a tool but maybe that's a further bit of misinformation that's caused me to shoot off in this direction.

Thanks for your insight and sharing your experience.

 

[etaf]

you are welcome
I guess the additional problem, which will give issues , is not so much the tool but the users

can you isolate to maybe a smaller group - section or region and do like , what we used to call a straw-dog/straw-man, maybe as a small pilot to see what issues you actually get.\
i suspect like most things i was involved with - it was wanted yesterday and someone further up the chain has promised a solution

would this all be via a sharepoint solution - I started paying around with sharepoint early 2000's as we had a free version , but i know it has come on a lot since then

Also new tools like powerquery etc available in excel - so maybe better dashboarding - i did mine in a lot of VBA

Also would IT be backing up the file, and supporting - there lies another can of worms , I was working in the business and liasing and trying to get support across the globe from IT was a nightmare for these homegrown applications/files - i was guilty of that to - had a server under a desk with 3 PC's connected and running various programs, MSaccess mainly , created small datamarts to then run off various performance reports and excel pivot tables linked to the database - that was in1997 - copied the entire EMEA data base, over night - all with the knowledge of the IT director and my service director a peer.... challenging to say the least

 

[halesowenmum]

you are welcome
I guess the additional problem, which will give issues , is not so much the tool but the users

can you isolate to maybe a smaller group - section or region and do like , what we used to call a straw-dog/straw-man, maybe as a small pilot to see what issues you actually get.\
i suspect like most things i was involved with - it was wanted yesterday and someone further up the chain has promised a solution

would this all be via a sharepoint solution - I started paying around with sharepoint early 2000's as we had a free version , but i know it has come on a lot since then

Also new tools like powerquery etc available in excel - so maybe better dashboarding - i did mine in a lot of VBA

Also would IT be backing up the file, and supporting - there lies another can of worms , I was working in the business and liasing and trying to get support across the globe from IT was a nightmare for these homegrown applications/files - i was guilty of that to - had a server under a desk with 3 PC's connected and running various programs, MSaccess mainly , created small datamarts to then run off various performance reports and excel pivot tables linked to the database - that was in1997 - copied the entire EMEA data base, over night - all with the knowledge of the IT director and my service director a peer.... challenging to say the least

Thankfully, had a really good meeting this week with the Programme Director, on this and other things - she was totally up for getting a proper database set up and fully agreed with my concerns - so that is a huge positive. Inherently, security, backups and stability should be covered off by that. As things stand on timelines, I'm actually in on the role I'm doing quite early, which whould give me time to explore across the business, how will we go about it, who will support at which levels/areas of the business, and how etc. And to get reporting out of the DB, I've already flagged up we may need someone with a bit of DB knowledge and Power BI abilities as again, I think even just doing the reporting in Excel (even with Power Query) would not give me the confidence level on stability and things not breaking. I thank you very much for your comments and for relating your below-desk setup!! We already know there are many workarounds (off-system / manual) that are happening currently, and the new system will put paid to the need for many of those where they really aren't the best so who knows what we will find!

 

[etaf]

good luck in the new role , and of course any further questions - just post a new thread in the forums
sounds like the programme Director is a bit more realistic then the one ( with that title) that I had, everything needed yesterday , and we often came to blows on unrealistic timings , usually when dealing with CEx & board members , I found much more realistic, with vision and ambitious but challenging although achievable targets.