PDA

View Full Version : 5 Years Annual Turnover calculated from a start date



aideycorc
12-03-2013, 02:53 AM
Hello,

I have a spreadsheet which needs to pick up a commencement date in "Column 1" and basically calculate a 12month turnover for the next 5 years.
"Row 1" has text string of dates that were generated by a computer in the format "DD MMM YY".

What I am trying to do is formulate this calculation process as it changes for each of the different clients. The difficulty I am having is creating the formula correctly with the complete build working correctly.

As I have start dates which can be anytime during the month this can't be directly matched to those in "Row 1" as they are always the last day of every month.

My thought process is to build a sum( X:X ) formula which is build up of other formulas which all derive from the commencement date in "Column 1". But you apparently can't just use a normal colon between the two formulas I have used? I have used a comma on my sample.

I know the IFERROR formula is incorrect, I am not quite sure how to make it ignore any 'non-summable' cells? Could someone possibly correct me on this :)

Could someone please do it their own way / correct me and explain what I was doing wrong?...

Thanks ,

Aiden :)

EDIT: I think it is because on my "8%" page I have an IFERROR that puts in "" rather than "0" ?? Any feedback on this?

10917

Bob Phillips
12-03-2013, 04:10 AM
In your example, is year 1 the 12 months since the start date, the rest of the year that the start date is in, or always 2005? Does a year run from Jan-Dec, or Jun-May? If a start date is 5/12/2013, does that mean that Dec is excluded or included?

Finally, can you update your example with some expected numbers?

aideycorc
12-03-2013, 04:22 AM
Hi There,


In your example, is year 1 the 12 months since the start date, the rest of the year that the start date is in, or always 2005? Does a year run from Jan-Dec, or Jun-May? If a start date is 5/12/2013, does that mean that Dec is excluded or included?

The Year will run from June - May if the start date is June for example.
September - August will have the start date of sometime in September. So the starting month is included :)

The years will be different for each client depending on when the start date is. the rest of the dates are irrelevant as far as I have been informed for each client.


Finally, can you update your example with some expected numbers?

I can't put any expected numbers in as that is what the first sheet is for. This is imported on a monthly basis in the future. This then gets picked up by sheet two and furthermore to sheet 3 where this calculation is then formulated to calculate the turnover...

Does that make sense?

So really I just need either a fix for my formula to get it to add up the months during the year, as at the moment I have got it to add up the months at either end of the financial year..
OR
A different way to do this but with the same outcome.

Thanks for your attention

Bob Phillips
12-03-2013, 04:59 AM
The Year will run from June - May if the start date is June for example.
September - August will have the start date of sometime in September. So the starting month is included :)

The years will be different for each client depending on when the start date is. the rest of the dates are irrelevant as far as I have been informed for each client.

I am reading that as saying that for Aberdeen for instance, Year 1 is 1st Sep 2007 - 31st Aug 2008, Ayrshire is 1st May 2011 - 30th April 2012, always a full 12 months.



I can't put any expected numbers in as that is what the first sheet is for. This is imported on a monthly basis in the future. This then gets picked up by sheet two and furthermore to sheet 3 where this calculation is then formulated to calculate the turnover...

Does that make sense?

No, not really, not to me. I cannot see how the numbers in Sheet1 breakdown into Sheet2, but what I wanted was an example with what you expected to see F2:J6 of Gross 8% sheet.


So really I just need either a fix for my formula to get it to add up the months during the year, as at the moment I have got it to add up the months at either end of the financial year..
OR
A different way to do this but with the same outcome.

I was thinking that I would write a different formula, so I was looking to know what I was aiming for.

aideycorc
12-03-2013, 05:36 AM
I am reading that as saying that for Aberdeen for instance, Year 1 is 1st Sep 2007 - 31st Aug 2008, Ayrshire is 1st May 2011 - 30th April 2012, always a full 12 months.

Well, where you have stated Aberdeen it is actually Altrincham, as Aberdeen doesn't have a start date.. but yes, your theory is correct.




No, not really, not to me. I cannot see how the numbers in Sheet1 breakdown into Sheet2, but what I wanted was an example with what you expected to see F2:J6 of Gross 8% sheet.

I was thinking that I would write a different formula, so I was looking to know what I was aiming for.

Basically in sheet 1 in "B2" there is a macro that picks up these figures and places them into Sheet 2 and then copies as values so if the date was to change this new information wouldn't. This is only active from Dec 13 as I have not used this spreadsheet prior to that. I don;t think the macro works on the Sample sheet due to me changing the names around and tidying it up in another file!

After this has been done, the third sheet then divides by 8% and sums it up into a Turnover figure between the start date and the next 11months (as you have correctly worked out form the above)

Bob Phillips
12-03-2013, 06:29 AM
I have to say I am totally confused now, as to what you are doing, and what you need to do. I struggle to see how the £500 for Altrincham gets churned to £1448.13 for Oct 2007, especially as you said it is only active from Dec 2013. But I did notice that you divide by%, shouldn't that be multiply, and shouldn't it be 8.333%?

aideycorc
12-03-2013, 07:19 AM
I have to say I am totally confused now, as to what you are doing, and what you need to do. I struggle to see how the £500 for Altrincham gets churned to £1448.13 for Oct 2007, especially as you said it is only active from Dec 2013. But I did notice that you divide by%, shouldn't that be multiply, and shouldn't it be 8.333%?

From what my knowledge is of this spreadsheet, my client gets 8% from each of the areas stated (in the actual version there are 100+) They want to find out their clilent's total turnover so they can negotiate new targets each year, hence why it is divided by 8%.

The macro from sheet 1 is only working from December 2013.. prior to that my client was just inputting it manually ... post November 2013 is where I am starting from...

The only thing that really needs sorting is on Sheet 3 and in the turnover table with the formulas. Everything else is pretty much correct regards a few tweeks on the mass formulas where "" is being input instead of what should be 0.