- 1 -
Self-Assessment and Addendum Tools Instructional Video
00:05
Welcome to the Office of Local and Regional Health video guide on how to use the Performance Standards for Local Public Health Self-Assessment Tool. This Self-Assessment Tool aims to allow municipalities to easily assess their ability or progress towards achieving the Massachusetts Performance Standards for Local Public Health at two different time points. The results can then be used to determine where to prioritize resources, start discussions with municipal leadership on areas of need, see if a Shared Services Arrangement might be right for your municipality, and track changes and progress made over the two time points. By the end of this video, you will be able to navigate through the different sections of the Massachusetts Performance Standards Self-Assessment Tool, understand how to enter your municipality’s information into the Tool, interpret the results and view trends over the two time points, use the Assessment Tool to support the specific needs of your municipality. And finally, we'll delve into the interpretation of the results and how to utilize them in the Addendum.
So, let's get started by navigating to the Performance Standards for Local Public Health mass. gov page. And then, if you scroll down a little bit and look on the right side, in the related section, you'll see a link to the Self-Assessment Tool. When you click on that, it will navigate you to the Assessment Tool webpage. And you can see what it contains right here in the table of contents. In order to access the Self-Assessment Tool, we can click on that part of the table of contents, and then download either the Self-Assessment Tool or the Addendum. Just a note that that page provides a ton of information about the Assessment Tool that we recommend you take a look at. So, once you dive into the Self-Assessment Tool, you will open up an Excel document that looks like this. If you're not very familiar with Excel, you'll see some tabs down at the bottom and that will be how you navigate through this spreadsheet.
So, when you open it, you'll see this cover page which just has this graphic here demonstrating that this is the Self-Assessment Tool. And we'll just walk through this document via the tabs on the bottom. So, when we click on the About, you'll be able to see some information about this Self-Assessment Tool similar to what's on the website.
- 2 -
You'll see some useful links like the Self-Assessment and the Addendum webpage, the Performance Standards for Local Public Health, the Capacity Assessment Data Dashboard, as well as the summary report. Then, you can move on to tab number two, which really outlines the instructions of filling out this document. I won't take too much time here as the video will walk through these instructions in the same kind of way. But this is what this page looks like.
Also, similarly to a table of contents, if you scroll down, you'll be able to see all the tabs that are in this document. And you'll also be able to navigate through them by clicking on the link here. And so, what this looks like is you'll have the about page, which we went through. This is the instructions tab. You'll have an all questions tab, which we'll walk through, then all of these subject area tabs, which are four through 11. Two results tabs, 12 and 13. And finally, results trends tab which is tab 14. So, what we'll do is navigate to the all questions tab. There is nothing to do here, there's nothing to fill in. We just wanted to provide you all with a way of seeing all the Performance Standards for Local Public Health questions that were asked in the Baseline Capacity Assessment. Let's start just at the beginning to the Administration tab. Sheets four through 11 each represent a subject area from the Massachusetts Performance Standards. The format of sheets four through 11 are identical. On the top of each sheet is the overview and instruction section. Underneath the overview and instructions, are the Performance Standards for Local Public Health questions relating to the subject area sheet you are currently viewing. For example, for sheet four, titled “Administration,” we'll have the performance standards questions relating to the administration subject area.
Each row of the spreadsheet represents one Performance Standard question. The number column refers to the question number on the Baseline Capacity Assessment. The performance standard question column contains the text of the performance standard question. The link column provides the hyperlink to the applicable Massachusetts General Law or Code of Massachusetts Regulations. Further to the right, are columns for response number one and response number two. These two columns are used to gather your responses to each question at two separate time points to track changes to your responses to your ability to meet the performance standard question. The response columns provide a drop-down menu of possible answers to this question. These options include, yes; no – more staffing required; no – additional training needed; no – lack of
- 3 -
funding (not staffing related); no – I was not aware of the requirement; and not applicable. Please note that the not applicable column provides an explanation for why a response should be marked as not applicable. Lastly, the directions column provides additional instructions for skip logic, meaning that depending on your response to the question you're currently assessing, your response to another question may be impacted.
So, if I'm a local public health entity, and I want to start filling this out, what I will do is navigate to the Administration tab. We will go through this systematically so we'll read the question in the performance standard question column, click on the link maybe if we need some additional information about what that may entail. We'll read the non applicable explanation to see if potentially it's not applicable for my entity, and then these two response columns here represent the two different time points that can be kind of baked into this tool. So, for example, if I filled out the Baseline Capacity Assessment in the fall of 2022, one option would be for me to fill all of these responses from Response number one from my Baseline Capacity Assessment in fall 2022. And then my response two could be another time point, so right now, in a year, in six months, etc. If you do not want to utilize the Baseline Capacity Assessment results, or you didn’t take the Baseline Capacity Assessment, you can use the response one column to be maybe a current assessment of your municipality’s capacity and then in six months, you can do it again for response two.
So, let's pretend that we are going through this as response one now and our intention is to do this again in six months to look at my municipality’s progress in that six-month period. So, I've read the question, I've clicked on the link – this maybe is applicable for my municipality, but unfortunately, we do not have enough staffing for this. So, I'm going to say no, that I was actually lacking some staffing here. And then what I'll do is just go on to the next question, and answer all of those in the administration category. One thing to note here is this skip logic that could be outlined in the directions column. So, if I ever see some orange text, it will kind of alert me that I need to be looking at this to make sure that I have this skip logic correct. So, what this is saying for this question is if no or not applicable, choose not applicable for question seven. So, let's say I go in here and I say not applicable. What this means is that because I said not applicable for this question, I should also say not applicable for question seven. So again, I'll go through, answer all of these responses for all of these questions, and then I will go through and do the same exact thing for all of the other tabs – so Administration, Community Sanitation, Disease Control and Prevention, Environmental Protection, Food Protection, Housing, Tobacco, and Other. So just a note here, that this other tab are questions that did not particularly fit into any of the other
- 4 -
categories. And so, when you go in here, you'll just be responsible for answering these the same way as you would and then they will be appropriately calculated in your results tab.
Now we're going to take a look at the two response results tabs. So those are tabs 12 and 13. These two results tabs correspond to the response number in all of the subject area tabs. So, for example, in tobacco use prevention, we have the response number one, and the response number two columns. In response number one column, all that data will be summarized into Response number one results, or tab 12. And then, once you record all of the information at a second time point, or response number two, all those data will be summarized into Response number two results, or tab 13. Let's just take a look at what is included in the results tab. What this tab does is really summarize again, all of the information from all of the subject area tabs that we went through into one table. So, you'll see here in the table that it outlines all of the subject area tabs, and then goes through and kind of breaks it up by the response group. So, it will count up how many times within each category, your municipality said that it meets the performance standards or does not meet based on training, funding, staffing, unawareness, or something being not applicable.
11:17
Additionally, this completion status column ensures that we're able to calculate the results accurately and in order to do that, we need every single one of the questions within all the subject area tabs to be filled out. If all of the questions are not filled out, you'll see that all of the subject areas say here incomplete. Once the results are inputted for all of the questions within all of the tabs, this will change to complete, and it will also calculate a result for you. So, what I'm going to do is pull up a filled in version of this document so you can see what this looks like once it's completely filled in. So now you'll see that you know, I’m the municipality, I went through, and I completed every single question in every single one of the subject areas. And just so you can see, if I click again on tobacco use prevention, I have both response one and response two, totally and completely filled out. So here again, it's summarizing all of that data. For example, in administration, our municipality said that it meets three of the performance standards, needed training for one of the performance standards, lacked funding for three, needed more staffing for four, was unaware of three, and none of them were not applicable. So, as you can see here now instead of saying “incomplete,” it says “complete.
- 5 -
” And then it's also able to calculate a result here for my municipality, and that result is 21%. So just to demonstrate, I can change – maybe I will delete a couple of the answers for response one in Administration. And then, when I navigate back to the response number one results, it shows me that actually, again, it just kind of notifies here, that all of the questions are not filled out, it's actually incomplete, and thus it will not calculate a result for that subject area. So, I'll go back and fill that in just so we can see it.
13:45
And again, it has calculated that result. Additionally, there's some conditional formatting that's applied to these results. And if a subject area has a result of greater than 75%, based on that calculation that’s in the table, it will come out as green, and if it's below 75%, it will come up as orange, as you know, there's a possibility for improvement or prioritization of maybe meeting some of those performance standards in that subject area. Just a note here, that if any of your results are not matching those that are in the Capacity Assessment interactive dashboard, it may be due to the removal of several antiquated regulations. And there are three in the Food Protection category and three in the Disease Control and Prevention category. Additionally, there's also the inclusion of that other subject area, which is not included in the Capacity Assessment interactive data dashboard. So just a note that if these vary from what is in the dashboard, that is the reason.
So, once you kind of have seen all of this aggregated data up here in the table, you can also scroll down to see a more graphical representation of this same data, but in a bar graph. So here again, it's the same data as up above in the table, but now you're kind of able to graphically see and compare your municipality’s ability to meet the Performance Standards by kind of subject area and response group. So, you can see you know, very quickly here that you're meeting maybe more community sanitation performance standards, or not as many housing in comparison. And if you kind of hover over any of the bars, you'll be able to see not only the value that is associated with that. So, for administration, its lack of funding was three performance standards, but also just the response group, which is also down here in the legend. And then when you scroll down a little bit more, there's just some additional resources that could help depending on what the most reported response group was, whether that be funding, training and unawareness, staffing, as well as some other resources, such as the technical assistance request form, um, and some subject area one-pagers that will be coming soon. So, this information is going to be the same as the information that's summarized in response number one.
- 6 -
The results, except again, rather than summarizing the response from response number one, that will be summarizing the responses for response number two.
The final tab of the Self Assessment Tool is tab 14, the response trends. So, what this tab is doing is demonstrating a summary or comparison between the response number one result and the response number two results for all of the subject areas that we went through. So, for example, if we take Administration, what this is showing us is the results from the response number one and the response number two side by side. So, we can kind of check that by navigating back to the response number one, and making sure that for Administration is 36%, which is accurate. And then for Response number two, Administration is 50%. So, we just wanted to put these percentages right next to each other to help you look at the progress over time. Which brings us to the change percent column, which is just calculating the difference between the percentages between response number one and response number two. So, if you see a green up arrow, that means that your municipality’s ability to meet the performance standards in any of the subject areas has increased by more than 5%. If it's a red down arrow, that actually means it has decreased by more than 5%. And if it's a yellow line, that means it's within a 5% difference between the response number one and response number two results. And then of course, we calculate the total results for response number one, which is 35% and response number two, which is 55%. And then calculate your total result difference, which was an increase of 20% in this case. Then, we also wanted to demonstrate this data in a graphical representation. So, this is similar to the bar charts that are in the results number one and results number two tab, but now we put them right next to each other so you can graphically see any change over time. So, for example, let's look at the Disease Control and Prevention two bar graphs. So, for response one when you hover over, it looks like at that time period, your municipality was only able to meet one of the performance standards within the Disease Control and Prevention category, but at the response two time, you were able to meet six.
19:17
During response one, you can see here by this gray bar that the vast majority of the reason that your municipality was unable to meet the performance standards was due to lack of funding. And now, we see no gray bar here, which might mean that funding is not the issue for not being able to deliver some of the performance standards, as well as this dark blue bar has also decreased. So maybe you all have received some training on one of the performance standards that you ended up meeting. So here you're kind of able to look at how the responses for response one and response two change between the two time points for all of the subject area that you answered for response one and response
- 7 -
two.
Now, I'm going to quickly highlight some additional considerations and frequently asked questions about this tool. First, as you'll notice, as you navigate throughout this workbook, all of the information is protected and uneditable except the fill-ins for the response number one and response number two columns. So, for example, in the Administration tab, if I wanted to change a performance standard question or edit anything other than the information in response number one column or the response number two column, I'll get this error that notifies me that I'm trying to make a change on a protected sheet and that I need to unlock it. So, if you would like to unlock any of the tabs for any reason, for example, maybe renaming response number one to BCA responses, and response number two for, I don't know, Spring 2024 if you're going to redo this at another time point, you can always unprotect any of these sheets by right clicking and clicking on “Unprotect Sheet. ” There is no password associated with unprotecting and you can do that for any of the sheets in this workbook. We would recommend however, to re-protect the sheet again by right clicking, clicking “protect sheet,” and then enter so that you don't accidentally make any edits to the sheet, especially in the results or the trends tabs as those all are associated with very specific information in the tabs that should not be edited.
Additionally, because there's so many tabs in this workbook, as I mentioned previously, you can always click on any of the links in the instructions tab to navigate to another tab, but you can also right click on this arrow right here, which will activate this tabs kind of pop up and you can click throughout this to navigate back to any of the tabs you're interested in. Another frequently asked question is whether your municipality’s Baseline Capacity Assessment information will be inputted into this document for you. And the answer is that no, when you navigate to the OLRH website and download the Self-Assessment Tool, it will be totally blank. However, if you are interested in receiving your Baseline Capacity Assessment results or having those results be actively populated into this document for you, you can request that via a technical assistance request form and that link is located on the About tab of this document. Finally, there is no need for you to upload this information anywhere or the document at any point. Nor will the Office of Local and Regional Health have access to any of the information or the responses that you input in this document because it is a local Excel-based file.
Now, we will walk through the Self-Assessment Addendum, which can be utilized by Shared Services Arrangements to compile multiple municipalities’ results from the Results tab of the Self-Assessment. So when you open up this document from the OLRH webpage, similarly to the Self-Assessment Tool, you'll see a cover page tab, an About tab with some useful links as well as the link to the Technical
- 8 -
Assistance Request Form if you need any help or assistance, interpreting data, putting information into the Addendum, or really anything else. And then the final SSA response aggregation tab, which will be where you will insert individual municipality’s data from the Results tab of the Self-Assessment Tool, and it will automatically aggregate that data in the top table. So, as outlined in the instructions above, we will copy the data from the Self-Assessment Tool on tabs 12 or 13 (these data are actually outlined in red to help you remember what data should be copied and pasted) into one of the tables below under the header “FILL IN: Municipality Response Result. ”
So that is this header and you'll be able to paste in municipality results up to 19 municipalities. So, if you scroll all the way down, this will summarize municipality results up to 19 municipalities. So, what we'll do is we will pull back up our filled in response results document and as you see here outlined in red, is the data that we would like to copy and paste into the Addendum. So, I will start here in the top left and highlight all the way down to the bottom right of this red data and I will right click and press copy. Then, I will go in and paste the data into the municipality one response results by right clicking and pasting values. As you can already see, these data have been added to this graphical representation up here as well as into the aggregated results table. So, this information is the same information that was in the results one tab. So, the interesting thing about this is now we can actually take data from say another municipality, like this one here, and again, I will copy the data in the red outlined table from the top left to the bottom right, right click, and press copy. And then I will paste that data into municipality two by right clicking and clicking paste as values. So now as you can see, this information has more or less doubled because we have two municipalities in here and it will recalculate all of the results for municipality one and two together, as well as a Shared Services Arrangement-level aggregated result. Then, just like all the other tabs, we did provide a graphical representation of this. So, you're able to look at your entire Shared Services Arrangement’s ability to meet the Performance Standards by response group and subject area. Then, this can be repeated for all of the municipalities in your Shared Services Arrangement. And then this final table and associated graphical representation can be used to prioritize next steps in your workplan or areas to prioritize going forward. Thank you for joining me for the tutorial on how to use this Self-Assessment Tool and Addendum. If you have any questions about how to use, interpret, or apply these tools in your municipality, please do not hesitate to submit a Technical Assistance Request Form or to contact Christina Moore. Both the link and Christina's email address can be located on the About tab of both the Self-Assessment Tool and the Addendum.
transcript