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.

To Install Excel Solver add-in:

1. With Excel opened, click on the File button, and then click on the Excel Options command.

2. Click Add-Ins, then go to the Manage box and select Excel Add-ins from the drop-down menu.

3. Click Go.

4. In the Add-Ins box that appears, tick the Solver Add-in check box.
In the event that Solver Add-in is not available in the dialog box, click Browse to locate the add-in.

5. Click OK.

The Solver command or feature will now be available on the Data Tab, under Analyze control group.

Primary Components of an Optimization Model: Click on the drop-down link to see description.

The target cell represents the objective or goal of the problem.

The Variables Cells are the spreadsheet cells that can be changed or adjusted to arrive at the desired outcome or to optimize the target cells.

Constraints are restrictions or limitations you place or apply on the variable cells.

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.

Above is a sample Slice to Excel report created from the UsingPO cube using the OLAPWriteTable Excel connection type. To begin, we must first set the OLAPWriteTable function to use or be set into the 'Use Excel Formula' Mode. For this example, a growth rate is specified on a separate cell which will be a basis for computing for the monthly sales values from February till December.

Next, we will define a target annual sales and use the Excel Solver to compute for the required growth rate to be applied each month in order to achieve the desired Total Sales amount for the year.

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).

For this instance, since we will be copying the formula logic to the rest of the months were: Prior Month Sales * Growth rate, make the reference for the cell containing the growth rate an absolute reference.

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

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

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

Next, use Excel Solver to know what monthly growth rate is needed to achieve a Target annual Sales of $30,000.

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

Note: If the Solver command cannot be found within the Data tab, it might be because the add-in is not yet installed. To install the Excel Solver add-in program, click on This Link.

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 text box.

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.

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

If you go back to the worksheet, notice that the new Growth Rate is now equivalent to 1.3708. Also, notice that since the Sales Values for February to December are formula driven and uses growth rate as one of its factors, these values are also adjusted.

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.