Hello Tuhin,
Your CNAME entry would still look like cdn.mysite.com, then after s2member has run its configuration routines you will be able to find you distribution url example.cloudfront.net in your AWS management console it will have a comment
Created by s2Member, for S3 Bucket: yourbucket.s3.amazonaws.com
Then point cdn.mysite.com to example.cloudfront.net in your DNS manager.
Or
Run the routine without CNAMEs then go to AWS find your distribution url, then go to your DNS manger point cdn.mysite.com to example.cloudfront.net, then when you know that cdn.mysite.com to example.cloudfront.net is live
you can go back into s2member tick Yes, ‘I want s2Member to auto-configure using custom CNAMES that I’ll setup.’ enter your CNAME and UNTICK ‘Yes, automatically configure my Amazon® CloudFront Distributions & Amazon® S3 ACLs for me.’ then save the changes
hope that helps