Formulation help

[trent92]

I'm trying to find out if there's a way to find possible funding requirements per Month - Accruals & Expenditures or just Total Spending (+/-) - each month the Spending Authority (+/-) will change. Not sure of a formulation and/or a if, what if statement..

 

[Becky]

I don't understand what you're asking - if you'd like us to give you a formula for 'Possible funding requirements' we'll need to know what it represents...

 

[trent92]

I don't understand what you're asking - if you'd like us to give you a formula for 'Possible funding requirements' we'll need to know what it represents...

- Thank you…each month I received additional funding for Districts (Spending Authority)

They accruals what they need – Invoices come-in and turn into Expenses/Expenditures

• Accruals + Expenses/Expenditures = Total Spending = Available Spending

What I’m looking for:

With the Spending Auth @ 6 months D12 has $51,842 – what would 7, 8, 9, 10, 11, 12 month require (Possible Funding Requirements) if Accruals & Expenses Expenditures go up or Total Spending.

If no change:

Available spending stays within their spending authority for 7, 8, 9, 10, 11, 12 month that could leave a Surplus

 

[Becky]

So you're looking to create a projection of spending? What does it depend on? Do you want to extrapolate from the info available or do you have forecasts?

 

[trent92]

So you're looking to create a projection of spending? What does it depend on? Do you want to extrapolate from the info available or do you have forecasts?

Yes...if you can help me with projection of spending and extrapolate the attach data.

 

[Becky]

I'm still not sure I follow you... but here goes.

The Remaining Funds % can be calculated by dividing Available Spending by Spending Authority. So on row 3 that would be =I3/C3. This figure plus 'Percent Spent' should add up to 100%.

Possible Funding Requirements: If you are looking to extrapolate existing spending over the rest of the year, then you can divide Total Spending by the number of months which have passed and multiply it by the number of months remaining. However this assumes that expenditure is incurred evenly across the year, which may not be accurate. If you are keen to do this, then you can use a formula to count the number of months automatically. This formula counts the number of months between the start of the year and today's date:

=DATEDIF(DATE(YEAR(TODAY()),1,1),TODAY(),"m")

Therefore you could express the formula like this:

=G3/DATEDIF(DATE(YEAR(TODAY()),1,1),TODAY(),"m")*(12-DATEDIF(DATE(YEAR(TODAY()),1,1),TODAY(),"m"))

Possible Funding Surplus (or shortfall) can be calculated by looking at the result of Possible Funding Requirements and comparing it to Available Spending, ie =I3-K3. If you want to express this as a percentage of Spending Authority, then it will be =(I3-K3)/C3

 

[trent92]

I'm still not sure I follow you... but here goes.

The Remaining Funds % can be calculated by dividing Available Spending by Spending Authority. So on row 3 that would be =I3/C3. This figure plus 'Percent Spent' should add up to 100%.

Possible Funding Requirements: If you are looking to extrapolate existing spending over the rest of the year, then you can divide Total Spending by the number of months which have passed and multiply it by the number of months remaining. However this assumes that expenditure is incurred evenly across the year, which may not be accurate. If you are keen to do this, then you can use a formula to count the number of months automatically. This formula counts the number of months between the start of the year and today's date:

=DATEDIF(DATE(YEAR(TODAY()),1,1),TODAY(),"m")

Therefore you could express the formula like this:

=G3/DATEDIF(DATE(YEAR(TODAY()),1,1),TODAY(),"m")*(12-DATEDIF(DATE(YEAR(TODAY()),1,1),TODAY(),"m"))

Possible Funding Surplus (or shortfall) can be calculated by looking at the result of Possible Funding Requirements and comparing it to Available Spending, ie =I3-K3. If you want to express this as a percentage of Spending Authority, then it will be =(I3-K3)/C3

Okay...looks good - but, if my Spending Authority stays at $336,391 (C3) and Total Spending goes down to $20,000 (G3) I will have $60,000 till the end of the year.

I think I have to re-think the Surplus - Outcome

Lastly...what if you wanted to start the year in October to Sept (Fiscal Year), Also, Spending Authority could come Quarterly - exp: (C3) get additional funds of $100,000 for spending - it looks like it wouldn't change the outcome (requirement or surplus).

 

[Becky]

Okay...looks good - but, if my Spending Authority stays at $336,391 (C3) and Total Spending goes down to $20,000 (G3) I will have $60,000 till the end of the year.

You're confusing available spending with funding requirements - you asked me to extrapolate potential future spending, which is what I have done. The formula for Possible Funding Requirements looks at what has already been spent (G3), divides it by the number of months passed, and multiplies it with the number of months remaining.

I presume the spreadsheet you attached is not the one you are actually working from - the cells should all be dependent on each other so that the number of cells that need input are minimal. eg Total Spending (G3) is =E3+F3, Available Spending (I3) is =C3-G3, etc. This would mean that when you get additional funds and update the Spending Authority figure, the other figures update as a result.

Lastly...what if you wanted to start the year in October to Sept (Fiscal Year)

To make things easier you could calculate it by the number of days - you can use the following formula, assuming that A1 is the start of the financial year and B1 is the end:

=G3/(TODAY()-A1)*(B1-TODAY())

These are fairly basic concepts to do with Excel and general accounting principles, so you might make things easier for yourself if you brush up on the basics.

 

[trent92]

You're confusing available spending with funding requirements - you asked me to extrapolate potential future spending, which is what I have done. The formula for Possible Funding Requirements looks at what has already been spent (G3), divides it by the number of months passed, and multiplies it with the number of months remaining.

I presume the spreadsheet you attached is not the one you are actually working from - the cells should all be dependent on each other so that the number of cells that need input are minimal. eg Total Spending (G3) is =E3+F3, Available Spending (I3) is =C3-G3, etc. This would mean that when you get additional funds and update the Spending Authority figure, the other figures update as a result.



To make things easier you could calculate it by the number of days - you can use the following formula, assuming that A1 is the start of the financial year and B1 is the end:

=G3/(TODAY()-A1)*(B1-TODAY())

These are fairly basic concepts to do with Excel and general accounting principles, so you might make things easier for yourself if you brush up on the basics.

Good Training...Thank you for your time.