Could someone please help me with the attached spreadsheet. I am trying to create a template for future use. Details as follows.
1st worksheet: Contains monthly fund performance information and cumulative value of $1 growth based on MoM %. The date range goes back a few years so that I can copy and paste future funds in there which might have more performance history.
2nd worksheet: Contains 3 benchmark monthly performance information (data matches the same date range as fund
3rd worksheet: Contains a chart of the fund performance + table of statistics on the Month on Month return
What I need:
1. Why is cell C22 and C26 on worksheet 3 showing a green error comment?
2. I'm not sure how to automatically put in the cumulative calander quarterly and annual return in columns C and D respectively on worksheet 1. Could someone please do this for me.
The quarterly return should only show every 3 months at the end of each calander quarter. The yearly return should only show the 12-months cumulative return once per year in Dec (and of course will only take into account the data available for that year). The annualised return should show a monthly % in each cell for each month and represent the sum of all monthly data returns (for that year only) divided by the number of months of data for that year so far, multiplied by 12. When it comes to Dec, if the formula is correct, it should match the yearly return column.
3. Why, when I click on one of the series on the chart in worksheet 3, does it then not VISUALLY reference (and flash) the appropiate data range in worksheet 1 - it only shows the formula?
4. For the summary statistics table on worksheet 3, could someone please dynamically update the annual returns section including the YTD 2011 keeping in mind that when I use this template for future, the YTD 2011 will need to reflect more months.
Thanks.
Master-Statistics-template.xls