Sales Realization Calculation

[Chandrashekhar]

Hi!!!
I am looking for the formula for calculating Sales realization in installments i.e. if i am selling $100 in 1st year i am receiving 50% in 1st year and balance in next 3 years same thing happens in 2 year. Can anybody help me in this.

No macro i am looking for plain formula, if possible it is urgent
Thanks

[DonkeyOte]

I think we need a bit more info... ie how and where are you results to be displayed... how many sales do you have per year and where are those sales listed etc... will all sales follow the same pattern - ie recovered over 4 years - 50% year 1 and 12.5% for years 2-4 ?

So

Year2 = (Year1*16.66667%)+(Year2*50%)

etc...

If you can post a sample of your workbook and outline expected results you're more likely to get a prompt resolution.

[Chandrashekhar]

Thanks

Please find attached the simple working which i have done mannually since there are only 2 years sales but if the sales are year on year then it is difficult please help

[DonkeyOte]

I don't think you need row 4.

Try the following:

Please Login or Register  to view this content.

[Chandrashekhar]

Thanks its working well but formula in B7 does not work but any way it is simple deduction

Thanks any way for quick solution

[Chandrashekhar]

Hi, Sorry to bother you again

This formula works fine if in row 3 format in numaric such as 2009, 2010 etc but if it is date format it does not work it takes only first caculation File Attached

Please Help and Thanks

[DonkeyOte]

If Row 3 will always be a date value then

Please Login or Register  to view this content.

re: your earlier post that the summation formula for row 7 did not work -- my error the reference to 9 should have been to 6 -- Drs should be Sum Invoiced to Date less Cash Received to Date, ie:

Please Login or Register  to view this content.

You don't need row 4.

[Chandrashekhar]

Thank you so much it works perfectly.

[Chandrashekhar]

Hi, Once again its me, if I am receiving sales realization in equally over a period of time say in 3 years then is my following formula is correct
in B6:
=ROUND((C$5*1/$B$2)+(SUMIF($A$3:B$3,">="&C$3-($B$2-1),$A$5:B$5)*(1/$B$2)),2)

Thanks once again

[DonkeyOte]

Wouldn't you just use the pre-existing formula and alter the parameters ?

Please Login or Register  to view this content.

There will be tiny variance given % roundings etc...

[Chandrashekhar]

OK and Thanks once again for solving my problem

[Chandrashekhar]

Hi Friends,
Actually this thrade was closed long back but know i need to have solution of the extension of that working.

How can i write the formula in same working if i am expecting sales ist year 20% 2nd year 30% 3rd year 30% and last year 20%.