Monthly Archives: March 2014

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.

John Tjia’s Presentation on March 15th at 9 AM

John Tjia will present

Developing an integrated financial statement model,
 the core modeling required in finance positions

The Subotnick Center, Baruch College
151 East 25th Street
New York, N.Y. 10010-5585
Located between Lexington & Third Avenues on the North side of 25th street

9:00 AM until 12 noon, Saturday, March 15th 2014

subotnick center

Tjia book

I met John at a preliminary meeting for the Modeloff Competition, he is one of the judges.  Please watch this video:  He gave me an autographed copy of his book and said that he wanted to meet my students because as Executive Director of Valuation and Business Modeling for Ernst and Young he is always recruiting.

Please be sure to read Adrienne Wheeler’s review of his book which you will find at this link:  Please click “Yes” button in response to the question “Was this review helpful to you?”  I am trying to build up my creds as an Amazon reviewer.

If you would like to pose questions to John Tjia before his presentation, (this is an excellent opportunity to distinguish yourself), please post a comment to this blog.  You can also submit your questions via the survey you will find at this link:


Danielle Bailey’s presentation on Saturday, March 8th

I would like to microgift Danielle by devoting my first blog entry for the semester to her.  This blog entry will also be helpful to our community because each of us will remember something different about her presentation, therefore compiling all of our reflections on Blogs@Baruch will probably reinforce what each of us learned yesterday.  The reason I prefer to use Blogs@Baruch is that sharing with people who are not registered in one particular class takes no effort on my part.  We actually had students from four different classes present: four of my honors students; two of my CIS 9467 students from last semester; two of my Saturday afternoon spreadsheet applications class students and my current CIS 9467 students.  All of us can interact in the Blogs@Baruch space whereas adding so many guests to BlackBoard is doable but certainly tiresome.

Danielle urged you to approach her about internships and also stated that L2 will be hiring in the near future.  If you think that working for her would be helpful to your purposes please follow up.

If you cannot write a lot in terms of microgifting Danielle the very least would be to tweet that you enjoyed her presentation using our course hashtag #cis67.  The second least time consuming microgift would be to write a comment to this blog post.  The third type of microgift would be to write a Blogs@Baruch post of your own.  I will set up a survey to collect the URLs of your blogs and post them on BlackBoard later today.

Danielle explained that Instagram is a pictorial social media and that people have been relating to one another through pictures for millions of years whereas speaking and writing came much later in our evolution as a species.  Her description of the Instagram’s growth in popularity was fascinating.  The fact that younger people are fleeing Facebook as the likelihood that their parents would have an account rises seemed intuitively logical but was still surprising as I had until yesterday considered FaceBook to be the most formidable social media platform.

Danielle had two large files that she receive daily form Instagram that contain API data.  Together we sorted several columns of this data trying to figure out what we could discern about trends and patterns.

I learned a lot yesterday and also came away with many questions…perhaps you will answer some of my questions in your comments.

Can I obtain API data as a private citizen?  I’d love to obtain one of the Excel files Danielle used yesterday.

Her presentation really demonstrates the power of Pivot tables.  I plan to present PowerPivot, a new Office 2013 application later in the semester.  Danielle did not hide the fact that she uses Excel on her Mac so she may not even have access to PowerPivot.  One cannot even do Pivot Table Slicers on a Mac so I wonder what other important Excel functionality is lost because she is using a Mac.

Will post more later this afternoon….forgot about daylight savings….