Schedule forecasting to maximize throughput

ScienceNerd

New Member
Joined
Jun 20, 2019
Messages
2
I am trying to setup an automated scheduling spreadsheet in Excel but have hit a road block.

The actual problem I'm working on is quite complex so as an example imagine a new delivery business (Acme Shipping) that is purchasing trucks and starting delivery routes. As Acme wants to maximize the number of routes it can complete per month. Acme is purchasing new trucks on an irregular basis and wants to keep them all running as much as possible. However, Acme only has a single cleaning/maintenance garage which creates a bottle neck.

For example, Truck A is the first truck purchased and is put into service immediately. It won't be ready for its next route until Jan 9th so Truck B is the next one to start a route, same with Truck C. However, when it comes to the fourth route, it's faster to run Truck A again instead of waiting another day for Truck D.

Lastly, currently all the trucks are running on route Alpha but in the future Acme will have trucks running on route Bravo which takes a day longer.

The goal of the spreadsheet is to quickly forecast which truck will be available future dates to maximize the number of routes completed per month. I've tried using combinations of INDEX, MATCH, and IF functions but nothing seems to be working as I'd like. Any suggestions would be much appreciated!

[TABLE="width: 500"]
<tbody>[TR]
[TD]days to complete a route Alpha[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]Truck[/TD]
[TD]Purchase date[/TD]
[TD]Start[/TD]
[TD]Next availability[/TD]
[TD]Next availability[/TD]
[/TR]
[TR]
[TD]days to complete route Bravo[/TD]
[TD]6[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]2020-01-01[/TD]
[TD]2020-01-01[/TD]
[TD]2020-01-09[/TD]
[TD]2020-01-17[/TD]
[/TR]
[TR]
[TD]cleaning[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]2020-01-03[/TD]
[TD]2020-01-03[/TD]
[TD]2020-01-11[/TD]
[TD]2020-01-19[/TD]
[/TR]
[TR]
[TD]maintenance[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]C[/TD]
[TD]2020-01-06[/TD]
[TD]2020-0106[/TD]
[TD]2020-01-14[/TD]
[TD]2020-01-22[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]2020-01-10[/TD]
[TD]2020-01-10[/TD]
[TD]2020-01-18[/TD]
[TD]2020-01-26[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Route schedule[/TD]
[TD]Start Route[/TD]
[TD]End Route[/TD]
[TD]Ready for next route[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Truck[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]2020-01-01[/TD]
[TD]2020-01-06[/TD]
[TD]2020-01-09[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]B[/TD]
[TD]2020-01-03[/TD]
[TD]2020-01-08[/TD]
[TD]2020-01-11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]C[/TD]
[TD]2020-01-06[/TD]
[TD]2020-01-11[/TD]
[TD]2020-01-14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]D[/TD]
[TD]2020-01-10[/TD]
[TD]2020-01-15[/TD]
[TD]2020-01-18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

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