This is an awesome question and I think that this would be a really useful analysis. Would you mind posting the data you have (or at least a sample) so we can understand the current structure of the data?
Hi Fabio - in your Accounts Receivable example, you could create a calculated field that merges historical (actual) AR figures with future (projected) AR based on an existing "Future Sales" forecast like this:
IF ISNULL(Accounts Receivable) THEN (Future Sales)*(Pct Multiplier for AR) ELSE (Accounts Receivable) END
This calculated field (call it "AR_display" or something) could be included in the table and would show both numbers in the same row of the balance sheet.
You could also create another calcualted field called "Type" that indicates whether the figure is actual or forecasted as follows:
IF ISNULL(Accounts Receivable) THEN "Forecast" ELSE "Actual" END
You could use this field to change the color of the table to make it easy to spot what type of figure it is.
Echoing Brandon - this is an intriguing use of Tableau. What would be even better is if someone in finance out there used Tableau to visualize the typical income statement / balance sheet / cash flow tables in graphs instead of tables.
I recently posted a waterfall chart for Facebook's income statement that attempted to do this: http://dataremixed.com/2012/02/a-facebook-waterfall/
Hi Ben, Hi Brandbon,
I read your blog yesterday. Waterfall chart work great in Tableau.
I think it makes sense to use Tableau rather than Excel because financial simulation is about modeling (for instance "putting together" one business with another, or splitting them) and unless you know from the start (which you rarely do) how you want to slice and dice your data, an Excel model (which is inherently "mono-dimensional") risks becoming very complex very quickly while you try to modify it to accommodate the different scenarios that pop up. I remember this as a nightmare.
So this time I decide to build up an excel "table" with all the data of the different companies, on record on top of another, and then see if I could build up the model in Tableau.
I am attaching the structure of the Excel. All numbers are random and the names of the divisions are also invented.
On the right side of the sheet you have the PL and BS entries. (of course BS entries do not balance since they are random). On the right of the sheet I put a formula that "creates" different "measures (revenues, costs, assets,...) that I need in Tableau.
For instance in Tableau I need a table that has the years on the columns, and revenues, direct costs, operating margin, indirect costs, Ebitda, eccetera on the rows. Operating margin must be revenues - direct costs. I did not know how to do that without a separate measures.
And then I have the issue of how to project all this into the future...
Thanks again Ben for the answer, will try it out!
all the best
financial model.xlsx 614.0 KB
I tried to apply your technique, and it looks good.
One question - when you do modeling of a company you are trying to sell or buy you often want to "slice and dice" your model by business, division, or whatever. You might want to see which of the divisions generates (or requires) more cash, and so forth.
Doing this in Excel is not easy, and quickly leads to a complex model. It should work much better or Tableau
Your idea assumed "one" Accounts Payable tied via some parameter to "one" stream of revenues. Would it be possible/make sense build the Balance Sheet in Excel as a sum of many Accounts Payable elements (each calculated as the product of the revenues of that division X the specific parameter of that division)?
Final question. In modeling it is very common to assume a "different" future. For instance to assume that the ratio between revenues and accounts receivable goes up because customers will pay you before eccetera. Is it possible to tie your function to different constants tied to time?
I'm pretty sure both of your ideas are possible - you can sum up multiple revenue entries for your AR estimation, and you can create different scenarios for the AR calculation using parameters and calculated fields. The attached example uses a simple slider to allow you to change the AR multiplier for all years, and you could even take it a step further and have different multipliers for different years, etc.
Hope this helps!
Thanks so much for this
Good question and nice responses which I'm going to use to think about something I'm working on. My problem has to do with cash available to a local government.
I've already done a basic dashboard with weekly snapshots of cash balances back several years. It provides some interactivity, such as a parameter based "danger zone" and the quick filter to choose past years for comparisons with current.
The past couple of years, the cash generated organically from operations and taxes has been supplemented by intra-fiscal year borrowing to smooth out cash flow. That's important to assess current position, but can also misleading in thinking about even near term futures.
The ability to do some projections will be very useful. So thanks!
Will post mine when it's done. I've just returned from four days of Tableau training so give me another week or two.
Fabio: No problem - happy to help.
John: Interesting, & have fun putting your training to use!
Can someone provide the underlying data or the twbx for this? I have similar data to that display above but I am having a hard time creating a graph like this.
@ben jones this is a very interesting concept to visualize could either of you share more details or step by step guide for other tableau users, thank you guys for such an amazing post