Project Overview
As a Business Analyst at Konica Minolta, I was responsible for conducting financial analysis for opportunities that our European subsidiaries had in pipeline.
Each European subsidiaries had their own CRM systems, but due to the complexity of the data, an in-house advanced Excel modeling was used at the Japan Headquarter (my team).
- Dataset: varied quarter by quarter
- Columns:
- Time period
- Name of NOC (National Operating Company)
- Name of Customer
- Mega factor %: How much % discount of hardware will be given to NOC in order to win the deal
- Status: Lost, Won, Canceled, Pending
- Other P&L metrics: Revenue, gross margin for hardware and consumables (toners, pay-per-page, professional services…)
- Tools: Excel, Tableau
Project snapshots
My approach
- Data cleaning and reorganizing, contacted Sales rep of the subsidiaries if finding missing data.
- Descriptive analysis: I explored the financial impact of the pipeline through time, and by subsidiary. I also analyzed the won/lost updates
- Predictive analysis: I presented different financial scenarios and projected the financial impact in each scenario.
- Prescriptive analysis: I provided suggestions in terms of how much financial support we should give to the subsidiaries, which subsidiary to pay close attention to, and whether we needed to contact the subsidiaries for more information, etc.