• ## Email Subscription

Join 1,624 other followers

## Creating a RAG system in Excel tutorial

It may sound a bit sad, but I love Excel – once you have mastered a few simple techniques, you can put these techniques together in different orders to create some very powerful effects, and for me one of the most powerful things that I can do in Excel, is analyse some data in a way that will visually highlight an issue to me, so that I can act upon that issue quickly. One such technique that I (and many others) use is using a RAG rating system. RAG stands for Red, Amber, Green – (based on traffic lights), where things that are on schedule and up to date are Green, things that are a possible concern are Amber, and things that are a significant concern are Red.

This following set of videos, are designed as a tutorial to teach you the skills required to create an effective RAG system within your own Excel files.

If you like this tutorial, then please subscribe to my YouTube Channel at:

## Introduction

The first video is an introduction, showing the end product of what will be created.

## Using the Now() function

The Now() function is a very simple way to bring today’s date and time into a cell within the spreadsheet, which can then be used to compare against other dates within the spreadsheet, e.g. to see which are in the past or future.

## Using a basic IF statement

The IF statement in Excel is one of the simplest and most powerful ways to carry out analysis of data in Excel.

## Using a Vlookup function

The Vlookup function, seems a little confusing at first, but once used a few times is relatively straight forwards – and allows you to lookup a value in the left hand column in a table, and then return a value from a specified column in the same row of that table.

## Using the Max and Min functions

The Max and Min function are very simple to use, and will tell you what the largest or smallest value is in a list.

## Using Conditional formatting to create horizontal bars

Conditional formatting is where the appearance of a cell changes based on values (either the value of that cell, or different cells). This video looks at creating horizontal bars that move further right as the value increases.

## Using Conditional formatting to create icons

Another option when applying conditional formatting is to add small icons to cells, for example up and down arrows, traffic lights, warning flags etc.

## Putting this altogether to create the RAG system

The final video shows how the skills covered above can be put together to create the desired RAG effect.

I hope that this tutorial has proved to be useful.

If organisations want training providing in things like using Excel more effectively, then please get in touch via http://www.a6training.co.uk/contact.php

## Template to quickly create a 2 Circle drag and drop activity in Moodle

On Monday I released a template that I have created allowing people to easily create a 3 circle Venn diagram activity in Moodle. Today I have created and released a similar template for a 2 circle Venn diagram activity.

The template is PowerPoint based, and allows the teacher, to quickly and easily create the Venn diagram with the correct dimensions, and then the required coordinates that Moodle uses to identify the different zones are provided for you, so it is possible to create such an activity in a matter of minutes rather than hours.

The following image shows how the activity looks in Moodle, in this case I have used a chemistry example – the beauty of this type of activity, is that it can be used in any subject area (not just maths).

http://www.a6training.co.uk/resources/2CircleVennDiagramActivityForMoodle.pptx

And a video explaining how to use this is:

I will be adding more similar templates to this collection in the coming days and weeks, and they will be available at:

http://www.a6training.co.uk/resources_Moodle.php

If you want to keep up to date with similar videos, then subscribe to my YouTube channel via:

## Strategic considerations if thinking of switching from Moodle to Canvas

Last week I attended a ‘Digital Innovation rOundtable’ meeting in London – this is an informal group of FE providers in the London area that meet regularly to discuss pertinent issues in the are of learning technology. Last week’s topic was around Canvas LMS – which caught my interest hence I decided to attend.

All of the providers at the meeting are either current or past users of Moodle, and 3 have made the change from Moodle to Canvas, and have been very pleased with the results.

The purpose of this post, is for me to reflect on the event and to provide some strategic considerations that need to be included in any decision making before switching VLE. I have met with various senior managers/leaders who have decided to make the switch, but the reasons they give are ‘we have heard it is better’, or ‘The college down the road is using it, so it must be good’, and these are not good reasons to make the switch.

At the meeting last week, many of the attendees were unhappy with their Moodles, but this isn’t the fault of the system, but faults in the way that it has been set up over the years, themed and supported. People were saying that they didn’t like Moodle because it doesn’t work well on mobile devices – when in fact if set up properly, Moodle works really well on mobile devices, and is one of its selling points. Other people complained that everything was too cluttered – but this again is down to the decisions made within the organisation. If a Moodle uses a 2 column theme like Adaptable, or the newer Boost them, then it isn’t cluttered at all.

During one of the presentations from an organisation that has switched, they kept highlighting things in Canvas that cannot be done in Moodle – but in fact most of these things, are things that can be done, and with the Moodles that I support are routinely done as standard, which highlights the problem isn’t with Moodle per se, but with the way that it had been used in that organisation. A lot of emphasis was made on the appearance and layout of Canvas, which for those that haven’t looked at it yet, is quite similar to the Boost theme in Moodle.

### Advantages of Canvas over Moodle

• The main selling point of Canvas is its simplicity – it is easier for staff and students to use, which is obviously a good thing, it has also been designed from the ground up based on the user experience, so is a lot less ‘clunky’ than Moodle (which having evolved over many years organically and by lots of different people , there are a few inconsistencies in the way things are done and the language that is used, which to an average tutor is confusing).

### Advantages of Moodle over Canvas

• Moodle has the potential to do a lot more than Canvas, with the huge number of plugins available, and its constant improvement, it is a far more powerful tool (in the right hands).
• It is also a lot cheaper than Canvas. A typical sized college in the UK should be able to have Moodle externally hosted in the region of £5,000 – £10,000 depending in the specs, and levels of support etc. Speaking to people at similar sized institutions that are using or looking to use Canvas, they are being quoted in the region of £25,000 – £30,000, so there is a significant difference in cost here. If an organisation spent half of the difference between the 2 on training, external support etc. then they could make their Moodles work really well.

### Breaking the decision making process down

One thing that became apparent at last week’s meeting, was the difference between people’s Moodles. Some are good, some bad, and some down right ugly. If you have a Moodle that is so ugly that people hate using it, it has a huge negative perception, then the decision to switch is going to be a very different one to if you have a bad or good Moodle, where investing a little time and money into what you already have is probably a better option moving forwards.

The first steps when considering whether to switch or not, is to consult as many stakeholders as possible – and for this I mean students, teachers, and whatever learning tech teams you have – find out what they like, don’t like, how they are using it, which features are widely used etc.

Then identify what you as an organisation need both now and in the foreseeable future. Many people are choosing options based on current behaviour – e.g. most teachers are sadly still using Moodle as a file repository for their learners, but as we move forward with the notions of blended learning, we need more than file repositories, and we have to be careful that switching to a ‘simpler’ system, may be a good thing for the current behaviour, but what about the expected and required behaviours in a few years time?

As mentioned earlier, if done properly then Moodle should be the cheaper route to go down, which for FE providers at the moment, has to be a serious factor in the decision making process. When Canvas first appeared in the UK, it was seen by many as a cheaper option than Moodle. That is no longer the case, and what we don’t know is what the pricing will be in the future. I firmly believe that Canvas are not planning on following the Blackboard model, of hiking prices once people are committed to using them, but if in the future the company is bought out by a bigger company that does have different morals/pricing ideology then we cannot rule out this eventuality, and this has to be factored in as a potential risk (even if a small one). With Moodle being totally open source and free, there is no risk of a price hike – it will always be free, and if the hosting companies put up their prices, you simply move to someone else. From a risk management perspective, this is a significant advantage of Moodle.

[Edited on 17/01/2018 – Since initially releasing this post, Dave Perry commented that Canvas is owned by a venture capital company – as per this press release: https://www.instructure.com/news/press-releases/instructure-secures-pre-ipo-series-e-round so the risk of a price hike is slightly higher than I realised, as the venture capital firm is going to expect and demand a return on its investment]

Going back to earlier in the post, most people that are unhappy with their current Moodles, are due to poor decisions being made at various points in the set up and deployment, and probably associated with this, is insufficient money and support to get it right. I foresee that many organisations that got Moodle wrong, will see Canvas as a magic pill, that will solve all their ills, and will make the same mistakes with Canvas’ deployment as they did with Moodle’s deployment – the result will be, in 3 or 4 years time, we will be back again having conversations about the problems with Canvas and thinking about switching to whatever is next around the block.

Whether an organisations stays with Moodle or switches, then there has to be an additional internal investment to get the best out of either tool.

### Strategic impact on switching

Something that is often missed during the decision making process, is the impact on the teaching staff. If all the teachers hate Moodle, then you don’t have a problem. However if you have some (even if only a few) who like it, and have used it effectively and over the years have invested significant amounts of time and love and energy into improving their courses, then they are not going to be happy to have to redo all that work again in a new system, and this has to be effectively managed. Thinking about myself as a former teacher and how I would react if this happened to me – I would be furious, and any future work that I do, I would make sure is more portable in case we change ship again, but in doing this I would be creating a weaker experience for my learners. If an organisation does have a few such teachers and decides that they are going to switch, my recommendation would be to set up a ‘super-user’ system. Any teacher can apply for this, you then select a handful of super users (based on their previous uses of Moodle). These are then given a single down payment to work an extra week in the summer holidays to transfer their Moodle courses into Canvas, and to use these as exemplar courses for others in the future.

### Conclusion

Canvas and Moodle are both excellent tools, and I hope both will be around for many years to come. If an organisation doesn’t have a VLE or their Moodle is so horrendous then the choice of Canvas is easier. If an organisation has Moodle and is either Bad, OK or Good then the decision to switch needs a lot more thought. My instinct would be to first investigate what can be done with what you already have. I support various clients with their Moodles, and the ones where I have a high level of control, then the Moodle is clean, mobile optimised, has high levels of accessibility, a good user interface, and is a pleasure to use – so it is possible to create what you want with Moodle.

The decision making process about whether to switch or not, needs to be a properly run project in its own right, firstly to identify if to switch or not, and then what next steps to carry out to ensure that the development and deployment of whichever tool is properly managed.

### Shameless plug

If an organisation wants an independent external person (who is a teacher by background, not a technical person) to come in and review what you currently have, what you could improve, and help you to make the sorts of decisions detailed above, then please get in touch. I have provided such a service for many providers, who have found the process extremely useful, and for many has resulted in significant cost savings, as well as the obvious quality improvements.

## Blended learning is not ‘new’ – and calling it so is damaging

I was recently in a conversation about blended learning with a senior manager of a college, who kept referring to blended learning as being ‘new’, or this ‘new way of working’ or this ‘new approach’. At first I accepted these slips of the tongue, as referring to the fact that it was new to him and possibly his organisation, but as the conversation developed, I realised that he was seeing blended learning as being new in general, which of course it isn’t.

I don’t want to try and pinpoint the exact point in history that blended learning started (many other people have done that) – as that isn’t the purpose of this post, the point here is that it clearly isn’t new. Although we didn’t call it blended learning at the time, I was doing a form of blended learning about 16 years ago. In 2007 I started working on the excellent AASE programme at Loughborough College – which was and still is a hugely successful blended learning FE programme, and I have been working on blended learning projects almost exclusively ever since. So for me that is at least a decade, which in education and technology terms, is a very long time, and certainly not ‘new’.

Going back to the conversation with the senior manager. He was clearly scared of this way of working, and a way of coping with that fear, was to somehow make it sound that this was an untested, or experimental way of working that hadn’t been proven, and in doing so justified his lateness when arriving at the party. But the problem here, is that this inaccurate fear, and his overuse of the word ‘new’ (I don’t think he was conscious how often he did it) – is going to have a very negative effect on his organisation. If he has to stand up and inspire teachers to change their ways of working, he will struggle to do so, as he hasn’t even convinced or inspired himself.

I also expect that this situation isn’t isolated to him or his organisation, and is quite widespread through education in the UK, and I predict is an issue that may take quite a few more years to go away. What I do expect to happen is a greater gap between those organisations that do and those that don’t, as the organisations with senior managers who simply don’t get it, being held further and further behind, whilst others progress into the future.

I cannot offer any magical solutions to this problem, as I feel it is possibly ingrained within the ‘DNA’ of the individuals – I just hope that over time enough people come into the senior positions that do get these ideas and notions, that there can be the widespread cultural change to stop treating things that have been around for years as ‘new’.

## A financial model for blended learning

I was recently involved in a training session with managers on blended learning, and the underlying issue for them was working out a sustainable financial model for this way of working.

The easy (but ineffective and ultimately expensive) approach is to simply ask teachers to develop the online learning elements in their own time, and then reward them by reducing their face to face contact time for each unit or module. This results in the teachers then teaching more units or modules in total, which means more marking (which as we all know, teachers do in their own time). Not surprisingly this method doesn’t work, but sadly it seems to be the approach that many are adopting – all that happens, is the good teachers leave to work elsewhere, and the organisation has to go through the expensive process of finding replacement staff, and the associated disruption to the team dynamics.

So the solution is to find a model that works for the students, the teachers and the organisation. This may sound like an unattainable Holy Grail, but it is possible, and  a college I supported recently used such a model in one of their HE areas which I will describe here.

## Need

The initial driver came from the students; who didn’t like travelling into college 4 days a week, and then find the lectures were often not ‘focused’, and there were big gaps between lectures. The idea was to reduce the face to face element so they only had to attend on 3 more focused days. Each lecture would be reduced in length by roughly 25% and be replaced by an online element that students do in their own time, either as preparation for the face to face element (flipped learning) or as a follow up from the face to face provision (it varied from unit to unit).

## Development

The team invested money into developing this model, by actually paying the teachers a small amount to develop each of the online chunks. I forget the exact amount, but it was something like £10 per online session, and they had to develop the relevant resources/activities before they were paid. Most of the staff carried out this additional work in the summer months before the start of the next term, and they were supported by the in-house learning technologists, and myself.

## First year delivery

In the first year of delivery, although the face to face time for students was reduced, the amount of teaching time allocated to the teachers remained the same, this allowed them to effectively support the online elements that they had developed – and to reflect on and improve them. This means there was no increase in the teachers marking commitments, and made the model attractive to the teachers.

## Second year delivery

In the second year of delivery, the teachers allocation was reduced to more closely match the actual face to face delivery time, but they were still given a one third allocation for the online elements (e.g. for every 3 hours equivalent of online element, they were allocated 1 hour of teaching time). They also changed the pay mechanism, so the basic pay was effectively less, but the teachers were paid for marking on a per assignment basis – e.g. if a teacher has a particularly large cohort, they are paid more for marking than another teacher who has a much smaller cohort. This payment was again relatively small, but an essential part of the whole mechanism, as a long term objective of this process, was to increase the student numbers on the courses, which wouldn’t be possible if teachers are paid a flat fee for the marking.

## Subsequent years delivery

Once set up and working, the model then becomes financially attractive for the organisation – even by paying the teachers to support the online elements, and changing the assignment marking element (neither of which were huge additional costs anyway) – the overall staff cost was less than before, but where the real financial gains came in, was in the courses where they were able to increase the student numbers – in some cases significantly, and easily offsetting the initial financial investment required in development and years 1 and 2.

## Result

This model worked, as it met the needs of students (who preferred this way of working, and the reduced travelling times/costs). The teachers were happy, as although their work had changed, they didn’t feel like their workload had been increased. At first some teachers were apprehensive, but they recognised that this was happening whether they like it or not, so got on board. Many of the teachers involved in the initial development, found that as well as being paid for this extra work, they actually reduced their overall preparation time that they would have done anyway. And of course the college was happy as this became a very lucrative source of revenue for the college, as well as overall raising the quality of the provision.

## Key points

This worked because the college had the ability and foresight to invest sufficiently in this area. They then approached this strategically, by planning, engaging with appropriate advisers, and then following this through. The initial driver for the change, was not financial, but was about raising the quality of the product/service being offered. The financial benefits although expected were secondary, and I think helped to make more money in the longer term. Yes, the college had a model whereby they could change the pay mechanisms for the staff involved, which was essential for this project, and some colleges will say they don’t have that flexibility, but if providers want to survive in these difficult financial times, then they will have to start to do things differently, or rephrasing this – be more business-like. And finally, they picked areas that they were confident they could increase their student intake, which was essential for the longer term sustainability.

## Can other providers use this model?

Simply put – yes, of course they can. Many organisations will come up with reasons why they cannot adopt this model or a similar one, but most of the ‘reasons’ will be self-imposed, and if unpicked can be resolved. The key is to identify a small number of areas to do this initially, areas where it is most likely to work, and where there is potential to increase student numbers over time (which gives the financial benefit of economy of size). Once these areas have been set up, and are into years 2,3 and onwards (and thus bringing financial benefits for the organisation) – then start to roll this out to other areas within the organisation.

A different organisation that I worked with, when implementing a similar approach, we developed a model which started with investing in a single area initially, then the next year expanding slightly, and building up bit by bit, until after 7 years, all areas would have been ‘converted’. This required an initial investment in years 1 and 2, but after that, the financial savings of the early adopters, funded the development of the other areas, and from year 4 on-wards, as well as funding the development, would also return a ‘profit’. I am aware that organisations will tell me they ‘don’t have the funds to make the initial investment’ – but this is where the strength of the organisation leadership comes in – in that strong leadership will find that investment somehow, and then commit fully to make this work, to ensure that they get a return on the investment.

I have made reference on a few occasions about the financial benefits of increasing student numbers (which gives economies of scale), obviously there is a finite number of students out there, so all organisations cannot increase their numbers in all areas. I think providers will have to carefully identify which areas they are strong in, and which areas they are weaker in. They will increase their numbers in the strong areas and reduce the numbers in the weaker areas (probably getting rid of that area of provision). Ideologically I don’t like suggesting that organisations should cull entire areas, but the sad reality is that we live in difficult financial times, where education is grossly under-funded and if we want to survive, we have no option but to make these harsh business like decisions.

## Will blended learning end in tiers?

Regular followers of this blog, or my work in general, will be aware that blended learning is probably my main focus of work at the moment, and has been for the last few years. As I have conversations with people about blended learning both in FE and HE, I am starting to realise that a 2-tier approach to this area of work is forming, as I will try to explain here.

### Tier 1:

Within FE in particular, as a consequence of FELTAG, many providers are now starting to do more blended learning, but in most cases this is taking the form of taking existing face to face provision, and introducing bits of online, to create the blend. There is no problem with this approach per se, however quite often we are experiencing a simple replacement mechanism, where a face to face activity is replaced with an online activity.

### Tier 2:

In contrast there are some providers across the spectrum, who rather than taking existing courses and replacing bits of it, are designing (or totally redesigning) courses as blended learning courses, to take the full advantages that Blended learning offers. This means that the face to face elements and the online elements are designed to both go hand in hand with the other (rather than one being a subservient bolt on of the other), and we aren’t just replacing face to face activities with electronic equivalents.

### Conclusion

This second approach often requires reasonable up front investment, so is currently mainly in the realms of HE, private training, and (interestingly) some voluntary sector provides – but it is clear that the quality of these products is far greater than the tier 1 approach. In FE at the moment the tier 1 approach works best in the short term, as cheaper to develop, and many FE learners don’t have the skills and discipline to cope with the higher quality tier 2 type courses, but the problem that I foresee is it isn’t a case that people can start on tier 1, and then over time they gradually morph into tier 2 – in order to move from tier 1 to tier 2, there has to be a major shake up and redesign of the course, and I don’t think that people are aware of this.

This makes me wonder whether FE organisations (and to a lesser extent HE), as well as (or even instead of) trying to manage the mass migration that is taking place to force all courses to have some online bits in it – should they be prioritising a few key areas or courses, ideally the ones that they are strong in and have a good potential captive audiences for, and trying to get those to go for a tier 2 approach. Yes this requires an upfront investment, that is an issue – but is not doing this a risk that organisations will have a problem down the line that in four or five years, we will end up in a similar place to where we are now, trying to manage a mass migration from tier 1 to tier 2?

Image Source: Source: https://morguefile.com/p/846201

## The 4 stage model for use of a VLE

A major part of my work at the moment, is working with and around VLEs, either by creating content and activities, or providing training to teachers or learning technologists in the effective use of the VLE. As part of my work in this area, I have identified that there are different steps to go through for the effective use of a VLE, which I have simplified into the following diagram, and which (I think) has huge significance strategically for organisations that are trying to get teachers in particular to make better (or at least more) use of the VLE.
The 4 steps are:

### 1: Managing

Many of the clients that I work for, hire me to design and create the various activities that form the backbone of a course on a VLE. The teachers then become skilled at managing these activities – e.g. pointing the students to quizzes at the appropriate times, moderating and encouraging forum based activities, providing constructive feedback for formative assessment activities etc. These teachers in the main are not involved in the design process, and certainly not the building/creating process.

### 2: Designing

Once a teacher has worked with and managed activities that has been created by someone else, they start to understand how such activities work, what the important ingredients are, and why and when the activities are used. They can then start to design new activities – this may be sketching out the ideas or concepts on paper, it may be creating source information in Word, PowerPoint or Excel – the information then goes to a learning technologist who turns their ideas and content into the actual activity.

### 3: Building

The third step is the actual building or creating of the activities, i.e. using the VLE tools to actually create the books, quizzes, assignments, forums etc. from this content.

### 4: Repurposing

Once someone has become proficient at building activities, they can then start to repurpose existing content, and hand in hand with this, build content in a way that makes it easy to repurpose in the future (either by them or someone else).

Some organisations have a centralised learning technology team, which is great, as they can help teachers gradually work their way up through the steps. When a teacher is new to this area of work, the learning technology team can do the building for them, allowing the teacher to concentrate on managing and designing. Then as the teacher becomes more proficient, they may start to do some or all of the building, and later repurposing.

However, there are many organisations out there that don’t have such a support mechanism, or the team is too small to be able to effectively meet all the building and repurposing needs of the organisation, and this then forces steps 3 and 4 onto the teaching staff, often without them having worked through steps 1 and 2.

I don’t have a magical answer to this problem, as money is tight, and organisations cannot just create large support teams out of nowhere, but if we think about this 4 step model, and identify the necessity for teaching staff to work their way up it, it is possible to rethink a little about how we do things. I have worked with some organisations recently where I have been asked to come in and run training, where I have had a few hours to try and get teachers jumping straight into step 3, and without any central support for the staff once I leave at the end of the day. This is always going to be tough for those staff. What would be really good is, if there is a mechanism where staff can manage some existing content first, then design some basic activities which someone else creates for them, and then they receive the training in how to build/create content etc.

Within FE and HE at the moment, there are huge pushes to get people using learning technology more (and in many cases the VLE)  – and what is very noticeable is the very different approaches that organisations offer in the way of support, and more significantly the different levels of understanding from the decision makers in these organisations.