 Welcome to video three of module two. Remember what we're concerned with here is a practical guide to applying a multi criteria decision making framework. And in this video, the focus is going to be the use of the Excel tool that we've developed to help us undertake this analysis. So in this video, as we said, we will take a look at the tool, which is based on Excel spreadsheet that we've developed for New Zealand. It will focus on what features are needed to undertake HP and how they are incorporated into Excel. We should stress that there's some important concepts that you need to have learned before commencing this video. We've raised them in earlier videos, but really you need to look through the reading to make sure you are confident about these. So the first is really about what are the domains and subdomains and the importance of them being clearly defined, which we talked a lot about in module two video two, but also in module one video three. What are the weights that are calculated and what do they tell us, and what is the consistency ratio and what does it show us. So if you're confident about the calculation of the weights structure and what we mean by consistency ratios, then it will be much easier for you to follow the discussion around the tool that we have developed. So we will now progress to examine the Excel spreadsheet tool that was developed in New Zealand study and adapted for Sentinel. I'm happy to discuss with people if you were interested in using this tool, please do feel free to contact me. But I should note that there are a range of software that are available for free or for purchase that can be used for HP and other multi criteria decision making techniques, and these may suit your needs. So a thousand minds is an example of commercial software, and we've put a link in the slide to that, or an example of freely available software can be found at business management performance Singapore website, and we've put a link to this. So if you're kind of interested and want to explore those more, feel free to look. This isn't a ring endorsement of those other other software but I think it's important to aware that other things are out there for you to look at. So we will now move on to consider spreadsheet. So this is a spreadsheet that we have built for our work in in New Zealand. And I'll just sort of talk a little bit about introduce it and then we will go through how we use it in a practical sense when we're using it so within our spreadsheet we have obviously a number of sheets which we will talk about as we go through. So we have an introductory sheet, then one where we discuss the domains, and then where we work our way through the six sub domains. There's a final spreadsheet which produces the final figure for this fall for based on the analysis has been undertaken. And then there's one that helps us with considering the consistency that we developed. So what we do in this first sheet here is really what we call introduction one which sort of highlights the context within which this is being used so as we said this is what we set up for New Zealand. So this page here has information that relates to what are the domains that we're considering here, and the sub domain to go our financial environment market regulation social and knowledge base. And here, we can insert the domains that we are interested in, and this will then feed in to the rest of our sheet so if you've changed these domains, you can change this and put it in here so we here we have our six domains, and this now populates the sheets that we are looking at. So this is the first stage of it is setting up the structuring, what are our financial, you know, what are our domains, what are our sub domains, and we can put these into analysis. So, let's think a bit about what it looks like when you begin to actually use the spreadsheet. So, as I said, it's very much based in Excel, and what we've tried to design is a graphical interface that allows us to do the HP, the pairwise comparison across it. We've set it up this way. And so in theory, you can begin when you discussed and introduced it to your respondent to your interview, they can then in the sense begin to the process. One thing I should perhaps emphasize is that actually it's very useful. If you've got these sort of domain documents would be to print this out and give it to the respondents so they've got it while they're thinking about the process because this will remind them what they're going to be considering when they're thinking about the financial domain or the environmental domain. So, how does this spreadsheet work. So when they first look at it, what you fundamentally see are the pairwise comparison diagram in here, and an indication of what the numbers mean. And based on the reading, you'll understand that if you stay at one, then you view them as equally important. And as you move to three, five, seven, nine, then the importance, the dominance of that factor over the other factor becomes more intense. And so, really, if they move to the left here, like this example, then we're saying financial performance dominates market factors. If they move to the right here, they're indicating that market factors dominate financial performance. And as you said, the further you go, the more stronger that domination is until if you get up to nine, then it's given extreme importance to financial over market factors here. So the process works really that you discuss with them. And as you're talking to them, you say well with relative importance of the two, and they talk to you and they begin to move the sliders here as we go through. So in this case, we're saying, for example, financial performance has strong financial performance has strong importance relative to market factors. We might say that we think it's moderately more important than social, we might find it as strong importance against environment, we might find it as only, you know, sort of, but I could argue that perhaps they are making it equal importance with the knowledge base, and that it's very strong importance over regulation. So through this process of making these pairwise comparisons, we could we can go through for this so we talk to them and we say relative so then we move on and say well what about market factors relative to social well being about market factors relative to the environment, and you go through and once they get the hang of the process it can be relatively smooth. So the key thing is, you know, to get them to discuss whilst they're talking about it, and to make sure sometimes you might need to help them think, you know, think about again what is in social well being what do we mean by social well being those sorts of factors here. They carry on with the process and obviously I'm doing it very quickly here just to show what actually happens as you go through. I haven't really thought about the relationship between these but hopefully what you're specifically going to talk about later hopefully they've been relatively consistent in their choices as they go through. I think I've been very inconsistent here because I put everything on to the left hand side, this might cause me trouble in the moment. Again just that process of going through. So now once they finish that the question is okay so what what does that mean, we're able to now actually use that through the eigenvalue the calculations that underline the spreadsheet to basically work out the weight that is given to the different aspects. I might just change these around a bit to be a bit more consistent and give us a bit more interest in what's going on. Okay, and we go here, we can now see the weights that have been generated as a result of the process. So what we tend to do when we do this is once they've been through it, we show them that these are the weights that they're given and remember you think about the way to sum into one, and we think about one as the totality of the factors influencing their decision. So, the greater the proportion of that one that is taken up by one of the domains, then the more important is, and the way that we'll just bring through it very quickly there highlights for example that knowledge base here takes up about 31.31 of that and this and linked closely to financial these two have come out on that basis has been, you know, of most importance and regulation of much less importance. Now the figures are interesting, but what is useful about this than the graphical representation is that you can put it back to the person you're speaking to and say well does this kind of fit generally with your perception your feelings about it no generally are you are you surprised or not surprised the financial rates have come out so highly and then it gives you just a discussion and if they are, you know, there's some things here the same all that doesn't look right to me and stuff then you can go back and begin to think about where some of these comparisons have gone that maybe they've actually created something or whatever so it just helps that process because really what we're trying to do is capture, you know, on paper, what's implicitly going on in their mind and so you want them to be comfortable that these weights sort of reflect the kind of importance that they give to the different areas. Now we've talked a lot about consistency and so I have included in our spreadsheet tool, a check here for consistency. So we can after doing the analysis we can click that to see how consistent they are remember from the analysis saute. Satay's consistency ratio ranges from zero if they're perfectly consistent up to one if they're completely random effectively and you're trying to get around about point one point one is a good. Below highlights a good level of consistency now you know I did this quite randomly so it's probably not surprising that the consistency ratio is a higher than point one it's round about point 25. Now, consistency is an interesting issue. I've set it up here so that you can go back and see where changes could be made that could sort of increase the consistency, but I think you have to be careful about this because really what you're trying to capture is what they feel simply moving around to reduce inconsistency. I'm not sure is necessary the right idea, but it could be worth having a discussion about some of these areas to feel that are they really you know satisfied and happy with with this so we've got consistency ratios in this. This is just an indication of you know how consistently been, but I do think you need to be careful about how you use it, because to me, the main thing is really what we're trying to get to is there, you know, are they happy with the sorts of weights that have been from this. Does it make sense to them that financial performance is roughly about 30% of the whole knowledge base also 30% because really that's what you're trying to get at yes this is physical process underlying this and it is important. But the end of the day, what you're trying to get at is an understanding of what's important to them in the decision making process. This kind of highlights you know quite closely how how it works, and this is that the overall domain level, and we get then the weights of the overall domain level. Then what we say basically as we go through the process to say well this highlights the financial performance, relatively point three or knowledge basis point three, but then we can proceed then to think about what within financial performance, how would you begin to trade off these particular aspects of capital investment here, look like towards profit, or capital to return investment payback period so we won't need to get too long going through this because it's the same fundamental processes before but now you're working back within the sub domain level. So again, you would then get them to work their way through highlighting the relative importance say of capital investment to other factors. And then we can go through and look at this so again on this sheet I haven't changed it so the consistency ratios come up straight away but we don't need to worry about those too much at the moment to say we go through. And here we have the results for the sub domain so again the same process can be used, you can show this figure back to your interviewee and or interviewees or group whoever it is you're working with. And then you can begin to say does this seem feasible for you so again in this case here we have capital investment being shown as having the greatest weight that what sort of sits comfortably with them. And so, again, you know they can go through this process and look at these particular aspects, you can have a graphical interface which helps them think about when they're. Moving this so it get a nice feel for the relative what's going on, but also the graphical interface in a sense in terms of what weights have been generated because I think these are useful for helping them picture what's happening in the process. And then for each of the sub domains, we have the same process. Now it does seem quite a lot of work as we go through this. The key issue here, I think is that as long as you can keep the conversation going, as long as you set it up right as long as they kind of keeps them engaged. And actually as they go through they get more used to the process of making these sort of trade off decisions. So remember, it's probably better that you if, as long as they're willing and you ask them that you record the conversation because you get lots of really great insights, as they're discussing no. In this case it might be why they're comparing similarity to the current system to the extent system is proven and this really gives you some richness in the analysis, as we go through. So we can, as we say we can use the tool this way, we can work through, it calculates the weights, it calculates the consistency ratios for us. If we click on the consistency diagram here, it shows the relative consistency across the various. So that's the overall domain that's within financial market environment, etc. Now these ones are completely consistent moment because I haven't changed them so they're all zero, but you can see where we've been playing around some of these consistency levels change so this gives you an overview of consistency. The diagram again we've only played with a few of them but the final spider produces some of those figures that we've looked at before. In this case, again it's slightly skewed because we haven't really looked in these areas, but we can see the relative importance and again, you know again once you've finished and been through this, you can show this to your interviewee, and again discuss it and see whether it makes particular sense to them. So this is sort of the process of it and collecting from you can be doing working with a group or an individual as you work through this process. And again, you know the the the domain subdomains you choose here, obviously from the earlier work when we talked about setting up the domains. We've talked a bit about you know how we calculate consistency ratios what they are and how we interpret them. And then also how we sort of get to the weights through this process of pair wise comparison. So just wanted to highlight you know there is a tool here. It's what we use is the way that we undertook it as I said earlier, there are other tools available with you know it's not a unique tool HP has been widely used and there's lots of things available to do it. But we've just sort of highlighted you know what do you want to see from a tool well I think obviously you need to be able to calculate the weights. I think we feel that the graphical interface is pretty helpful because it allows them to visualize what's happening when you're having these discussions. I want to think about consistency and consistency ratios, because as we said consistency gives you an insight into how well they're able to use the tool how well you've set it up. But again I will emphasize it's not the be all an end all again it really depends on what your objective is. If you end up out of this with a set of weights that the interviewee feels reflects what they are feeling so you're kind of putting out there on diagrams. Something that's implicit to them if they're kind of satisfied with that and feel that highlights the importance that they give to the various domains and sub domains, then I would say it's been a success. And so again, you know within this we've just basically highlighted quickly the tool that we used in here. So in that was a kind of a brief overview of the tool as I said, happy to discuss, you would like access to the tool. Please just email me and we can discuss but also there are other options available to you the. I guess the in the next video what we move on to thinking well who will you use this tool with so first of all think about the stakeholders and others who are you going to interview who's important to your study. Also then how do you use this tool to provide feedback to those that have taken part in it and how can you do that in a meaningful way that helps them make decisions there. So again, development of this tool was funded through the our land and water national science challenge project in New Zealand, and there are some reading materials associated with the development of the tool as well, and they will be good for you to look at and as we said the next video will be talking about how we work with the stakeholders and provide feedback.