Using Excel Macros with Essbase

1. Using Excel Macros with Essbase Brad KanterDirector of FP&A at Gander Mountain January 23, 2009

2. Agenda • What’s the Situation at Gander Mountain? • How Do We Manage? • Reporting • Budgeting • Forecasting • Macro Examples • Q&A

3. What’s the Gander Situation? • Old Systems • Essbase 7.0 and Excel 2003; manual ETL • Complex planning, analysis and reporting needs • 116 stores, 10 districts, 2 regions, 23 states • 66 merchandise departments, 20 groups, 8 divisions • 42 HQ expense centers, 12 pyramids • Retail, internet and catalog businesses

4. What’s the Gander Situation? (cont.) • User drill-down not available • Requires users to open separate applications • Or, users make a special request for details • No money or people for new systems or consulting • Gander Mountain lost $32 million in 2007 • Small information systems team fully tied up with higher priority projects

5. How Do We Manage? • Careful Tie-outs • E.g., Do the expense accounts in this spreadsheet add up to the total expenses in Essbase? • E.g., Do sales in report A tie out to report B? • Manual processes • Multiple lock-and-sends for budgeting • Hierarchy changes require massive spreadsheet updates • Forecast at a high-level in Excel and occasionally using macros and Essbase • Reporting and budgeting using Excel Macros and Essbase

6. Reporting • Using macros, we produce Excel spreadsheet reports for each reporting area (e.g., a store) • Over 600 Excel reports generated each month • Macros retrieve from Essbase (1 or more cubes) • Macros save reports into user’s specific folder on network that only they can view • Can be a pdf file, a paste-special values Excel file, or a regular Excel file • Also, we have used e-mail macros to distribute • Only the relevant team can view their folder • e.g., only the store manager for store 491 can see P:\Financial_Reporting\Store_Ops\District8\491\

7. Budgeting • Using macros, we produce budgeting worksheets so users can view history and input their budget • Macros retrieve from Essbase and save into user’s folder on network. • Spreadsheet has upload area for lock-and-send. • It’s a challenge if a budgeting assumption changes • E.g., if benefits change per HR, then each expense center’s total expense budget changes

8. Forecasting • Typically, forecasting is at a high-level in Excel without uploading into Essbase • When Essbase is used, it can be similar to the budgeting process • Excel spreadsheets are created via macro with Essbase retrieval of YTD history plus prior forecast • Upload section in spreadsheet allows for lock-and-send into Essbase

9. Macro Example • Scenario • At our monthly Store P&L Meeting, we notice that supplies are out of control in our Texas stores. • We decide to start a “Texas Supplies Initiative”. • For me, I need to produce and distribute a report specifically on Supplies to each store manager in Texas. • I start a new spreadsheet “TX_Supplies.xls”. • First build report for one store • Name one worksheet Essbase and one worksheet Report.

10. Macro Example • Here’s what the Essbase tab might look like: • Range Name the store cell “Store_Input” • Range Name the retrieval area “RetrieveArea”

11. Macro Example • Here’s what the Report tab might look like:

12. Macro Example (continued) • Name new worksheet “Table” and list out Texas store numbers. Name this range “TX_lookup” • Create a simple macro using the Macro Recorder. Tools > Macro > Record New Macro. Immediately stop the recording and save the macro as SuppliesInTexas. • Alternatively, I could have started my macro using Tools > Macro > Visual Basic Editor. Then, Insert Module. • Or, I could have copied an existing macro-tized spreadsheet and renamed.

13. Macro Example (continued) • Go to Tools > Macro > Macros > select the macro > Edit • At the top of the visual basic code, above the macro name, add VBA code: Declare Function EssVRetrieve Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal lockFlag As Variant) As Long Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal username As Variant, ByVal password As Variant, ByVal server As Variant, ByVal application As Variant, ByVal database As Variant) As Long Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long • Now, you can use Essbase connect, retrieve and disconnect • For other Excel declaration and Essbase macro functions • Look for your file ESSXLVBA.TXT (located in the \ESSBASE\BIN directory) • The Essbase Help menu has a list and some examples

14. Macro Example (continued) • Use “Dim” to define terms for the macro • Dim Ess_Ret As range • Dim StoreList As range • Dim StoreMember As range • Use “Set” to associate the macro terms with range names in the spreadsheet • Set Ess_Ret = range("RetrieveArea") • Set StoreList = range(“TX_lookup“) • Sheets("Essbase").SelectX = EssVConnect(Empty, username, password, server, application, database)

15. Macro Example (continued) For Each StoreMember In StoreList application.Goto Reference:="Store_Input" ActiveCell.FormulaR1C1 = StoreMember X = EssVRetrieve(Null, Ess_Ret, 1) Cells.Replace What:="_0", Replacement:="0", LookAt:=xlPart, SearchOrder _ :=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _ False Sheets("Report").Select ChDir "P:\Financial_Reporting\Store_Ops\District7\" & StoreMember & "\2008\" ActiveWorkbook.SaveAs Filename:= _ "P:\Financial_Reporting\Store_Ops\District7\" & StoreMember & "\2008\" & StoreMember & " Dec Supplies", FileFormat:=xlNormal, _ password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False Next

16. Q & A • Final Comments • Yes, we have a complex, disjointed spreadsheet process • But, we are making the best of our situation • Questions?

17. Final Macro Declare Function EssVRetrieve Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal range As Variant, ByVal lockFlag As Variant) As Long Declare Function EssVConnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant, ByVal username As Variant, ByVal password As Variant, ByVal server As Variant, ByVal application As Variant, ByVal database As Variant) As Long Declare Function EssVDisconnect Lib "ESSEXCLN.XLL" (ByVal sheetName As Variant) As Long Sub SuppliesInTX() ' ' SuppliesInTX Macro ' Macro recorded 1/16/2009 by bkanter ' Dim Ess_Ret As range Dim StoreList As range Dim StoreMember As range Set Ess_Ret = range("RetrieveArea") Set StoreList = range(“TX_lookup“) Sheets("Essbase").Select X = EssVConnect(Empty, username, password, server, application, database) For Each StoreMember In StoreList application.Goto Reference:="Store_Input" ActiveCell.FormulaR1C1 = StoreMember X = EssVRetrieve(Null, Ess_Ret, 1) Cells.Replace What:="_0", Replacement:="0", LookAt:=xlPart, SearchOrder _ :=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _ False Sheets("Report").Select ChDir "P:\Financial_Reporting\Store_Ops\District7\" & StoreMember & "\2008\" ActiveWorkbook.SaveAs Filename:= _ "P:\Financial_Reporting\Store_Ops\District7\" & StoreMember & "\2008\" & StoreMember & " Dec Supplies", FileFormat:=xlNormal, _ password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _ CreateBackup:=False Next ' End Sub