The Client requested -

A way to simplify the use of Excel Solver to calculate the least cost formulation of 70 products and to store the output as a history and for use in analysis.

What we provided -

• A SQL database to store -
  • The target ingredients for each product
  • The composition of each raw material and the cost
    • The client operates at multiple locations and compositions can vary by location
  • Minimum and maximum constraints on the use of each raw material
  • The Solver run parameters
• An Access app that allows the user to -
  • Maintain the data
  • Run the Solver for any number of products from a single click - this replaced a lot of copy and paste!
    • The user is alerted to any products for which no solution can be found
  • Report on the results at summary and detail level
  • See a history of past Solver runs, so as to explore why product costs have changed over time

Our solution -

• Saved many hours of work
• Reduced the scope for errors
• Provided additional functions that would have been very convoluted to create in Excel