EXCEL SOLVER TUTORIAL
EXCEL SOLVER TUTORIAL
Many firms face the problem of how to best use multiple scarce resources. Linear programming is designed to help find the product mix that maximizes profits in the short run when multiple constraints exist. While linear programming can be solved as a mathematical problem using pencil and paper, it is much more efficient to use Excel Solver. The key to using Excel Solver is to make certain you have modeled the problem correctly and then interpreted the results appropriately. In this problem we will practice the use of Solver.
In this example suppose that you manufacture regular and premium golf carts. The selling price, variable costs and manufacturing times are as follows:
Regular
Premium
SALES PRICE $ 8,000 $ 10,000
VARIABLE COST 5,600 6,500
CONTRIBUTION MARGIN $ 2,400 $ 3,500
Assembly hours 20 50
Inspect and Test 5.0 2.5
Your company currently has 10,000 hours available for assembly and 1,200 hours for inspection and testing. There is also a limit to how many premium golf carts that can be sold (150 max). Given this information if you want to maximize profits what mix of regular and premium golf carts should you produce?
SOLUTION:
1. Input the above data into an Excel Spreadsheet (including constraints). Add cells for total hours used (these should be formulas). Add two additional lines for quantity and total contribution margin by product and overall. Keep in mind that the total contribution margin cells must be input as formulas. It is also a good idea to write out your linear programming model (as shown below) to help guide you as you set up Solver, but it is not necessary for the program to work.
Your excel spreadsheet should look like this:
2. Under the “Data” tab in Excel open solver
You should see a box that looks like this
3. Enter all the data from your spreadsheet into Solver by clicking on the box then the
Many firms face the problem of how to best use multiple scarce resources. Linear programming is designed to help find the product mix that maximizes profits in the short run when multiple constraints exist. While linear programming can be solved as a mathematical problem using pencil and paper, it is much more efficient to use Excel Solver. The key to using Excel Solver is to make certain you have modeled the problem correctly and then interpreted the results appropriately. In this problem we will practice the use of Solver.
In this example suppose that you manufacture regular and premium golf carts. The selling price, variable costs and manufacturing times are as follows:
Regular
Premium
SALES PRICE $ 8,000 $ 10,000
VARIABLE COST 5,600 6,500
CONTRIBUTION MARGIN $ 2,400 $ 3,500
Assembly hours 20 50
Inspect and Test 5.0 2.5
Your company currently has 10,000 hours available for assembly and 1,200 hours for inspection and testing. There is also a limit to how many premium golf carts that can be sold (150 max). Given this information if you want to maximize profits what mix of regular and premium golf carts should you produce?
SOLUTION:
1. Input the above data into an Excel Spreadsheet (including constraints). Add cells for total hours used (these should be formulas). Add two additional lines for quantity and total contribution margin by product and overall. Keep in mind that the total contribution margin cells must be input as formulas. It is also a good idea to write out your linear programming model (as shown below) to help guide you as you set up Solver, but it is not necessary for the program to work.
Your excel spreadsheet should look like this:
2. Under the “Data” tab in Excel open solver
You should see a box that looks like this
3. Enter all the data from your spreadsheet into Solver by clicking on the box then the