8 Most Common Problems I Had Working as a Business Planning Analyst
Updated: Nov 15, 2022
I have 15+ years of experience in Pharma multinationals, out of which 10+ spent in BP&A - business planning and analytics. I had a blast during my time in the industry, but I also faced many, many, many problems. Most of them were related to Excel, workflows and software redundancies. Let me tell you more about it, but I’ll first set the scene by explaining how I got where I am today.
Starting out as a CRM admin in Pharma
I ended up in Pharma by accident. I started working for a small IT company providing services to a rep office of a global Pharma company, which soon decided to implement CRM. As I was the tech guy, and THERE - it was natural for them to ask me to take on the function of CRM admin.
Logistics and Sales also needed help at the time, and there I was, at the age of 19, staring at Excel for the first time in my life. The position was called Business Analyst, and I was starting to comprehend Excel - the tool that would change my life, for better or worse.
At the time, the planning part of the title and the job still didn't appear. But we are slowly getting there.
My first steps in Finance
For several years I handled local internal and external data and paperwork. This, again by luck, included market and competitors' sales data, so I had access to the industry pulse from early on.
As a CRM admin, I was also in touch with Sales Force Effectiveness and customer segmentation. In hindsight, this was the best possible combination of insights that prepared me for the rest of my path.
Gradually, I started migrating to Finance, at some point spreading my wings to the cluster level. The cluster is the Balkans, Baltics, and CIS markets. Data was more on the financial and less on the 'soft' side, and I have started to feel the pain of running multiple countries’ P&L reports and consolidations in Excel.
At this point, the planning part of my BP&A life emerged - I became a BPA - business planning analyst.
This was my first encounter with the world of Finance, multiple languages, time zones, currencies, and cultures. It was overwhelming, but I handled it well, considering I was somewhat of an outsider at first.
Becoming a business partner at a generic Pharma company
My next stop was a multinational generic Pharma company. BP&A function got global traction and popularity, and it almost became a buzzword. This is also where I’ve found out what BP&A should ideally be. And what business partnering really is. In theory, at least.
Long story short, it’s not providing countless spreadsheets and data without understanding the context but focusing on understanding your business counterparts and providing them with the support they need.
Unfortunately, it’s not quite like that. In every single company, problems are painfully similar.
Most common business planning and analysis problems
1. Manual spreadsheet consolidation
Here is the gist of the story and the cold hard truth: companies usually have multiple enterprise tools for the purpose, but actual planning is done in Excel. The problem is that different files are floating around the company. Visibility and confidentiality of data are sorted out by simply trying not to send the wrong files to the wrong people.
Mistakes happen, though.
Files come around in emails, at least the first versions. BPAs then manually consolidate a bunch of spreadsheets into the 'final' one. And as you may expect, whenever there is a lot of manual work - mistakes happen.
Not to mention multiple versions arriving after the deadline, making it impossible to consolidate everything properly before the business review…
2. 'Quick' changes on the P&L
After the business review, there is usually a subtle request for several changes on the P&L. ASAP. Then it's back to planners, back to consolidation. Manually, of course. Once again, mistakes happen. At this point, it would make everyone's lives easier if planners could do a quick What-if analysis to prevent the back-and-forth and breaking of the deadline again.
But, there is also a zero possibility for that because of Excel.
3. Silos
There are silos everywhere, especially in multinationals. Different Divisions, Business units, and even departments have their own data needs. Report format needs also, but ok, another story.
The right is unaware that two lefts and a foot are already requesting similar data, which, of course, all comes back to Finance.
Countless different Excel templates around the world, developed by local BPAs, help ease everyday pain, but that is far from enough to eliminate it.
Now and then, there is a new global/regional request for new data. Global also likes to cascade requests to ALL markets, regardless of the data availability.
Sales per indication? Don’t have that.
Competitors' share of voice? Don’t have it.
So, what happens? People improvise.
Garbage in, garbage out.
4. Data collection is a b***h
Consequently, you get countless abandoned Excels on various (abandoned) SharePoint sites. At some point, usually around September, you can feel the exact moment when data requesters stopped looking at the files. But you still collect the same useless numbers and send them in every month.
Standardized data collection tools simply do not exist. Everything is always done in some newly formed Excel template.
Consolidation? Good luck.
5. Multiple decision-makers and consequently, multiple tools
Multinationals spend so much money on software tools. What is sad is that they often create more problems than they solve.
What happens is multiple decision-makers at different times purchase different planning tools. And this is where the fun starts.
Let me paint a picture for you:
Cost actuals come through some SAP views, and sales actuals come through BPC or SAP BW but on different Brand IDs. You can get sales excluding Fx impact, but you can’t get costs the same way.
Unless you know a guy and you’re kind enough or ready to flatter your way to data.
All of the above doesn’t include one-third of the markets in the cluster, as they have still not transferred to HANA or whatever.
So how do you consolidate?
Excel, how else!
6. Currencies, multiple currencies everywhere
The notorious exchange rates. Using local currencies in a cluster of 20+ countries and then manually consolidating totals. Why? Because someone forgot to click a button in one of the tools, you only have EUR data for the Baltics, HRK for Croatia, and USD for others. So you become the tool and start consolidating.
Planning-wise, I had a case that went something like this:
Actual sales landed in EUR.
We had to plan for sales in GBP.
Centrally charged costs we planned for in GBP.
For the local costs, we had to use local currency (HRK)
The consolidation currency was the USD.🤯
Consequently, you stop traveling to foreign countries. All holidays are in local currency.
7. 'Can you JUST…?'
Ah, yes, the can-you-just people. We all know them. They don’t know who they are, though.
It always starts with a 'simple' request that goes something like this:
'Could you just give me one number for 15 countries? No need for bottom-up...'
You fall into the trap, and you give them a single number. Then the actuals come in, and the next one is:
'Which country is driving the gap vs. the number you gave me?'
You scream internally.
'Just put it on the adjustment!' - they say.
Deadlines are closing in, and there is no time to go bottom up again. Just put it on adjustment after the review and submit.
Then you start to do the ppt for the budget review, and you realize that your biggest sales driver next year is the brand DUMMY in the market ADJUST.
You scream internally. But you do the work anyway because you want to be a decent human being.
8. Plan_v19_final_final.xlsx is NOT final
When the budget submission deadline is finally announced, and your budget is already located in B23_v19_final_final_afterreview.xlsx, you will need to cancel all incorrectly planned holidays, weddings, etc. The life side of work-life balance, in general.
Although, there IS something special about spending the night bent over (the laptop, no pun intended) while trying to put the #!@**!!! data in the @**#!!!! planning tool.
Along came a Farseer
I was so used to this life that I didn’t want to look at a new planning solution. Nothing used to beat Excel in my life. It took me six months to even submit to the pressure and have a look at Farseer, and I did it as a favor to a friend.
It blew me away instantly. Why? Because it fixes most of the problems I mentioned above. It won't solve the can-you-just people, but you'll handle them easier with Farseer.
It’s easy to set up permissions per user or groups of users. Apart from confidentiality issues, this also speeds up the process. Accounts and account groups are just not visible to selected users (fewer distractions - a better life, and all that). You can create a top-down plan in 7 minutes (I tested this). Best thing? It automatically propagates to SKU and account levels.
Farseer can also connect to all it-was-expensive-so-we’re-keeping-it tools. You can use it for consolidation and planning, add versions and users, and lock specific months in specific versions. Also, it's fast.
Conclusion
Planning is a f***** up activity.
Planning in Excel is perfectly fine for a company with a few planners. Two at maximum, preferably. Top-down high-level planning on a piece of paper is also good. As long as the person doing that is an actual owner of the company.
I would not recommend the same approach to middle management. Also, if you are doing this on a piece of paper, make sure it doesn't get lost in the next twelve months.
In this day and age, there is A LOT of data out there. Management analysis appetites start to grow fast, and BPAs need to become wizards. 200MB Excel files start to shiver a bit. It is just not sustainable.
I still think Excel is brilliant, and I wouldn’t call Farseer an 'Excel killer'. But Farseer does kick Excel’s ass in the planning process. Without the '... has stopped responding' horror.
If you think that your financial models are overgrowing spreadsheets, and you need a better financial modeling tool, Farseer might be for you:
- Financial modeling in Farseer is centralized, fast, and consistent.
- You won’t need to worry about errors and mistakes.
- Natural language formulas are intuitive, error-proof, and cannot be deleted by accident.
- Sharing and exporting your work is granulated - you can share only parts of your model relevant to your user
- Built-in hierarchy makes modeling transparent by default
- Reorganize models by simply dragging and dropping entire spreadsheets to a specific location in the model.
LEARN MORE ABOUT FARSEER HERE