Formulation help


Joined
Jul 19, 2016
Messages
9
Reaction score
0
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..

upload_2018-4-3_16-28-4.png
 

Attachments

Becky

Administrator
Joined
Aug 3, 2011
Messages
70
Reaction score
6
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...
 
Joined
Jul 19, 2016
Messages
9
Reaction score
0
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
 
Ad

Advertisements

Becky

Administrator
Joined
Aug 3, 2011
Messages
70
Reaction score
6
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?
 
Joined
Jul 19, 2016
Messages
9
Reaction score
0
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

Administrator
Joined
Aug 3, 2011
Messages
70
Reaction score
6
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
 
Joined
Jul 19, 2016
Messages
9
Reaction score
0
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

Administrator
Joined
Aug 3, 2011
Messages
70
Reaction score
6
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.
 
Ad

Advertisements

Joined
Jul 19, 2016
Messages
9
Reaction score
0
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top