Excel Hollier Solver
The Hollier Solver tool is an Excel add-in that takes a from-to table and solves it using the Hollier methods.
Preview
Example Output
Introduction
What are Hollier Methods?
Hollier methods are algorithms used to order machines to minimize the backtracking of parts. In the context of manufacturing, it is implemented after separating components of an assembly into part families, splitting them into machine groups using rank order clustering, and finally generating from-to tables of each group.
Hollier Method 1 Algorithm
- Develop a from-to chart based on part routes.
- Calculate the "to" and "from" sums for each machine.
- Assign the machine position based on minimum "from" or "to" summations.
- If a tie between two sums in the "from" or "to" category exists, the machine with the lowest from-to ratio is chosen.
- If a tie between a "to" and "from" sum exists for the same machine, it is skipped, and the next machine with the minimal sum is chosen.
- If a tie between a "to" and "from" category exists, the machines are assigned the next and last positions, respectively.
- Eliminate the assigned machine from the from-to table and repeat the process until all machines have been assigned to a position.
Installation and Use
The quickest way to run the program is to use the macro-enabled template file,
Hollier_Macro.xltm
. To keep this as a personal template that shows in the New
tab in Excel, add the file to C:\Users\<username>\Documents\Custom Office Templates
.
Report An Issue: If you discover any issues, please report them at the issue tracker.
How to Use the Solver
Use of the Hollier solver is simple:
- Add a from-to table to the worksheet called
Hollier Solver
. - Click the
Run Hollier Solver
button and enter the following:- Input Range: Select the from-to table excluding any sum columns.
- Machine Labels: If the machine labels were included in the input range, check this option.
- Output Range: Select a cell for the program to output to.
- New Worksheet: The program will output to a new worksheet.
- Holler Method 2: Select this option if to solve using Hollier Method 2 as well.
- Flow Diagram: Select this option to generate a flow diagram for each method.
- Press
OK
to run the program.
Warnings
- Setups with a singular machine are not accounted for.
- All nonzeros in the diagonal are not accounted for.
How to Add the Solver as an Add-in
This option allows the program to be selected in the ribbon by navigating to Add-ins > Hollier Solver
.
To add the solver as an Add-in:
- Copy the
Hollier_Solver.xlam
file toC:\Users\<USERNAME>\AppData\Roaming\Microsoft\AddIns
. - On the ribbon, navigate to
File > Options
. An option window should pop up. - Select
Add-ins
and on the bottom, selectGo...
- Check the
Hollier-Solver
box and chooseOK
.
To uninstall, delete
Hollier_Solver.xlam
atC:\Users\<USERNAME>\AppData\Roaming\Microsoft\AddIns
and restart Excel.
How to Add the Solver to the Toolbar
Follow the steps in this tutorial beginning at Step 8.
Terms of Service
As defined in the BSD 3-Clause:
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.