For this lesson, you need to open the sample file: Bicycle Manufacturing Schedule-1, from your Sample Files folder.

This lesson introduces Solver. Most of the business people that take my courses have no use for Solver. The tiny percentage that do, often get wildly excited about the potential of this tool.

Because Solver is really an external tool, installed as add-in, rather than a core part of Excel, this video doesn't explore some of the more exotic features of this remarkable tool.

There are a huge number of tutorials, covering some extremely advanced uses of Solver, at www.solver.com. You'll find all you could ever want to know about Solver there.

So what exactly is Solver? Well, conceptually, Solver is similar to Goal Seek.

While Goal Seek can only change one input cell, to set a value in one result cell, Solver can change any number of input cells. This means that the tool is necessarily a lot more complex.

You can also define a set of rules, called Constraints. That Solver needs to observe, when finding a solution.

Now, let's look at the sample worksheet for this lesson. The worksheet models a bicycle manufacturing company.

The company manufactures four different types of bicycle. But has a limited number of parts available.

Cells: B4 to E4 are the input cells (or the changing cells, in Solver terminology) for the worksheet. They define how many bicycles of each model will be manufactured. And at the moment, you can see that we're proposing to manufacture 20 bicycles of each type.

The parts needed to manufacture each type of bicycle are shown in cells: B7 to E11. For example, a street bike needs two wheels, one steel chassis and one set of Derailleur gears. While a mountain bike requires two wheels, one alloy chassis and one set of Hub gears.

Column I shows how many parts are available. And you can see that, with the current manufacturing schedule, we're going to have quite a few parts left over. We're going to have 20 wheels left over, and 20 steel chassis, and 10 sets of Hub gears.

Cells: B14 to F14, show the profit for each bicycle type, along with the total profit for all bicycle types, in cell: F14.

Your challenge is to maximize the profit, by manufacturing the optimum number of each type of bicycle.

Before we can use Solver to solve this problem, you'll probably have to install it on your computer, because it's not automatically installed with Excel. It's an add-in.

In order to install the Solver add-in, you need to click: File, on the Ribbon: Options, from the left-had Menu Bar. And then: Add-Ins, from the left-hand Menu Bar. And here, you can see all of the add-ins that are available for Excel.

At the bottom-left of the dialog, you can see: Manage Add-Ins, and a Go button. So I'll click the Go button, and I can see that there are four add-ins available for Excel. And one is Solver. So I'll check the Solver checkbox, and click: OK.

And if I now look at the Data tab, on the Ribbon, you can see that a new group has been created, on the right-hand side of the Ribbon, called Analysis. And in the Analysis group, there's a Solver button.

So I'll click: Solver, to bring up the Solver Parameters dialog.

First of all, I need to tell Solver what its objective is.

Well, the objective is to maximize the total profit. And the total profit figure is shown in cell: F14. So I'll click in the Set Objective box, and then, click on cell: F14.

Next, Excel wants to know: what do I want to do with this cell? Do I want to maximize it, minimize it or set it to a specific value? Well, of course, I want to maximize the profit.

Next, Solver wants to know which cells it's allowed to change. Well, the only cells it can change are the quantity of each bike to manufacture. So I'll click in the By Changing Variable Cells box, and I'll select cells: B4 to E4.

And next come the Constraints, because if there were no Constraints, Solver would simply propose to manufacture an infinite number of bicycles.

Of course, we're constrained by the number of parts available. But there's one other constraint that is obvious to us, as humans, but not at all obvious to Solver. And that is that you can't manufacture a negative number of bicycles. So let's add that constraint first.

I click the Add button, and I'm going to tell Solver that cell references: B4 to E4 (that's the bicycle numbers) must be greater than or equal to zero. And I'll click: Add, to add my next constraint.

And now, we need to tell Excel that it cannot use more parts than there are parts available. So I'll click in the Cell Reference box, and select all of the parts needed. That's cells: G7 to G11. And the default constraint (less than or equal to) is good.

I'll click in the Constraint box, and select cells: I7 to I11.

So I'm telling Excel that the parts needed, to manufacture the bikes, must be less than or equal to the number of parts available. In other words, it can't use parts it doesn't have.

So I'll now click the OK button, and you can see that I've defined both of my constraints.

So let's click the Solve button now, and see if Solver can find an answer to the problem. So I'll click: Solve, and Excel tells me yes, it has found a solution.

So I'll click: OK, in order to save Solver's solution. And let's see what Solver's proposing to do.

It wants to manufacture 25 mountain bikes, 35 street bikes, 5 racing bikes and 25 commuter bikes.

Let's see how well it used up the parts available. Well, for the wheels, it's managed to use all of the wheels. For the alloy chassis, there's going to be 10 left over. But it's used all the steel chassis, all the Hub gears and all the Derailleur gears. So Solver's done a good job of utilizing the parts available.

Now, let's look at the profit figure, in cell: F14. That was originally $4,200. But the new solution has increased the profit to $4,750. So Solver's done a good job all around, on maximizing the profits and maximizing the number of parts that we're going to use.

Well, all that remains, for this lesson, is to save your work. And I'm going to save with the new name: Bicycle Manufacturing Schedule-2. And, as usual, I'll save to the folder above my Sample Files folder.

I click the Save button, and you've now completed Lesson 7-6: Use Solver.