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