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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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