Did you know that Excel contains a special tool that can help you find optimal solutions for various decision problems? This tutorial covers all main aspects of the Excel Solver add-in and provides a step-by-step guidance on how to use it most effectively. Continue reading
Comments on: How to use Solver in Excel with examples
by Svetlana Cheusheva, updated on
Comments page 2. Total comments: 51
Hello! please help me with the steps on creating named ranges and how to enter the constrains for the solver parameter based on the name ranges created.
Good work
Very nice tutorial. Lot of thanks.
A theatre company needs to determine the lowest cost production budget for an upcoming theatre show. Specifically, they will have to determine the lowest which set pieces to construct and which pieces must be rented from another company at a pre-determined fee. The time available for constructing the set is two weeks after which rehearsals commence. To construct the set, the theatre has two part-time carpenters who work upto 12 hours a week and each at $100 per hour. Additionally, the scene artist can work 15 hours per week at $150 per hour.
The set design requires 20 walls, 2 hanging drops with pained scenery and 3 large wooden tables serving as props. The number of hours required for each piece for carpentry and painting is given below.
Carpentery Painting
Walls 0.5 2.0
Hanging Drops 2.0 3.0
Wooden Tables 3.0 4.0
Flats, hanging drops and props can also be rented at a cost of $750, $5000 and $3500 each. How many of each unit should be built by the theatre company and how many units should be rented to minimize costs?
Hi everyone, i am Silvana Pantic, from Slovenia, am so glade coming back to this great forum to testify about the help i received from Credit-Suisse Loan Film. I was in desperate need of a loan in other to be free from debt and financial bondage that was place on me by my ex husband. It was really bad that i have to seek for help from Friends,family and even my bank but on one could assist me because my credit score was really bad. So i was browsing with my computer and saw some testimonies from people that Credit-Suisse Loan Film assisted with a loan, then i decided to contact them via email {credit-suisse147@hotmail.com}
Hello there!
I have a problem with production scheduling.
We have three wire cutting machines and 90 different tools for contactor crimping and seal application (automotive harness business) that are being used on these machines as active processing parts for the different wires and other harness components. Operation on each of the machine is similar except that combination of tools is different, first the machine unroll the wire form the spool, then it cuts the wire on a predetermined length, then applies a seal (water protection) and then crimp a contactor. There are operations where we use two seal applicators and crimping tools on a single machine, so both ends of the wire are sealed and crimped. The combination depends on the wire cross-section, seal specification and contactor specification (crimp parameters vary based on the client specification). Goal - is to prepare the production plan with minimum change over of the tools and eliminate the situations when the tools are needed on more than one machine. The replanting has to be flexible, even daily. Some tools are available in more than one unit (two three). The changeover of applicators is longer than for crimping tools (1.5 hours vs. 30 min). Would be good to have a tip how to solve this complex task.
Look forward to hear from you!
Slava
I tried the Magic Square one with my students and we were not able to solve it as directed. We received error messages, such as "An AllDifferent Constraint must have either no bounds, or a lower bound of 1 and and upper bound of N, where N is the number of cells in the Constraint." However, we could not figure out where to set this parameter.
Can anyone help with this?
Hi Michelle,
To figure out the source of the problem, you can download our sample workbook and compare our Magic Square model with yours.
Hi. I’m very new to solver and was asked to solve this question:
Mathew is the business owner of a laundry shop located at City Plaza. He has operated the
business since June 2018 and after operating it for 6 months, he has realised that in certain
months, the sales revenue is sufficient to cover the operating expenditures, while for certain
months the sales revenue is not enough to cover the operating expenditures and he has to rely
on his personal savings to tide through.
It is now the last week of December 2018 and he realises that moving forward, it is better for
his business to have access to loan facility from the bank to ease out his operation. However,
he is unsure of which loan package to sign up and has approached you, a close friend, to help
him as you are trained in financial planning. To perform the analysis, you have requested
Mathew to give a projection of the sales revenue and operating expenditures for the next
twelve months. The estimates are as follows:
Month Sales Revenue ($) Bills ($)
January 4,000 6,000
February 3,000 5,000
March 3,000 4,000
April 3,000 3,000
May 5,000 4,000
June 9,000 1,000
July 3,000 6,000
August 2,000 6,000
September 1,000 4,000
October 2,000 2,000
November 6,000 1,000
December 10,000 1,000
Based on the whole year projection, Mathew will make $8,000 net profit at the end of the
year. However, since all expenditures must be paid in full by the end of every month,
Mathew may be short on cash in some months until he sees the big sales in certain months,
e.g. June and December. Mathew has two sources of loan:
Annual loan at 12% of interest per year, e.g. he borrows $100 at the beginning of
January 2019 and pays back $112 at the end of December 2019. Early-pay-back is not
allowed and Mathew can get an annual loan in January only.
Monthly loan at 2.5% of interest, e.g. he borrows $100 at the end of March and pays
back $102.5 at the end of April. Early-pay-back is not allowed and Mathew cannot get
a monthly loan in December.
He needs your help to determine whether he should just take up the annual loan with effect
from January, or a mixture of both types of loan facilities. Assume that Mathew has zero cash
balance at the beginning of 2019.
I have tried to look up similar questions online but the prob is I don’t understand how the solution was derived. Can someone help please? Thank you.
Dear Cheusheva
I have tried my best to study your instruction and practice with my problem but I fail to come to an acceptable result.
I hope you help me.
I have a table as follows
x1 x2 x3 x4 Age Code Name
34 36 38 42 17 0 A
32 38 40 41 19 1 B
36 39 32 40 20 0 C
42 34 42 41 19 1 D
33 38 42 29 20 1 E
31 39 41 45 18 0 F
(others in similar form)
I want to have a minimum of sum of four variables with the constraints
- A person (name) is chosen only 1 time in the sum (4 people for four variables)
- Sum of code is 2 (two "1" and two "0")
- sum of age is <=60
I hope to have your reply soon.
Best regards
You explain very well! You have a gift
Fantastic Examples to make you understand the algorithm.
Great. Everything that I wanted to know more about solver is here.
Question? I created a workbook for scheduling hours for employees working at a movie theater for 1 week. I need to have a certain number of employees for each day of the week, but I need to deal with their timeoff requests. And some of my employees are fulltime and some parttime. The timeoff request says "Can't work Saturday". I need to write a constraint based on those entries, add constraints so that employees are not scheduled to work on days when they are unavailable to work. How do I write a constraint to cover this?
Excellent Sir, thanks a lot.
i faced a problem with exelsolver by error how can i correct that?
hello,
please i was giving this assignment but i am finding it hard to understand it. please can any one help me solve it?
To create a Linear Programming model using MS Excel Solver (25%)
A metal works manufacturing company produces four products fabricated from sheet metal in a
production line that consist of four operations: 1) Stamping, 2) Assembly, 3) Finishing and 4)
Packaging. The processing times per unit for each operation and total available hours per month
are as follows:
Product (hour/unit)
Operation 1 2 3 4 Total Hours available per
month
Stamping 0.07 0.2 0.1 0.15 700
Assembly 0.15 0.18 -- 0.12 450
Finishing 0.08 0.21 0.06 0.10 600
Packaging 0.12 0.15 0.08 0.12 500
5
The sheet metal required for each product, the maximum demand per month, the minimum
required contracted production, and the profit per product are given as follows:
Monthly sales demand
Product Sheet Metal (ft2
) Minimum Maximum Profit(£)
1 2.1 300 3,000 9
2 1.5 200 1,400 10
3 2.8 400 4,200 8
4 3.1 300 1,800 12
The company has 5,200 square feet of fabricated metal available each month.
Formulate a linear programming model and use Excel Solver function to suggest the best mix of
products which would result in the highest profit within the given constraints.
Hello Charles,
I managed to solve the problem that you posted (albeit some understandings I have changed to suit the scenario). Post your email ID and I will post the excel file to you asap.
Warm Regards,
Bhupesh
Can u better rephrase or construct the question properly
Many thanks I have been trying to learn this function for decades.
Thank you there is no things more than this to upload your knowledge
Great explanation! Thorough Demonstration of Skills and a brilliant performance
Excellent & very descriptive examples
thanks
excellent tutorial
Thanks for such a nice & easy tutorial of difficult commands.
Thank you sir a wonderful article
Awesome knowledge
Keep it up
AbleBits team,