+ Reply to Thread
Results 1 to 9 of 9

Calculating revenue based on accounting months

  1. #1
    Registered User
    Join Date
    02-01-2006
    Posts
    60

    Calculating revenue based on accounting months

    Hi,

    Hope you can help. We have sales reps that sell online advertising space that can start and finish when the customer wants. EG booking from 06/01/06 to 05/02/06 for a total of $1,000.

    The accounts department needs to show the revenue in their accounting month. In this case Jan month is from 01/01/06 to 27/01/06, Feb month is 28/01/06 to 24/02/06.

    So in this case some of the $1000 would be Jan revenue and some Feb revenue.

    What sort of formula could I use to show what accounting month the revenue should appear in based on the start and finish date of the advertising?

    i.e The sales rep can enter the contract amount and the start and finish date of the contract and the formula will work out what accounting month the revenue should appear in and show this in the appropriate month columns.

    Regards
    Garry

  2. #2
    Fred Smith
    Guest

    Re: Calculating revenue based on accounting months

    You will need a definition of how the revenue is to be prorated, but let's
    assume it's by the number of days in the contract. Therefore, let:

    a1 = start date = date(6,1,6)
    a2 = end date = date(6,2,5)
    a3 = revenue = 1000
    a4 = revenue /day = a3 / (a2-a1)
    a5 = revenue in first month = a4 * (date(year(a1),month(a1)+1,0) - a1)
    a6 = revenue in last month = a4 * day(a2)

    It gets more complicated if the contract spans more than two months, but you can
    extrapolate the above to accommodate.

    --
    Regards,
    Fred


    "Gazzr" <[email protected]> wrote in message
    news:[email protected]...
    >
    > Hi,
    >
    > Hope you can help. We have sales reps that sell online advertising
    > space that can start and finish when the customer wants. EG booking
    > from 06/01/06 to 05/02/06 for a total of $1,000.
    >
    > The accounts department needs to show the revenue in their accounting
    > month. In this case Jan month is from 01/01/06 to 27/01/06, Feb month
    > is 28/01/06 to 24/02/06.
    >
    > So in this case some of the $1000 would be Jan revenue and some Feb
    > revenue.
    >
    > What sort of formula could I use to show what accounting month the
    > revenue should appear in based on the start and finish date of the
    > advertising?
    >
    > i.e The sales rep can enter the contract amount and the start and
    > finish date of the contract and the formula will work out what
    > accounting month the revenue should appear in and show this in the
    > appropriate month columns.
    >
    > Regards
    > Garry
    >
    >
    > --
    > Gazzr
    > ------------------------------------------------------------------------
    > Gazzr's Profile:
    > http://www.excelforum.com/member.php...o&userid=31075
    > View this thread: http://www.excelforum.com/showthread...hreadid=507492
    >




  3. #3
    Registered User
    Join Date
    02-01-2006
    Posts
    60
    Hi Fred,

    Thanks for the quick response. I have tried out the example you provided but it doesn’t seem to account for the fact that the Jan financial month ends on 27th Jan and not the 31st. So the number of Days revenue in month one should be 1000 / 33 * 22 Days, then month two should be 1000 / 33 * 9 Days.

    It all sounds too hard doesn’t it!

    Kind Regards
    Garry

  4. #4
    flummi
    Guest

    Re: Calculating revenue based on accounting months

    I think what you need is something like this:

    Jan Feb Mrz Apr
    1 2 3 4
    offset 1
    start 01.01.2006 28.01.2006 25.02.2006 25.03.2006
    end 27.01.2006 24.02.2006 24.03.2006 21.04.2006
    days in m 27 28 28 28

    Booking
    start end Total jan feb mrz apr
    06.01.2006 05.04.2006 90 22 28 28 12
    17.01.2006 03.03.2006 46 11 28 7

    Formulas:

    in D4 enter: 01.01.2006
    in D5 enter; =D4+28-1-D3
    in E4 enter: =D5+1 and autofill right to December
    autofill D5 right to December
    in D6 enter: =D5-D4+1 and autofill right to December

    in C10 enter; =SUM(D10:O10)
    in D10 enter:
    =IF(MONTH($A10)=D$2;IF(MONTH($B10)=D$2;$B10-$A10+1;D$5-$A10+1);IF(AND(MONTH($A10)<D$2;MONTH($B10)>D$2);D$5-D$4+1;IF(MONTH($B10)=D$2;$B10-D$4+1;"")))

    and autofill right to December. Depending on your config replace ";"
    with ",".

    That will give you the total days between the two dates in the Total
    column.
    For each new line in your bookings you will need to copy the formulas
    from C10:O10 into the new line.

    If your bookings were in a separate Excel workbook you could
    incorporate them into a new workbook by means of a query. That will
    update the formulas automatically for each line in the query.

    Hans

    Sorry if the text layout of the post is crap. Don't know how to format
    properly.


  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996
    Try this example:

    In A1:C5 enter this table:
    Period______MthBeg_______MthEnd
    200512_____01-Dec-05____31-Dec-05
    200601_____01-Jan-06_____27-Jan-06
    200602_____28-Jan-06_____24-Feb-06
    200603_____25-Feb-06_____24-Mar-06

    E1: StartDate
    E2: 01-Feb-06

    F1: EndDate
    F2: 25-Feb-06

    G1: Days
    G2: =+F2-E2+1

    H1: Fee
    H2: 1,000

    I1: 200601
    J1: 200602
    K1: 200603

    I2: =MAX(MIN(VLOOKUP(I$1,$A$2:$E$5,3,1),$F2)-MAX(VLOOKUP(I$1,$A$2:$E$5,2,1),$E2)+1,0)*$H2/$G2

    Copy I2 across through K2

    These are the returned values
    I1: 0
    J1: 960
    K1: 40
    Totalling: 1,000

    Is that something you can work with?

    Regards,
    Ron
    Last edited by Ron Coderre; 02-02-2006 at 12:31 PM.

  6. #6
    Ron Coderre
    Guest

    RE: Calculating revenue based on accounting months

    Try this example:

    In A1:C5 enter this table:
    Period______MthBeg_______MthEnd
    200512_____01-Dec-05____31-Dec-05
    200601_____01-Jan-06_____27-Jan-06
    200602_____28-Jan-06_____24-Feb-06
    200603_____25-Feb-06_____24-Mar-06

    E1: StartDate
    E2: 01-Feb-06

    F1: EndDate
    F2: 25-Feb-06

    G1: Days
    G2: =+F2-E2+1

    H1: Fee
    H2: 1,000

    I1: 200601
    J1: 200602
    K1: 200603

    I2:
    =MAX(MIN(VLOOKUP(I$1,$A$2:$E$5,3,1),$F2)-MAX(VLOOKUP(I$1,$A$2:$E$5,2,1),$E2)+1,0)*$H2/$G2

    Copy I2 across through K2

    These are the returned values
    I1: 0
    J1: 960
    K1: 40
    Totalling: 1,000

    Is that something you can work with?

    ***********
    Regards,
    Ron

    XL2002, WinXP-Pro


    "Gazzr" wrote:

    >
    > Hi,
    >
    > Hope you can help. We have sales reps that sell online advertising
    > space that can start and finish when the customer wants. EG booking
    > from 06/01/06 to 05/02/06 for a total of $1,000.
    >
    > The accounts department needs to show the revenue in their accounting
    > month. In this case Jan month is from 01/01/06 to 27/01/06, Feb month
    > is 28/01/06 to 24/02/06.
    >
    > So in this case some of the $1000 would be Jan revenue and some Feb
    > revenue.
    >
    > What sort of formula could I use to show what accounting month the
    > revenue should appear in based on the start and finish date of the
    > advertising?
    >
    > i.e The sales rep can enter the contract amount and the start and
    > finish date of the contract and the formula will work out what
    > accounting month the revenue should appear in and show this in the
    > appropriate month columns.
    >
    > Regards
    > Garry
    >
    >
    > --
    > Gazzr
    > ------------------------------------------------------------------------
    > Gazzr's Profile: http://www.excelforum.com/member.php...o&userid=31075
    > View this thread: http://www.excelforum.com/showthread...hreadid=507492
    >
    >


  7. #7
    Registered User
    Join Date
    02-01-2006
    Posts
    60
    Hi all,

    Thanks for the responses. I will check them out over the weekend and let you know how I go.

    Your help has been much appreciated.

    Regards
    Garry

  8. #8
    Registered User
    Join Date
    02-01-2006
    Posts
    60

    Thanks

    Just wanted to let you know that the examples you provided worked fine and the person who I was doing the work for was amazed!

    Regards
    Gazzr

  9. #9
    Registered User
    Join Date
    02-02-2012
    Location
    South Africa
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: Calculating revenue based on accounting months

    Another method which returns accounting period is

    =IF(DAY(A1)>6,1+MOD(MONTH(A1)-12,12),MONTH(A1))

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1