How Imagen Dental Keeps Execs Smiling With Substitution Variables with Gaylord Miller

Gaylord Miller: So everyone, yes, I'm Gaylord Miller. I am the director of financial planning and analysis at Imagen Dental Partners. We are a dental partnership organization, in that we partner with dentists who own their own practice, they get an ownership company in our company. We get an ownership stake in their company, and we move forward with common goals and common outcomes. And what we want to talk about today, substitution variables, how they were a key part of our decision process to go with Planful and what they have meant in practice for us. So, I have been in the Planful environment for more than six years now, two separate companies. My first time around, I just showed up to work at a company and they said," Hey, this is what we use for financial reporting and analysis." At the time, it was host analytics. And really liked the application. It's very powerful, it's very flexible. Pretty easy to learn. So I'm sure most of you have had similar experiences, and that got me started. When I joined Imagen, one of my first charges was to go out and select and implement a enterprise performance management system for the company. And no preconceived notions at all. I sort of evaluated four different packages. A little bit selfishly, I was hoping to use another one to expand my knowledge base and add something new to my resume. Not surprisingly, Planful rose to the top of 140 point evaluation program. So, here we are back in Planful and I'm pretty happy to be there. So we've talked about Imagen dental partners. We were founded last April, so just what 18 months old and very inaudible position business. So we affiliate with... When I started in December, we had three practices, we now have 17. We have 25 more on deck through the rest of the year and a plan next year to be about 60 more. Over the first three years of our company life cycle, we expect to have about 300 practices affiliated, so there's just a lot to do from a financial reporting perspective. These are highly trained, very sophisticated dentists. Very technical, used to doing their own accounting, being in charge of their own business. So it was really incumbent upon us, as they sort of handed us over their little baby, or their big baby in some instances, to be really responsive and really responsible and very transparent with them. And for them to be able to see, quickly and easily, in a very good way, how the practice is performing, what the financials look like, what the projections are. And so this is why substitution variables played such a key part in our decision to move forward with Planful. So what are they? Substitution variables and derived substitution variables. Like an avatar, I guess. So substitution variable being a constant you can use across many facets of the application, across every dimension, and report headers. And that you set to a value for that dimension that you change as needed, and that change goes across the entire application without you having to sort of go into individual reports and edit those and modify those report sets or column sets. Very powerful tool that allows you to smartly build reports that then need very minimal maintenance to keep up with where you are in your financial planning cycle. I'll talk through decider for us. When we were evaluating packages, one of the APM systems I looked at really required you to go into every single report, every single month, and update the date or update the forecast you're using. Update the budget you're using, in order for the report to correctly reflect financials for that time period against those varying scenarios. Not doable for us. And I'm sitting there going,"Geez, in 36 months I'm going to have 300 practices and I need to be able to run these financials and to produce financials for all these different dentists and all these different partners in a timely fashion. Get them distributed." And the power of this substitution variable to change this one time in the application and have it applied to report headers, have it apply across all dimensions, was really sort of critical for us and made it to be just a critical decider. Derived substitution variables play off of a substation variable. So because substitution variables are maintained monthly, quarterly, annually, the fewer of those you can have, the better. And we'll go through some of those examples. But then a derived variable plays off of that, so that you don't have to go update a multitude of things to keep your application in- sync, right? And then again, when you're on a report, the ability to override what you may have settled, substitution variable, if you want to look at a particular report in that moment in a different way, and we'll go through that as well. Use cases, where we are using substitution variables today and where we have used them in the past. So again, right? A name that we can use and reporting that automatically updates based on how you've set the application. Can be created and used across all dimensions. I know we each have eight dimensions in our application, some of the common ones being time, account, entity, department, things like that. And you can create substitution variables along each of these dimensions and use them again in reports and reports sets, rule sets, column sets, report headers, all those different places. The values for substitution variables are user defined and maintained. So, through the menu, right? Maintenance reports, cube settings, select substitution variables at the top, and you get a screen that looks much like this little screen I have posted on here that says code. So it tells you what substitution variable you're looking at. It's in the financial reporting area with dimension it's on. This particular substitution variable is on this scenario dimension, and that's currently set to be 20-21 budget, right? So again, not limited on how many you can create and how many you can use. And in this particular instance, this is one that we use to show that we pull our current budget into our reporting. This is examples, I keep talking, I've said a couple of times that they can be used in report headers in combination with other variables or terms. So if I format a report header to be at current month and then scenario and the square brackets, it's going to show up on that report page as September 21 6+ 6 forecast because my scenario is set to, or my current month variable, is set to September 2021. I'm pretty traditional when it comes to report headers. I like three line headers, right? So it's who, what, and when. And I use substitution variables pretty much in two of those to say what and when it is. So if it's a P& L or a balance sheet, I use a substitution variable to say what month it's for on my forecast reports. I would have an actuals through when I would use a substitution variable for forecast month, to say that it's actuals through this month. So in my 8 + 4 forecasts, it's actuals through August. In my 9+3, it's actuals through September. And again, it's something where I just go into the substitution variable setting and I set it once and it grows across all of my reports and all my dimensions, paid settings, report headers to reflect that substation variable. Another place that I use substitution variables pretty consistently is in preparing various types of, various sequences of scenarios, right? So currently we have a current forecast, which right now we're in our 8 + 4 forecast, is our current forecast. And we're building our 9 + 3 forecast. So have a substitution variable for add current forecast at, and that is set to be our 8+4 forecast. And then I'm building the 9 + 3 forecast. So I have a new forecast, add new forecast, substitution variable that's set to be my 9 + 3 forecast. And where this gets to be especially powerful for me in forecasting and in budgeting is when I want to compare those two, right? So if I build a report and in that column set, I say, compare at current forecast at new forecast, it sort of doesn't matter which version of forecast, or my current forecast, every new forecast, right? So that I don't have to go into the report and change that forecast to say, use my 8 + 4 for my current forecast or my 9 + 3 for a new forecast. I just go into the substitution variable setting and I set my current forecast and I set my new forecast. And then I use the same report without having to go into that report and maintain that report to produce the comparison between those two forecasts, between those two budgets. Pretty much every scenario comparison that I'm doing because we want to know there are changes from one forecast to the next, changes from one budget to the next, in addition to changes from the current forecast to the next year's budget. And so all my reports are built with these substitution variables in there that help drive those comparisons without having to actually go back into the report and manipulate the report to get that output. Creating substation variables. So if you had an implementation partner, they probably created a few for you. We create them all the time. As we see a need as something we can do, we go out there and like most things in Planful, fairly straightforward exercise, right? Again, we go maintenance reports, cube settings, substitution variables, click on the plus. The syntax for a substation variable or derive substitution variable is the same. Start with the add symbol, enter any text you want in one in the middle, end with the add symbol. There are substitution variables. They are pretty much replicated between the financial reporting area and the workforce reporting year. So workforce reporting just overflows, planning just needs its own substitution variable. And in fact Planful will duplicate those for you. In our instance, they duplicate overnight. So, I create one in financial area and I come in the next morning and it's duplicated that into workflow setting, workforce planning for me. Again, any of the dimensions, right? So we have substitution variables created on our entity dimension, on our time dimension, on all scenario dimension. Are pretty much the three most common ones that we use. And this is the screen that comes up when you're at a substitution variable, right? So it starts with that add symbol in the code line. You get to fill that in with whatever text you'd like in the middle and put the add symbol at the end. We typically create them in the financial reporting area. Particular dimension in this case is account, you can switch that dimension to any dimension that you use. And then you assign the substitution variable for it, right? So when I create, or when I use that current month, the substitution value right now would be September. And as we roll in the month, I would go back in there. This is one that I change every month and make it October. And then November. And that again, rolls all my reports, all my report collections, all my report headers. I run the same report collection without going into the reports, having to change the month that the report is running for, or the forecast is running again, budget is running again. I just changed that in my substitution variable, because those are embedded in my reports. The report now runs against that particular view of data. As I said, we create our own, right? So we started with a few basic ones, but here is a screenshot of some of the substitution variables we have created I'd imagine. So we have a current budget and this screen does not have one... Yes, it does. It has one new budget down at the bottom, right? So our current budget being the 2021 budget and our new budget being the 2022 budget. So we're working in both of those. And if at any point in time, I want to switch what I'm looking at in a report, I want to compare these two side by side, I just run that report that has these two substitution variables embedded in them. And I get to see that, right? And so these aren't limited. I had a very interesting time with my team. We were running reports against our current budget and somebody asks to see, well, what would this look like again? So now where we are, what we're looking at for next year's budget. And my team started stressing about having to rebuild these reports to go have to... No, go change the substitution variable in your current budget field. Just make that the 2022 budget and run all the reports. And because it already says to use the current budget variable in there, whatever scenario is defined in that current budget is what it ran again. So it takes a minute to make that change. And here we are, we're running all of our reports against the next year's budget already, before it becomes the official budget, just in order to have a look, to see what things look like, to contextualize stuff, to get a kind of a feel for where we are in the process. inaudible at current month, really popular substitution variable. I think that was probably one that's needed to all of us. Current quarter, current year. And again, this is something that you're not going to want to build too many of because you have to go in here and maintain them, right? So this is where the power of derived substitution variables really comes in. But you build a substitution variable, and then you're allowed to build variables off of that, that are dependent on that setting. So you'll see this last column out here, that in that current month substitution variable, we have 30 derived variables build off of that. And we'll see that list here in a minute. So derive substitution variables, like I talked about, dependent on the primary substitution variable, right? So these aren't ones that you have to go update that automatically update based on what you have set for the substitution variable that they're dependent upon. So syntax is exactly the same. It starts with the add symbol, put in the text you want and the add symbol on the end. Like I said, they're created off an established substitution variable. So we'll go through an example of exactly what that looks like. And the syntax includes the substitution variable we're using. So in the example that I showed before, at current month that had 30, then we use the syntax to play off of that substitution available, right? So if you want the current month plus one month, you're going to go lead, plus one. If you want the month prior to this month, you go lag, minus one, right? Or lag one, actually. So it's very simple syntax. It doesn't require knowledge of coding to figure this stuff out. I think the first time I started building these, I was just looking at what other people had done and just started inaudible the language and stuff worked, which was great. More complex example of coding, because these are fairly straightforward examples that I've shared with you so far, they work within the hierarchy that you have set up, right? So on that time dimension, I think most time dimensions are set up with month at the leaf level, a quarter above that, and then year above that. So in this example, if you started at current month, and then you go parent dot parent, every time he used that parent's syntax, it rolls it up one level in the hierarchy, right? So the first parent month would be the quarter and the second parent of the month would be the year. So it's just, again, an example of how you use the syntax off of a established substitution variable to create a derived substitution variable. And this is an example of those substitution variables, of the derived variables, that we've created off of the existing substation variables. So the substation variable we're using is month. And you can see in the far right column where it says formula, that they each started at current month at, right? And then we go lead one, lead two, lead three, and those are forward months from the existing month. So if you are building a, like a rolling forecast, those get to be tremendously helpful, right? So you can build a concept that says, use that current month plus one, that current month plus two. And whatever time period your forecast starts with current month, the report is always going to run and show you the upcoming months in your rolling forecast. Or if you did quarters, you could build it off a current quarter as well and show forward quarters. About two to three quarters of the way down the list, you see I start with a lag one, lag two, lag three. So we do a lot of TTM reports will show us trends, right? And my executive team loves to see TTM reports that's the 13 months prior. So they see the same month in the prior year. So we go back as far back as like 12, and we build that directly to a column set. I think I have an example of that. Yeah, right here. And so when we run this column set, it shows the prior 13 months or prior 12 months, and then this current month, so that you can see in this instance, right? You'd see September of 2021 compared to September of 2020, and all the months in between. But again, this is an example of using substitution variables in a column set so that you don't have to go maintain this report, right? We have these reports in our collections. We run the collection at month end and here it all is without having to go in and maintain the report for this time variable, across the top and the columns. This was really cool because we started our implementation, I want to say February 8th. We were live 45 days later. And my CFO comes to me and he says," How long after accounting closes are you going to be able to run finance reports for us for the month?" And I said," Maybe 10 minutes?" And it's 10 minutes because our application updates from our accounting system every 10 minutes. So, if they close right on the 10 minute mark, I probably have to wait 10 minutes for an update before I could run everything. But he was pretty astounded that we don't have reports to maintain. We don't have all this work to go do before you can see financials. Right now our reporting team in our accounting team, closes the books. We run consolidations that takes all of three minutes. And we have a inaudible in five minutes after accounting tells us he's closed. And it's through the use of substitution variables in our reporting that we're able to do that. Oh, here we go. In closing, I think substitution variables are a way to really enhance the power of what you do in Planful and how quickly and how efficiently you do it. How consistently you do it. By building out a good library of substitution variables and derive substitution variables, it really allows you to build smart reports that don't require maintenance on a monthly, quarterly, or even annual basis. In our budget reports, we don't go rebuild those reports every time we roll a budget here. We just change the substation variable for our current budget, our new budget, and all of those reports they run. They're the same. We haven't changed them. Really, really efficient was, as I said before, a key decider for us in choosing Planful over the other applications. The most strongest use of this for us is in those dynamic and low maintenance reports. That using them in the report title, so that every time a report runs people can clearly see what report this is, what scenario it's running against. Is it running against the 6 + 6 forecast? The 9 + 3 forecast? Is it running against 2021 budget? 2022 budget? And it's not something that we have to go into the report again and go into the report header and make sure that's right. By building that right in as a substitution variable in the creation of the report, in the report header, these update on the fly without any maintenance from my team. And finally, the ability to create your own substitution variables, right? Not being limited to what was put in there, what was predefined. But both substitution variables and derived substitution variables, building those as you see fit for the needs of your business. Just again, the flexibility of the strength of Planful, just allowing us to move faster, be more accurate, be more in depth, spend less time talking about" Are the numbers correct?", instead looking at the numbers and saying," What's this telling us? What do we need to understand? What do we need to do?" So I think that's my presentation.