Excel - A way to use Solver

Purpose -

Solver is a useful tool that lets you try to find an optimal value within a set of data if you set it up right

Issue -

The set up for the solver modeling if you are trying to do something advanced can be quite complex

Solution -

1 You will have 2 areas of data. one with the list of information and one that i will call control tables

2 The list of information will be the hard data you have that you want to try and optimize around

3 The control tables will in effect be the relative weights you want to test based on the information you have

4 Lets say our example was simple and we wanted to see what factors would effect our sales the most. I take all the historicla sales points and all the relative data elements that you think may be a factor. You may need to add some from your data set like age or seasonality.

5 Then in the cotrol table section put a part that lists out each type of each factor in a seperate table and a starting relative weight.

6 Then in the list section put look ups to the weighting. and multiply out each weighting times a baseline sales of the item in question.

7 From here take a differance between the projected number you have and the actual

8 Sum up this projected number and then have solver set to minimize that value