Using Excel Solver in Slice to Excel Reports

Excel Solver, or simply ‘Solver’, is an Excel add-in program which is a tool used for performing what-if analysis and optimizatize or solve problems in models or Excel spreadsheets.

The optimal solution may be achieved by determining tha minimum or maximum values of a certain problem, such as maximum profits and minimum costs, or achieving some target values.

To take advantage of the Excel Solver feature, you need to enable this add-in from within Excel, since this add-in is generally not enabled by default.

For instructions on how to Install Excel Solver, click here:

Primary Components of an Optimization Model:

Using Solver in an Excel Report: OLAPWriteTable

One example where we might want to use Excel Solver is when working with an Excel report that uses the OLAPWriteTable functionality.

For this particular example, a slice is created out of the *Future Year Model* cube from the *UsingPO* database. The objective in the sample demonstration is to compute for the monthly growth rate required, and generate the corresponding target Sales values for each month to achieve the targeted annual Sales value.

The sample demonstration for using the Excel Solver with OLAPWriteTable is as follows:

1. Open the *UsingPO* database in PowerOLAP, then create a slice out of the *Future Year Model* cube with the following Slice configuration:

Page: Actual VS Budget (Actual), Regions (USA)

Columns: Accounts (All)

Months: (all members except the Quarters)

2. Create an Excel worksheet by going to **Slice** tab, then clicking on the **Worksheet** drop down command button and selecting **OLAPWriteTable** option.

3. Double click on the cell containing the OLAPWriteTable function and select ‘**Use Excel Formulas**‘ as the mode.

4. On the cell **G9**, specify the initial growth rate, for this example specify **1.1** as growth rate.

5. On the cell corresponding to February Sales, create a formula that will multiply the prior month sales (January) to the growth rate (see screenshot below for the formula).

**= B9 * $G$9**

6. Press **F9** or recalculate. Notice that the cell is now marked with a cell/comment indicator.

7. Copy the formula to cells **B11 to B20** (March to December Sales).

8. Press **F9** or recalculate. Notice that all affected cells are marked with cell/comment indicators.

9. On cell **B23** create a formula to sum January to December Sales.

10. Press **F9** or recalculate. Notice that the cell is now marked with a cell/comment indicator.

11. From the Excel ribbon, go to **Data** tab and from the Analysis control group, click on the **Solver** command button.

12. Begin by setting the value for the target cell, in this case our target cell will be the cell **B23**, the cell that contains the excel formula which calculates the Total Sales for all months January to December. Next, select the radio button **Value Of** then type **30000**in the corresponding textbox.

13. Next, define our variable cell whose value will be adjusted to satisfy the objective, which in this case is the cell containing the growth rate or cell **G9** in the example.

14. Click **OK**.

15. A message will prompt the screen, with the **Keep Solver Solution** radio button enabled, click **OK**.

16. To commit the calculated values to the database, double click on the **OLAPWriteTable** function and select the ‘**Commit formula values to cube**‘ mode. Then to remove the formulas in the worksheet, double click again on the **OLAPWriteTable** function and select ‘**Clear formulas and display cube values**‘ mode.