Monthly Archives: April 2016

John Tjia’s Financial Modeling Guest Lecture on April 9, 2016

John Tjia, Executive Director, Valuation & Business Modeling at Ernst and Young gave his second Baruch Financial Modeling guest lecture today.  Twenty five Baruch students enrolled in Adrienne Wheeler’s spreadsheet courses were present to learn why “accounting is beautiful.”  I can’t really emphasize enough that if you want to become a financial modeler you have just had an opportunity of a lifetime.  One of the pioneers in this field has shared his 75 minute take on how he pleases his customers.  He has even given you a book you can read during the upcoming spring break.

John began by asking students what they wanted to learn in today’s session.  He wrote each question on the board and responded to these questions at the end of his presentation.

John’s focus was on three statement modeling: the income statement, the balance sheet and the cash flow statement.  This is what we earn, this is what we own, and this is what we owe.  John showed us a spreadsheet that presented the basic core ideas of accounting.  He kept saying that accounting is beautiful because it explains everything.  After creating the Profit and Loss, the income statement, build the balance sheet that includes assumptions underpinning forecasts.  The last step is the cash flow, if everything works then it balances.  If your model is not balanced then check everything that has a Profit and Loss as well as balance sheet presence such as depreciation.  John’s teaching spreadsheet was admired by all of us, he says it is proprietary.

At some point he asked us for definitions of depreciation.  Although all of answers were intelligent it was surprising to hear how different they were.  The matching principle is the basis of depreciation, match the expense to the time it is being used.  It defines a certain economic life but only during the time that you use it.

One of his clients had several initiatives so John created a model with toggles.  In the first run the client might only want the first three initiatives.  In the second run the client might want to see the outcome of having initiatives 1, 5 and 7.  Many of John’s clients tell him not to include VBA in their models.  However it is really necessary when he needs to reset some numbers and then turn on the iteration of the interest calculation for example.

There is only one “Master Modeler” and there may be a helper who does things on the side.  When two people work on the same model it becomes inconsistent. April9 432April9 431April9 401April9 402 April9 403 April9 405

When building a model John used to think that when he had a rough idea he should start keying numbers into his spreadsheet.  In his current position he has to be more disciplined.  After figuring out what the model is supposed to do, he has to understand what the client wants, then determine if it is doable, for example a model that needs Python or C++ is not feasible.  Here are the phases of a project: define the scope, design the project, do the actual keyboarding, test it so that there are no unfortunate mishaps when presenting to the client.  Please note that if the client finds an error, the financial modelers may have to work for free to fix it.  After you fix all of the version one mistakes and you are working on version two you may have a serendipitous realization that, “Oh we can do it this way instead.”  We were cautioned however not to do something so creative that the client asks, “What is happening!!”

John said that although some of the people who report to him use SharePoint most of the work that he and the 70 people who report to him do is in Excel, VBA and a little bit of Access.  His Financial Modeling department staff is dispersed among these offices, Times Square, New York City, Chicago, San Jose, Atlanta, Houston, Charlotte, Boston and McClean.  Some of his clients are General Electric, a couple of banks in Chicago, Xerox and many Fortune Five Hundred firms in Houston.


April9 408 April9 409 April9 410 April9 411 April9 412 April9 414 April9 415 April9 417 April9 418 April9 420 April9 422 April9 424

John showed us a program he created that auto loads comparable companies’ Capital IQ data into a spreadsheet.  We were pleased to see Honeywell, Lockheed Martin, General Dynamics and Boeing, the companies we used for our XBRL lesson in the example he selected.  Apparently John has been examining the printouts you are required to bring to our spreadsheet classes.

Please make a comment on this blog.