Formulation help

Discussion in 'Excel' started by trent92, Apr 3, 2018.

  1. trent92

    trent92

    Joined:
    Jul 19, 2016
    Messages:
    9
    Likes Received:
    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
     

    Attached Files:

    trent92, Apr 3, 2018
    #1
    1. Advertisements

  2. trent92

    Becky Administrator

    Joined:
    Aug 3, 2011
    Messages:
    67
    Likes Received:
    5
    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...
     
    Becky, Apr 4, 2018
    #1
    1. Advertisements

  3. trent92

    trent92

    Joined:
    Jul 19, 2016
    Messages:
    9
    Likes Received:
    0
    - 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
     
    trent92, Apr 4, 2018
    #2
    1. Advertisements

  4. trent92

    Becky Administrator

    Joined:
    Aug 3, 2011
    Messages:
    67
    Likes Received:
    5
    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?
     
    Becky, Apr 5, 2018
    #3
  5. trent92

    trent92

    Joined:
    Jul 19, 2016
    Messages:
    9
    Likes Received:
    0
    Yes...if you can help me with projection of spending and extrapolate the attach data.
     
    trent92, Apr 5, 2018
    #4
  6. trent92

    Becky Administrator

    Joined:
    Aug 3, 2011
    Messages:
    67
    Likes Received:
    5
    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
     
    Becky, Apr 6, 2018
    #5
  7. trent92

    trent92

    Joined:
    Jul 19, 2016
    Messages:
    9
    Likes Received:
    0
    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).
     
    trent92, Apr 6, 2018
    #6
  8. trent92

    Becky Administrator

    Joined:
    Aug 3, 2011
    Messages:
    67
    Likes Received:
    5
    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.
     
    Becky, Apr 9, 2018
    #7
    trent92 likes this.
  9. trent92

    trent92

    Joined:
    Jul 19, 2016
    Messages:
    9
    Likes Received:
    0

    Good Training...Thank you for your time.
     
    trent92, Apr 9, 2018
    #8
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.