Unable to set custom Data Refresh credentials for PowerPivot workbook in SharePoint

Last week one of our customers had interesting issue, they were not able to set alternative Windows and Data Source credentials for PowerPivot Scheduled Data Refresh in SharePoint.
The deployment is typical BI Farm based on SharePoint 2013 Enterprise SP1 and SQL Server 2012 Enterprise SP1.
Since I was unable to find information about this and I also think that the authentication and refresh of external data sources is always a bit tricky in the SharePoint BI world, I decided to share my solution.
In PowerPivot for SharePoint you can configure Scheduled Data Refresh, including the option to set alternative credentials. This empowers BI Experts and BI site administrators to set connection credentials without having access to Secure Store Service(SSS) or even know what SSS is. See how this looks like below.

There is a good article, that explains how to configure Data Refresh in PowerPivot, you can find it here.
The issue with the customer was that when they try to set alternative credentials they receive generic SharePoint error page and changes are not applied.
If you read the article I referred above, you will see that every set of alternative credentials is actually saved as target application in SSS.
If you search in the ULS logs for Correlation Id you get, you will find something similar to below Unexpected error:

---

System.ServiceModel.FaultException`1[[System.ServiceModel.ExceptionDetail, System.ServiceModel, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]: Access is denied to the Secure Store Service. Server stack trace: at System.ServiceModel.Channels.ServiceChannel.ThrowIfFaultUnderstood .....
.......

---

This error message actually indicates that PowerPivot application pool account does not have permissions in SSS to create target application for the new credentials.
My solution to that was to add the account as Administrator of SSS with Create Target Application and Manage Target Application permissions. After doing this it worked out like charm!