Representing Unlevered Discounted Cash Flow Model Using XBRL

Tuesday, September 4, 2018 at 02:27PM
Charlie in Becoming an XBRL Master Craftsman

One approach to determining the value of an economic entity is to use discounted future cash flows.  You can determine discounted future cash flows by creating a discounted cash flows model (DCFM). Finance majors, accounting majors, and MBAs learn how to create these models in college.

If you are not familiar with the DCFM, here is some information that will help you understand it:

• Six steps to building a DCFM
• Quick DCFM lesson with Excel including videos:
  • Part 1 of lesson
  • Part 2 of lesson
  • Excel spreadsheet that goes with lesson
  • Video of part 1 lesson
  • Video of part 2 lesson
  • Adding a sensitivity analysis to the model

Now to be clear, the example above shows an unlevered discounted cash flow model.  There is also a levered discounted cash flow model.  In fact, there are lots of different types of models. Here is a list of some of the different types of models.

Microsoft DCFM represented in XBRL

I wanted to see if I could create a DCFM for Microsoft in XBRL.  I selected Microsoft pretty much at random although I did give them high consideration because their fundamental accounting concept relations are extremely good (i.e. they have no errors) for the 9 10-Ks they have submitted to the SEC.

In building the Microsoft DCFM model, I considered information in all of these models.  I decided to use the Excel template from the lesson above.  Here are the steps:

1. I got a list of all the Microsoft filings so that I could extract all the information using XBRL, this is the Excel spreadsheet that I used.
2. I used that information to populate the Excel spreadsheet DCFM template used in the lesson above, here is that. (completed)
3. I then constructed an XBRL taxonomy that would be used to represent that model; here is the XBRL taxonomy.
4. I created all of the business rules so that I could make sure the information I was entereing in the XBRL instance was correct; here is the XBRL Formulas.
5. Then I created the XBRL instance; here is that XBRL instance.
6. I tested (and tested, and tested, and tested) the XBRL instance to make sure everything was working correctly.  For example, the roll up computations, the XBRL Formula based assertions, the XBRL technical syntax, the XBRL Cloud Evidence package was used to manually verify certain items, I tested the DCFM model using Pesseract, and finally I had someone that was helping me create some of the XBRL Formulas validate the model using the Fujitsu Formula Editor.
7. Then, I auto-generated Inline XBRL for the model.
8. Finnaly, just for the heck of it I added pseudo-Korean labels for the model to show how easy it is to make the model multi-language; here are the English and Korean labels.

Now, I am not holding myself out as a financial analyst.  I am simply testing the mechanics of creating a DCFM using XBRL.  My next step is to auto-generate the XBRL instance from the XBRL-based reports that have been submitted to the SEC for some other company, maybe Apple or Google.

So now I am thinking "self-driving" financial analysis!  If these guys can do this, so can I.  Something else I am noticing is that I have general ledger trial balance information in XBRL, financial reports in XBRL, and higher-level financial models in XBRL like this DCFM.  In one straight-shot you can go from trial balance to report to model.  The modern finance platform might not be as far away as it might seem.

I am happy to answer any questions about this that anyone might have; just send me an email.

 

##########################

Three Financial Statements

Article originally appeared on XBRL-based structured digital financial reporting (http://xbrl.squarespace.com/).