What makes designing a viable budget plan difficult for businesses is the sheer variability between different bills and invoices. Each transaction is due on a certain date and each transaction has a different amount. It's not immediately intuitive from a list of dates and numbers when bills should best be paid or when you should encourage your clients to pay by. To make this even more complicated, more often than not, bills and invoices aren't paid on time. There's a lot of uncertainty surrounding the number of weeks late your clients could end up paying you. Maybe sometimes the business itself needs to pay its bills late due to extenuating circumstances. For management software to be reliable and trustworthy, it has to be able to account for this uncertainty that's ubiquitious in the real world. Luckily, the machine learning technology behind Invrea Scenarios is built to handle unknown variables and how uncertainty affects the outcomes you do care about, like the budget plan or future profit.

Let's take a look at a spreadsheet with a toy dataset of bills and invoices, not unlike the ones you would find in any business. Download it to follow along.

Once you open the spreadsheet, you should immediately notice three sections: Anticipated Cash Flow Events, Customer Characteristics, and Cash Flow Statements. Starting with anticipated cash flow events (blue box), it should be clear that this is just a dump of the data. We've got three separate bills to three organizations and three different invoices to three customers. And our small business has a starting balance of 1,000 pounds.

As mentioned before, often these bills and invoices are paid late. To represent that, we added a Weeks Late column, which means the number of weeks that a particular invoice or bill can be postponed to (after the official due date). For example, the bill to Organization 1 is officially due on January 16th but it can be postponed for at most 4 weeks. Using that, we created a projected date that is actually a random variable that picks a random week between the official due date and the max-allowed postponed due date. If you refresh the spreadsheet (by pressing F9), you can see that the projected dates for each bill and invoice change. For Organization 1, the projected date is either going to be 1, 2, 3 or 4 weeks after the official due date.

The max number of weeks late for the bills are fixed, since as the business, I know exactly how much time and how much leeway I have to pay my bills. However, th max number of weeks late for the invoices are random and change with each spreadsheet calculation. Why? It's because how late the invoice is expected to be paid depends on the particular client we are working with. For example, we expect a bad client to probably pay us pretty late (somewhere around 5-8 weeks late), but we expect a good client to pay us back close to on-time (around 0-2 weeks late). That's actually what our Customer Characteristics table (green box) is for.

Here we've created three types of customers: good, average, and bad. For each type of customer, we defined a probability distribution around when we expect this type of customer to pay us back. For example, a good customer has a 80% probability of paying us back on time, while an average customer only has a 30% probability of doing so. Back to the blue table, each of the invoices actually has a customer type attached to it, defining whether this client is good, average, or bad. Now, using the right probability distribution, we can get a prediction for how many weeks late this invoice will be by sampling from that distribution. Again, we can get a projected due date per invoice just like we did for each bill.

Using the information from the blue table and green table, we can get a week by week projection of our balance, aka how much money do we have left in our bank on week X? Because our projected due dates for each bill and invoice is random, our balance projections are also random (refreshing the spreadsheet changes the monetary values). You can see that depending on when we pay our bills and depending on when the clients pay us back, we sometimes get a negative balance on week 1, sometimes on week 2, ..., sometimes we don't get a negative balance at all. Now, we come back to our original problem, how do we decide when to pay our bills such that we don't get this negative balance at all?

We can get a summary of all the possible scenarios that can happen with Invrea Scenarios. Our Excel plugin adds a new tab to the Excel ribbon that has a "RUN" button. Pressing this generates thousands of scenarios (different combinations of values for each cell in the spreadsheet). For example, I ran 5000 scenarios, and here's the distribution of our expected balance over time.

Here the blue line is the current scenario; the red line is the average over all scenarios; the red region is a 50% confidence interval; the yellow region is a 95% confidence interval. You can see that in some scenarios we are definitely going under 0 pounds, while in some scenarios we never do. We can also look at the distribution of when the first week we run out of money is (17 means we never run out of money). From the histogram, it looks like two things could happen -- we could really run of money in the first eight weeks, or not at all!

The power of the Invrea Scenarios plugin is that not only can you define random cells as we did with the projected due dates, but you can generate scenarios given a certain condition. For example, we might want to only see cases in which we don't run out of money at all. That might be useful for us in developing a good budget plan. To do that in Scenarios, we add an ACTUAL cell that acts similar to a filter that only lets scenarios pass through if we don't run out of money ever.

Then, when we regenerate 5000 scenarios, all 5000 scenarios are guaranteed to be cases in which the business stays afloat. With that, we can analyze when the business paid its bills and when the clients paid their invoices given that we know the business never runs out of resources. Those distributions help us create a robust and practical payment schedule. Let's first take a look at the same balance distribution and distribution of the first week out of money but with our added condition.

From the new balance time-series plot, we can confirm that the distribution never crosses the 0 pound line. And if we look at the predictions for the first week out of money, the only value with 100% probability is 17, which indicates that we never run out of money. Cool, our condition worked. Given that, let's look at the distribution for our bills and invoices.

Distributions of expected payment dates for bills

Distributions of expected payment dates for invoices

Digesting these plots a little bit, an educated budget plan that probabilitistically handles uncertainty in payment dates would look something like this (made by taking the most probable value from each plot):

Description Week Number
Bill (Organization #1) No earlier than 5-6
Bill (Organization #2) No earlier than 7
Bill (Organization #3) No earlier than 6
Invoice (Organization #1) No later than 5
Invoice (Organization #2) No later than 2
Invoice (Organization #3) No later than 6

If you adhere to this budget plan, the chances are that your business won't run out of money given the invoices and bills and customer characteristics described above. Once the business gets more invoices or more bills, it's easy to add them to the spreadsheet, regenerate scenarios and reanalyze how their budget plan should adapt to the new data. Using Invrea Scenarios, you get to create a budget plan that changes with your business and adjusts to new information. That kind of power can be really useful when making important business decisions like choosing customers or setting payment deadlines, etc.

One of the cool things we can do to improve our spreadsheet is to make the customer characteristics section better. Before, we were clustering our customers into three categories, but that might be oversimplified. Clients can sometimes be good, sometimes be bad, and often somewhere in between. So, what we can do is actually build a machine learning estimator to predict the number of weeks late a client is going to pay an invoice given the client's complete record of transactions. This way, we can get a more accurate and precise estimation of lateness that is personalized for each client. We can also do this directly in Excel. We added a new section that holds all the previous transactions for each of our three clients. Each transaction has 3 properties: it can be recurring or one-time, it has an amount, and it has the number of weeks late this transaction was paid. Using the first two, we will build a predictor to estimate the number of weeks late. Using Invrea Scenarios, we can use the BAYESLINEST function, which is a Bayesian Linear Regressor, or in other words, a severly upgraded best-fit line. You can download this version of the spreadsheet here.

History of Transactions per Client

3 BAYESLINEST estimators (one for each client)

Invrea Scenarios helps a lot with making these kind of predictions but it doesn't stop there. The plugin can model uncertainty and make predictions given our assumptions and new data for business decisions, insurance claims, consulting cases, etc. If you can model your decision as relationships between cells in an Excel spreadsheet, then it's quite likely that Scenarios can help. The team at Invrea is dedicated to opening this kind of machine learning to every industry possible. If you would like more information, a more detailed demo, or some help setting up a worksheet of your own, we'd love to lend a hand. You can find us at this email.

The alpha version of Invrea Scenarios is free. You can request a download link here.