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.
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 30000in 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.