Building a FE College Financial Benchmarking Tool with Power BI

Detailed financial information about FE colleges is published annually by the Skills Funding Agency and the Education Funding Agency. The latest available data - for the 2014/2015 academic year - is here in MS Excel format.

There is a lot of data - the workbook contains almost 400 columns - and that makes it seriously difficult to compare colleges without quite a bit of further work, particularly if you want to use charts to make the comparisons easier. There is a separate MS Excel benchmarking tool that makes comparisons easier but it's published later and the current version is for 2013/2014 data.

Power BI makes it much easier to create interactive data visualisations and then analyse the data. It is surprisingly easy to build an interactive FE college benchmarking tool once the source data is in the right format - the hardest part is getting to grips the raw data and then deciding what ratios and metrics might be of value to the user.

The first 'beta' version of the FE college financial benchmarking tool is below - click on it and then use the filters on the left to select colleges. There's also a full size version here that's easier to read/use.

The highlights of the approach to building this first version of the tool are: 

1. Copy interesting columns from the raw data into a much simpler MS Excel worksheet and then format as a table (Power BI likes data in tables when you import from Excel).


2. Create some 'bands' or groupings that could be used for filtering. Maybe users want to compare colleges of similar income size? They may also want compare colleges with a similar learner profile - so I've also banded colleges by the % of their income that comes from the EFA (they fund 16-18 learners). Another obvious grouping is by the standard government English regions.


3. Use 'slicers' to create the filters. Clicking/unclicking items in the filters changes the colleges on the chart. And holding down the ctrl key lets you select multiple groups of colleges.


4. Decide on some ratios/metrics that might be of interest to the user - the hard part of the job. I then calculated most of my ratios in the Excel table before import into Power BI though one or two were added later as 'Calculated Columns'.


5. Do some basic checks to make sure I've not misunderstood the columns in the original data or subsequently introduced errors (email us if you spot anything that looks wrong).


6. Publish to a Power BI account on the web and then choose to share it publicly. One point to note here - sharing publicly is currently free but there may come a time when Microsoft start charging for that service.

One of the nice things about Power BI is that it makes very easy to respond to feedback from users - you can build iteratively and roll out new features almost immediately. In the next version we'll tweak the existing charts and add some new features. Contact us at info@skillslogic.com if you've got ideas about how we could improve the tool.