Baruch College Spreadsheet Applications in Business Class
Corey Bogen’s Midterm Video
Ming Chan’s Midterm Video
David Chen’s Midterm Video
Jihye Han’s Midterm Video
Shantal Lester’s Midterm Video
Thomas Luong’s Midterm Video
Chad Meirelles-Rodies’s Midterm Video
My Fall Spreadsheet Applications in Business class and I faced several confounding challenges this semester. We carried out investigations that took up time outside of class, asked other people who have expertise in Excel for help and worked together during class to find a solution.
Our class sessions were not routine, we experienced discoveries together, because everybody made a contribution the connections between us will last beyond this semester. The City University of New York does not have a Power BI license so even though we were working in a lab classroom students had trouble accessing Power BI …. until we figured out a workaround. I am excited because my future classes can build on the workarounds we created for Power BI. Later in the semester we figured out how to complete a Solver Watershed Management Goal-Programming Problem.
When students and instructors engage in productive struggle the learning lasts longer. It is like doing a work out that makes you sweat versus a workout that doesn’t make you sweat. You feel like you accomplished more when you actually had to exert yourself.
Here are four students’ outstanding videos from our Fall 2022 semester:
Michelle Liang
Benny Xu
Shuai Jin
Sean Gonzalez
Tenzin Sherpa
My Fall 2022 CUNY School of Professional Studies also created outstanding videos:
Leslie Kelly
Veronica Edwards Thomas
Yesterday my Spreadsheet Applications in Business students gave me two incredible gifts. Their videos were incredibly good. Nobody complained because the process of connecting with computers on campus was no good, they just used what they had to make your masterpieces. They kept trying to use Microsoft Teams and persevered. At this point I would like to showcase their videos:
Ann Lee: https://www.youtube.com/watch?v=3J_zrFuckZE
Khandaker Hridika: https://youtu.be/8-2Ehlw4mXw
Mauro Puello: http://youtu.be/PR1AzlobKsw?hd=1
Marcos Cortes: https://youtu.be/TCRHC6842VA
Liubov Volkova is one of my former Graduate Financial Modeling students, this is her final project video: https://www.youtube.com/watch?v=70qna_ks2yA
Dasha Kaverina is one of my former Graduate Financial Modeling students, this is her midterm project video: https://www.youtube.com/watch?v=M_PfSBCex2M
Qishen Li is one of my former Graduate Financial Modeling students, this is his midterm project video: https://www.youtube.com/watch?v=h937c_OuH5k
Bernard Williams is one of my former BUS 410 School of Professional Studies Research Methods students. Here is his reverse engineering assignment video: https://www.youtube.com/watch?v=I9s7PKMiKlE
Roxanne Provident is one of my former BUS 410, Research in Business and current BUS 410 Research Methods in Business, School of Professional Studies students. Here is her final project video: https://youtu.be/YZxJlS5P9jo
If you make your midterm video available on your LinkedIn profile your classmates will be able to find it. I am also hoping to list your videos with this blog post. If you would like me to include yours please send me an email giving me permission to do so.
For the past 2 years all of my students created YouTube video analyses that may give them a means of achieving internet fame as well as academic excellence. Millennials peruse and contribute to Snapchat, Instagram, YouTube and other types of social media on a daily basis so creating videos of their Tableau visualizations could be a compelling form of edutainment for them. Capturing a viral video is an elusive goal that many young people pursue with tremendous enthusiasm. In my grading comments I congratulated Research Methods students who picked topics that are so compelling that I felt like I was learning in two dimensions. Having explanations of Tableau/Excel features interspersed with insights about topics that really do matter to me on a personal or intellectual basis keeps me on the edge of my seat literally.
Things got particularly interesting for me when a few people drilled down into the data to show what is happening with a very small subset of the data. I told one of my students that perhaps this is because I feel like we can solve the problem if we just understand how it impacts a smaller community. The videos with the research question stated right at the beginning were the easiest to review. As you read and watch analyses you will begin to appreciate what an excellent organizing principle a research question is. Here is one example of the midterm video a current student created for BUS 410, Research Methods in Business at the CUNY School of Professional Studies. If you check back tomorrow there may be a few more examples that I am sure will be of interest to you. If you like the video please leave a comment.
Dale Beaumont’s video
Here is Xiao Zhang’s blog with his embedded video. This is an outstanding video that is worthy of your attention: https://blogs.baruch.cuny.edu/xiaozhang/
Biancamaria Billington’s video
Sage Lastimosa’s video
Daniel Kisin’s video
https://www.youtube.com/watch?v=tSrSMnZudKc
Christopher Bonet’s video
Eva Law
Wenbin Lin
Mengmeng Liang
Professor Fred Goldstein and Professor Wheeler
Robert Avery gave a very interesting hour long talk. He started out by talking about his background…
I was a University of Pennsylvania Economics undergraduate and then I joined a hedge fund. It seemed like the sexy thing that people did at the time. It was a lot of fun, I was there for eight years. When I first got there they gave me five bosses – simultaneously! The main problem with that was that none of my bosses talked to each other so they all expected me to do only their work. It was sort of an issue but I managed to handle it. This happens in small firms, I think I was the 45th employee there. You have to wear so many hats, they had me in the agency CMO arbitrage group, as well as the STRAITS group (statistical group), the credit sensitive portion of securitized bonds, they had me building a legal database to store all of their legal documents, as well as an accounting database. So over time I managed to consolidate myself into subprime.
Subprime is a term that people associate with the financial crisis. Some people say that subprime caused the financial crisis. That is not really true. What is subprime? It is a portion of mortgage backed securities that grew extremely quickly coming off of the dot com bubble. The reason for this is that they took mortgage rates really low because there was so much Chinese money flowing around the system that just had to go somewhere. Eventually it made its way out of more regulated areas to less regulated areas and eventually into US housing, because people viewed stocks as not doing very well. When people wanted an attractive investment vehicle they started looking at houses as an investment vehicle. These savvy investors continued to renovate their homes and then move into bigger homes. In fact working as a real estate agent was a really good job from 2003 to 2007. Real estate prices were going up by 15% per year. Why invest in the stock market when you can make money in housing? Well there are some good reasons for not doing that, transacting in houses is more difficult than buying a stock which you can sell in a second. Whereas selling a house takes a long time. You have to go through the closing process, you have line up financing, in addition there are fixed costs like you have to pay the brokers 5 to 6 percent of the value of the house and the transaction costs. So it is not a really great vehicle but people kept doing it because they saw housing prices kept going up and up, and they thought they could never go down. Why not just keep piling in? Then the banks decided to do the same thing since the entire country was going home buying crazy. Why not continue to lend to more and more people because that is what they want? So a couple of Wall Street banks as well as the so called shadow banks country wide started ramping up their origination and not just to the normal high quality borrowers who get fed into the Fannie Mae and Freddie Mac systems but also lending to riskier borrowers. People who are first time home owners, or have FICOs less than 620, or who had a loan in the past, or who have other loans but no credit, started being able to get mortgages. The banks just assumed that prices were just going to keep going up and up at 15% each year, it didn’t matter who the borrower was because you still had the collateral, the value of the house. This meant you could always just take away the house and sell it. So they just kept giving riskier and riskier mortgages. But what were the banks going to do with these mortgages? They didn’t want to hold them on their books. First of all that would limit the number of transactions they could carry out, so instead they created securities called residential mortgage backed securities. This is what we call subprime. So once all of these subprime instruments were out there, they were defined as fixed income securities. Fixed income securities are defined as well it is not a stock, it is more like a bond. It has a payment schedule that is often known in advance, but not really definitely not for a mortgage backed security. Usually you get a fixed coupon for life, floating off a platform, which is going away, and it gets very complicated. They also have a payment schedule that is dependent upon rules. These rules are created by these Wall Street underwriting shops, slicing and dicing the deals to make certain types of risk exposures that the market wants. I have been using a ton of jargon, please feel free to stop me at any time. I will try to explain these terms before I use them.
A securitization is called a deal, within a deal there are certain tranches. So the simplest way to think about it is if you take a tranche of mortgages say 100 of them and then you slice it in half. The first loans that pay off in full go in the A tranche so all of the bottom bonds go to the lower tranches. If the A tranche pays off at 90% and the B tranche pays off 80% then the C tranche is going to take losses. If you are an investor these losses are not a good thing. So what this is doing is tranching out the risk of the securities in ways that generate very highly rated bonds that have low yields, and very lowly rated bonds that have high yields. When people want to generate a very high return they are going to go into the riskier bonds. Usually with very lowly rated bonds. So with subprime Wall Street has figured out how to take all of these loans that are created by them and by their shadow banks and put them in these deals and sell them to investors in bond form. Who are these investors? Well there are tons of investors. The banks were investors themselves, hedge funds were all over this, and German banks were investors. This kept going on and in retrospect everyone says they knew it was a bubble, but often when you are in the midst of a bubble you don’t know it is a bubble. After the fact they say, “Hindsight is 20 20.” So this bubble will pop and it did in 2008 in rather spectacular form. You could see it starting in late 2005, borrowers start defaulting, and there was no real market for these bonds in 2007. In 2007 in February ADX cracked. ADX is the Average Direction Index tracker and that’s when we knew that things were really bad but the stock market didn’t care, the stock market kept going up. It went all the way up until October 2007. At that point it started to decline and you see it crack in 2008. There were tons of people losing money, hedge funds starting blowing up. Instead of a virtuous cycle with prices going up, now there is a situation where Wall Street banks were no longer able to place these bonds in investor hands. Then the Wall Street banks stopped buying these bonds from the shadow banks. The problem is that once you stop originating these loans then the borrowers who had taken on riskier products, like instead of a 30 year fixed mortgage which is the traditional market standard, it had evolved to 2/28 structure this is where you have a fixed teaser for two years then you float based on a platform thereafter. The problem is that the borrowers are only good for two years once the two years expired they start defaulting unless they refinance the loan. Which they usually did until the banks stopped securitizing the loans. So once you couldn’t make any more new deals the market just blew up. Many of the people who had these mortgages defaulted. Many of them had gotten loans for modifications from the government or private servicers and a lot has happened in that market. So I am happy to talk about that part but that is why people say that fixed income caused the financial crisis.
I did that at Ellington from 2003 to 2011 after I originally fixed the five boss situation. I was focused on subprime, I was responsible for loading deals. That means that whenever a securitization comes out I had to look at the collateral, run it, figure out what the bonds were worth, tell the senior portfolio manager what I think and then he made an investment decision. It was pretty boring. It involved opening a lot of PDFs, parsing the PDFs to get the tables to see what it looked like, then jam it into the model which was a the horrible thing I had to deal with. Since there were always four or five deals happening every day it was just a churn. I didn’t particularly like that so I decided to use VBA. Excel is extremely useful but even more useful after I started programming. After I had automated this to a large degree I got to hire four guys to take over the aspects of it that I didn’t really want. I needed to understand risk management positions. In the context of 2005 to understand risk management positions, I needed to run them on an automated basis. So I went through several iterations of batch processes and I realized that Excel was ideal not only to view the deal but to see the requests. So what I did is I paired up with another guy, a CIS genius. He created a calculation cluster so it was all these C plus plus programs connecting to the ADX API running on a bunch of Linux boxes. My job was to connect that in order to get the results, but I wanted maximum flexibility. I wanted to set it up so that, “OK this is going to be the screen that the traders use, this is going to be screen that risk management is going to use,” I wanted them to all use the same interface I didn’t want to rewrite any software or to have to deal with any technology issues. Eventually the way it worked was I defined a lot of functions and these functions were – “I want to get the name of these inputs, I want to get these outputs.” So I would just give it to the technician cluster guy and he would go implement it on his side. Then I built a sequel server to be an intermediary between myself and the cluster server. Basically when you hit F9 in Excel all of these custom functions in Excel were automatically generated. So with the push of a button I could generate one thousand tranches, I could get one thousand custom PDFs to get whatever you wanted. So it generated the PDFs, you can use in in Excel, it works immediately, hit F9 it will then send the asynchronous request to the sequel server where it is going to store what question I am asking. Meanwhile the cluster is pinging that looking for answers to the questions I am asking and if you hit F9 you are going to get an answer eventually. It is an extremely flexible tool that traders use. It is actually quite convenient. This is at the advent of single link CDS one of the other magical inventions of the time. This is where we have this enormous issuance of subprime and it wasn’t enough you still wanted more and the way you get more was that you would synthetically grade it. How would you synthetically grade it? Well the CDS (Credit Default Swap) existed for corporations for quite some time. If IBM issues a bond you can buy CDS on a bond to protect you in case that bond defaults. That hadn’t existed in securitized products because there are a variety of nuances that it is based upon. But they figured it out in spring of 2005 which was very convenient because by then I had built the Excel platform and that enabled me to run all the bonds in the universe to determine what is cheap, what is rich and we became the first guys to put out a major CDS list. So literally we would put out a list, got bids in from the street and executed it and we got 750 million back in one day. So for context 750 million of foreign exchange risk maybe your daily var might be .1% but in structured products it could literally be enormous, a 75 million dollar swing either way based on the forecast. [Structured products have longer tenors and so to cover the risk the cost must be higher] Since I was the guy who built the tool I knew how to use it.
I continued to meander on my way and did a lot of interesting things. So our equity portfolio managers came in one day and said, “Peace out, we are going to Moscow and we are never going to see you again.” So the managers of the Hedge Fund took me and one other guy, and put us on it, and we had to learn in two hours how their eTrading platform worked, what their algorithms were, and what their fundamental ideas were. I maintained it for a year before I determined that it had some magic numbers that didn’t make any sense, and I didn’t believe in their hedging methodology either. So I decided to just shut it down. So I did that. I eventually I became the guy who was trying to stay ahead of the entire market, after the financial crisis there were a lot of interesting trades to do because there were mechanisms within these securitizations which literally had never been triggered before.
Someone asks a question but I don’t have a good enough recording of that person speaking
The problem with that is Bitcoin gets to the topic of the FinTech revolution. Most prices are not linked to gold anymore. They devalued it a while back, they have a fiat currency so you control your own destiny by printing as much as you want, this is very helpful for getting you out of recessions. Now they tried it with the GFC (Great Financial Crisis), it basically worked and it never created the inflation that was feared. One of the supposed benefits of Bitcoin is that it is not controlled by any government and so nobody can go and inflate Bitcoin away. There are only a fixed number of coins that you can get and as you approach that and the miners continue to mine that you are not going to get anymore. So what is probably going to happen is that if more people want to use Bitcoin they will put more money in it. Then Bitcoin will only go up so you get a weird kind of inflation. As more people want to use it, especially people who want to take money out of totalitarian regimes, or out of China where they have all kinds of national controls to prohibit people from translating their fortunes. That is like ICOs, I have read about them and they seem like total scams. It seems like the most ridiculous thing, basically with an ICO somebody gets on a platform like Ethereum. Ethereum is the big one where Ethereum is kinda like Bitcoin except they allow other things like encoding legal contracts so you can theoretically automate payment mechanisms. This eliminates counter party risk, if you provide someone a service, as soon as the network detects that there has been a service money is going to be automatically deducted from their account, and put into yours. You don’t have to worry about their willingness to pay. The thing is you can make an app on top of Ethereum for example a messaging app, let’s say I want to make the next FaceBook Messenger. In order to send a message you have to have enough coins to send a message. What I can do is sell all of these initial investors coins, so I could sell one coin for one penny. Then I sell a billion of them so you have all of these people lining up to buy coin, and they paid you 10 million bucks initially. So the people who are going to build this get the 10 million bucks, they probably have not even built the software yet. So the coins are not really useful yet, but theoretically they are going to be useful in the future. So people are probably going to keep buying in because they are assuming that just like Ethereum or Bitcoin, the prices are probably going to continue to go up. These people actually become less interested in the package they initially bought the bitcoin for. In a couple of these cases they were just total frauds. They just absconded with a hundred million of investor’s money and it is not even clear if they are obliged to produce the software anyway. I would advise you to stay away from ICOs.
On Saturday, October 14th Janine Billadello delivered a fascinating lecture on geospatial data for a morning Spreadsheet Applications in Business class. Please click on the links below to look at her PowerPoint Presentation and the recorded video of her lecture. Because of the pivot to mobile throughout the world geospatial data has become very important. If you decide to use geospatial data for your midterm I am sure that you will get many views on YouTube.
William Glass gave a guest lecture on April 25, 2017 that will be hard to sum up simply because he covered so many aspects of corporate advisory and asset management. He described the process of developing ideas, pitch books and financial models so that the C-level executives can receive clear, concise, persuasive suggestions on how to grow, shrink, and change the nature of their business or competitive position. In contrast to larger financial services organizations that interface with several different aspects of a client’s business, William and his team and their other Perella Weinberg Partners colleagues provide specialized nuanced advice.
William has personally worked on so many financial models that he is able to review work done by his analyst or associate and quickly figure out if there are mistakes or misconceptions. He also understands how craft models that can be adjusted to reflect anticipated client concerns without making catastrophic changes. People in his firm use modular design principles so that it is easy to recognize which parts of the model belong to which train of thought. They also use the same file format over and over again, use of templates saves them from tediously replicating similar analyses.
An important take-away is that financial modelers are storytellers, they try to explain what could happen given certain assumptions. Consequently it is important to put the assumptions for each of the model’s sheets at the very top.
William shared many “best practices” with us. Of particular interest is the color coding of cells:
These vary a little from bank to bank, but blue, green, and black are pretty much always the same. On the graph above, the “Key Inputs” should be blue text with a yellow background. Most of the data providers we use have built in tools to make hotkeys for the coloring. The standard ones I’m familiar with are FactSet (which we use), CapIQ, and Bloomberg.
(William Glass, Personal Communication, April 26, 2017)
In case you are interested in working for Perella Weinberg Partners, here is an outline of what entry level analysts do:
Analysis
Presentation preparation
Research
Administrative work
Associate and senior banker jobs are a bit different. Generally more idea generation and relationship management, but tend to rely on the same documents and processes.
(William Glass, Personal Communication, April 26, 2017)