Archive for the ‘Excel Solver’ Category

Excel has many advocates as a system for the development and implementation of supply chain processes. At first glance, it is extremely attractive – a flexible application that many people know how to use. It is portable and the cost of ownership is negligible (http://logisticsviewpoints.com/2013/11/20/5-reasons-excel-champ-supply-chain-apps/).

However, this ease of use and ubiquitousness comes at a cost. As with any product that offers an initially short learning-curve, Excel becomes extremely complicated to use and maintain in the mid- and long-term development cycles. Problems include:

Performance – Excel runs on a single desktop or laptop. As such, its ultimate performance is limited by the device and it cannot take advantage of distributed (cloud) computing.

Development Sprawl – Development of an application in Excel is usually ad hoc with a single expert developer working part-time on the project. Adequate documentation is rare and version control non-existent.
Code and Data Comingled – Is that a formula or a data cell? There is almost nothing to protect data cells from being overrun by formula cells and vice versa.

Isolation of Calculated Results – These are stored in just one place in the workbook and are often the result of other cascading calculations. Thus changing an intermediate step can cause widespread changes throughout the workbook.

Scoping – What formulae have access to a given cell? Which ones can change its contents (and how would you know)? An object oriented language such as C# utilizes a core principle of encapsulation, which is all about the exposure (and protection) of data. Although spreadsheets have rudimentary methods of data scoping (protected cells, separate worksheets), they are seldom used.

Transactions – Excel does not have a built-in mechanism for recording changes to data and formula cells. This means that changes do not comply with ACID standards (http://en.wikipedia.org/wiki/ACID). If a large spreadsheet is in the process of updating and Excel crashes, can you reliably restore it to its pre-update state?

Poor Security in Older Versions of Excel — From Wikipedia: “Currently, the 40-bit key protection used in Office 97–2003 can be easily cracked by the password-hacking software. The 128-bit key AES protection employed in Office 2007–2010 can still be considered as a relatively secure one. At the moment, however, cloud computing facilities are capable of unlocking a substantial number of the files saved in the Office 2007–2010 format.”
Corporate password and security policies may not apply to desktop applications such as Excel, meaning that strength, frequency of password change and other standards are not enforced.

Flexibility and Scaling – If the supply chain process changes, how can the code and business logic be changed with assurance? Excel has many “nooks and crannies” in which to hide data and formulae.

Conclusion
For small and even mid-sized solutions, Excel has many strengths and advantages. However, the same elements that make Excel so attractive in these situations proves to be a critical flaw for large scale projects. Companies with sizable logistical planning requirements need to look to more formal and distributed solutions for long-term sustainability.

IDK (“I Don’t Know”)

After listening to a Freakonomics Radio podcast on NPR, the following question and blog comments emerged:

Why do people feel compelled to answer questions that they do not know the answer to?

What I’ve found in business is that we are all prone to hiding our ignorance when asked a question that we cannot answer. So even if someone absolutely has no idea what the answer is, if it’s within his or her realm of expertise, “faking” seems to be an essential part of the response.

My professor friend told me that she has learned the following from teaching MBA students: “One of the most  important things you learn as an MBA student is how to pretend you know the answer to any question even though you have absolutely no idea what you’re talking about. It’s really one of the most destructive factors in business. Everyone masquerades like they know the answer and no one will ever admit they don’t know the answer, which makes it almost impossible to discover the correct answer”.

I ask: Does every question need to be answered?

Everyone expects answers to every question, especially if the question comes from someone higher up in an organization. However, not every unknown question is worth the time and resources to research. If it comes down to the choice of making-up an answer or being saddled with a research project, many people will prefer to make-up an answer. Perhaps in some situations, combined with the ego/self-image issues, every question will be answered, regardless of the person’s knowledge.

I ask: Should IDK be a legitimate response?

Perhaps, if the question has minimal economic impact on the business, and you know something related to the question, then maybe a guesstimate (an estimate made without using adequate or complete information) is fine.

But then, for significant economic impact questions …maybe it’s better to say “IDK the answer to that question, but we are studying it”, and then do the study!

As an example, management asks: Will our delivered cost per SKU increase or decrease if we add more distribution centers to meet expected growth rates and satisfy customer service levels?

The first reaction guesstimate might be “yes they will increase”, although, this might not be true.

The smart analyst will say: “Hmmm, IDK! Give me a few hours (days) to do a quick analysis, and see what the true impact will be.”

A small spreadsheet study looking at the increase in production and distribution levels combined with the increase fixed and variable costs associated with adding a few new distribution centers may be surprising. It may indicate that the increase volume and revenues and lower transportation costs will offset the increased DC costs.

This small study may also be the first in a stage gate approach to perform a forward looking comprehensive supply chain infrastructure study. A detailed strategic infrastructure study can capture the manufacturing and distribution details, including costs and constraints, generating results that will allow management to make a reliable strategic economic decision.

No field is exempt from their know-it-alls, even when the correct answer really is IDK.

I submit, if you are in an uncertain position, try the IDK approach and then offer the following response “I can check into that and find an answer for you”. You may be surprised to learn that your credibility with management will improve.

“Every act of conscious learning requires the willingness to suffer an injury to one’s self-esteem. That is why young children, before they are aware of their own self-importance, learn so easily.”
–Thomas Szasz

Profit Point’s optimization expertise will be available to users of Excel Solver and other leading Frontline products.

North Brookfield, MA (PRWEB) August 4, 2009

Working to deliver state-of-the-art analytics to Microsoft Office users around the world, Profit Point and Frontline today announced a partnership to bring Profit Point’s optimization consulting expertise to the large community of Solver users. Frontline Systems is the worldwide leader in spreadsheet solvers with more than 500 million copies of Solver distributed to users, in every copy of Microsoft Office sold since 1990. Profit Point is a leading supply chain and business optimization consultancy with deep expertise across a range of optimization technologies.

“We are excited to further extend our reach into supply chain business processes and applications,” stated Dan Fylstra, Frontline’s founder and CEO. “Profit Point’s consultants can deliver expertise to our customers in several key areas, including model design, application development and deployment, and integration of our advanced technologies into sophisticated supply chain business processes.”

Frontline, which was the original developer of the solvers/optimizers in Microsoft Excel, Lotus 1-2-3 and Quattro Pro, has now moved far beyond competitive offerings, to deliver the first and only commercial software that can solve the full range of optimization models with uncertainty, using the new methods of robust optimization, as well as classical stochastic programming and simulation optimization methods. The company is also a technology leader in the emerging fields of convex, conic, and global optimization.

“Frontline provides a rich array of leading technology solutions that integrate optimization and simulation,” noted Alan Kosansky, Profit Point’s President. “The breadth of their installed user base enables us to provide clients the right technology to improve the difficult decisions they need to make to continue to improve profitability in challenging times.”

Profit Point, which has supported Solver users for years, has deep expertise and experience in using Solver and the associated optimization techniques to implement practical business solutions. Although specializing in supply chain optimization, the firm’s experience covers a broad range of business problems including planning, scheduling, routing, risk management and financial optimization.

To learn more about Profit Point’s Excel Solver services, visit www.profitpt.com/solver or call us at (866) 347-1130.

About Profit Point:
Profit Point Inc. was founded in 1995 and is now a global leader in supply chain optimization. The company’s team of supply chain consultants includes industry leaders in the fields infrastructure planning, green operations, supply chain planning, distribution, scheduling, transportation, warehouse improvement and business optimization. Profit Point has combined software and service solutions that have been successfully applied across a breadth of industries and by a diverse set of companies, including The Coca-Cola Company, General Electric, Logitech, Rohm and Haas and Toyota.

About Frontline Systems:
Frontline Systems is the worldwide leader in spreadsheet Solvers — advanced software used for optimization and simulation of business and engineering models in Excel. Frontline developed the solvers/optimizers in Microsoft Excel, Lotus 1-2-3 and Quattro Pro. Over 500 million copies of Frontline’s Solvers for optimization have been distributed to users, in every copy of Microsoft Office sold since 1990, and Frontline’s Excel Solver upgrade products, sold directly to advanced users, are by far the market leaders in their field.

Business Problem: Dole Food Company, Inc. is a producer and marketer of high-quality fresh fruit, fresh vegetables and fresh-cut flowers, and markets several lines of packaged foods. Dole globally purchases containerboard from several paper companies to manufacture containers to transport and inventory their products.

Dole uses an MS Excel spreadsheet to optimize the variables and constraints to develop an annual strategic purchasing plan and on-going monthly tactical purchase plans for the year to minimize the total costs of buying paper products.

Dole had a desire to improve the optimization tool by using Profit Point’s supply chain consultants to:

  • Validate the current optimization methodology and algorithms
  • Investigate if there might be a better approach or tool to solve this problem

One of Dole’s purchase challenges was developing dynamic monthly plans that were consistent with the annual plan as they move through the year. The terms and conditions offered by the containerboard manufactures include variable costs and constraints that were non-linear. The monthly plan needed to consider these conditions to produce a purchasing plan that provided Dole an optimal cost minimization solution as they reach year-end.

Profit Point’s Solution: Validation: Profit Point reviewed Dole’s data inputs, assumptions, optimization process and validated that the current spreadsheet model was operating correctly and that the spreadsheet was providing Dole an optimal answer. A few modifications were made to the model which allowed Dole’s purchase managers to quickly update and run the tool, and review the output reports. This provided them the ability to confidently make a purchase decision by using the model output or a variant of the output or change the input and re-run the Optimization Tool.

Improved Process and Tool: Profit Point provided Dole with an improved Containerboard Optimization Tool using Frontline Systems‘ solvers that gave management the ability to:

  • Dynamically solve the optimization problem on a monthly basis and consider all the contractual terms associated with optimizing annual tonnage purchase levels.
  • Easily develop strategic plans that include multiple prices and programs offered by suppliers.
  • Improve the current optimization performance.

A better model and process created value to Dole by:

  • Reducing management’s time to analyze multiple scenarios each month
  • Improving management’s confidence in making purchase recommendations
  • Reducing total paper purchase costs through use of better management practices and model resources

And creating the opportunity for Dole to:

  • Decrease paper purchase costs
  • Increase management productivity
  • Provide Dole’s container production plants with improved purchase plans

To learn more about how Profit Point can help improve your Excel Solver optimization, contact us here:

(866) 347-1130 or
(435) 487-9141

Send us an Email

Solver Consulting Partner

October 20th, 2004 8:13 pm Category: Excel Solver, Optimization, Press Releases, Solver Optimization, by: Editor

Solver Consultants

Frontline Systems develops mathematical solvers and optimizers and has customers in over 50 countries. Frontline’s flagship product, the Premium Solver Platform, greatly extends the power of the Solver in Microsoft Excel, which Frontline developed for Microsoft. This powerful product offers built-in linear, quadratic, conic, nonlinear, interval global and evolutionary Solvers.

Profit Point is Frontline Systems’ consulting partner. Profit Point can quickly assist Microsoft Excel users with their modeling needs and projects, reducing model development and programming effort and time. Profit Point’s consultants can assist with development of models “from scratch”, or help companies that are starting a project that requires more in-depth technical consulting support.

Frontline’s Solver technology spans the full range of linear and nonlinear, convex and non-convex, discrete and continuous optimization. The Premium Solver Platform offers the world’s best platform for convex optimization – the natural extension of linear programming – and the world’s best platform for global optimization.

For more capacity and speed, users can choose from eight plug-in large scale Solver Engines that offer a range from traditional linear, quadratic and mixed-integer programming to powerful convex and conic optimization, nonlinear optimization, global optimization, and optimization of arbitrary Excel models using genetic algorithms, tabu search and scatter search.

To learn more about how Profit Point can help you get the most out of Excel Solver optimization, contact us here:

(866) 347-1130 or
(435) 487-9141

Send us an Email

Contact Us Now

610.645.5557

Contact Us

Contact UsInfo

Please call:
+1 (610) 645-5557

Meet our Team

Our Clients

Published articles

  • A Fresh Approach to Improving Total Delivered Cost
  • Filling the Gap: Tying ERP to the Business Strategy
  • 10 Guidelines for Supply Chain Network Infrastructure Planning
  • Making Sound Business Decisions in the Face of Complexity
  • Leveraging Value in the Executive Suite
  • Should you swap commodities with your competitors?
  • Supply Chain: Time to Experiment
  • Optimization Technology Review
  • The Future of Network Planning: On the Verge of a New Cottage Industry?
  • Greening Your Supply Chain… and Your Bottom Line
  • Profit Point’s CEO and CTO Named a "Pro to Know" by Supply & Demand Chain Executive Magazine