MHB Case Study for Commercial Delivery Specialist position

[Night_Hacker]

Savings Analysis
Please proceed with the following calculations, based on the data you can find in the attached Excel sheet:

• Forecast the ARPUs for voice services by country: Determine the ARPU discounts that are needed by country in order to deliver cumulative reduction of 27% by the end of year 3 and 35% by the end of year 4
• Forecast the Actual savings of the customer over the life of the deal in total and by country
• Forecast the YOY revenue of Vodafone for voice services in total and by country

Negotiated reductions (blank means reductions to be determined)

Country	Connections	Existing ARPU in local currency	ARPU in €	1​	2​	3​	4​
UK	156​	52​	€ 63.44	12%​	10%​	blank	blank
Spain	420​	68​	€ 68.00	15%​	0%​	blank	blank
Germany	321​	45​	€ 45.00	13%​	blank	8%​	blank
France	51​	51​	€ 51.00	10%​	5%​	blank	blank
US	1245​	75​	€ 58.99	15%​	5%​	blank	blank
Japan	892​	9213​	€ 85.57	30%​	0%​	5%​	blank
	3085​

 

[HOI]

Do you understand what "cumulative" means? If you do you should recognize this as simple arithmetic.
You want "a cumulative reduction of 27% by the end of year 3 and 35% by the end of year 4".
The first reduction is 12% and the second is 10%. That is a cumulative 12+ 10= 22%. To get a cumulative reduction of 27% the next next year, the amount for that year must be 27- 22= 5%. To get a cumulative reduction of 35% by the fourth year, the amount for that year must be 35- 27= 8%. 12+ 10+ 5= 27 and 12+ 10+ 5+ 8= 35.