Results 1 to 2 of 2
  1. #1
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66

    Calculate budget requirement for current fiscal year with overlapping contracts


    Yesterday I decided, conceptually, how I'd like to approach calculating how much I should budget for a contract that overlaps a selected fiscal year. I've spent some time thinking about translating that into an actual Access query, and need some help.

    I have contracts that overlap the fiscal year on either end (start during and end after, or start before and end during). I need to write a query that does the following for a specified fiscal year:
    1) Determine percentage of overlap (in days) between the contract and the fiscal year. FYs start 1 Jul and end 30 Jun. Contracts start/stop any day of the year.
    2) Multiply the following:
    a) Quantity (stored in OrderProduct)
    b) UnitCost (stored in OrderProduct)
    c) InvoicePeriod (stored in InvoicePeriod reference table, values of Monthly (12), Quarterly (4), Semiannually (2), Annually(1))
    d) Percentage of overlap (in days) between the contract and fiscal year. EDIT 1: Fiscal year data is stored in a FiscalYear reference table, including FYID (FY in two digit format), FYSTART, and FYEND (dates). Contract data is stored in the Order table, with ContractStart and ContractEnd dates recorded for each contract, with the order inner joined to the OrderProduct table (which stores the Products associated with the contract).

    This value will be used as a "floor" number for budgeting purposes; i.e., for that cost center and department, the budget must be at least as much as committed contracts.
    I'll also use this number to check invoice processing progress (by determining percentage up to current date in the FY, and determining if the recorded invoices are within one "UnitCost" of that percentage to determine if the invoices are up to date).

    Thanks in advance,
    Phil

    EDIT 2: Just occurred to me that I have to also prepare for the edge case where a contract is multi-year, and starts in a prior FY and ends in a future FY. So it won't be enough to simply see if the contract start is after FY start, or FY end is before FY end.
    EDIT 3: I have successfully built a query that multiplies a, b and c (yay!). I'm actually kinda proud, considering my lack of familiarity with Access. Still have no idea how to tackle d).
    EDIT 4: I continue to plunk away. I'm thinking a need to build a second query, sourced on the first (from Edit 3 above), that calculates percentage overlap of every contract with every fiscal year. Then I can SUM the query on a FY parameter to get the total amount for the year
    EDIT 5: Been searching around for fiscal year ideas, and found some examples of how to convert a year into a fiscal year...but then stumbled upon Julian dates! I'm thinking using Julian dates is the best way to tackle #1 above. Might make the math easier. Hoping someone really smart jumps into this thread with a suggestion...otherwise I'm just recording my random thoughts for posterity!
    EDIT 6: Never mind Julian dates. DateDiff("d",ContractStart,FYStart)/365 will give me a percentage. Now I have to work out the validation to figure out which direction the overlap goes...
    Last edited by pncbiz; 07-15-2020 at 01:30 PM. Reason: Added information

  2. #2
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    EDIT: Not solved entirely...some math errors...will post the solution when I get it.
    EDIT 2: Solved it! Query was pulling for every fiscal and calculating negatives. I added code to specify the fiscal year, and to not include contracts falling entirely outside the specified FY.

    Well, I solved it myself. To recap for any archaeologists...

    1) Built a query that multiplies the first three (qryOrderAnnualCost) that normalizes the data into annual numbers. Here's the SQL:
    Code:
    SELECT Order.Contract, OrderProduct.UnitCost, OrderProduct.Quantity, InvoicePeriod.InvoicePeriodID, InvoicePeriod.AnnualNumber, Order.ContractStart, Order.ContractEnd, [Quantity]*[UnitCost]*[AnnualNumber] AS TotalFROM InvoicePeriod, [Order] INNER JOIN OrderProduct ON Order.OrderID = OrderProduct.OrderID
    WHERE (((Order.Contract) Is Not Null) AND ((InvoicePeriod.InvoicePeriodID)=[Order].[InvoicePeriodID]));
    2) Built a query that calculates the percentage of the year as a calculated field (qryOrderFYCost), then multiplies that percentage against the normalized total from qryOrderAnnualCost. Here's the SQL:
    Code:
    SELECT FiscalYear.FYID, FiscalYear.FYStart, FiscalYear.FYEnd, qryOrderAnnualCost.ContractStart, qryOrderAnnualCost.ContractEnd, qryOrderAnnualCost.Total, IIf([ContractStart]<=[FYStart] And [ContractEnd]>=[FYEnd],1,IIf([ContractStart]<[FYStart] And [ContractEnd]<[FYEnd],DateDiff("d",[FYStart],[ContractEnd])/(DateDiff("d",[FYStart],[FYEnd])),IIf([ContractStart]>[FYStart] And [ContractEnd]>[FYEnd],DateDiff("d",[ContractStart],[FYEnd])/(DateDiff("d",[FYStart],[FYEnd])),0))) AS FYPercentage, [Total]*[FYPercentage] AS FYTotal, qryOrderAnnualCost.DepartmentID, qryOrderAnnualCost.SubAccountIDFROM qryOrderAnnualCost, FiscalYear
    WHERE (((FiscalYear.FYID)=[txtFYID]) AND ((qryOrderAnnualCost.ContractStart)<=[FYEnd]) AND ((qryOrderAnnualCost.ContractEnd)>=[FYStart]));
    Just have to work out passing the parameter from the form, and I should be good.
    Last edited by pncbiz; 07-16-2020 at 09:41 AM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-14-2020, 02:00 PM
  2. Calculate fiscal year, something wrong in code
    By securitywyrm in forum Programming
    Replies: 3
    Last Post: 07-02-2018, 05:32 PM
  3. Return Records in Current Fiscal Year
    By BLFOSTER in forum Programming
    Replies: 5
    Last Post: 05-06-2016, 07:53 PM
  4. Replies: 19
    Last Post: 01-24-2016, 07:48 PM
  5. Replies: 7
    Last Post: 01-11-2015, 09:45 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums