Financial Analytics * OUTDATED, SEE BELOW FOR THE LATEST * DataSelf & Tableau

For joining us, this is Johnny Girardi, Data Self founder and CEO. And today we'll talk about financial analytics with Data Self in tableau. So what we're going to be covering is first of all I want to start from the, from the end of the process how one of our clients, the CFO of one of our clients is usually using our out of the box in our financial analytics out of the box to come up with very insightful and very interactive dashboard. So I want to show you that first, get a glimpse of possibilities. Then I'll talk a little bit about the pieces, balance sheet and property losses. A lot of people ask us about it, so I'll talk about it. I'll talk a little bit about budgeting, what if scenarios and forecast. Focus a little bit more on the financial analytics components that quite often I don't see many people taking advantage of good financial analytics. And we have a varied solution right now. At the end of the session we'll have a Q and A. So let's switch gears and I'm going to go now and switch to Data Self analytics. Show me the dashboard put in full screen. This is an example of a CFO dashboard built by one of our clients. The idea of the CFO is he needs to be on top of several financial metrics as well as operational metrics. He goes beyond the financials. He's really looking at the whole operation and he's managing many of the KPIs. This dashboard alone, when he logs to every morning and he opens the data analytics, he logs into data analytics. This dashboard pops right off the bat. It's the first page that he sees. This dashboard combines 11 different data sources. It has receivables, information, payables, payroll cash on hand, open sales orders, historical sales, CRM opportunity information, AR, aging, GL financials, many pieces of the GL financial metrics, inventory on hand and also website visitors. All this information comes into the Data Self data warehouse, then comes into the CFO dashboard. He actually built this dashboard himself. But let me give you how insightful and interactive this dashboard is. One of the most important pieces is the 60 day cash flow projection. This could be 30 days, 90 days a year, doesn't matter. In this case was a six day cash flow projection. What he has here is he has the receivables with a due date for next 60 days, the payables as negative amount of money, what is supposed to be paid as payroll and also how much cash he has on hand today. When you add all of these metrics Together there is this projection cash on hand for the next 60 days. And he has a line of credit available. In this case, it's just a sample data, it's not his data, but has a line of credit that covers, in this case, $1.5 million. So as long as he's projected on hand is below this threshold, no worries. Now, sometimes they're doing what if scenarios and let's say, hey, you know, this customer here has a major payment on this day and is not coming because of some problems. And then he removes this payment, this inbound money, and what happens is now the cash flow is going to go over the line of credit and he'll have to deal with that. So this is a very simple but very interactive way to look at cash flow projection and allow them to be on top of things and work proactively before they hit issues. This whole left column has what is called lead KPIs. Lead KPIs is looking into the future. And the idea is to allow decision makers to come up with decisions to influence the process that's going to come in the future. Like I said, cash flow projection. If we're going to run out of money at some point, what should I do? So before it happens. This is another example of lead KPI is orders to be shipped in the next 60 days. And as he puts the mouse over each one of these, it shows the customers and how much money is supposed to be shipped in the next 60 days. This section is open opportunities from their CRM system. And it's just showing hot opportunities expected to be closed in the next 60 days. By hot is above 75% probability. So by clicking here, he can see how much is probable from each of these opportunities. All of these segments, they are interactive in a way. Let's say in the AR aging. If I click on, let's say on the dashboard here, it goes into a more detailed tab of this whole segment. In this case, it's showing detailed AR aging information. So by customer and what's on time in the other AR aging buckets. And then let's say, hey, this client has very late invoices. What's going on? I click on it and by clicking on it, I could right off the bat email the customer. I click here, I go into my email and I can send a message to the customer or I can view the customer in another application like their CRM or when I clicked here, when you click on the left, it shows what invoices are for this particular customer. So for this one Before I actually email the client, I want to see what's going on. And this invoice here is big and maybe I want to take a look at the invoice and I click on it. Their system allowed us to connect all these pieces together. So by clicking here I can open an invoice. So the invoice is in a certain place. The PDF file with a specific name in the tool can make this very interactive to let the CFO jump from one place to another. The database all within the BI system. So very interactive. This second column here is about, I mean this whole other section of Dashboard is about lag KPIs meaning KPIs looking at performance that's been done already today from the past. This column in the middle here is about last year and this year. So last year is blue, this year is black. The top one shows revenue year over year. So I can tell I'm growing a little bit since last year to date. And I can see on the curves, you know, sales by month last year and this year side by side and see that performance. The bottom is showing website visits. So this year quite bigger than last year, which is good. And how are we doing on the east coast? So I can select the east coast and right there I see that on the east coast we grew more than twice as much. Big increase. And maybe I want to see more details about it. So in this case I can click on website visit and I can go into another page that gets more detail and and here can be very interactive. So I'm seeing colors darker. More visits and I can tell Florida grew a lot. New York and Virginia also grew significantly. Here there are a bunch of financial metrics, EBITDA for instance. Click on it, go again is more details to see the information by month and accumulated throughout the year. And the same information on a chart format. Oopsie. Revenues, gross profit, cost of sales, revenue mix. This top right corner is very interesting because the CFO has to keep a close attention to three accounts from expenses, three expense accounts very closely. So he has those three accounts. Admin, marketing and operational expenses. The bar shows the total and the line shows the budget for that particular account. And I can see on marketing that it's a red bar meaning it's above the budget. And this pop up window is showing the the amount of the expense, the budget and what budget is available in this case is a negative number, means I'm over budget, so I have to work on it. This section here is inventory assets by quarter and they have you know they sell products and they have a maximum on hand. In this quarter, we are over the maximum on hand. So when he clicks on this bar, he goes to a different dashboard. Here he sees again the same initial chart, but then he sees from the financial metrics, the inventory assets by business unit. And then on the right side, this is actual inventory on hand today. And these are the top 10 products by cost on hand, which adds up to a total of $207,000. So they take a big chunk of the maximum is just by the top 10 products. And if I put the mouse over each one of the products, I can see on the bottom sheet how much of each item I have in different warehouses. So for my top item, most of the inventory is on the China warehouse. So very interactive. The CFO built this dashboard about six months after they deployed the Data Self system in pretty much in the course of a month. He started from scratch and he took care of it. He uses every day and it's usually a starting point to see what's going on and start taking actions, making calls and help the operation be on top of their goals. All right, now let's talk a little bit about balance sheet and profit and losses. Now, when it comes to balance sheet and profit and losses, many of our clients still want the information to be showed in Excel. So let me start showing in Excel first. So I'm going to open Excel now. And this is a very common kind of, you know, simple in a way, income statement in Excel, nicely formatted with the right spaces and columns and underlines, whatever you want with different, you know, metrics, you know, whatnot. Once a Excel dashboard report like this is being created, pretty much the refresh, the report becomes completely automated. You don't have to worry. Every day when you open, the report is refreshed. So this is pretty much looking at actuals and growth year over year. When it comes to budget, fairly similar in Excel, you have the freedom of changing the layout the way that you want. So information looks very clean, very organized and completely automated. Now, we always encourage people to look at also their financials, such as balance sheet, profit and losses in the tableau format. So let me go to some examples of the tableau format we have. And here I'm looking at PL this year. Very simple. The formatting is not what is called the traditional format of a, P and L, but it has things grouped the proper way and the numbers match. So for format is a little different, but you're looking at the same numbers. And when you go to these Tableau based analytics. There's a lot more possibilities of how you can slice and dice the data and how you can arrange it. And it's so much easier to do those things in this example here. Let's say if I'm going to drill down from this level, I just click expand and now I'm seeing more detailed information. If I click expand I can drill down to the next level and we can build drill down capabilities to go as far down as is possible with your data. And then you have the filters on the right if you want to just see for a certain division, certain region, different GL segments, very straightforward. And then out of the box. We have actually a lot of pre configured of these reports so you don't have to start from scratch if you want to see let's say this year by period. So right here we have a P and L by month showing the total on the far right and then you see all the details at the bottom. So very straightforward. Financials in the web format. Sometimes people want to license their financials by different GL segments. Doesn't matter how many segments you have 2, 3, 5, 10, 20 in the framework. Once they are mapped they become easy to slice and dice. The data in this example is just another pl. But then the segment two, which in this case is for division is on the columns and then it shows a P and L for each division. And let's say, hey, in this case I don't want to see my direct division. I just uncheck the division that box and then I narrow down my analysis for only the commercial and retail divisions in the title says that right there. So very straightforward, very easy. These reports can be accessed using web mobile devices. You can also email them out and make the whole process of distribution the reports completely automated. For balance sheet is the same idea, same kind of concept. You can come to the balance sheet and start expanding it going drilling down to more details as far down as you want. So balance sheet and property and losses, you can't change much. The basic framework is the same. It meets the requirements of each individual client. It can be multi company, it can be multi currency. So very framework, very, very extensive framework to tackle different needs. Let's talk a little bit about budgeting. So now I'm switching to a different example. Budgeting can be very different depending on the needs of each company. Some companies keep their budgeting numbers in the RP system and they just use the analytics to slice and dice their data. This is one example. They come to the report, they see the accounts, the sub accounts, what's being posted, what the budget is, variations by figure and by percentage. And then you're just using to report. You can use colors to tell what's above and below budget and make charts out of it. So many companies using the BI only to slice and dice their budgets by different gel segments, territories, divisions and whatnot. Some companies, they want to do more kind of analytics out of it. And this is a very simple example and it's a very common example that many people tell me. Well, when we put in charts, the information doesn't look good. It's very hard to read. And I'm just simulating one problem here, which is when you open this actual versus budget, there is one account in this case that is so big that the charts for everything else like, you know, this chart is almost like meaningless. It should be very good because the bar represents what the actual budget is. This little line is what the budget in the color. If it's blue, it's below budget. If it's above, above is beyond budget. In this case, our expense account. So beyond is bad. But you know, when you look at the small bars, it's almost like, you know, you can't make much use of this particular chart. But once you are using this framework, it's very easy to say, hey, this account I already see, I don't care about anymore. I'm going to exclude. And there you go, I excluded the big account. Let me just go back to show I'm excluding the NSEN and leave IXAS and everyone else. And by doing that, so access is the next one. It's so much easier to now see how each account is doing. So if you're focusing on the red ones, you can tell many of them are below the budget. Actually this is actually a revenue, so being below the budget is not good. You want to be above the budget. So red means you are below budget, below the you forecast the revenues. Now in this case, we also have ways to influence the budget. So the budget numbers are coming from the erp. But in this example, they want to actually apply a growth range to see what happens. So let's say I want to increase by 15% my budget. And by doing that now, many of the accounts went below budget and we need to work better on those things. So I'm just showing here what happens when you apply the 15% and how the colors change and how that can be insightful for people to be on top of things and focus on the ones that are having the most challenges. The Next example is a simple forecast with what if. Usually what if scenarios, they have a lot of different variables that influence different parts of the process. So let me start first talking about the forecast of this example here. This example we have actuals as the top line in the forecast at the bottom. In this example, the forecast comes from last year plus a growth percentage which is defined by this parameter, minus a churn rate. You know the on average, this customer has a 5% drop in their customer base. So they usually put the churn rate here and then to make the projection for this year. But then they want to grow the accounts and they put hey, want to grow our accounts by 20% total this year. So you make those changes and you see what happens with your numbers. Now in this case, for each of the accounts here on the far right, we have a little chart showing the bars which are the actuals this year. And then the line is the forecast from taking last year in applying these different ratios, the churn rate and the growth. If the actual is below, the forecast is red. If the actual is above is blue. So by looking at this, you can have an idea of how well each account is doing. If you try to have the same perspective from the numbers, it's so much harder. So using this kind of visual cues can make people much more quickly focused on the right accounts. In this case, maybe I don't want to see the future. I just want to focus through August, which is my current year to date. So I just removed those three months and now I can see in a much more concise view the what if scenario. In the what if in this case, I'm using these two different parameters to control growth and decline. Let's say if I want to grow 30% so far, in this case, now I'm below my target. This is the total for the whole year which comes from the same year. So I have several different components here influencing the whole what if scenario. Now let's talk a little bit about financial analytics and how that can fulfill the needs of different clients. And let me go to the desktop client here and show the framework we have to fulfill different financial analytics needs. So let me take this financial analytics and drag and drop my analytics one bucket and then let me put the posted amount. So what I have here is we add to the data self data warehouse a table that will allow you to put into buckets your different GL accounts. This is the out of the box, but it can be customized. But initially I have all different accounts and this is showing how much is being allocated. If I expand the analytics one to analytics two, now I have more details from each one of these big buckets. If I drill down out of the box, we go into the actual segment one of your GL chart of accounts. But all of these things can be customized in different shape or form. Now why do we need this? Well, it's because quite often companies want to keep track of ratios like, you know, your wages versus your revenues or your gross profit or maybe your marketing expenses versus other of these buckets or any bucket that you can imagine. So let me show an example of a custom application that some of our clients are doing. So I just created this, my KPI and I just created. Let me show how I created. I'm going to go to edit this. My KPI example is a little expression built by the user that there is no IT involved, no data, self involved personnel, it's just the user, they say, hey, actually I am the person responsible for bad debt expense. So he said my KPI is when the analytics 3 equals to this, then I have the actual amount. So he created this KPI. Let me show how it works. I'm just going to drag and drop this KPI into my query. So simply what it does is when the tool finds that particular account, let me just show it again. When it finds a particular account, it tells this KPI has an actual value. And for this particular example, this person who is responsible for the bad debt expenses, he also created a ratio. Let me show the ratio. The ratio is he divides the bad debt expense by all the liabilities accounts. So what happens is he takes all the money that goes into the liability. In the analytics one, everything's liability adds up this and makes a ratio. So now he has again the KPI which looks only at an expense account, bad debt expense and divides by the whole liability segment. So now he created these two metrics that he needs to monitor constantly. And then here I have in this other tab an example. So he has my KPI and my KPI ratio by year, by quarter and see how things are going. So financial analytics, once these buckets have been mapped to the way that you want to analyze your different GL segments, it will allow users to be very creative and create their own metrics to keep track of their own important needs or from a company wide standpoint. And here we have a bunch of different KPIs we have available out of the box. So if the out of the box is valuable for you, let's say you want to create a new view that would look at let's say revenues, gross profit and net profit amounts and percentages. I want to put this all in a dashboard. How can I do it? Well, let's do that. So I'm going to create a new view here from scratch. And again I want to see revenues, gross profit, net profit, and net profit ratio versus gross profit. All of these four different KPIs are available out of the box. So if I want to see revenues, I come here and these are my revenues. I'm going to make them in my rows. So this all revenues. And actually let me break this down by date and let me make this a bar. And I want to make this on a month to date basis. So right now what I'm looking is revenues by month in my sample database. I also want to see gross profit. I come here and I find gross profit right here and I just drag and drop. So right now I have gross profit here and revenues here. Let's put them together. So I'm going to put a dual axis and I'm going to synchronize the axis to be on the same size. And there it is. I can even hide this. No, let's leave it. So right now I have gross profit in green, new revenues in black by month. Very quick and easy. Well, I also want to see net profit. Well, beautiful, it's ready to go. I come here and I find net profit right here. And net profit is this one. I'm going to drag and drop. So at the bottom now I have gross profit for the whole operation. And I also want to see the ratio of net profit versus gross profit. Maybe I don't care about revenues or if I can, I just choose revenues. But in this case, I want to see net profit divided by gross profit. It's right here. It's ready to go. I just drag and drop. And in this case I want to make the net profit percentage a line. Oops, sorry. I want to see the net profit divided by gross profit a line. So there's a line here. I, I want to take this one and put labels on it. And also I want to put the color to look nice and I want to combine this with my net profit. So I'll have here I have and actually let make my net profit as a different color so they just don't get confused. I'm going to use brown perhaps maybe purple. There you go. So in a few minutes I was able to build from scratch this nice chart that here. The top has revenues, gross profit amount per month. And at the bottom I have the net profit, which is the bar and the line is the net profit divided by gross profit showing also in a color format. So using the financial analytics built into the data self, you can allow users to easily slice and dice their data by taking advantage of this very comprehensive list of KPIs. How do we customize this? Now I'm showing a sample of a table that tells how we put your different GL account numbers into these different buckets. This is just an example that we use out of the box. So you just map your GL accounts in this from 2 and you tell how you want to group into the analytics one into analytics two. And by default analytics three is your GL segment one. But if you want to create a segment in analytics three, four buckets, as many as you want can be created. Once we bring you to data warehouse and we plug to the data cell of tableau extracts, then you have the beauty of having all of these things and you can start slicing dice your data in any shape or form. All right, and we have created actually hundreds of dashboards looking at these financial analytic KPIs. This is just an example where we're comparing operating income versus gross profit. So in this case the gross profit is the blue bar, the operating income is the other bars, with either orange for negative numbers and blue for positive numbers. So you see the amounts here and the percent at the bottom of your operating income growth and all information also on a tabular format in the actual operating income report at the bottom. So we have hundreds of these dashboards out of the box already using all of these different KPIs that I'm showing you here. Sometimes people say, hey, actually I want to see how my wages are versus other of your main KPIs. Well, you don't need to build from scratch. It's ready to plug and play. Once you map your wages and tell what the range of the agile account numbers come from, reports like this will pop right off the bat showing your KPIs. All right, okay, so I covered what I want from a presentation standpoint. Let me go back here and just talk a little bit about how all of these things come together. So what we do is we pull data from your data sources, your ERP system, your CRM system, Google Analytics, payroll, whatever your data sources are, we can pull into the data self data warehouse. A lot of the magic or the heavy lifting of easy analytics is done in the data warehouse. If you have multi company, multi currency, if you have complex rules to define how you want to see your metrics. Usually the data warehouse is where we do the heavy lifting. Then we plug the tableau server to the can you hear me? Looks like I'm having problems with my sound. Can you please use the chat box to tell me if you can hear my voice well? Can you hear my voice well? If you can please chat me? Someone told me a message that they cannot hear me. Okay. I'm assuming. Okay, it's back. Thank you for your patience. So once we plug the tableau server to the data warehouse and we bring that information into tableau extracts, then it's when users regardless of how much training, not regardless of how much training they have, but users will be able to easily access their reports and dashboards. They will be easy be able to slice and dice the data, create new reports and dashboards from scratch, or receive alerts out of the box. We have more than 5,000 reports, dashboards and KPIs. On the financial side we have about a thousand. So if you want to learn more about from it, please contact me and I'll be glad to talk more details with that. I cover what I prepared. If you have questions or comments, you can use the chat window on the Goto webinar panel and I will be entering right now. Let me see if there are any questions. The questions here. Question for Carl Carl is asking if we have our solution for Sage X3. Carl, thanks for your question. Yes, let me actually come here and go to dataself.com so if you go to dataself.com sorry. Go to Solutions and click analytics by ERP and CRM. This lists all the current systems that we have out of the box. Solutions ready to go. So yes, Sage XV is one of them. Even if you don't have one of these, usually we can build connectors to new data sources fairly quickly. And if you want to learn what is available out of the box, you come to Solutions and click 5000 KPI's and this page will list kind of a summary of what we have for CRM for core financials and for distribution, manufacturing. And if you want to see in detail a list of these reports, you can click Download phone list. You get this Excel file and if you open the Excel file you can come here and say I don't want to see what's available for sales. You come here, you have how the data source looks in Tableau. So all sample of available out of the box. And if you want to see reports and dashboards, maybe dashboards, I can click Dashboards you can click expand to see detailed name of all the dashboards available and reports available out of the box. If you want to see for GL Trend for instance, then here we have again the data source available in Tableau. And if you want to see your dashboard, you can click here and just expand to see kind of out of the box solution we have. Carl, thanks for your question. Let's see if we have more people with more questions. Nope, that looks like the only question. Okay, well folks, this session is being recorded and we'll make the recording available by tomorrow. If you have more question or comments, feel free to contact me. You'll find my cell phone here as well as my email. I hope you have a great Wednesday. Thanks for joining. Have a great day. Thank you.