Share via

How to create a data table that splits revenue into months based on a start date and duration of time

Anonymous
2025-05-23T18:18:07+00:00

I am trying to build a revenue forecasting table for my company and am given the project, a start date, a duration and a total amount. I need to build a pivot table or similar that will take that data and build a forecasting model. in other words if I had a list of 5 projects that start on different months and go for different periods I want to show monthly revenue in a chart without having to do it manually.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

12 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Andreas Killer 144.1K Reputation points Volunteer Moderator
    2025-05-25T09:06:15+00:00

    build a forecasting model.

    As far as I understand the details, the important point for you is the forecast. Well, predictions are difficult, especially when they concern the future. :-)

    Let's start with some random data:

    Duration means days, so we can calculate the revenue per day by divide [Total Revenue] / [Duration]. We can calculate the [End Date] by [Start Date] + [Duration]. And so we can create a list of days, each has [Revenue / Day]. Now we can sum up [Revenue / Day] by Year and Month to get [Revenue / Month].

    So far so simple, till this point there is not really a difference of the other solutions in this thread. In my sample file I use Power Query for all this and if we create a Line chart from the data we get this:

    The point is, what is the forecast for the next months?

    If you select that table, click Data \ Forecast Sheet you can see this:

    Sample file:
    https://www.dropbox.com/scl/fi/hqbds3cfolid6fbuxv9fm/463d7b3b-0c38-4a24-8745-78afefa600d6.xlsx?rlkey=1fmghyvkz7m0r7lsnq5864q7l&dl=1

    Is that what you want to achieve?

    Andreas.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2025-05-24T18:21:57+00:00

    Excel 365 Pro Plus with Power Pivot and Power Query.

    ...show monthly revenue in a chart...

    Five projects, multiple start/end dates, estimated daily revenue, ten months.

    No formulas, VBA optional.

    Color coded.

    https://www.mediafire.com/file_premium/d0wfm515a5jgh2d/05_24_25.xlsm/file

    https://www.mediafire.com/file_premium/s0c7bjfr9iq897l/05_24_25.pdf/file

    Was this answer helpful?

    0 comments
  4. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2025-05-23T23:08:21+00:00

    Hi,

    Share some data to work with and show the expected result for a couple of projects. Share data in a format that can be pasted in an MS Excel file.

    Was this answer helpful?

    0 comments
  5. Anonymous
    2025-05-23T20:17:33+00:00

    Make a table that has all possible dates within your time period of interest, and fill the table with formulas that assign revenue to each day, with consistent rules.

    For example, a project that starts 5/1 and ends 7/4 for 50,000, the daily amount is 50000/(end data-startdate+1) and a formula like this in a table, for a list of daily dates starting in cell A2:

    =ROUND(AND(A2>=StartDate,A2<=EndDate)* 50000/(end data-startdate+1),2)

    For a table with start dates in E3:E7, and end dates in F3:F7, and value in G3:G7, the formula would be

    =ROUND(SUMPRODUCT((A2>=$E$3:$E$7)*(A2<=$F$3:$F$7)*($G$3:$G$7/($F$3:$F$7-$E$3:$E$7+1))),2)

    Copy down to match your dates in A, then use the dates and amounts in a pivot table, grouping by month.

    Was this answer helpful?

    0 comments