At last, I've contributed my first ever TechNet Wiki article! I chose my blog post to make it as Wiki article so that people can conrtibute to alternate solutions if any.
Going forward, will try to contribute more to SharePoint community.
In Project Server 2013 (or even 2010), when a PWA instance is created, Project Server provides some default Excel reports under Business Intelligence Center subsite. Basically these Excel workbooks contain data connection to ProjectWebApp Reports tables/views. If you click on any of these Excel and get an error something like "Data connection file used in workbook not in a trusted location", that means some configuration is yet to be done by your farm administrator.
Basically, you need to configure Excel Services Settings and Secure Store Service Settings.
These prerequisite steps are well documented in Technet and you can check it here.