Month 1 - 4 Recovery Assumptions

[Andybuck86]

Hi All,

I'm hoping one of you can point me in the right direction on a problem I have please.

I have a table called tblInvoiced. This shows everything my company projects to invoice each month (Feb 15 - £200k, March 15 - £300k etc) - I need to budget what we expect to recover each month from this.

Essentially I need to use what my business calls 'recovery assumptions' to calculate what money we will recover each month. For example, anything invoiced in month 1... 20% would be recovered in the same month, 40% in the 2nd month, 30% in month 3 and 10% in month 4.

I'm really not sure of the best way to go about this so any help to point me in the right direction would be fantastic.

Many Thanks


Andy

[jonh]

Well, say tblInvoiced had these fields

FinYear FinMonth Invoice
2015        1     100000 
2015        2     100000 
2015        3     100000 
2015        1     100000 
2015        2     100000 
2015        2     100000 
2015        3     100000 
2015        3     100000 
2015        3     100000 
2015        4     100000 
2015        4     100000 
2015        4     100000 
2015        4     100000 
2015        4     100000

and you add a new table called RA with these fields

FinYear    FinMonth  RAPC
2015      1        20
2015      2        40
2015      3        30
2015      4        10

You could write a query that sums up the invoice values and calculates the percentage...

SELECT 
    tblInvoiced.FinYear, 
    tblInvoiced.FinMonth, 
    Sum(tblInvoiced.Invoice) AS Inv, 
    RA.RAPC, Sum([Invoice]*([RAPC]/100)) AS RA
FROM 
    tblInvoiced 
INNER JOIN 
    RA ON (tblInvoiced.FinMonth = RA.FinMonth) AND (tblInvoiced.FinYear = RA.FinYear)
GROUP BY 
    tblInvoiced.FinYear, tblInvoiced.FinMonth, RA.RAPC
HAVING 
    tblInvoiced.FinYear)=2015

Result:

FinYear FinMonth  Inv    RAPC   RA
2015      1       200000    20    40000
2015      2       300000    40    120000
2015      3       400000    30    120000
2015      4       500000    10    50000