***

Straight off, I want to say that I have never used a spreadsheet throughout my life.I tried to stay as far away from them as possible. Although I did well in high-school quantitative subjects, I deliberately chose literature since it was free and open to interpretation. I was never good with analyses. My internship at the FMCG was in the planning department but I physically never changed a cell in any of the economic modelling. Therefore, it was a bit of a surprise to me when I passed BCG’s screening test and even more of a surprise when I made it through all the interviews. I knew I could do it, but felt that some of my lack of analytics experiences could have held me back.

Straight off, I want to say that I have never used a spreadsheet throughout my life.I tried to stay as far away from them as possible. Although I did well in high-school quantitative subjects, I deliberately chose literature since it was free and open to interpretation.

Background. Within six months of joining BCG, and after being staffed on two organizational design studies, I was assigned to a pharmaceutical new division feasibility study project. It was an interesting engagement. One of the largest pharmaceutical companies was undergoing a complete transformation. The engagement was led out of the Boston office, and teams were sent to locations around the world to analyses different issues. Eastern Europe had been identified as a potential base for new manufacturing facilities. An abundance of talent, tax incentives, good universities and land made it a good choice. My home country was at the center of a discussion to build a multi-million dollar manufacturing facility. I was staffed on a multi-national team with six nationalities and overall led by the one of BCG’s most respected senior partners, David Matheson. In the world of healthcare, this man is given god-like status. So, although I was not expecting much face time with him, at least the opportunity existed.

Too many people, and consultants, do not understand the visceral fear others have of spreadsheets. If math is scary, than spreadsheets are the king of boogeyman.

Challenge. I knew less about Pharma than most sectors but the idea excited me since I had read so many medical thrillers. I was less pleased about the role. I was assigned to build the economic model to determine if the plant would be profitable. You have to imagine my feeling! Imagine never having driven a car in your life and being told you would need to go out the next day and drive a car to work. It’s similar to this, but with the added pressure that if you do not arrive at work, you will be fired! Too many people, and consultants, do not understand the visceral fear others have of spreadsheets. If math is scary, than spreadsheets are the king of boogeyman. It is widely believed that only the smartest and most numeric consultants can tame these beasts, and the ability to do so is an essential rite of passage to promotion. I left the BCG office that Thursday fully convinced I would be exposed as a fraud within the next 2 weeks.

That night I had a nightmare about a nameless BCG partner walking in and taking away my laptop, then escorting me to the front door.

Friday morning, the day of the team debriefing, I spoke to my mom for some advice, convinced that her eldest child, who supported the entire family of 4, would soon be unable to take care of everyone. She offered the usual encouragement of doing my best and not letting anything get me down. I dragged myself into the office and listened as this jumble of ideas fell out of the partner’s mouth. For the record, everyone at BCG was very supportive and the firm had plenty of resources on which I could draw.

Patient, deliberate and willing to answer any question I had, you would think it would be enough. Yet, it did not work.

It’s like having Ayrton Senna next to you and providing instructions. Sure, he is the best tutor, but am I ready to be the type of student who would thrive under him?

I went to see my assigned mentor, an economics graduate of a prestigious Grand Ecole in France. He gave me 6 power-point guides to read, two books on economic modelling and 3 sample economic models. He also spent a good 40 minutes talking me through how to build the model and how to get help. It felt like I was on a trip to Cambodia! I understood nothing. I visited the knowledge managers who also just gave me more reading material. I looked at the training videos and reached out to other consultants who had built similar, but not the same, models for other Pharma clients. That weekend was not a good time for me.

All the material was so confusing. I literally did not know the beginning point or the end point. We would be planning the engagement on Monday and meeting the client soon, yet I had no clue what was happening. I cried a little that weekend. There, I said it! Panicking, I admit that I reached out for help. I called Michael for advice. For the record, I did not share any details whatsoever but wanted help on how to approach the problem. What follows is how I applied his advice on the engagement.

Step 1 – Forget about the economic modelling, excel, spreadsheets, macros and formulas.

Model building does not start with economic modelling. It starts with plain logic. I was told to do a few things, which was different from what the BCG manager recommended, but impressed him no end. Later I would see my BCG manager was telling me exactly the same thing but I just failed to grasp his advice because I think he was speaking a language just a little out of my reach.

First, I was told to think about the problem I was solving. What was the key question I was trying to solve. The team was trying to determine if the new division and product should be launched, but surely the model was not meant to do be doing all of this? So, over the course of the first day I spoke to the engagement manager and the rest of the team to determine what they wanted me to do. Over coffee, pastries and countless scribbles on my notebook, I realized they wanted me to calculate the return-on-invested-capital (ROIC) from building the plant. Moreover, they had more than one way to build the plant and they wanted to compare the returns for each way.

At this point, we are still not talking about economic modelling. All I am trying to understand is what I needed to do. With my manager, we agreed the most important question I would need to solve was to calculate the ROIC for the new division. Mind you, I still had no clue what ROIC was (roy-EEk; sounded more like an Icelandic village!), but I knew that this was the main question I needed to answer.

One great piece of advice was to always place a +, -, x or / sign between the branches. If you cannot find this relationship then you cannot model it. I did this and it forced me to make sure I understood the relationship between each branches.

Step 2 – Write down the levers and drivers of ROIC

Remember, we are still not using excel or even talking about it at this point. I am also still following Michael’s advice, which is different to what my manager recommended but he is still incredibly happy so far. So I worked out the levers and drivers of ROIC. The early stage of this was easy to do. You can get it from any finance textbook and BCG has many guides with the ROIC tree. I just took one and worked with it. The hard part is in adapting it for the industry in which you work. So in this case, I basically had to do three things:

(1) Understand Revenue

This meant understanding the sources of revenue for this division. I knew they sold drugs to pharmacies, directly to patients, and online through their website. Since revenue is driven by price and volume of drugs, I had to get the pricing sheets and then estimate the volume of drugs sold. So you can imagine this ROIC tree expanding from left to right over the 2 meter width chart behind the desk where I worked. One great piece of advice was to always place a +, -, x or / sign between the branches. If you cannot find this relationship then you cannot model it. I did this and it forced me to make sure I understood the relationship between each branches.

For volumes sold (demand), it was a bit harder. I need to estimate the growth in each segment by looking at past segment growth and extrapolating it based on expected market demand. Easily explained here, but something I only figured out once my manager explained it to me.

(2) Understand Costs

As with the revenue side, I needed to continue building my tree on the cost side. I split costs in fixed and variable costs, and then collected information from the company to work out the relationship between variable costs per pill manufactured. This way, as I changed the demand, and the volumes of pills manufactured increased, I could determine the increase in variable costs.

For fixed costs, I basically needed to determine all the major cost categories and work out over what period to depreciate the costs. This was the easiest part to do in the model, but the hardest to understand when building my tree. In fact, I never really understood this part until I built the model. Depreciation is technically easy to model but intuitive hard to understand.

Key thing to remember here, is at this point, I have just done two things. I knew what the team wanted me to calculate and I also had built a large decision tree to calculate ROIC. That’s it.

(3) Understand Capital Use

Once I knew all the costs (capital) I would use, I could then split it up and divide its use for fixed or variable costs, and work out the return on capital. This was the easiest part since the formula is given to you.

Key thing to remember here, is at this point, I have just done two things. I knew what the team wanted me to calculate and I also had built a large decision tree to calculate ROIC. That’s it.

Step 3 – Map the process

This was the easiest and most fun part. I worked with a BCG internal pharmaceutical manufacturing expert and the client operations manager to build a simple process map all the way from the procurement of raw materials to the arrival to pills at the three customer segments. This took me about 3 days to do, but was really useful because I took my decision tree to the workshops and found a few parts where I had either misunderstood a part of the process or ignored a vital step. For example, in my decision tree, I assumed volume of pills produced was driven by the demand in the market. I found out that some pills were manufactured and shipped to developing countries, irrespective of the demand in the market. They were a type of gift whose volume was fixed and not driven by demand.

This took me about 3 days to do, but was really useful because I took my decision tree to the workshops and found a few parts where I had either misunderstood a part of the process or ignored a vital step.

I also realized how complicated the quality approvals process was for checking drugs. A huge amount of money was spent on equipment to screen drugs after they had been prepared. The amount was at least 15% of all capital costs and made me realize that by lumping all the capital costs together, I was missing opportunities to understand the cost structure and cost drivers better.

I had also assumed that demand drove prices. This was not the case. The 4 countries which this plant would supply all had regulated drug prices which tried to guarantee a net-margin, after shipping costs, of 7%. So the prices were basically fixed and could only change every 4 years. An important insight!

Step 4 – Model description

I was asked to write a one slide description stating what the model would do. I thought this was a joke! Just one slide! Surely I needed to put together a 10 page description at the least. This was the one part I wanted to ignore Michael. In the end, after a few more calls, I decided to go with the process. This was the toughest thing I had to do. I needed to write about 40 words which explained to people what the model did, and therefore, did not do. It took me about 20 tries to get this right.

Michael told me that Winston Churchill had a war-time rule. His cabinet was only allowed to present their ideas on a single-side double-spaced typed page! Irrespective of the size of the issue or its complexity, they had to get the idea, recommendation and required decision on one page. His view was that if Britain won the war with this approach, surely it could work for any other planning. It did.

Attempt # 1 – Too technical

Attempt # 4 – Forgot to mention the model outputs (Graphs I would produce)

Attempt # 8 – Ignored the key variables (data I could change in the model)

Attempt # 12 – Forgot to mention the 2 key assumptions (In my mind all the assumptions should be listed. Only when I ran the sensitivity analyses, did I realize why some assumptions are more important than others)

Attempt # 16 – Ran out of space

Attempt # 20 – Perfection!!

Michael told me that Winston Churchill had a war-time rule. His cabinet was only allowed to present their ideas on a single-side double-spaced typed page! Irrespective of the size of the issue or its complexity, they had to get the idea, recommendation and required decision on one page. His view was that if Britain won the war with this approach, surely it could work for any other planning. It did.

Step 5 – Building the model

By knowing the main question I was trying to answer, having a clear description of the model and the drivers of ROIC, it was very easy for me to sit down with my manager and take him through my thinking. To be honest, I was a little slower at getting this all done, but I think my manager was very impressed with my work. For example, I could show him, on the decision tree, what I would change to test all three different options for building the plant, as well as how I would test some assumptions the team was making. It is really simple to do so by pointing out the changes in the decision tree and together we could work out the likely impact by following the decision through the decision tree. It helped build my credibility. Many of my colleagues were surprised I even know of this approach since I lack an engineering background.

The model I built, pretty much mirrored the decision tree. One page actually had this big tree with all the numbers flowing in.

All he said at the end was to make sure I remembered to build an income statement, balance sheet and cash-flow statement. Which I forgot to do! Thankfully, my colleagues showed me that I already had all the data in the model and it was just a question of bringing them together.

The model I built, pretty much mirrored the decision tree. One page actually had this big tree with all the numbers flowing in. It was probably not the most beautiful way to depict data, but it was incredibly useful when it came to sitting down with the client and explaining how different issues and changes would impact the returns. The clients happiness at the end of the day, translated into some great feedback for me, and my continued career at BCG.

This experience taught me several things.

It is a myth that model builders must have math or engineering degrees. By attacking a model as just another consulting problem and trying to understand the logic, before thinking about the excel component, anyone can build models. The logic is far more important that any fancy functions in the model.

You do not need to know how to programme. My model was really simple but it worked well. It had no macro’s, vlookup’s and what-if statements or code embedded. I still do not know how to do these things and have now completed about 6 engagements where I have built or helped build a model.

You can only build the model if you really understand what you are doing. If you do not perfectly understand the engagement, you will never be able to build the model. Think of the model as translation of the engagement into another language. You must understand the nuances to complete the translation.

The best models DO NOT do everything. The best models only answer a few key questions. They are not meant to help the clients with other issues, or simulate ROIC and help optimize inventory. They are focused with a well-defined boundary.

The model is not the deliverable. This was a bit of a shock. I expected my manager to parade my brilliant model. He did not. The model did not even come up much in major client updates. Interestingly the team was far more interested in understanding the implications of the model output. I suppose that’s the difference between to the top firms and others. The top firms care about the implications of their findings.

Model building is easy; relative to building storyboards. That’s for another post, but in hindsight, I think building effective storyboards is far, far more difficult than building a model.

An arts degree does mean anything; positive or negative. Having an arts degree does not at all speak to your ability to build economic models. Your ability to think in a clear and structured way is far more important. So, measure that skill before thinking your arts background is a liability.

I urge everyone to seriously consider management consulting irrespective of the background they have. Please post comments if you have any questions.

Maria