How YMCA of the North Maintains Financial Health by Planning on Uncertainty | Jon Kortebein

Speaker 1: Basically, navigating through the pandemic a little bit. I've been with the YMCA for 13 years. I've spent the last six years as the membership analyst, which is a big part of our organization. I'll tell you a little bit about the Y, we're a nonprofit. One of our associations was founded in 1856, the St. Paul one, and then 10 years later, the Minneapolis association was founded. And then in 2012, relatively recently, the two merged. You might be asking, what is an association? And I'll tell you a little bit about the Y in general. You see Y's all over the country and all over the world. They are all linked by common values, by bank association dues, to the YMCA of the USA or international Y, but they're operated as a separate business entities. So our association is separate from the others in terms of it being a separate business entity. Our total revenue is 167 million, and we serve about 164, 000 members and programs participants, so adults and children. I think when most people think about the Y they think about the gym and swim, but we do a lot more than that in our association, every association is different, we do a lot more than that. A few of the things are in the slide here, this is from our annual report, and you can take a look at those, but just to highlight a few, we offer childcare. We do outreach, especially during the pandemic, to older adults. We served as a distribution center for supplies and for people to drop off and pick up supplies for basic needs, also for meals. We do outreach to young adults, kids who are in high school or out of high school and might be at risk of homelessness. We have family camps, we have camps for kids. We have a whole breadth of things that we do. So what we would talk about today, I'm going to give you a few more numbers about the Y, just to give you some context so you can understand what we're up against as financial analysts. And then talk about some of the challenges we face once the pandemic hit. Then I'm going to get into the solutions in Planful that helped us navigate that specifically on the membership side, and then how we leverage those tools to our advantage. And then if we have time, questions. So it seems like a silly statement, it's simple, but it's complicated. It'll make sense. First, let's get into our Planful implementation. And this is probably something everyone here can relate to one degree or another, we use a lot of Excel spreadsheets, we did a lot of manual data entry, and we had a really difficult reporting interface. So if you needed a report, you had to go into the report writer to find the report, so really difficult and time consuming. So why did we pick Planful? Well, Excel, it was familiar to us, so we felt confident that we could implement it without too much trouble. We like the dimensionality, and you'll see in a few minutes why that was important to us. We liked the data load. So worst comes the worst, do it in Excel and load it into Planful. And then the ad hoc reporting feature of course is awesome, you just drop and drag some stuff, make a few selections and you have a report. Our implementation started at the end of 2016. We had built our budget in our prior tool for 2017. We got the reporting up and running first so that we could report on the budget that we had built in the other tool. And then in the spring of 2017, we started getting the planning stuff together, so we started building templates and thinking about how we were going to use those to build our budgets. So here's a few more numbers about the Y that'll give you some context, we are the second biggest, YMCA by revenue in the country. We have eight overnight camps, so you can go and camp up in the North of Minnesota, or in Wisconsin. We have eight day camps. So during the summer, your kids can go to day camp, have a blast and come home and do it again the next day. We have 21 membership ranches. And in those membership ranches, we have about eight basic products that people buy, adult membership, dual membership, family membership, scholarship, and unscholarshiped, financial aid. We have just five basic membership revenue drivers. But when you boil that down with time, the months and having unique values for each branch for each product by month, you get 10,000 plus membership variables that do you have to account for. I'm sure everyone was challenged by the pandemic, and we certainly were. This slide is a slide of our active membership, so we don't have the axis there labeled, but you can see that in February, roughly, we started to see the impact. Our membership is generally pretty stable across the year, some cyclicality, but it's essentially stable. And so when we saw that starting in February, we knew we might be in trouble. And then when we had to close all our branches in March, we knew we would be. So by the time we got to May, we had lost 60% of our membership base. Some of them did stay with us, but they weren't active, they weren't paying full dues. So it was a huge, huge impact on us. And even after we began reopening our branches in June, we staggered them to see what the outcome would be. We didn't see a lot of... We didn't see any growth. So we were wondering at that time, what the recovery timeline was going to be, we had no idea. This is a totally new world, and we didn't have any idea what could happen. And luckily, our budgeting cycle was beginning, so we were in a tough spot. So what we decided to do was be optimistic. We thought, people will eventually come back. We didn't foresee Omicron, so that was one curve ball that we had to deal with. But we really hoped that once the vaccine was out, people would get vaccinated and that would improve customer confidence and we'd see people come back. How did that work out? The bottom line is what actually happened, and the top one is the budget. So almost immediately we're falling off of what we had planned. And we were challenged on the basic drivers. We were challenged on sales. We were challenged on retention. One of our big drivers is also the units that we have at the beginning of year, and we actually got very close to that plan. We pushed our budgeting cycle way further to the end of the year than we normally would, and we got very close to what we had planned, but we were not seeing the sales and we weren't seeing people come back as soon as we thought they would. So we got to make a new plan. So, we'll talk a little bit about the stuff in Planful that we used to help us do that. The first one is multiple scenarios. And if you use Planful, you know that it's easy to create a new scenario. And if you're not, if you don't use Planful, scenario just means budget or forecast for any plan, whatever you want to call it. And it's simple to copy, you just click on the copy icon and then okay. So it's literally that easy. So just think about that for a second. In a situation like the pandemic where you don't really know what could happen, you have the ability to quickly make multiple copies of a forecast that you might have, and then make some different assumptions for each of those scenarios, implement those and now you've got some comparisons for yourself. And then of course you can rename it however you want, and make your changes. This is kind of a flow chart of the components of our membership revenue model. So we start with statistical accounts, and I'll get into each of these in a little more detail. And we merge those with metrics. So inaudible accounts are the revenue drivers, they could be expense drivers. In this case, they're revenue drivers. Metrics are basically our membership products, so it's a different use of the term metric than I think is commonly used. And then we use a data load rule and that pushes it into a block template in Planful, and that's our revenue model. And that revenue model also calculates non- financial things. So it's calculating our revenue and any non- financial stuff that we are tracking for membership. So one of the things that is integral to making this all work is the hierarchy and the dimensionality. So the hierarchy organizes your dimensions, the dimensions organize your business. So common dimensions for our organization, branch is one, department, account, metric. So when you have that kind of dimensionality, you can plan to a really specific level of granularity or a really low level granularity, or high, I guess, high. So we budget revenue down to the individual product. We budget the sales down to the individual product. We don't budget revenue at a high level or anything like that, we budget it down to the individual product and then it all rolls up. All the revenue rolls up, all our sales roll up, our retention rolls up and so forth. Statistical accounts, we're looking at the hierarchy in the statistical accounts. And the stuff circled there, those are all statistical accounts. They're not even all of our statistical accounts, but the point being you can have as many as you need for your business case. And these are non- financial accounts and they store your operational data. So for us, that would be sales. It would be retention rates. Do I have that in here? And you can use those to drive your revenue and expense calculations. So they're the YMCA drivers. So BOY, Beginning Of Year units, sales units, retention rates, discount percent, which we are doing, we're giving discounts, and price, of course. So all those are accounts in Planful, and we load values for all of those. And all that data that you load, whether it's into a budget or the actual data that you load is exportable, so you can manipulate it if you need to. You can adjust planning that way, and then you can also use it for non- Planful reports. So it's very, very useful. Metrics, so this is also a look at the hierarchy. And I said earlier we had eight membership products, I lied, we have many more. But a lot of these are for specific branches. We have a few one offs. So the metric, as I said, is a product. It's the products we sell. And you can organize those into categories. If you look at the arrows, it's a hierarchy, so we've organized our membership products into regular units, which are dues paying units. We have some third party payers that pay for other memberships, so this is dues paying units. And then we have full pay, so people that pay full price. And then down at the very bottom, well actually before that, full pay YSA youth, student, adult, so grouped into like categories. And then we have P3, same categories as full pay, but they're scholarshiped. And the reason we break that out and then down to the level, is we do report in those aggregations on a monthly basis, actually on a weekly basis, report progress to that in both sales, retention, and then just the number of units we have active at the time. So the key here, when we're thinking about statistical accounts and metrics, is that all that data is at the intersection of the different dimensions. So if you have a product and you have a statistical account and you have a location, it's at the intersections of those three things that the data resides. So if you think about that, you realize that you can get to a granularity. As I said earlier, that is important. And then all of this is reportable in Planful or outside of Planful. So you can, you know, you're obviously tracking your financial progress, but as a proxy for that, you can also report and track your, your non- financial progress. And then finally the data load. Well, not finally, but the data load rule. So we're looking at a data load rule. What is that? It's just a way to import data into Planful, and you can do this manually, but you can also do it in modeling. And it's a rule because it just defines the layout of the spreadsheet that you have. And that spreadsheet, this can just be a tab in some other spreadsheet or in a workbook where you have other calculations going on, you just write it back to that tab. And you can use a data load, inaudible load plan data, so budget or forecast, or the actuals. We do both. And key, you can load multiple years. So you can see year is one of the columns in here. So you can plan, you can load years of data and that can drive your forecast. So if you're doing long term planning, you could use that for that purpose. All right, so how accurate do we want our budgets and forecast, or at least the calculations? That's what we want, how do we get there? Well, one way to get there is with a block template. So what is block template? It's in structured planning, it uses the Excel environment, Excel formulas, you can hide rows. So the sort of pale blue rows, those are in there to drive some calculations but in the actual structured planning live environment, we don't need to see those, we just need to see a limited amount of stuff. You can format as needed. I noticed in looking at some of Planful's presentations of templates that their color scheme was a little less gaudy than the one I used, but I thought green was appropriate for our revenue, maybe red should have been used for contra revenue, but anyway. So in theory, block templates are going to be really accurate, because all you do is build one model. In Excel, if you have a model you have to copy it, and you can run into problems with whether you have the cell references right or not. With a block template, you just build that one model in this block, which I'll show you in a second, and if you think about, I said we had 21 branches, we had eight products, we had a bunch of revenue drivers, so all that data has to come together to do the calculation, but you only have to build one calculation and then it repeats the calculations, but you got to test it. So before you ever roll out a block template, you test it the way you would test anything in Excel. In fact, you can export this to Excel, just put some data in there and see if you're getting the results that you expect, and then you can roll it out. But that's a key thing, that's a key step you need to take. And the block template's pretty flexible. So this is probably the... Nope, I can't tell there. I think it's the fourth iteration of this. So we started with one when we implemented and we realized," Well, maybe we need to do this a little differently, but we just iterated just copying the existing one and then making changes. But you can model anything. You can model both financial and non- financial stuff. And in this template, we're calculating some non- financial stuff, the key steps at the bottom, but there's other things in here that we're modeling. And again, multiple years, you can't see past the top margin there, but it goes out five years. I don't know if that's by default, I can't remember, but yes, five years.

Speaker 2: inaudible.

Speaker 1: Is it a setup thing, yeah. But at any rate, we have it set up for five years, so I'm sure you can too. All right, so this is the block template in a budget scenario. So it's the same template, you can see that some of the rows are hidden, but you can see that it's calculating a bunch of stuff. So when you roll this into the the structured planning module, you have to load your metrics. So in our case, we have to load the products. And so you can do that in three ways. You can load the full hierarchy of products, which is what we do. But if you didn't want to, or there's some reason why you didn't need to, you could do it by a sub- category. So however your hierarchy is arranged, you could just do it to that sub- category. So for instance, on a prior slide, we had full pay YSA, full pay dual, you could do it to that level or whatever level you have in your hierarchy, or you could just do it for specific metrics. So let's say that you budgeted for the products that you had at the time. And then in midyear, they said," You know what, we're rolling out this new product." You need to add it to your hierarchy, and then you would need to go into structured planning and actually pick it and it would load the metric. But once you load the metrics, it loads all the drivers that you have. So in our case, it's sales, it's retention rate. It's how many units do we have at that time? And then it repeats the calculation for every metric. So the blue shading here on top, that's identifies what metric it is, in this case, the product. And then for each of those, it's doing the calculations that we built and producing the revenue or the discounts as appropriate. And it's also calculating non- financial things. So you see EOM units there, you see retained units, those are getting calculated in Planful, by the template. I guess, I did have some examples there.

Speaker 2: inaudible.

Speaker 1: So great, you have that, but how does it help you? So that's the name of the conference, the theme of it is better together. When I was preparing this, I was asked to give some examples of collaboration, and the sad truth is that our finance team, my boss, Andrea and I, and a few other people, do a lot of heavy lifting. So there is some input from other people on the operating side, but not a ton. We do most of it. So I'm not going to have a ton of examples for this. If your organization is different than ours, you could definitely leverage some of this to do some collaboration. So our CFO is asking, how bad could things get, because we didn't know, we're in April of the year and we're well behind what we had planned, it had huge revenue impacts for us. So she was really asking, what's the revenue landscape, as I said before. So what we did, we did three scenarios, best case, worst case and likely case. All guesses, more or less, some we felt more comfortable with than others. And then after rolling out those different scenarios and loading the different assumptions and letting Planful and the block template calculate all the revenue, we had an idea of what the impact on the membership revenue would be in each of those scenarios. And so through all that, and through discussion with the CFO and other people, we decided let's forecast the likely scenario and try to hit it. Now this adds some implications for staffing because we staff sales relatively light, we thought that people would just be itching to come back and they could join online if they wanted to. And we just felt like, at that time, that we didn't need that level of sales force. We found out we were wrong, so we took the history of sales and staffing from Planful, we did a little analysis, to try to figure out what level of staffing would support the sales that we were planning and then we defined a new sales structure based on that likely scenario. So a way to think about this is that we had a very location based sales team, where they had a sales director at each location and then they may have had one or two or three sales people, depending on the size of the branch and the size of the opportunity. We had no idea which branches were going to bounce back, what the sales volume would be at one versus the other, because the historical data that we had really wasn't going to help us. So what we ended up doing was coming up with a sales team where we put one sales person at each location, and then we had, I guess, you would call them floaters, we called them regional, and they could sell to anyone, any prospect at any location, which was not the case before. So with that, we moved ahead. And so, one neat thing about... I said earlier that, or maybe I didn't say this, I've had so many conversations with people today, I don't know whether I said it here or somewhere else. But, like I said, we had three scenarios, we decided on one. Those scenarios existed outside our current forecast. It was super easy to just take the data loads that I had created for the likely scenario and load them into the current forecast so that that new membership forecast would be a part of our current forecast. The thing I wanted to say was that other parts of our business weren't doing that badly, and so they were doing some tweaks in the current forecast. I was doing this because it was kind of like wild west, we didn't know what was going on and so we did these outside of the current forecast, but it was easy to update the current forecast, is what I'm getting at. So we moved ahead with our plan. And with that plan, we were able to address something that was pretty big for us. We were coming up against some debt service coverage ratio issues. And once we developed this plan, we were able to go to our lenders and say," Look, you probably are wondering what's going on, are things going to be okay?" And we were able to take that plan and say," Look, this is what we think we can deliver, and you can see that we're going to be well within our ratios." And they appreciated the fact that we were proactive about that, that they didn't have to come to us, we came to them. And we were able to do that because we had this plan that we were able to decide upon and so on. And then of course, cash flow planning. So you budget for a certain amount of revenue, but if that revenue's not going to come in, then you got some cash flow issues. So based on the revenue that we were forecasting, we were able to do some revised cash flow estimates and so on. So how did things turn out, for the most part, really well. So the top graph there is the percent of the monthly planned revenue and the bottom one is the percent of the planned active membership. So we were well over what we had forecasted for most of the year in each of those metrics. So we topped our planned revenue through November, and we topped our planned memberships through October. We still had some challenges because Omicron was on the horizon, people were hearing about it and we missed... We had more aggressive sales targets towards the end of the year. Like I said before, we really hoped that the vaccine would set people's minds at ease and they would be way more willing to come back, and we didn't see that, so we missed those targets. And we started seeing some retention challenges, so probably also related to people not feeling comfortable. But we beat our likely case forecast for membership dues by just under 10%, so I'd say that's a success. All right, so could we do this without Planful? So remember before Planful, spreadsheet intensive. So imagine trying to do 21 locations on the fly and down to the level of granularity that we planned, it wouldn't be possible. And then even if we did all those calculations, we still have to manually enter it into our budgeting software. And then I'm guessing here that it would've been a week to prepare all of that, and who knows if it's accurate. And then, again, the old system, very rigid in terms of reporting, no ad hoc reporting at all. So you'd have to spend time building this report where you're comparing the different scenarios and seeing what the revenue landscape could look like. So with Planful, there was concurrent workflow. What I mean by that is, we determined what the assumptions for each of those scenarios were, we apply those to our drivers, we load them into Planful, Planful calculates the block template and I move on to the next scenario. So while Planful is calculating stuff, and it does take some time, but while it's doing that, I'm able to move on and so on. So it's a big time saver. And then I'm confident that the revenue that we're forecasting is accurate because I've tested, tested, tested the block template. So I'm estimating it saved days of work. I don't remember exactly how long it took to do these three scenarios and get this all up and running once we understood what the assumptions should be, but I'm confident that it was least a couple days of work. I can remember just building budgets back in the old days before Planful, being up until midnight keying stuff in, or working in the spreadsheet, and that did not happen with this situation. And then once you have it all in there, it's all calculated, it's easily reportable. And when I mean easily, I mean, two minutes. That's all I have.

Speaker 3: inaudible.

Speaker 1: So now if you see me after the session and you notice all the lint and dog hair on my clothing, you know who's to blame. Yep.

Speaker 4: Tell us about your block templates. So you did block templates that sort of incorporated it looks like each product for the group. And you did those by branch as well?

Speaker 1: Yeah, by branch. Yeah.

Speaker 4: So what does that look like when you inaudible that template inaudible basically, sort of expands all the products inaudible-

Speaker 1: Yeah, exactly. It just repeats the view for each product. So it looks exactly the same. Yeah. Oh, I'm sorry. Just one more thing on that. Like I said, we do a lot of the heavy lifting, a lot of heavy lifting in our planning process. And so those templates, you could set up for people to go in and enter stuff if you wanted to, and you've seen examples of that. We don't do that, so yeah.

Speaker 4: Yeah, inaudible. So distributing, instead of at branches inaudible.

Speaker 1: Yeah.

Speaker 3: So you mentioned that you review your inaudible. How often do you review or readjust your scenarios inaudible?

Speaker 1: Yeah. So just to be clear, on a weekly basis that reporting is in Excel, but it leverages the data that's in Planful. So it's easy to pull the budget of the forecast data out and put it into whatever format you have set up. And then unfortunately we don't have Boomi set up to, and I can never remember which acronym is correct, but we don't have it set up to interface with our... Planful to interface with our customer data. So we have to manually pull that out with a report and do it that way, but then... Sorry, what was the rest of the question?

Speaker 3: How often do you inaudible adjusted scenarios inaudible?

Speaker 1: On a monthly basis. So one of the things we do is we figure out where we land at the end of the month in terms of active memberships, and we load those into Planful. And then we have scenario processing set up in the cloud scheduler to run, I think it's twice a day, twice a day. And so that includes the block templates, and so on the first day of the month it recalculates the revenue based on that. And then we haven't, as a usual practice, we haven't changed their sales forecast that much in the past, we are now doing that, we're revisiting it every month and either reducing or increasing depending on what promotions we have and so on, but it's monthly.

Speaker 4: So if you do that, when you revisit it, so basically you take your existing scenario, copy it forward, that monthly inaudible. inaudible you're actually inaudible that in and-

Speaker 1: Yep.

Speaker 4: ...then you reforecast where you want to? inaudible.

Speaker 1: Yep. Yep. And we're doing it at, we think we're going to sell this many more memberships or this many fewer, and we allocate it out. And that, for now, is all done in Excel because it's been a path of least resistance. But those calculations are really simple in Excel and we're just going to increase this X percent, so I don't... And it's not financial. I mean, we feel comfortable doing it that way. Yeah. Anything else?