Hyperion User Group-Minnesota Using Excel Macros with Essbase

Brad Kanter Director of FP&A at Gander Mountain January 23, 2009

1

Hyperion User Group-Minnesota Agenda

• What’s the Situation at Gander Mountain?

• How Do We Manage?

• Reporting • Budgeting • Forecasting • Macro Examples • Q&A 2

Hyperion User Group-Minnesota 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 3

Hyperion User Group-Minnesota 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 4

Hyperion User Group-Minnesota 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 5

Hyperion User Group-Minnesota 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\ 6

Hyperion User Group-Minnesota 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 7

Hyperion User Group-Minnesota 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 8

Hyperion User Group-Minnesota 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.

9

Hyperion User Group-Minnesota Macro Example

• Here’s what the Essbase tab might look like: • • 5410-SUPPLIES-GENERAL/MISC 5411-SUPPLIES-RECEIVING/BACKROOM 5412-SUPPLIES-CHECK OUT/POS 5413-SUPPLIES-OFFICE SUPPLIES 5414-SUPPLIES-IN STORE SIGNAGE 5415-SUPPLIES-STORE FIXTURES 5416-NON CAPITALIZED SOFTWARE 5417-NON CAPITALIZED HDWE 5420-UNIFORMS 5440-SECURITY SUPPLIES SUPPLIES 400-CORSICANA, TX COST CENTER DEC DEC DEC YTD DEC YTD 2008 ACTUAL 2008 BUDGET 2008 ACTUAL 2008 BUDGET 497 29 694 44 26 0 0 329 47 352 2,017 Range Name the store cell “Store_Input” Range Name the retrieval area “RetrieveArea” 1,576 247 2,611 392 172 447 0 95 185 26 5,751 16,796 2,108 5,729 4,553 863 193 0 1,141 539 2,051 33,974 15,082 2,714 7,721 5,295 2,804 2,747 0 1,464 1,691 1,239 40,757 10

Hyperion User Group-Minnesota Macro Example

• Here’s what the Report tab might look like:

400-CORSICANA, TX Supplies Expense by Account for December and Year-to-Date

5410-Supplies-General/Misc 5411-Supplies-Receiving/Backroom 5412-Supplies-Check Out/Pos 5413-Supplies-Office Supplies 5414-Supplies-In Store Signage 5415-Supplies-Store Fixtures 5416-Non Capitalized Software 5417-Non Capitalized Hdwe 5420-Uniforms 5440-Security Supplies

Supplies

Actual 497 29 694 44 26 0 0 329 47 352

2,017

December Budget 1,576 247 2,611 392 172 447 0 95 185 26

5,751

Variance 1,079 218 1,917 348 146 447 0 (234) 138 (326)

3,734

Actual Year-to-Date Budget 16,796 2,108 15,082 2,714 5,729 4,553 863 7,721 5,295 2,804 193 0 1,141 539 2,051

33,974

2,747 0 1,464 1,691 1,239

40,757

Variance (1,714) 606 1,992 742 1,941 2,554 0 323 1,152 (812)

6,783

11

Hyperion User Group-Minnesota Macro Example (continued)

• • Name new worksheet “Table” and list out Texas store numbers. Name this range “TX_lookup” TX_lookup 400 401 402 403 404 407 408 409 410 411 412 470 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.

12

Hyperion User Group-Minnesota 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 13

Hyperion User Group-Minnesota 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").Select

X = EssVConnect(Empty, username, password, server, application, database) 14

Hyperion User Group-Minnesota 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 15

Hyperion User Group-Minnesota Q & A

• Final Comments – Yes, we have a complex, disjointed spreadsheet process – But, we are making the best of our situation • Questions?

16

Hyperion User Group-Minnesota 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 17