Scheduling Variable Feed Sources

In Manufacturing, Mining and many other industries, bulk commodities are received or delivered in batches or parcels of various sizes and with various properties.

Businesses are often required to schedule the usage of these commodities in quantities which differ from the deliveries. Often commodities are used on a First In First Out (FIFO) basis, or they may be scheduled to meet certain input qualities, ie: Constant or Min/Max of an input quality.

This post will give some basic ideas for this type of scheduling within Excel.

Scheduling First in First Out Usage

Unlike my previous posts, this post will not be walking the reader through the actual scheduling or setup of the worksheet, but will look at each section of the scheduling process and discuss the relevant points where appropriate with reference to the implementation in Excel.

The Scheduling Workbook, Page Sched1 shows a simple FIFO schedule using an Iron Ore mine as an example.

Each section below is highlighted in the sample workbook.

Section 1. Inputs

Various parcels of Iron Ore are presented in order of delivery to a plant or as mined.

Each parcel is of a different sizes (Tonnage) as well as having various qualities of 3 different properties, namely the Iron (Fe), Silicon (Si) and Aluminum (Al) content. These are expressed as percentages but could be Kg/t etc.

Parcels may further be categorized as High, Medium or Low Grade.

From a production point of view it is important to know how much feed is required to meet a certain output from a process or what the quality of mixtures will be.

Section 2. Production Schedule

The mine/plant has a production schedule, ie: a quantity of Iron Ore that it is expected to mine or process every month. The schedule may have ups/down to reflect shutdowns, holidays, changes in rosters/workforce  etc.

Section 3. Schedule

This part does the mechanics of the actual scheduling, allocating parcels of each block until the monthly requirement is met.

In operation it takes the minimum of the size of each parcel minus what has already been processed from that parcel or the production requirement minus what has already been processed that month.

This is done using a simple Min and Sum formula.

Example: K18: =MIN($C18-SUM($I18:J18),K$6-SUM(K$9:K17))

Conditional formatting is used to highlight the cell as a scheduled cell (tonnes > 0)

Example: Conditional Formatting, Cell Value > 0

Section 4. Reporting

Reporting can be prepared for Month by month production, Cumulative from the start or Remaining until the end.

Sum and Sumproduct formula are used to calculate weighted average for elemental grades as you have the quantity of each parcel, each month and the associated grades in the input areas.

Example: Cumulative %Fe in April 10, K36: =SUMPRODUCT(K$10:K$31,$E$10:$E$31)/K$35

More complex Sumproduct formula are used to report by various classifications.

Examples:

Used High grade tonnage in march, J53: =SUMPRODUCT(1*($D$10:$D$31=$I52),(J$10:J$31))

Used Medium grade %Fe in April 10, K59:  =IF(K58>0,SUMPRODUCT(1*(($D$10:$D$31)=$I57)*(K$10:K$31),($E$10:$E$31))/K$58,0)

Section 5. Charting

Having produced a schedule and associated reports you now have a large amount of data which can be plotted to suit requirements.

The Next Step

Once a schedule has been achieved you can feed the production quantities directly into a budget or other downstream system as required.

Scheduling Random or Planned Usage to Achieve Goals

Although the above example, Sched1, is simplistic, First In First Out schedules can and are used in real life. However often some degree of stockpiling is allowed.

This means that parcels can be used in a different order to which they are delivered.

Often this is done so that the input quality of the feed source is varied or maintained, ie Averaged at a level, maintained below or above a level or maximised or minimized according to constraints.

The Scheduling example file, page Sched2 offers a simple manual way to account for this.

Sched2 varies from Sched1 in that it allows manual selection of the order in which parcels are processed.

This is done by simply allowing the user to specify which order parcels will be treated.

The spreadsheet then does all the work with the added benefit of tracking stockpile levels, as often these must be maintained at certain levels.

The spreadsheet has the same reporting and charting functions available as in Sched1.

Summary

In both the examples the scheduling is done using simple, Sum’s, Min and Max formulas.

The actual scheduled production is highlighted using conditional formatting.

The reporting is done using Sum and Sumproduct formulas.

In both examples adjust the values in the various Yellow cells and watch the scheduled tonnages and qualities change.

Next

The purpose of this post was simply to introduce the reader to simple options for scheduling.

It is clear that you now have a simple process from which to derive inputs to a budget, tracking and prediction reports

The post doesn’t attempt to go anywhere near optimization of the schedule using linear programming or other techniques.

However you can see that the addition of Excel solver may be possible to attempt to balance or minimise or maximise outputs, but this is beyond this simple example.

Functions Used:

Min: =Min(Range) returns the Minimum number from the Range

Max: =Max(Range) returns the Maximum number from the Range

Sum: =Sum(Range) adds the values in the Range

If: =If(Condition, Do this if condition is true, Do this if condition is false)

Sumproduct: http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

How do you Schedule your production scenarios? Let us all know in the comments below:

Next week: Word Art – Yes it has a use !