Payment timeline forecast....

hro5e

Board Regular
Joined
Jan 13, 2012
Messages
60
Hello all,

I require a little help with forecasting the 'cost' per day based on a timeline.

I have created a project timeline which each row (task) has a start and end date.

Each task has a value (cost).

Each task requires a 20% deposit, 4 weeks before the start date.

The remaining 80% cost needs to be distributed evenly to the amount of workdays between the start date and end date.

So, if a task costs $1,000 and starts on 01/01/2023 and finishes on 01/20/2023, there are 21 network days between these dates.

I need 20% so $200 to be paid 4 weeks before the start date. and then $800 to be divided by 21 working days. equating to $38.10

I need the $38.10 to be shown on each workday (network day).

Ultimately, the goal is to calculate how much each day is costing based on totality of the tasks within the project.

I have attached a screenshot of my timeline. The timeline uses conditional formatting to show when each task is active.

If you need anymore information to help solve my problem, let me know.




Screenshot 2023-03-15 at 13.44.20.jpg
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Would you be kind enough to post the worksheet portion using the xl2bb add in? The forum needs to completely recreate you data to try to figure out a solution. The link is below. If that is not possible, then please just post a table of the data. If you have formulas that reference other sheets, those sheet portions should be posted as well. Thanks in advance for helping the forum help you.
 
Upvote 0
Would you be kind enough to post the worksheet portion using the xl2bb add in? The forum needs to completely recreate you data to try to figure out a solution. The link is below. If that is not possible, then please just post a table of the data. If you have formulas that reference other sheets, those sheet portions should be posted as well. Thanks in advance for helping the forum help you.
Of course, I will do this now
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,207
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top