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.
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.
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.
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.
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
How do you Schedule your production scenarios? Let us all know in the comments below:
Next week: Word Art – Yes it has a use !
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Show Months & Years in Charts without Cluttering||VLOOKUP Formula Cheat-sheet – FREE Download »|