Forum Discussion

lloydantonio's avatar
lloydantonio
Copper Contributor
Feb 27, 2024

Quarterly revenue formula help needed

Hi all, I need some urgent help today if anyone has any ideas. I have revenue with start date and end date (e.g. 10th January to 5th December) and need a formula to allocate the proportional revenu...
excel
Formulas and Functions
djclements's avatar
djclements
Silver Contributor
Feb 27, 2024

lloydantonio Please open the attached workbook and see if the results are what you were aiming for. You can change the Calendar Year from 2021 to 2020 and the results will update accordingly. I'm not entirely sure how you wanted to handle the different currencies, but it's a start anyways...

  • lloydantonio's avatar
    lloydantonio
    Copper Contributor
    Feb 27, 2024
    Thats amazing thankyou. Quick questions:
    (1) How does the info in row2 work? The EOMONTH(2)?
    (2) is it possible to do all of this ONLY using the green cells. In other words to not have the orange text you added to the top and in some way incorporate it into the formula itself?
    • djclements's avatar
      djclements
      Silver Contributor
      Feb 27, 2024

      lloydantonio No worries, in the newly attached workbook (v2) I used the DATE function to reference the quarterly start and end dates directly within the IF formula, and simplified the logic used in my previous response. I also removed the "Days" column and calculated it directly within the same formula.

       

      Regarding the EOMONTH formula, it can be used to return the last day of the month, n months in the past or future. If you're interested in learning more about this function, please see: https://exceljet.net/functions/eomonth-function 

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Feb 27, 2024

        djclements 

        Surely this is not your usual style! :suprised:

        This is taken directly from the djclements formula but refactored to give the result as a dynamic array.

        =LET(
    qtr, {1, 2, 3, 4},
    closeDate, IF(
        DATE(calendarYear, 1 + 3 * qtr, 0) < End,
        DATE(calendarYear, 1 + 3 * qtr, 0),
        End
    ),
    openDate, IF(
        DATE(calendarYear, 1 + 3 * (qtr - 1), 1) > Start,
        DATE(calendarYear, 1 + 3 * (qtr - 1), 1),
        Start
    ),
    partQtr, (closeDate - openDate + 1) / (End - Start + 1),
    percent, MAP(partQtr, LAMBDA(x, MAX(x, 0))),
    revenue, percent * totalRevenue,
    HSTACK(percent, revenue)
)

        There are slight differences in the results, but I think that is due to the difference in calculating month ends (otherwise it may be me fouling up).

Resources