Module 6: Excel Modeling
Introduction
In this module, we look at some examples through which we can learn some important principles of Excel model building.
Please use the comment section below to post your questions and thoughts!
Structure of a Good Excel Model
Problem: Channel Profits
Tom, an inspiring author, has written a new novel and is using four channels to publish and distribute his book. There are two large online channels (called A and B), an independent self-publishing channel (called C), and a physical store (called D). Each channel has its own fee structure, and each commands different market share (listed below).
| Online store A | Online Store B | Independent C | Physical Store D | |
| Market share | 73% | 14% | 12% | 1% |
| Fee percentage | 49% | 30% | 7.50% | 35% |
Takeaway: Structure your model into four blocks:
Assumption/Input -> Decision -> Goal -> Calculation
Keep your reader in mind. One step a time.
Problem: Sizing the market for a new product
A start-up firm has developed a new drug in treating autism in children aged between 0 -14 in the US. It is estimated that roughly 3% of children in this age group are diagnosed with this condition every year. This drug is especially promising in that most patients can be treated in about six months, assuming they take one pill a day. The firm hope that it can be adopted by 10% of those patients through aggressive marketing efforts. The firm is targeting at $13 per pill. Develop a model to estimate its annual revenue potential (you may focus on the new patients. Assume for now existing patients will not use this product).
Takeaway: Work Backwards. What pieces of information are needed to get the final number? How to get those pieces?
Case study: Does it pay to invest in the stock market the long run?
Problem
Does it pay to invest in the stock market for the long term? Imagine you are someone who is newly employed, and you are thinking about investing for retirement. You of course would like to have a decent-size nest egg when you retire in, say, 30 years. But can your investment grow at a good rate every year, just like what we have assumed in the future value calculation?
People have been arguing over this for some time. There are those who argue that the stock market pays in the long run. For example, it has been widely said that the long term stock market return, measured by the S&P 500 index, is about 12% per year - see, for example, one of those articles titled “the 12% reality” (goo.gl/gnpwdc). On the other hand, others talk about “the lost decade”, i.e., the fact that the stock market has gained almost zero during the period 2000-2010 (see Dow Jones Industrial Average Index since 1990 below).
So which view should we believe in?
Since we have all the market data, why don’t we settle it ourselves by building a spreadsheet model. Let’s take the S&P 500 index as our measurement of the stock market movement (you can use any other index such as the Dow Jones). The question is: If an investor invests $1 in the market , how will she do if she takes out her investment some years later? Would she always be making a decent return regardless of when she decides to withdraw the entire investment?
