MS Excel SQL Integration

A popular business scenario for integrating MS Excel with a SQL Server database involves using MS Excel as a flexible front-end “calculation engine” to perform complex business functions that would otherwise be more expensive and less flexible to build into SQL Server (Goal Seek and Solver, as two examples). This is a cost-effective means to provide very flexible and highly interactive method for users to manipulate large sets of data stored in a SQL Server database — while taking full advantage of powerful MS Excel functionality that is familiar to most power users.

MS Excel As A SQL Server “Calculation Engine”

A great example of using MS Excel as a SQL Server calculation engine is the MS Excel Goal Seek functionality. Goal Seek is part of a suite of commands sometimes called “what-if analysis”. When you know the desired result of a single formula but not the input value, you can use the Goal Seek feature. When goal seeking, Microsoft Excel varies the value in one specific cell until a formula that’s dependent on that cell returns the result you expect. In the example below, Goal Seek is used to determine the appropriate interest rate in cell B3 for the target payment value in B4 of $900.

MS Excel SQL Integration

The Goal Seek command uses a simple algorithm and, as a result, may have problems converging on a solution if the function is not linear. Because the iteration process begins with guesses around the source cell, if Goal Seek is having problems converging on a solution, changing the value in the source cell may help. Alternatively, you can use Microsoft Excel Solver Add-in for a more powerful alternative to Goal Seek for SQL Data Integration.

MS Excel Solver And SQL Server Data Integration

The Excel Solver function helps determine the optimal values of specific cells in a spreadsheet. Excel Solver is commonly used to determine the minimum or maximum range of values to satisfy specific business objectives:

  • How can a large drug company determine the monthly product mix at their Indianapolis plant that maximizes corporate profitability?
  • If Microsoft produces Xbox consoles at three locations, how can they minimize the cost of meeting demand for Xbox consoles?
  • What price for Xbox consoles and games will maximize profit from Xbox sales?
  • A large manufacturing company would like to undertake 20 strategic R&D initiatives that will tie up money and skilled programmers for the next five years. They do not have enough resources to undertake all 20 projects. Which projects should they undertake?
  • How do bookmakers find the best set of “ratings” for NFL teams to set accurate point spreads?
  • How should I allocate my retirement portfolio among high-tech stocks, value stocks, bonds, cash, and gold?

The Excel Solver optimization model has three components: the target cell, the changing cells, and the constraints. The target cell represents the objective or goal. We want to either minimize or maximize the target cell. The cell that measures profitability would be the target cell. In some situations, you might have multiple target cells.

Changing cells are the spreadsheet cells that we can change or adjust to optimize the target cell. The cells in which amounts are recorded are the changing cells in this model.

Constraints are restrictions you place on the changing cells. For example, perhaps there is a limit to the amount of available resources or a limit to the amount of product people are willing to buy.