Read Devarajan from Microsoft's response from here --> http://social.msdn.microsoft.com/Forums/en-US/sqlkjpowerpivotforexcel/thread/45d6a242-b501-44c9-aec0-1032a38f07eb

"

this seems to be a feature limitation in PowerPivot, We haven't provided custom impersonation capabilities as it's always current NT user while connecting to SSAS. There is a workaround for this, you can run excel using runas to launch excel using that credential.

runas /user:domain\username excel.exe

This will launch excel with the user are trying to impersonate and it will let you do the job you want.

Once you finish creating model and publishing the model, you can schedule refresh job using any nt credentials.

"