ModelOff New York Meetup 2016 on September 15th


Johnny Keyu Chen, Emerensia Widjaja, Professor Adrienne Wheeler, Marc Henry Bien-aime and Kebin Xing on our way to the ModelOff Meetup


Professor Adrienne Wheeler, Diarmuid Early the 2014 ModelOff winner, Emerensia Widjaja, Marc Henry Bien-aime and Kebin Xing


Johnny Keyu Chen, Emerensia Widjaja, Professor Adrienne Wheeler, Erica and John Dudley, Associate Director, Ernst and Young, American Talent Team Recruiter

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.


Reflections on Ric Dragon’s guest lecture

Besides being a wonderful speaker and generous person Ric Dragon has a great name.  He began by telling us that it is the name his Great Grandfather handed down to him and not a name that he made up.  He confessed to being an internet marketer, artist, painter, drummer and writer.

Ric told us he would try to “riff off” the following questions that students sent to him:

Kwesi Keteni – Social media is becoming “the platform” for fast exposure. What will it take to be ahead of the pack?

Meghan Belinski – What major companies could be doing a better job with their social media presence?

Shoshana Filene – How do you measure the success of your social media campaign? How do you sell your company on the need to engage more people in social media processes?

Tsz Chung Wu – What is the most common web marketing strategy in use nowadays?

Luke Wallace – How do you see the maturation of disruptive media technologies/outlets (eg roku, apple tv, youtube, etc) playing a role in business to consumer effectiveness?  For example, greater sales metrics to measure effectiveness, closer communication and engagement C2B and B2C, etc…

Michael           Thai – Should a public relations team spearhead the company’s social media campaigns?

Ric launched his company, Dragon Search as a search marketing company before social media got underway.  He speaks to the executives or owners of a company about how they measure success, what are their desired outcomes and what are their purposes or visions for being in business.  He recommended Avinash Kaushik’s blog Occam’s Razor as an excellent place to read about analytics.  Ric taught us that goals are fuzzy and vague, in the distance like goal posts and balls are like objects that are very clear and lead up to the goals.  So Ric works at using specific metrics to build out a system for measuring the success of a brand.  Many brands aspire to increase sales but other brands have different goals such developing the brand’s vision or purpose.  He explained the AIDA classic marketing model and then used a hypothetical model to illustrate how the AIDA model works.

A fundamental question is whether social media boosts the advertising that a brand already has in place and how to measure this.  Ric and his colleagues recognize five modalities of social media:

Brand maintenance – This is being in social media, having your profiles, listening.  If you read Ric’s blog post about Pharrell and Arby’s you will recognize that the Arby’s social media person was listening to social media and recognized a great opportunity:  Please comment on Ric’s blog when you visit.

Any brand that has high customer engagement or numerous customer touch points has to be constantly listening and also be highly invested in brand maintenance.

Community – Some brands naturally have a community of advocates, people who love the brand or an internal community of employees who are willing to engage in social media to support the brand.  It si appropriate to give some attention to supporting and joining these communities.

Influencers – This may take the form of “professional stalking”.  If a person is very influential then somebody from the brand should “follow” him/her on FaceBook, Twitter, LinkedIn, Pinterest and read his/her blog posts…. and every so often, as appropriate comment on things pertaining to his/her social media presence.  Eventually the “influencer” will acknowledge the follower and this could create the kind of free advertising that Pharrell gave Arby’s.

Reputation management – Proactive reputation management – Become part of communities, talk to influencers before something negative happens.  Using BP (British Petroleum) as an example if they had done their groundwork by connecting with ecological communities before the big oil spill they would have had a much easier time after the catastrophe.  Reactive reputation management – This is the kind of reputation management BP was forced to do after the oil spill.  He also explained how the Unilever campaign with the forensic artist drawing women to show them how other people perceive their beauty is an excellent example of content marketing.

The last social media modality that Ric described is the Big Splash and he used the Old Spice commercial with Isaiah Mustafa to demonstrate that.

Ric told us about his admiration for the Red Bull Passion Point marketing that focuses on an attribute that Red Bull shares with prospective customers – extreme living – instead of trying to convince these prospective customers that they should buy Red Bull.

Before he spoke about the ROI of social media Ric said that we need to read books by Brian Solis and Chris Brogan to acquire the vocabulary of social media evangelists.  Then he explained the concepts presented in the Cluetrain Manifesto, the ground breaking “how to manual” on how to carry out social media that was published before social media existed.

Emily asked him how social media impacts B to B marketing and then Luke asked him “How do you see the maturation of disruptive media technologies/outlets (eg roku, apple tv, youtube, etc) playing a role in business to consumer effectiveness?”  Michael Thai and Thomas Eckmier also posed their questions.  Ric answered everyone’s inquiries quite nicely so perhaps you should ask these four members of our class to provide a synopsis of what they learned from him.

Please read and comment upon Ric’s most recent Big Brand Theory post which is available:

Ric Dragon’s guest lecture on May 3rd

Ric Dragon will be our guest lecturer on May 3rd.  I made a really nice discovery last month, Ric is writing a weekly blog called “Big Brand Theory”.  Please take a look at it:  His April 14 blog post is particularly interesting:

Ric is traveling into NYC from Poughkeepsie, New York to talk to us so please try to make a good impression on him by having a few questions about social media ready to ask him. There is a NYC office for his firm on 23rd Street so you never know; perhaps he is looking for prospective employees or interns.

I will send Ric your questions at the end of the week.  Now how much more memorable can you be than to be the first person to pose a question?  Here is the link for the Ric Dragon survey:

I used Ric’s book Social Marketology extensively in my Social Media class last semester.  The objective of my Social Media class was to have students learn how to drive traffic to their blogs so that later in life when they started their own companies they would know how to drive traffic to a company website.  Since our course is also somewhat focused on Social Media it is appropriate for you to think about how Social Media would help you enhance sales for your own existing company or launch a new company (along with a Financial Model for that company).  Ric Dragon’s book is on reserve for our class.  If you read his book or at least read the pages on blog management you will be in a better position to engage with him when he is our guest speaker on May 3rd.  I found his book to be fascinating. Please read the Amazon book review that I wrote about it: R1IVNB68GZSSO2  Please click the checkbox in response to the question “was this review helpful” this will help me improve my creds as an Amazon reviewer.  You can buy the book Social marketology: Improve your social media processes and get customers to stay forever from Amazon for $17.50 or from an Amazon reseller for less than $5.

One of the topics that Ric will be talking to us about is value modeling for social media (or even marketing in general).  Here is a video that relates to his topic: “Understanding the Full Value of Mobile: Adidas Drives In-Store Traffic with Mobile:

PowerPivot lecture on March 22nd

You will find the files for the PowerPivot lesson on Saturday in the BlackBoard course  documents area.  You can also download the data from this link by clicking on the purple download button:  If you will be bringing your personal laptop to class please install PowerPivot before class.  Here is a very helpful 4 minute video on the Books 24X7 database from the Baruch library that will guide you through the install: Business Intelligence with PowerPivot: Getting Started, Featuring Joe Khoury, Skillsoft Ireland Limited © 2013, Length: 4 minutes.

Preparing for your midterm exam

Your midterm on April 5th will have two hands-on problems.  You need to assume that I will give you any of the problems I haven’t assigned as homework from Chapters 4 and 5 of the New Perspectives textbook.  That makes five problems that you should attempt to do ahead of time.  These problems are not really difficult if you start early and pace yourself.  By giving yourself enough time to work on each these five problems you will be well prepared for the exam.

I think the subject matter for our course lends itself nicely to short answer or short essay questions so the third part of the exam will be short answer and multiple choice questions.  You will have three minutes to answer a multiple choice question and five minutes to answer a short answer question. Consequently there will be between 25 and 15 of these questions on the exam.

I would like you to write questions as well as the “correct answers”.  You will then post your questions but not the answers on Financial Modeling blog.  The authors of questions will be obliged to send me the answers to their questions.  This will be accomplished by setting up a survey.  I will select the questions that actually appear on the midterm exam and possibly add questions that I compose myself.

Since everyone will be able to read the questions in the pool (but not the answers) those who have been most thoughtful in terms of composing questions will automatically be rewarded (because they have written answers that I accept).   You are also encouraged to collaborate as you prepare for the exam.  An important criteria for doing well will be having an extensive “memory” of ideas and issues covered in the course.   There are 3 PowerPoints on Bb, the initial PowerPoint from the Soubeiga book, the XBRL PowerPoint and the PowerPivot PowerPoint (not there yet).  I will also give you hard copies of John Tjia’s handout.  So there are 4 documents for you to take your questions from.

I read a book chapter describing this exam procedure for use in a class by Michael C. Elavsky, “You Can’t Go Back Now: Incorporating “Disruptive” Technologies in the Large Lecture Hall.” Social Media: Usage and Impact (2011): 75.  You can read this book chapter on Google Books at this link (page 75):

Naturally if nobody makes any question/answer contributions all of you will have to study all of the material I assigned as opposed to only studying the question/answer pairs created by the 29 students in my graduate level class and the 14 students in my Honors undergraduate class .  I am making the midterm exam low stakes, only 20% of your final grade.  But still a significant event that people who want a high course grade must pay attention to.

Please attach your questions without the answers as comments to this post on the course blog:  Please use this survey to submit your questions a second time, include the answers to your questions when responding to the survey:  You can submit as many questions as you like.

John Tjia’s presentation on March 15th

John Tjia’s presentation of “Integrated Financial Statement Modeling” yesterday was well attended.  Approximately 15 Master of Science students who learned about his presentation at 4:30 on Friday were joined by students from four of my courses.  This makes me think that announcing our guest speakers via Blogs@Baruch is an efficient yet effective means of enlarging our class community.

John is now Executive Director of Valuation and Business Modeling at Ernst and Young, but earlier in his career he did some forecast planning for Microsoft and met Steve Ballmer.  John chuckled and told us, “The Microsoft people invented Excel but when they needed to know how to use it….they came to us.”

John provided a 39 page handout that he referred to as he explained how the underlying task of the financial statements is to track cash.  We learned from him that the Income Statement, Balance Sheet and Cash Flow Statement are connected by the flow of cash.  If you would like to go over the material that John covered again please refer to his book, Building Financial Models, Second Edition.

John is a director who knows what characteristics make a person successful in his organization.  He stated that it is better to build Excel skills after mastering accounting and finance instead of having a strong Excel background before acquiring accounting and finance proficiency.  This may not be what some of us want to hear but for those who hope to follow in John’s footsteps it may turn out to be prophetic wisdom.

According to this pioneer in the financial modeling field, the models he has built for each of his customers are distinctly different.  He always examines their old model first because this helps him understand how they like to look at their business and the framework they have been using.  Even within the same industry modeling assignments can be very different, there will be some common key points but there is no “one approach.”  John’s models inform the C Suite, operations people, people who are endeavoring to determine a realistic and profitable pricing structure for new products and services, investors who are interested in the potential profits and security of their prospective investments, start-ups who are soliciting investor funding and other groups internally within Ernst and Young.  He uses Excel, Access, SQL and PowerPivot.

I was intrigued when John mentioned that sampling data, the purview of statistics, is no longer necessary because we can grab all of the data and analyze it with new really powerful tools.  John’s story about how he and his colleagues created a macro that helped them translate a very lengthy financial model into Japanese and Chinese was also fascinating.

I think that people in attendance yesterday are all hoping that John will capture a few of his financial modeling stories in the next edition of his book.  Who knows, perhaps you will someday play out your own story by becoming a Modeloff finalist.  When this happens please tell John Tjia that hearing him sparked your interest in the contest.