Managing Financing Programs -- Spreadsheet Models (Text Version)
Chris: - financial market development team. I'm really glad that all of you have been able to take time out of your busy days to come and share some time with us talking about using spreadsheet models to manage financing programs, particularly for energy efficiency.
A little bit of background on me. I am a - I'm here at the U.S. Department of Energy and working with the financial market development team. Our mission is to help grantees, state and local governments, primarily in employing the SEP EECBG and Better Buildings grant monies in starting up energy efficiency programs and particularly ones that employ different financing mechanisms.
Benefit of financing obviously is that you get to extend over a long period of time the types of benefits that we can accrue from these grant monies by creating financing programs that revolve and that get replenished with new capital. We can ensure that our grant dollars are being put to work, not just this year or next year but 10 or 20 years down the line.
And particular focus for our team is helping grantees to explore the opportunities to build financing programs that more than just put capital into the market to do good work, like energy efficiency. Actually go into the market and transform it in some way. Catalyze something where private capital starts to realize that this is an attractive business space to be in. And private capital then starts charging into this market to make these good, high-value, well performing energy efficiency loans and starts to take the burden off of public funds to drive this work.
We see as - in McKinsey studies and others a phenomenal amount of work to be done in the energy efficiency. Trillions of dollars is often thrown around as the estimate of the cost-effective projects that are available in the United States. That's obviously greater than what - anything we can accomplish with just public funds alone. When we start talking about getting massive â€"
Leslie: Excuse me, Chris. Chris, can I stop you? You need to change the pop-up for your e-mails 'cause it showed a pop-up e-mail.
Chris: Oh, I'm sorry.
Leslie: Your settings and make it full view.
Chris: I'm sorry. Yep. I got it. Started ahead of myself there. Sorry about that, folks. And I will get my Outlook closed here in just a second. Darn it. There we go. Sorry about that, everyone.
And so that's the basic reason that we need to get private capital involved in this. Today what I'm gonna talk about is specifically how a program manager can employ a particular tool, spreadsheet model, to get greater control, greater visibility and ultimately make the most of out of their - not only their financing program, in fact, but their entire energy efficiency program.
The - I'll go over the basic mechanics of a spreadsheet model. Show you how you can do some scenario testing and then show you a couple of different spreadsheet models that we've built for grantees to use. Talk about how they can be expanded out to not only cover several different types of financing programs but also be used to integrate your demand creation and your workforce management functions. And then we'll wrap it up and I'll try to maintain enough time at the end there for questions.
As you - as a question occurs to you, you can type it into your question field here. And, in fact, I see that one has already come up in someone asking me to adjust my volume. Let me see if I can help. I got my microphone as close as possible to my mouth and hopefully that will have alleviated the problem there.
And, yes, there is a question already. I will have the presentation and all of the spreadsheets that I can e-mail. And the only problem would be is if the spreadsheets are of such size that I can't e-mail them out, in which case I will be posting them to the Solutions Center website. And we'll get everybody - we'll e-mail out the links to that so that you can go onto the center and download those, which would be a whole lot better. Okay.
Volume was better before you changed. All right. Leslie, can you give me a real-time indication of whether or not this volume is working right?
Leslie: It appears great to me. I'm not having any issues with the volume.
Chris: Okay. Terrific.
Leslie: Now they really can't hear you.
Chris: Now we really can't hear you. You did sound better before.
Leslie: Did you change anything?
Chris: Yeah. I'm just moving the microphone position around.
Leslie: It's fine now.
Chris: It was better at beginning of call. Well, let me say in just a basic troubleshooting if some folks can hear me okay, I'm guessing that it's not primarily my microphone. If you're not able to hear, let me suggest that you hang up and dial back in. It might be your particular connection with the webinar here. So I apologize for that but hopefully - okay. Great. Somebody says that there's a microphone - a volume dial on their controller there and it looks like if they put it up they can hear okay. All right. Terrific. So hopefully that will take care of most of the technical issues here.
With that I'm gonna start, which is wading into one of the primary models that we built and start showing you the way it's built, some of the tricks that it can do. And then always kind of be talking in general terms about how everything that's built here can be - can be? Should be tailored and modified to fit precisely your program. None of this is suggesting that your program should do it this way. This is built to match one particular - I'm sorry.
I just got a question. Would you please make your screen full screen? And I do have my excel spreadsheet up there filling my screen right now. So that's as full as I can make it. I don't know any other way to make it even more fuller than that.
Leslie: Chris, that's on the Windows box. The Go-To Windows box there's a view option at the top. Do you have that marked full-view?
Leslie: Oh, okay.
Chris: I don't - okay. I've got the view drop-down and I don't see full view here.
Leslie: You should have the option at the top there. View, full view, window. It should give you â€"
Chris: No I don't.
Leslie: You don't have that. Hmm. Okay, well. Hang on. Let me see if there's - hang on.
Chris: Okay. Well, let me just â€"
Chris: Someone mentions here that it's a setting at the attendee's end to do full view.
Leslie: You should have the settings at the top of your Go-To Window box. That's what I thought because you are the organizer and the presenter. But if you don't, you should see that. That's mine. I put it on full view. Oh, it is full view. I see it in full view, Chris.
Chris: Okay. Terrific. Okay. Folks. I apologize but if you don't see it in full view I think it's a setting on your side that you need to tweak over there. Okay. Then moving forward we've got the basics for a spreadsheet model are going to be a dashboard up at the front, which is your control panel. Which is what we're looking at right now on this tab called, no surprise there, dashboard. And then several tabs underneath it that are kind of the engine for the spreadsheet that will drive various portions of the calculations that are going on.
You'll typically have input cells here as we've got in green here in which variables that can be manipulated are input by you. You get to choose what these values are in order to play with different scenarios, in order to test different things. And ultimately, potentially to down the line as your program is actually running and executing to start inputting the actual values. Excuse me. On this dashboard we also have output cells. That's typified here by this one. They're all gray with a blue text in which you don't manipulate that number because embedded in that cell is a calculation that's yielding a number from - that's derived from your variables.
The simplest one here is in C10. $250,000 dollars is the result of subtracting $1.75 million from $2 million directly above it because in this particular scenario we've got a total number of grant dollars in the program of $2 million and equally split - that $2 million is equally split between a loan loss reserve and an interest rate buydown. And so we've got a real simple calculation in here saying that if you tell me how many total dollars we have and what the loan loss reserve portion of it is, naturally the remainder has to be to the interest rate buydown.
We've got another block over here of results where a lot of key output numbers will come to you where you'll see a particular metrics. And, again, these are metrics that this particular grantee wanted to see. They can be structured in any way necessary for your particular program. And then often because it's a dashboard and you want to be able to see visual very quickly - understand visually what's going on inside your model - we can build a couple of different charts that in very simple ways that show very quickly what's going on. Bottom chart that we have here is entitled Loan Generation is showing steadily climbing the cumulative dollars worth of loans made by this particular program, which is obviously a heartening sight here.
The chart above that is entitled Projected Expenditure. And this is something where you're tracking the metric of when will my Department of Energy funds be fully expended? And when will I meet, most critically, my contract date of usually for most of the grantee programs 36 months after the inception of the program? And so you've got a couple of different metrics there that are coming out, either in these output cells for you to glance at or even you can build some charts to do it visually.
Let me show you real quickly what you might be doing here with a particular scenario testing here. What we've got built into this particular model right now is these inputs. These variables of deciding, "Well, I think that I'm gonna do about 50 loans per month in my particular program." And right now my selection is tuned to that baseline right there. Likewise for my guess that I'm gonna do averaging about $8,000 per month in my loans. And that's also tuned to the baseline.
But you see here in this column called actuals average in which we've got some different numbers coming out here. And this is the result of actually having put in some genuine data. In the example that we're playing with here your program has now been running four months and you have actual results coming in. You've actually made four-months worth of loans. And you've input the number of loans you've made each month. You've put the actual dollar values of each of those loans.
And so the model here's actually calculating what your average monthly loan volume was and what your average expected - what your average loan size was over the last four months. And then you can use those as new assumptions to project out where your program's gonna go. Now in this case when we switch from baseline to actuals, we're gonna see some movement in these charts. You see these - this blue and green line suddenly move to the right.
I mentioned that this chart here, projected expenditure, was showing when your DOE dollars would be fully expended in this model. And in this case we went - switching it back to baseline now. We went from oh about July, August 2012 if I'm reading that properly on the chart to switching that over to the actual data that we've got now in here four months worth. That just bumps it out, oops, all the way out to March of 2013. No surprise because your average loans per month is below what your initial assumption was. Likewise, if we throw our average loan size into the actuals, that just bumps the date well out beyond even what we've got set up here in our dashboards out to the end of 2013.
The good news is though that with - this is one of the steps that these models can help you - empower you with the most. You look at these numbers and you say, "Wow, that's not acceptable. I need this poor program to be performing in a better way." And so you would go through identifying what the causes were for perhaps the smaller number of loans made per month, what the causes were for perhaps that smaller average loan size. And you'd intervene with your program.
You'd spend some more money on marketing to get your demand up so you'd have more loans. You'd streamline your loan process. You'd have fewer people dropping out as they were writing their loan application. You'd educate your contractors and your auditors on how to convince people that genuinely deep and substantial home upgrades really makes a big difference for them. And so you'd get folks to agree to more deeper retrofits in which case then you could do new projections based on those improvements. You could build those new projections into your model.
And now, of course, with those improvements data that we've built into this particular model we see our key metric here. Our projected expenditure date pull back into an area that is satisfactory. In this case back up to 2012. And I can get into the guts of this engine of how we've done that in a little bit. But as a quick overview I want to show you that that's one of the important things that a spreadsheet model can do. It can do a lot of very complex calculations very quickly.
And within the constraints of course of what it's doing and what it's calculating and portraying it can allow you to do a lot of thinking in a very quick sort of fashion. What ifs get answered pretty quickly in a spreadsheet model like this where you play with different rates. Where you play with different terms. Where you play with different variables and very quickly see how they interact with each other to yield either acceptable or unacceptable results within your overall program. So that's basically what we're going to be trying to do here with these models.
I'm going to show you the guts of this program now just to give you a familiarity of exactly what's going on here. I'll start off by going into a little bit of detail on what your variables are here. Clearly you want to be able to tell it when your fund is starting so that you've got a - everything starting off in the right time and you're referring not to period number 97 but actually to November of 2013 if that's what it is.
We've already talked about how this number at cell C8 is the total number of funds being contributed to the program. And you can be splitting it up amongst any number of different actual portions of your program. In this case, in this particular model we've got a loan loss reserve and we've got an interest rate buydown activity. You could have other activities that are involved in this program model and be splitting up those funds as well and seeing how they interact.
Because we've got those two activities we've got the variables for those two activities and the ability to actually toggle on and off whether or not one of those activities is inactive. We've got a yes or no for whether or not that loan loss reserve is in effect. Another yes or no for whether or not that interest rate buydown is in effect. That's because in many cases you'll be building a model like this in order to test and exam whether or not you want to use an interest rate buydown.
Oh, and I see a quick question here of can you please repeat the meaning of IRB. IRB is that interest rate buydown that I've been referring to. An interest rate buydown generally is when you find out that in the marketplace, for example, borrowers don't feel that they can - will want to take out a loan unless that loan is at the maximum of say in this case, eight percent. An eight percent loan - more than that your marketing folks tell you that borrowers just get turned off on it and they're not interested in taking out the loan at all.
But the problem is that loans aren't being made because the lenders, whether they're banks or other sorts of financial institutions say that they can't recoup their operating costs, recoup their losses on the number of loans that will default, and make the acceptable level of profit unless they're earning, say for example in this case 11 percent is what we've put in here.
And so you've got a gap to bridge there between the 11 percent and the 8 percent. And that's an acceptable use of federal funds here in the ARRA programs coming into an interest rate buydown, where in effect you're coming in there and you're paying the bank. You're paying the lender that 3 percent worth of interest for the life of the loan in order to cover what the borrower otherwise would have been paying in order to get it up to that 11 percent.
So the bank gets 11 percent effective interest rate on their loan. The borrower gets an 8 percent effective interest rate on their loan. And the public entity pays the differential there. And what you can see here, in fact, is the calculation of how much that public entity would have to pay. Given a market rate for the lender of 11 percent and a target loan borrower's rate of eight percent on our average loan size of $8,000. That's being paid back over a period of seven years. It's gonna cost $718 for the grand T for the public entity to buy down that rate.
What's fun about that is that you can start experimenting obviously. If you've still got those basic assumptions in there and you say, "What would it cost for us to get it down to a five percent interest rate 'cause that would sure be attractive?" And, whoa, you very quickly start seeing numbers like $1,396. And you're thinking, "Gosh, that's very rapidly approaching 15 percent or exceeding 15 percent of my actual loan principal here. Maybe I should be using my money in a slightly different way rather than this massive interest rate buydown here that I'm doing. It seemed so easy before I started plugging in the numbers."
The - okay. So this interest rate buydown calculator is built in there. I should just mention really quickly. This is the top level of that interest rate buydown calculation. We've got a tab here that's actually entitled interest rate buydown calculations in which the engine is performing here. And you can see that it is a somewhat more involved process than what we were looking at just up front and there. It gets into the genuine financial calculations here extrapolated over a 12-month period and pulling back all the interest payments that would be made on a particular loan here and then totaling them up in order to come to this fairly clean, straight-forward number of $1,396.
So that's the basic top level here of the loan loss reserve model. Digging down into it a little bit more, we get into the detailed cash flow, which is the second tab on this spreadsheet. And I should mention that, yes, I will get out - I see another question here about getting the spreadsheets out in public. Yes. I'll get them out to you. As I said, I will e-mail what I can. Some of them are very large documents and I might find that they jam up the pipes , in which case I'm going to post them to the solutions center and I'll e-mail you the link to that posting. You can download them there.
These are also - I'll say this - available through our technical assistance providers. We have a team of technical assistance experts who are mostly contractors who are armed with both these particular models and the ability to build custom models for your particular program. And so we can get them involved with any of your programs that haven't touched them yet and start working on these together.
The - so this is the engine here. Obviously tiny little script here because there's a lot going on. So let me zoom into this a little bit and show you a little bit better what we are looking at here. These engines can be as extremely involved or less involved as necessary. If you're in the initial design phases for a program it makes sense to start building from the ground up small and slow and deliberate, modeling exactly what you want it to do. But as programs get more developed, as they start making decisions about what they are or not going to be - what they are or are not going to do, more and more functionality can be built into a model and in order to more closely, more granularly model precisely what that program is doing.
It's gonna start off here with talking about some limitations within the model. We've got a calculation saying what are the max. loans possible in order to prevent the model from projecting loans being made when there aren't any funds available. And then we've got a couple of different ways to input the actual loans that are made that month. The - one of the inputs that we saw on the top level on the dashboard was what your basic assumption's gonna be for the number of loans made per month. And here we've got that 50 that we had input on that repeated month after month, straightforward. Straight along the line there. The weakness of that, of course, is it's a best guess.
When you get into actually executing the program and start generating actual loans you're going to get a much better idea of exactly how many loans you are making. And so we can have a projection like what we've got here, which is we actually made 43 loans in the first month. We actually made 42 loans in the second month. And then we've got a real basic straight-line assumption of going forward we're going to be making an average of those two months. Not necessarily the most accurate projection but probably more accurate than the 50 that we initially sort of pulled out of the sky.
You can do any number of other extrapolations beyond this saying that while this is our baseline now with the level of marketing that we've done but we're going to do a lot more marketing and so we expect it to grow at a certain rate. And you can put in here a 10 percent growth rate or 20 percent growth rate. You can do a number of other different types of projections and I'm gonna show you one of those when we get around to the portion of this model where we integrate the demand creation function into it.
Likewise I've got in here another line and this is where we had talked about when we switched over from baseline to actuals and then to improvements. I've got another potential input line here in which I've manually inputted some loan volume here based on maybe a more reasoned approach to exactly what my overall program is here after I've made some improvements to it. And this is the line here that was used when we toggled over to improvements to drive the function of the program. This model, of course, is built so that selector is active so that it runs off of whichever one of these lines we've selected from the dashboard.
The result is here, of course, the loans made per month, a cumulative loan adds up so that we can start tracking that. We've got average loan value from the dashboard and an average loan value override here. And in this case more accurately this is the actuals because this is - that's the way that this model is built up to be. When we select on the dashboard the actual values, these are the actuals. I can show you where this would come from. These particular numbers are outputs from a tab that we've built in here called average loan size calculator.
And now this shows you how a model can go from merely a development model, a design model to show what the program turns into to an active management model in which on a tab that is added to a spreadsheet like this you can start inputting your actual loans made. So in month one here, these were the actual loans made. Month two these were the actual loans made. Month three, these. And month fourth, these. And this, obviously, was billed out by each month as additional loans were made.
We've got a quick graphic here demonstrating what's happening to the average loan size with the actual average loan size in blue here and a trend line for running average loan size. And then what we've got is the running average of all the loans made up to this date is in the $6,879 and that is the number that is pulled forward here that we saw on the dashboard, underneath the actual average number that was used in the recalculation of our model when we selected from baseline to actuals.
Again, there is a host of different ways that you could use the data once you generate it. My choice here was to use my baseline projections on this running average loans. But you may find, for example, you're 18 months down the line in executing one of these programs. You may feel like the most accurate data to use for your projections is only the most recent data. Only the most, say, only the last three months' worth of data because you've made so many developments, so many improvements, so many changes to your program as your program has learned over the past 18 months that the first 12 months worth of data is - really reflects a different program. A program with different activities and different competencies.
And so you may change that to say that, "No, all my projections going forward should be based on a running average of only my past three months because that really reflects what my program is capable of doing right now." There's a host of different ways to do that and the final arbiter is you. You, the program manager. What you'll find in a spreadsheet model is a wealth of quantified data and metrics that you can use in a lot of different ways. To suggest that this is a science is inaccurate. It as much an art as it is a science. And that means that there's as much subjectivity and judgement that's going on here as there is objectivity.
The difference is that as a program manager when you subjectively make a judgement about how to use these numbers for projections, what you've got to defend you is a wealth of objective data. When somebody comes in and challenges you and says, "Well, why did you do it that way?", you can reason with them. You can show them, "Listen, what I've got here is 18 months' worth of data. And this is the trend line that this data is showing. And here are the points in which I made significant changes to the program, significant improvements to the program. And these were the results. And so these periods here are drastically different from this period that I'm involved with projecting forward. And so here's why I'm using these particular numbers."
And most importantly they've just immediately been briefed on the fact that you're not pulling numbers out of the air. That you are in a very reasoned way using the data available to make some subjective forecasts into the future. But the - but it's nothing that is simply pulled out of the sky. I'm sorry. I just glanced over to the questions here. And let me try to answer these questions as we go forward here. I'll keep - I imagine some folks have been coming in a little bit late here. But I will get this spreadsheet out to you after the webinar. I apologize for not getting it out before but I ran into some problems generating them and e-mailing them because of the size. I'm gonna try to strip some of them down so that they can be e-mailed and if not then they'll be posted up on the Solution Center's website. And I'll e-mail out the link to you to get access to those.
Are you basing B5 on maximum monies allowed? And I'm gonna assume. Nope. I don't know which detailed cash flow, B5. Oh, okay. I think that question is. And let's see. I apologize. Let's see. I'm not 100 percent sure. Yes. The maximum possible in this particular model are being calculated on the basis of what the fund balance is at the beginning of the month based on and then divided by the number of loans made, the average loan sizes.
In another words at the - how many dollars in loans are being sent out and then reduced down by - to the 10 percent, which is the loan loss reserve percentage for this particular model because that's what the grantee's responsible for pulling out of their fund each month to match the loans that are being made. There are a number of other different ways to calculate this but this is the - that's the particular way that this model is being run.
An excellent question here about - oh, let me get to that one in a second. What have you heard - is grant money going into a loan loss reserve fund is not expended until loans are made? And I'm gonna apologize and deflect that for just a second because we're gonna get a new piece of guidance coming from general counsel on exactly the definition of expenditure for loan loss reserve funds.
It should be mostly just a clarification and going into a little bit more detail on how exactly to define expenditure for loan loss reserve funds from the guidance that was out previously. And we'll get a full communication out to you on exactly what that is. I wouldn't anticipate any major changes to it unless they give us even more leeway in some cases and some detailed specific cases in order to achieve expenditure.
But generally, yes, a loan loss reserve fund is expended as loans are actually made. So if there are no loans made then the expenditure does not occur, generally, but we'll get you a lot more detailed communication on the latest general counsel guidance as soon as possible.
Does this model show homeowner repayment of the loan and that's an excellent question? This particular model doesn't but there's no better time than the present, I suppose. Let me show you one that does. So this is another model that someone else who has a slightly different color palette than I do but the same basic construction. An inputs and outputs tab is what it's called this time where they have a dashboard for playing with different variables and then seeing the relative outputs for the particular model. And I'm flipping over to the next tab here for fund cash flows. And let me get this zoomed in a little bit more tolerably.
How about one more? Not so big. And here they have got - they have got plus. This line here you see plus scheduled principal and plus interest payments coming back into the monthly calculation being added back into the gross fund capital. And so this is - it's a revolving loan fund where the principal and interest payments are coming back in each month and being added right back into the available loan funds to be loaned right back out again.
And just to give you an idea of what that requires then to be built into your table - and, in fact, specifically this is the big spreadsheet that's giving me the biggest problems e-mailing it to you because you have to build these amortization schedules that you see here. And, yeah. There we go. Essentially what you have to do is you have to start calculating every month's worth of loans each month again as those loans go through and experience interest payments on it. Principal payments on it and default rates on it.
Each month what you've got is - so for July of 2011 you have calculated out what that original $8,343 did each month in order to get to July 2011 and be now valued fund principal at $8,645. And that has to be done for each one of the month's worth of issued loans. And you see those start cascading out there and obviously getting very, very large. And because you've got both principal and interest to deal with you have to do an amortization schedule for principal. And we're scrolling down here and now an amortization for the interest.
And in this case we've also got amortization for principal over the life of the loans and interest over the life of the loans for the purposes of creating this present value of both future principal and interest cash flows. And that allows us to then do secondary market transaction in which at some point in the future an investor may come along and ask the - ask to buy the portfolio of loans that have been made. And they have to have a valuation on those loans. And so this model here's actually giving us a value for the entire portfolio of loans being made based on what their current cash is and the future cash value of the principle and interest payments on it. Again, more and more amortization schedules here calculating all of that out.
So it's not rocket science to build it. It's actually relatively simple. You build the - a basic calculation in the initial cell and then you repeat it out for many, many columns, and down many, many rows and then link it into the model. But it does start building some - start sucking up a lot of memory on your spreadsheets here.
Let's see. We will now go into - let me go back to the loan loss reserve model that I was showing you initially and show you - yeah I think we're about at the right time now. I'm gonna start going into now how financing programs can be integrated into your demand creation programs and with your workforce management programs because as a program manager your primary concern here isn't just how loans are being made. It certainly isn't just how your marketing folks are doing and it isn't how - whether or not your contract's being retained. It's all of those things. And most importantly it's - are projects being completed successfully with satisfied homeowners and business owners.
And for that to occur you have to have all of your other functions within your program working and working together appropriately. You know the classic example is your advertising guys, your marketing guys get very excited about a campaign. They launched something. They generate a huge amount of demand. People are jumping all over our website requesting audits before you have a fully trained auditor force. And you've got your community college that's moving forward on training auditors, but they're on a different schedule than the demand creation folks. They aren't talking to each other perhaps and then you end up with some very unhappy homeowners sitting around for six or nine months waiting for an audit to come to their house.
On the other hand, of course, you could have the exact opposite. You could have a community college that gets program in gear and firing off very quickly. And then you have auditors sitting around on their hands waiting for the other elements of a program to come together. And six or nine months of no work, they abandon the job space and they go look for work in some other industry and generally then you lose your workforce that you had taken so much time and effort to build.
On the financing side to you've got folks who have to be in place and ready to accept applications, read those applications, underwrite loans, and to generally move the paperwork through the process. Obviously you have to have the funds available and that financial partner set up and ready and willing to make loans. And you - and then have to map out just as we have here when a program would potentially run out of money. How many loans do you have available in your program and when do you - when have you used up all of your funds to make loans?
So all those are timing issues, integration issues and so it often leads to the question, "Well, how do I do that?" And the spreadsheet is a great way to, once again, manage a lot of detail. Play with scenarios. Test things. And then as actual activity is occurring to input that data in and see where your new projections lead you from what the calculations look like.
So what we've got here is naturally a big financial program calculator but I've attached to it a demand creation tab here where I've created a typical demand creation process for a grantee's program here. And, again, starting it off I've got a quick dashboard up here in which I've pulled up a number of key metrics. So as a program manager I can come in here daily, weekly, monthly, whatever the appropriate period is for you to manage your program. Take a quick scan of these numbers, see where they're at and take action from them.
These numbers are once again designed by you. They're designed by you saying to yourself, "What are the answers that I want to be able to take action from? Do I want to know the difference between my different advertising programs? Do I want to know whether - where my bottlenecks are? Do I want to know if I've got a shortage of capacity in one of my key functions?"
You ask yourself those questions and you say, "Well, yeah. Those are the things that I want to be able to know each month in order to make sure that my program is running as well as it can be." And then we figure out what those particular metrics are gonna be to give you those answers and give you the ability to make that well informed decision that you want to be able to make six months from now. In this case - excuse me - we have mapped out a - the answer to the question of which one of my marketing campaigns is working the best? Which ones are the most cost effective and which ones are getting me the most number of projects?
We've looked at a whole host of different potential marketing campaigns from door-to-door campaigns to print advertising. Google search and different teams of contractors out there. And we have month by month allocated a certain spend to each of those marketing campaigns and you so those spend dollars here. Now very simplistically we've said that each one of these marketing campaigns will yield a certain number of audits in which folks request an audit to come to their house and evaluate the energy performance of their home and then to provide a recommendation on effective measures to improve the energy efficiency of that.
And the - and so in a very simplistic way we've built this engine to say that the money spent this month on this marketing campaign will be compared against the number of audits that the marketing campaign yielded in that month and we'll then be able to calculate expenditure per audit of how much did it cost us to get an audit through that particular marketing campaign?
So in this example we've got 10,000 spent on door-to-door campaign A. $50,000 was spent on contractor team A. So we've spent a lot less on the door-to-door campaign than on the contractor team. But we see from our calculation that we got a lot less or it was less cost effective on our door-to-door campaign because it cost us $10,000 to get only one audit versus our contractor team A where it only cost us less than $2,000 for each audit because the contractor team A turned in a stellar 26 audits.
Okay. So the weaknesses of this is obviously apparent to anybody. The one-to-one relationship of audits resulting in the same month as the spend is challengeable. That may not be the right way. But it's up to you then to just decide is there a lag period here? Do I average a certain number of months in the past to assume that my marketing will generate results in a certain number of months afterwards? Do I just have it a total running average anyways and say that I can't put up with a marketing effort that only has long-term results. I do need direct results.
You may be asking yourself, "How do we know that a particular energy audit resulted from a particular marketing campaign?" And that's a great question for your marketing consultants and the folks engaged in that. But there's a host of different ways. Everything from a Google search campaign, which is really simple because every time somebody clicks on a Google search ad they are electronically tagged and when they get to your website Google will report to you what they did on your website, including requesting an audit. When your contractor team knocks on somebody's door they should be the one helping them fill out the application for the audit. And so they naturally are gonna make sure that that they fill in the box at the bottom saying referred by contractor team A to get credit for it.
You go with other sorts of things like a print advertising campaign in which you're putting an advertisement in a magazine. And the direct marketing industry has put together a number of different techniques, all of which include - some of which include adding a coupon to that print advertisement saying that you'll save 10 percent or you'll get a free gift if you mention promotion code XYZ. And your marketing folks are tracking that promotion code XYZ refers directly to this particular ad that ran in a particular magazine. So as soon as they hear that somebody mentions promotion code XYZ they know precisely that the persons referring to that particular advertising campaign. And, of course, there's the always straight forward, "How did you hear about us?" We'd love to know so that you can record it.
The energy audit naturally then turns into a loan application. And what we've got here is number of loan applications as a result of those energy audits that were linked to those particular marketing campaigns and a metric here. Now what we'll find out is that because there are several different conversion points here from the initial contact through marketing to the audit, to applying for a loan, to closing the loan, to actually doing the project. Each of those conversion rates along the line will affect the ultimate conversion of person to end result - to project. That you as a program managers are really gunning for.
When you find out that that bottom line conversion rate is lower than you want it to be then the question is, "Well, what's causing that?" And this is where you dig back into the intermediary points there and find out where things are falling apart. That original door-to-door campaign A that we had that we badmouthed because it cost so much to get a single audit? Well, at least it seems to be yielding really high-quality people.
At least maybe it's not getting a lot of audit requests but the people who are requesting audits are all going towards applying for a loan, which is a terrific result here as opposed to contractor team A, which is still doing pretty well but only 85 percent of their audits actually turn into loan applications. That's not too bad, especially considering when there are some other programs here like the Facebook campaign that seems to have extremely poor conversion from audit to application.
But that will - as a program manager then start to shed some more light on where your choke points are. Where things are the weak links in your particular program. And it will lead you to then make your subjective decision of, "Well, do I cancel my Facebook campaign or do I try to improve it and get it up to the standards of one of my other marketing campaigns out here?
Is there something about contractor team A that I can help share with them that what they're doing at door-to-door campaign A that would actually get contractor team A up to that 100 percent of door to door campaign A." I don't know? But it's a more educated more informed position that you're now in as a program manager. So cranking through this, this all leads to loans closed right here of the actual loans going forward here.
And this then is based on what your demand creation programs are. And what you can see here is that at some point you can start taking your projections and say that, "Well, if I were to take this $50,000 from contractor team A and spend it again up here in the future, I'll think that I'll get an equivalent amount of demand created from this marketing campaign." Likewise you can say, "You know what? Last February we saw a huge spike in demand as people's furnaces started breaking down and they needed to replace them. And so they started calling for energy efficiency audits and new projects to replace their furnaces. I think that's gonna repeat itself next February."
And so I can put in into my projection here a new number of audits based on maybe not one of your marketing campaigns but simply on weather factors you can insert here to create a seasonality factor. And you can start injecting these more sophisticated prognosis of what your marketing and demand creation is going to be in order to generate a new calculation of when and what level of closing - loans closed you're gonna have.
The cool thing about it is that now that then can get pulled forward right here in this same spreadsheet right to loans made per month. And now you've got a loans made per month number that isn't calculated by a banker. That isn't calculated by your finance guy who, at best, can just sort of pull out of the air what his projected number of loans are gonna be. You've got a projection number of closed loans being made in a fact by your marketing guy. You've integrated them together taking your marketing guy's best knowledge of what demand he's gonna create and your finance guy's best knowledge of what need - what the result is gonna be from that demand creation and how it's gonna impact the financing program and you've linked them together right there in the same sheet.
One change to the marketing plan that will immediately change your projected number of closed loans per month and that will then immediately affect your financing program and show you on your dashboard a change in your key metrics and how your financing program is performing right there. So there's your demand affecting your financing program.
Let me show you how this can also start affecting your workforce management program. Scrolling back up here to the number of energy audits being requested. And what we've got here is a projection of the number of energy audits being demanded by the folks. We're gonna pull this number into this other tab called workforce. And here I've done a basic calculation of how many auditors we're gonna have in the field on a particular month, their ability - what their capacity is for actually doing audits on a month to month basis and the resulting capacity here on Line 18 for a number of audits that can be performed in a particular month.
And as you can see we've got a community college and a non-profit training folks, adding to the existing workforce with graduates coming out on a particular school calendar that they've been able to draw up. And that adds to the actual number of auditors in the field. And I've thrown in a factor that calculates exactly how many audits per month those particular types of auditors can do. My assumption here was that the existing force being more seasoned and experienced could perform perhaps five audits per month.
A community college which has a robust curriculum would be yielding auditors that could do four per month. And the non-profit that is experimenting with a distance learning program might not produce quite as capable auditors so they may only be doing three per month but it yields a capacity of number of audits per months. That's being compared to as we had just mentioned on the demand sheet the numbers being pulled forward for audits demanded. Compared to the capacity demanded that yields some interesting highlights here.
What we see initially is a shortfall. We have a demand of 93 audits per month and a capacity only for 75. That improves over time. Month to month as our capacity grows it's growing at a faster rate than the growth of our demand, which is good because it's narrowing the gap. But as a program manager you may look at that and say that's unacceptable for the first month.
I've got to call up either my training institutions and figure out how I get them to graduate their classes faster or I call my marketing folks and tell them to put the brakes on their marketing efforts for - by a month to let our workforce catch up to it so that we don't put ourselves in a massive deficit and start getting newspaper articles about folks who called up for an audit and haven't receive the audit that they asked for.
So that sort of function can obviously be repeated for contractors who are doing actual projects. This workforce calculation can be and should be done for the underwriters who are receiving loan applications, reading through them, and determining who will be granted a loan and any number of other key functions that are going on inside your program where the capacity to deal with the volume being pushed through it is of key concern. That pretty much shows you what an overall spreadsheet can do for you.
There's a number of different financing programs, of course, that we can model. We've got some models here as I showed you for a loan loss reserve, for interest rate buydowns, for revolving loan fund. We can - we've built in the amortization tables into some of them to show that the pure revolution of the principal and interest coming back in. And then also the ability to value that portfolio at any moment in time in order to enable a secondary market sale to another investor who might want to purchase that portfolio of loans in order to hold it to duration while replenishing the capital of the original lender allowing that original lender to make new and additional loans.
And we can combine a number of different means by which grantees are exploring - adapting their programs to meet their particular program goals and their local market needs. This is a tool that can be used through both key stages in the program or I should say all three key stages. The initial state being where design and development where you're imagining what you program is going to do and trying to figure out what the best way is to make it do the most, while you're also negotiating with your financial partner and helping to come to a mutually beneficial point with them by playing with the different variables to find out what benefits both of you at the same time.
And then in the actual execution portion of a program where you're constantly ingesting actual data, updating your projections, and using it to adapt and improve your program to make it the most of what it is. And to get as high and achieve as many of your goals as possible. And then the third phase is in the after-action phase. In the learning phase - well, now let's take a moment to step back and figure out what this program has taught us about how to stand up programs like this. And just sort of a spreadsheet model will be yielding a tremendous amount of rich lessons learned on how that program has performed to inform the generation of program managers that will come after you to - who will be starting up even more programs across the country to support energy efficiency like this.
So with that I'm an hour into it and I think that this will be a good point for me to stop and turn it over to some more questions here. And we can spend the last 30 minutes or so going through different questions. Question in here is aren't we doing more of what caused problems in the housing market if we sell this in the secondary market?
That's a great question and let me address real quickly what happened in the housing market. Fundamentally what happened in the housing market is folks started buying things without any concern for what the underlying inherent value of the asset was. They were buying largely mortgage-backed securities not on the basis of what it actually would be to them to hold it but purely on the basis on of the faith that some other sucker would come along tomorrow willing to pay more for it than they paid today.
And that occurred for - the biggest reason that that was fueled or one of the big reasons that fueled that was the government guarantee by FHA by Freddie and Fannie essentially making a mortgage seem, although it was never explicitly stated seem like it was a full 100 percent guarantee by the federal government behind it. So it was an asset that was - would never come back to bite you. The government or Fannie and Freddie would always buy it from you. And we are not doing that here.
This is - that is definitely deliberately something that is not part of any of these programs. The loan loss reserve program, for example, caps the liability for losses at a very finite amount. Specifically if a grantee is putting up a 10 percent loan loss reserve they're putting up a million dollars, for example, there is only a million dollars that is going to be paid out for any losses there, never more than that. And very importantly never more than 10 percent of the losses on that portfolio will be paid out. So that means that the holder of that asset is - knows and is fully aware that they are responsible for the vast bulk of that asset. The underwriter, the institution that initiates that loan is responsible for the vast bulk of that asset. So they better do a good job underwriting it.
The secondary investor then is - knows also that they're holding the bulk of the rest of the loan but they're equipped much better than the mortgage industry equipped its secondary investors even just two years ago. The amount of information on the credit worthiness of the borrower and the level of thoroughness that was put into this particular loan by not only the underwriter but also by you, by the program manager in ensuring in most cases that the work is done only by a fully certified auditor and contractor. That the loan is only issued for predominantly energy efficient measures and not for granite countertops or swimming pools.
The fact that there is actually a consultation with the borrower beforehand showing them that this is a cost-effective loan in which there's actual genuine savings on a utility bill being made here. So there's a lot more about these loans that makes them very positive and that information is all being captured and transferred to the investor so that they know what they are buying. They know that there is inherent value to these assets and they know that - they're in it to - and they're liable for it. The vast majority - in fact I would say the only investors who are going to be interested in these assets are gonna be investors who are interested in holding these assets for the inherent value of them.
It will be ____ funds. It will institutional investors who have large sums of money that they need to put away into long-term assets that have steady, stable cash flows and low default rates. And that's precisely what these products offer. The size of this market will never approach what the mortgage market is and so the overall risk to the economy is nil as far as that's concerned.
But the reason that your question is so excellent is because that is first and foremost on everyone's mind as we're working on these sorts of programs and these sorts of products. How do we make sure that this does not repeat itself - does not become another mortgage-backed asset security market? And those are a couple of the ways in which we're ensuring that will not be the case.
Are these actual figures? No. Very importantly to tell you that these models are samples that I have built based on a lot of grantee's programs that I've helped and worked with. And so some of them are ballpark-ish figures but you should not take these as - you should not draw lessons from the particular numbers in these models. In many cases I had to manipulate a number here or there in order to make sure that it - it just worked out in a particular way that was visually satisfactory.
So don't use these numbers as something that you can learn from. If you're looking for some perspective on exactly how - where to start, what assumptions to start making, get in touch with me. I'll make sure that we've got a great technical assistance expert set up with you and depending on which grant program you're with I'll help make sure that you and your project officer are tapped into the peer-to-peer networks that have been established where you can reach out to other grantees who have similar types of programs or aspirations and bounce ideas off of them.
How do you adjust the introductory loan loss reserve percentage? What happens after the initial three months? Good question. In many loan loss reserve programs what we're finding is that lenders are apprehensive about entering a new market. Not only often new for them but a new market in general because there have been very few - relatively very few energy efficiency loans made in the country so far. So it's very hard for a lender to get enough data to put together a statistical analysis of exactly what the default rate is gonna be for these types of loans. And they feel like they're dipping their toes into a very deep and very unknown pool here.
The loan loss reserve by saying that we're gonna take say typically 10 percent of the first 10 percent of losses on a portfolio is a good way to get a lot of lenders involved in this market and a lot of them will look at that and say, "Well, okay. I think that's a pretty good risk coverage here. We think it's reasonable to say that, yeah, it won't be more than 10 percent losses even though we don't know exactly where the losses are gonna line up. We think it's under 10 and we'll move forward with you on that."
In some cases though we found out that lenders are still apprehensive about it and so one of the concessions that a grantee can provide a lender is to say, "Let's - why don't we create an introductory period where the loans have a much higher loss reserve percentage associated with them for an initial period of their lifetime."
In some cases though we found out that lenders are still apprehensive about it and so one of the concessions that a grantee can provide a lender is to say, "Let's - why don't we create an introductory period where the loans have a much higher loss reserve percentage associated with them for an initial period of their lifetime."
But once that introductory period is over, in this case three months, but it could be three months, six months, nine months, twelve months. Whatever works for you and your partner. That money is clawed back into the general - your fund. Your holding fund where it could be then dispersed as a reserve for a new loan that's being made.
It extends your - the number of loans that you can make while mitigating another aspect of risk for your lending partner, potentially getting them engaged in a lending program that they wouldn't have otherwise been engaged in. And, of course it's important to model that because you could get yourself into a situation where loans are being issued so fast that you - and your large reserve percentage is going out the door so fast with them that you run dry before those monies get clawed back. And all of a sudden you're choking off your loan program because you don't have any more money in your reserve to go out with new loans and so no new loans can be made until the reserve gets clawed back on their initial loans made. That's one of the reasons why it's so important to have a spreadsheet model like that to be able to track that. Okay.
Hmm. Who's responsible for loan payments if the home is sold? Is the new owner responsible or is the originator of the loan still responsible? In this particular model we're modeling an unsecured loan product. Unsecured essentially means that when Jane Smith comes in and applies for the loan she's applying for it on the basis of her own credit and that makes her liable for the loan regardless of whether or not she owns the house. And so if she sells the house she remains responsible for this loan.
They tend to be smaller value loans. $2,000 to maybe up to $15,000. And so they are much less expensive to underwrite. Generally it's just a quick check of the credit score of the borrower and then they're able to issue the loan. It does not have advantages of staying with the house, such as a PACE loan, Property-Assessed Clean Energy loan in which the loan is assessed on - through a tax bill.
And so the tax bill comes to whoever happens to own the house, regardless of who originated the loan. It does also not have the same sort of thing that you can have with some utility on bill payments where whoever's holding - whoever's paying the utility bill owes the money rather than the originator of the loan. So there are advantages and disadvantages to all those different programs. This particular model is modeling the unsecured where the individual is responsible for the loan regardless of whether or not they still live in the house.
Could these calculations and spreadsheets be used for a revolving loan fund internally in a county or city? Absolutely. It's a little bit easier to model because there's a lot - there are fewer moving parts but an internal revolving loan fund being managed by a county or a city, absolutely this could be extremely valuable and we could easily model that so that you see exactly when your loans are going out. What rate have they been going out at? Project kind of where things are going.
And very importantly, see where - at what point the loans start getting - coming back in and when you're gonna be able to loan additional money. And how much more money are you gonna be able to loan in any particular period. And that may change your decisions about which projects then to fund, what length of term to offer, and all of that in order to make your program sustainable at the level you want it to be sustainable at.
Couple of questions here that I don't quite understand the question from the way that it's - the grammar. If you want to rephrase the "We must be loaned for fixing our homes that cost effectiveness?" And I can get to that one on the swing around.
So could the base loan loss reserve percentage be up to 90 percent? Yes, absolutely. There is - you could bring that base - either the base or the introductory loan loss reserve percentage up as high as you wanted to. If - up 200 percent in fact. If it's still in loan loss reserve it's capped in there. The liability is finite. You're only gonna lose as much money as you put up against it. Folks will obviously - oops. Sorry about that.
Folks will tell you, advise you to say that you want to balance the amount of loan loss reserve you put against there with not only mitigating the risks but you also want to make sure that the lender, the underwriter does know - does feel that they have skin in the game. That they will incur a loss if the loans go bad. You - if you did a 100 percent or if you did an excessively large loan loss reserve you perhaps will be inviting sloppy underwriting because the lender feels like, "Well the loan's guaranteed. I don't have to worry about the quality of the loan."
But on the other hand there are communities in which - that have been hit hard by the tumble in real estate prices that have been hit hard by unemployment and where, therefore rightfully so, lenders feel like there is greater risk of defaults in lending in those particular areas. And so there are grantees that are working loan loss reserve percentages at the 50 percent level. That's the - that's a point where it's extremely high. They're not getting a ton of leverage off of it but they're getting loans made where there wouldn't be loans otherwise made.
Why would a lender feel comfortable with a high loan loss reserve rate that only lasts for three months or even for a year? Would not the default rate be more likely to occur later in a seven or ten-year loan? That's a good question and that is, in fact, an answer that you as program managers are going to be finally providing sufficient data to the research community to answer. I don't think that anybody can say right now whether or not - where the default rates will most likely occur or where the default will be highest.
In a couple of our models we've got some points where we build out or where we allow you to actually - yeah, here. I think on this loan loss, the revolving loan fund model where we've got different loan terms here showing different length of lives of the loans and an initial assumption that there's gonna be an even distribution across those four buckets of 25 percent each. But in a default calculator here like this one in which, again, you've got amortization against every month's worth of loans put out there, we can get pretty sophisticated in modeling when those loans will occur.
And your guess is as good as mine if not better in trying to figure out whether or not most of the losses will occur early in the period - life of the loan or later in the life of the loan. I think that there's a reasonable argument to say that if a creditor is a poor credit risk and they're likely to default, it's more likely that they're going to just not pay. So in effect then the default occurs early in the life. And if you've got someone who's been successful in paying month after month after month for six years it's probably a better likelihood that they're gonna continue to pay in the seventh year than not. But I don't know for sure.
What the introductory loan loss reserve mechanism is there to do is it's one, a concession that to somewhat subjectively say to the lender that, "Listen. We'll cover an additional amount of potential risk for you in order to get you in the game here if that's what it takes. We can concede a little bit more risk coverage for you, this initial introductory period in which we've kind of quarantined the loans. And in effect only the healthy loans come out of the quarantine period. So you can feel like only healthy loans are being held in your portfolio."
But what is that quarantine? It's only quarantined them from early defaults. It hasn't quarantined them necessarily from late defaults. We don't know exactly when that period's gonna be. And it doesn't guarantee the - that it saves you in the late default points but it's what we can do right now. So it's a mechanism that we can use right now.
Let's see. Why not attach the loan on such - as college loans so they must be paid? Again, if you could rephrase that so that I understand a little bit better what the question is I'll be glad to jump in on that one.
This spreadsheet looks very valuable. Will there be support on customization? Yes, absolutely. As I said our first line of support is our technical assistance experts who are available to help you customize and tailor a spreadsheet to your particular financing programs.
We also will have a lot of peer-to-peer networks set up, both SEP and EECBG, as well as Better Buildings have big peer-to-peer networks set up to get help from your peers to see what they've already built. And we've got a bunch of resources on the solutions center. And you've got some folks like me at the DOE who are always happy to take your call and see what we can help you.
Usually it's a matter of just getting somebody who is - understands the financing program on the one hand. Sitting down at the same table with somebody who spent too much time on Excel and knows a lot of the Excel tricks in order to build these things out. None of it's rocket science though. It does obviously sometimes get to being large spreadsheets in which we're doing multiple, multiple calculation in different ways. Let's see. Where are we next?
Okay. Well it looks like we've pretty much come to the end of the questions there that I've seen pop up. Oh. Could you please discuss repayment terms? Does this model show homeowner - no, I already got that one. Got to that question. Great. All right. Well, I hope I've been able to answer those few questions. I will, as I said, e-mail out to everyone who is attending this the spreadsheets that I've just been showing you.
I know I'll definitely be able to show you the smaller spreadsheet, which is this one here, which is a little bit prettier graphics but it's got less in the engine because it's not amortizing month by month loan principals, loan interest repayments, and all of that. And then I'll see what I need to do to get this - some of these larger, fully amortizing models out to you as well. And if I can't get 'em out to you in an e-mail I'll provide you a link as soon as I've been able to post these up to the Solutions Center where you'll be able to download them.
This is hopefully been something that has inspired some thought and triggered some different imaginations along the lines of what you as program managers can do to enhance your control over your program, your visibility into your program, and the ways in which you're gonna learn from what your programs doing how to adapt your program and constantly improve your program.
A lot of different tools out there and we'd love to help you develop them, customize them, tailor them to your particular needs. Reach out to me and the DOE any time or go to the Solutions Center there. You'll find a host of resources as well as links over to the technical assistance side.
So, you know what? Someone just asked for my contact information and I'll make sure that's included out to an e-mail to everybody. You'll get my e-mail address so that you can reach out to me anytime. Great.
Well, thanks again for your time. I hope this has been helpful and I hope that you all have a great deal of good fortune and luck with your programs. So thank you very much and have a great day.