Technical Assistance – Annual Training Template Demo

OK, Thank you Carmen. I will be going through how to complete the Technical Assistance Annual Report template. First, I just wanted to kick it off by explaining that the annual report this year will have eight tabs provided on it. There will be 4 tabs for summary information, tool tips, and some other helpful resources, and then four tabs for data entry. You can see the breakdown of that on the right side here. Treasury will also be providing 2 copies of the annual report template this year. Option one will be for manual data entry, so that is if you are typing in each data element line by line and row by row, you'll want to use option one. Option 2 will be a copy paste data format, so if you are exporting data out of a source system and copying that into the template and pasting that into the template, you'll want to use option 2. So I will go through an example of both of those briefly now. OK, so let me go ahead and pull up. The templates to demonstrate here. OK, so on screen you should see an Excel template here. I'm going to start by walking through the manual data entry version. So the layout and the data format in each of the templates is going to be exactly the same. Really the only difference between the manual data entry version and the copy paste data entry version is there will be some additional data validation and helpful validation steps in the manual entry one for those people that are typing in the data directly. So I'll go through the layout here. First we'll start on the cover page. So on the cover page here you can see there is going to be some summary information available to you once you start completing your report. You notice the reporting period here and then as you begin to enter your data, you'll be able to see summary roll ups on the total hours of TA services, the number of businesses served, the number of small business referrals made and the total amount of TA grant funds budgeted to your providers. And then the breakout of SEDI and VSB businesses. Also on the cover page here you'll see this section for the data validations. I will come back to this in a second, but essentially what's happening here is once you enter your data in the in these data entry tabs in the template, once you're completed entering your data, you'll come back here and click this Run Validations button as an extra step to help you ensure that you are providing clean data to the Treasury Department. So I'll come back to demonstrate that briefly later. So that is the cover page and then all the way at the end. Here will be the tool tips information here on the Tool tips tab we have each of the data elements that are collected as part of the Technical Assistance annual report, a breakout of the location of that data element, the requirement level of the data so if it is required, optional or conditionally required. The expected data type alphanumeric, Single select text, Currency, date, and then a description of that data element as well. The majority of these descriptions are coming directly from the reporting guidance, so if you have additional questions you know please just reference that And then also on the tool tips page here is a color legend. You'll notice as we're going through the reporting tabs that the column headers of each of the data elements are color-coded to help you identify related data elements. All right, so now, starting with the provider information, this will cover all of the data collected about the providers of the TA services. Eligible beneficiary Data will cover data collected on the eligible beneficiary as well as the Small Business Referral Program, if applicable. TA Services data will cover the TA services provided to the eligible beneficiaries, and then the Classroom Style TA Services tab will collect information on any classroom style events that were hosted. I'll go through starting with the provider information. So in addition to the Tool tips tab, we also have provided the tool tips in the column header. So you can hover over the column header to read the tool tip in the description of the data element. If you have any questions about what's being collected or requested here. So starting with the TA provider, we just want to enter the name of that provider, you know, please just enter it. However, it's on your documents that you have with them OK and then relationship to the TA grant. This is going to be a single select drop down. Again this drop down will be available in the manual data entry version but in the copy paste this drop down will will not be available and you would just be pasting in the information. So here I can select you know whichever relationship to the TA grant is applicable. Moving into TA provider type again, please just select the relevant type. Here let's say we select for Profit entity. If we move into column D other TA provider type and start to type something in there, you'll see you get a data error. Please only enter a value if you selected Other for the data element TA provider type. So I'll go ahead and hit cancel and you'll notice if I do select Other in column C, Column D will become highlighted in yellow to help indicate you know. That you're required to enter this information here and I can fill that out and once I enter that the yellow highlight will be removed moving into Column E, Self Certified SEDI Owned Business TA Provider. Again this is a option set that you can select from. However in this case you are allowed to select more than one option. So instead of a drop down list, please double click this cell and you'll get a pop up where you can select multiple options from. So please just select all the relevant options for the given TA provider and then you can hit OK, clear, Cancel as needed and it will input the results into the cell for you. Column F covers the TA services provided. Again, this is a multiple selection allowed, so you can just come in here and make your selections. Once again, the description of other TA services is a conditionally required column dependent on your responses here. So since I selected Legal Services Other, this becomes highlighted indicating that I need to provide some description of what those legal services are. All right. And then moving into Column H, the final column on the provider data is the amount of TA grant funds budgeted for this specific provider. So we'll go ahead and just say 50,000. And just for an example, I'm going to quickly delete this so I can show you how some of the data validation works. So that was an example of entering a set of provider information. If we briefly go back to the cover page here, you'll see that some of your sums are starting to total up. So that 50,000 that we budgeted will continue to accumulate as you enter more provider information. And then this is also a great place to begin the data validation. So now that I've entered some data into my provider tab, I can click this Run Validations button and it'll give me a list of any potential data failures that I have. And we don't have any at this time. So we can move on to the eligible beneficiary information. OK, so next would be the eligible beneficiary data. So again, we can just start by entering the name of our provider, moving to the eligible beneficiary identifier. So this can be the name of your eligible beneficiary, but if they do not provide your name to you or that's just not how you're tracking them in your system, you can also provide some sort of like alphanumeric code to identify the business with. So whether that'd be the name again or it could be some code that you're using to identify the business, whatever you would like is fine. Please just be you know, as consistent as possible across your reporting. But this is up to you to decide how to report that. The only thing we're we ask is please do not provide a Social Security number as the identifier for that business. And then from there you can enter the remaining information, the eligible beneficiary street address, the city name, The eligible beneficiary state will have a drop down to select from. So you can select the appropriate state there and then you can enter the zip code information. Please note that if your zip code starts with a leading 0, so like 00012, it might try to throw an error for you. You'll just need to enter an apostrophe ahead of the zeros and then it will go in. So that is the case where you have a leading zero in the zip code. Moving on to the NAICS code. Again, we're just looking for the six digit next code here, and the reference for that is listed in tool tips as well. I'm going to put an extra number on there so you can see the data validation moving into the full time equivalent employees. Again the calculation for how to come up with this number is listed in the tool tips. So please read that carefully and then you can go ahead and enter your full time equivalent employees And this is a number that should correspond with your VSB indication. So anything with more with fewer than 10 full time employees will be considered a very small business. So in this case where we have 18 it would not be considered a very small business. Moving into this self certified study owned business status. Again this is a multi selection option so we'll double click on the cell here to get our list of options to select from and you know we can make multiple selections if it's relevant. Column K, Minority owned or controlled business status, Women Owned or controlled Business status. Again please just make you know the relevant selections here. Refer not to respond or business did not answer are are eligible selections, but please don't leave these blank. Race of Principal Owners This is a multi select. You may have more than one principal owner with a different race, so you can select multiple options if that's relevant. Same goes for the ethnicity of the principal owners. Middle Eastern or North African ancestry and gender of principal owners. Column R will be conditionally required based on your response to Q here. So if you select Prefer to self describe, you'll see this becomes highlighted in yellow asking for a response there. OK And then moving on to column S, the sexual orientation of the principal owners. Again this is a multi select that you can pick from. And finally the last demographic question will be Veteran status of principal owners. Again a multi select in case you have multiple principal owners for the business, OK. Once you've completed the demographic information, you will be asked about the small business referral. So here the small business program referral. So this is if the TA provider made a referral to the small business program with respect to the eligible beneficiary. Again this is a multi select, so you'll double click and you can select from the options here. So an SSBCI supported lending program, an SSBCI supported an equity or venture capital program, other small business federal program or a non federal program. Again please do selection select NA if you don't have any small business referrals instead of leaving this option blank. So let's say we do have a lending program here, maybe it is your own lending program that you're referring to them, which would be great. So we'll just say SSPCI lending as the name of that, but if it is your SSPCI program please just put you know the full name of the proof program there Referral to source Capital. Again this is going to be a multi select so you can double click here and you know indicate where that you know private capital would be coming from and make multiple selections if applicable. And then column X here will be dependent on your response. If you select Other again, that will become highlighted and you'll know that you need to provide a description there. So that would be all the information that needs to be entered on the Eligible Beneficiary Data tab. We'll go ahead and move on to TA Services data. So for each of the eligible beneficiaries, of course you are providing TA services to those beneficiaries. The way that it's laid out in the reporting guidance says TA service one, TA service two, TA service three, and TA service four. However, we've modified the layout here to allow you to enter a variable number of services provided. So if you only have a single service being provided to a given beneficiary, then we would just see one row of data, one row of data for that service, you know, tied to the eligible beneficiary ID. And then if you have more than four instances of services, you know, 5678 instances of services for a given eligible beneficiary, that's fine as well. So you would just duplicate, you know, the provider name and our eligible beneficiary for as many instances of services as you have, where this eligible beneficiary is what you would have listed in the template here. So if I have three services, maybe from two different providers, that's how you would set that up here. So you can enter as many services for a given eligible beneficiary as you have recorded for them and then we can complete the rest of this section here as well. So for the given service, please just indicate the primary purpose of that surface. You'll have a drop down list to select from and depending on your selection in this drop down list, you may be asked to provide a description of the other purpose of the service in TA service Brief description. This is just something that will be entered by the jurisdiction. Please just provide you know a brief description here in text the style of the TA service. Again, this is a drop down option to select from. Depending on your selection here, you may be asked to provide additional description of the other type services and then finally on the hours of TA service, just indicate the number of hours that they received those services for. And once again as you begin to populate this information, we can come back to our cover page and see some of that data being populated here as well. So our small business referral that we went out and the businesses being served and then those eight hours of TA services that we just provided and then the final tab on the report here is classroom style TA services. So these classroom style TA services are not tied to a specific provider and are not tied to specific eligible beneficiaries. So if you do not have any classroom style TA services to report, you could just leave this tab entirely blank. Conversely, if you only provide classroom tile services and you don't provide any individual. One-on-one services, then you would just fill out the provider information in the Classroom Style Services tab and leave eligible Beneficiary and TA services data blank. If you have a mix of the two, providing one-on-one services and the classroom style, then we would expect to see data across all three tabs. OK, so for the classroom style, TA services again, you know please just report one line of data for each event that is occurring. For the primary purpose of the event, we'll have a drop down selection for you to choose from. Here again, other may be conditionally required based on your responses. Classroom Style TA Service Event Brief Description Again, this is for your discretion what type of information you would like to include here and this will be looking for the number of attendees and then the date of the event occurring and obviously this would likely be within the reporting period. So again that is an example of how to fill out an instance of a classroom style TA service. And once you've completed all of the completed all of your data on all tabs here you can come back to the cover page and look at your summary information. Make sure that is lining up with how you expect it and then you can click this Run Validations button. It will go through and check that the data is the appropriate type that you know you're not missing any information, stuff like that. So you can see here you'll begin to get a list of failures and you can go and resolve those data issues. So it'll start by telling you which tab you need to go resolve it on. It'll tell you the column that is the data element that you need to resolve the row, and then it'll describe the issue that you're having here. So you can see on the eligible beneficiary data sheet the NAICS code in row two. It must be 6 digits. So I entered A7 digit NAICS code just so we could see that example there. And if we back it out to the six digits and run our validations again, you can see it gets rid of that error. So again, this will just help you make sure that you're submitting clean data. A lot of this is just I didn't enter it as it was expecting. You know, I I didn't didn't fill any of these out, but I started filling it out in rows A&B but didn't, you know, fill out the remainder of the information. So I should get rid of most of my errors here. If I just delete those, OK, there we go, Just one left. So please, you know, do your best to remediate any and all issues before submitting back to the Treasury Department. OK, so that is the manual data entry template. I will briefly switch over to the copy paste version. So essentially it's it's the exact same template. It has the same layout of the data and the columns and all of that. It just will not have the integrated data validation or drop down options multi select options for you to pick from since you're just pasting the data in so you know before when you had like this the list of state codes to select from. These are just, you know blank cells for you to paste into. So if you are going to be entering the data manually we definitely suggest using the other template. But if you're just going to paste in from your source system, then this should work just fine for you. In addition to everything that was provided in the manual entry tab, you'll also see two additional tabs called Single Select Data Values and Multi Select Data Values. You will receive an error if you are not matching the allowable option exactly. So unfortunately even if you are missing something like the comma, it will say like hey, you didn't pick from the allowable values. So we've given you provided you with this list of the options that are the allowable options to select from. So if you are getting any of those weird errors and you're not sure why like you think you put in non profit entity but yours has a period at the end of it, you can reference these lists to help you ensure that you are entering the data as expected. So again, just to kind of briefly show you, you know in practice what that might look like, say you get an export out of your system that looks like this here. I mean, obviously what you would do is just copy your data set, move to your template, This is my provider information and then you could just paste that in to the cells here. And then once you've pasted in all of your data, please go to the cover page, you know again, make sure that your summary and total information is as expected and then click that run validations and I put mine in to get some errors here so we could look how to remediate that. But again, it will tell you like hey this is this is not an approved value or on row 4 here I didn't enter any information on the other TA provider type even though that was my selection in column C So it's the same data validations that you would experience if you were doing the manual data entry. So please just make sure to resolve those before submitting. Great. So that will conclude the demonstration of the technical assistance templates, and I'll go ahead and pass it over to Jay who will show you how to download these templates and then upload them back to the portal.