Value allocation by Milestone and Month

I have a financial forecasting scenario where I need to allocate a cost (let’s say $100,000) across 4 milestones where each milestone has a % allocated (40%, 50%, and 10%). I want to identify the values by milestone and by month.

It gets complicated when I have multiple rows, with varying values and dates starting and ending mid-month.

Input:
Project | Value | Date1 | Date2 | Date3 | Date4
A | $100,000 | Jan 4, 2023 | Mar 5, 2023 | Mar 5, 2023 | June 2, 2023
B | $50,000 | Feb 4, 2023 | Mar 30, 2023 | Jun 5, 2023 | Feb 2, 2024

Output:
Project | Milestone | Value | Jan 2023 | Feb 2023 | Mar 2023 | Apr 2023 | May 2023 | Jun 2023 | Jul 2023 | Aug 2023 | Sep 2023 | Oct 2023 | Nov 2023 | Dec 2023

Output Notes:

  • Output date range (Jan 2023 to Dec 2023) is user defined
  • 3 rows for each project as a result of 4 milestones (potential to increase to more milestones)

I was able to do this in Knime, however the workflow is very complex. I am hopeful the Knime Community can identify a simply solution, or create a node in a future update.

Hi @Rokank

Your description brings up a few questions. You mentioned 4 milestones that each have a percentage but you mention 3 of them which already accumulate to 100 (40%, 50%, and 10%).

Your expected output is not very understandable since it only mentions column headers I assume? Is your goal that 40% of 100.000 for project A should appear in the column Jan 2023, 50% of 100.000 for project A should appear in the column Mar 2023?

This is also somewhat vague. You start with one row for each project that already has 4 milestones so how do you end up with 3 rows?

Since you mention you have done it in KNIME, it would help a lot if we could see the actual desired output :wink:

Hi, I hope the following visual can assist with describing the scenario:

Thanks in advance,

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.