Easy Data Entry from Excel Using GLSU and Budget Loader Charlotte Janzen, RailAmerica Jim Halepaska, Z Option What We’ll Cover … • RailAmerica background – What drives the need for uploading data? • GLSU: Overview and Examples • Budget Loader: Overview and Examples • Question and Answer • Wrap-up 2 What We’ll Cover … • RailAmerica background – What drives the need for uploading data? • GLSU: Overview and Examples • Budget Loader: Overview and Examples • Question and Answer • Wrap-up 3 RailAmerica Operations North American Operations Has Five Regions Divided Into Two Corridors Western Corridor Sunset Lone Star ENR 8,900 miles PSAP CSCD OTVR OVR 2,000 employees HESR NECR CORP MMRR GEXR CBNS 500 Locomotives MSR MSTR SOR CFE GR 9,100 Railcars KYLE TPW CERA IORY CSO CFNR ISRR CIND Alcoa Railroads MNA VSRR SJVR CA Hawaii CPDR ARZC KRR BXN EARY NCVA Eastern Corridor VCRR DGNO TNER SCRF AGR North East SDIY RSS Atlantic PCN Heartland 4 RailAmerica Operations (cont.) • 44 railroads located in 26 states/3 Canadian provinces • > 8,900 miles of track (per road 13 to 692 miles) • > 2000 total employees (per road 3 to 148) • 1.2 million total carloads (per road 5,000 to 114,000) • 2005 revenue budget of $425M (per road $1.5M to $26M) • Operated as individual profit centers • Employees are entrepreneurs • Bottom-up planning/zero-based planning 5 RailAmerica Operations (cont.) • SAP Version 4.6C • Single SAP instance (i.e., all companies in one box) • Each railroad is a separate legal entity and is defined as a separate company code • All company codes are assigned to a single controlling area (USD currency, calendar fiscal year, same chart of accounts) • Cost centers record both revenue and expense • Cost center planning for both revenue and expense • Report Painter/Writer 6 Upload Process Before GLSU and Budget Loader Hard to change content/format of the spreadsheet Doesn’t check for errors, creates BDC session errors Three-step process Uses “macros” which require maintenance and resources 7 Upload Process After GLSU and Budget Loader Z Option cuts out the middleman by posting directly to SAP from Excel 8 What We’ll Cover … • RailAmerica background – What drives the need for uploading data? • GLSU: Overview and Examples • Budget Loader: Overview and Examples • Question and Answer • Wrap-up 9 GLSU Features: • GLSU = General Ledger Spreadsheet Uploader • Post directly to SAP without leaving Excel • Validate your SAP data before posting • Lookup SAP master data values in the spreadsheet • Post more than 999 lines at a time • Post, Park, Accrue, and Foreign Currency Adj. • Supports multiple worksheets in a workbook and multiple documents in a single worksheet • Optional off-line validations • Easy Implementation / 30 Free Trial 10 How Rail America uses GLSU • Everyday GL Entries • Month-end Accruals • Specialty Postings Posting History from Budget Loader Extract Complex Spreadsheets with GLSU Expense Report (from Z Option) 11 GLSU Examples: Everyday FI Documents • Currencies • Cross company posting • Document number returned to the spreadsheet • Posting Messages returned • Post Non-PO vendor invoices 12 Everyday FI Documents Cross company Document posting Doc Num returned balanced? Currencies to the spreadsheet See Posting messages without leaving Excel 13 GLSU Examples: Specialty Posting (Eliminating history using Budget Loader Extract) • Downloading historical data using Budget Loader Using a report writer group for selection criteria • Feeding the data into GLSU for posting Using standard excel filters to sort data to “SKIP” $0 lines etc • Used to be much more of a manual process 14 GLSU Examples: Specialty Posting (Eliminating history using Budget Loader Extract) SAP Budget GLSU Loader Excel 15 GLSU Examples: Specialty Posting (Cont.) (Eliminating history using Budget Loader Extract) Using a Reporting Group to get Actuals history 16 GLSU Examples: Specialty Posting (Cont.) (Eliminating history using Budget Loader Extract) 17 GLSU Examples: Specialty Posting (Cont.) (Eliminating history using Budget Loader Extract) Use standard Excel filters to sort Skip $0 rows and subtotals 18 GLSU Examples: Complex Example • Old Process required 12 people working an entire day • New Process takes 1 person 4 hours Get Vendor data Interim calculations from 3rd party in Excel GLSU Workbook (40 – 50) worksheets / Entries SAP 19 GLSU Examples: Complex Example • Vendor Data arrives in a flat file from 3rd party vendor 20 GLSU Examples: Complex Example • Do some additional calculations in Excel Imported values Calculated values 21 GLSU Examples: Complex Example • Sheet 1 is an input area for the common header data on all 50 sheets. You can use Excel formulas in GLSU templates just line any other Excel worksheet Use standard Excel features like password protection 22 GLSU Examples: Complex Example Use standard formatting options for easy to use GLSU templates Use formulas to calculate “Skip” rows and anything else 23 GLSU Examples: Expense Report (Z Option) Never re-key another Excel based Expense Report 24 What We’ll Cover … • RailAmerica background – What drives the need for uploading data? • GLSU: Overview and Examples • Budget Loader: Overview and Examples • Question and Answer • Wrap-up 25 How Budget Loader Works • Post directly from the spreadsheet to SAP • Flexible layouts • Import plan and actual history into Excel • Use Master Data Hierarchies/Groups/Sets to quickly build templates • Master Data Lookups • Easy installation / 30 day free trial 26 How Rail America uses Budget Loader Annual Budgets Quarterly Forecast (Operations) Monthly Forecast (Corporate) Extract historical information for planning and eliminations 27 RailAmerica Budget Loader Example 28 Report Writer Report Example 29 Report Writer Report Example (cont.) We’re using different Report Writer sets to create the columns we need 30 Checklist Before Loading Budgets • Unit of Measure has been added to Cost Element • Exchange rates have been updated • Correct currency is specified • Planning versions have been created for: Cost centers Profit center Special ledger • Integrated Planning is active (to allow the plan to flow to Profit Centers and Special Ledger) 31 Quantities and Units of Measures • Quantities must be configured prior to any postings in the new year • Posting multiple Units of Measure may prevent reports from totaling correctly • Correcting Unit of Measure problems requires the deletion and reloading of all postings 32 Currencies • Each forecast has an Version 1 Description January Forecast Rate O associated exchange rate 2 February Forecast O 3 March Forecast O 4 April Forecast O • Confirm that all the 5 May Forecast O 6 June Forecast O 7 July Forecast O appropriate exchange rates 8 August Forecast O 9 September Forecast O 10 October Forecast O are updated before loading 11 12 November Forecast December Forecast O O plan data (regardless of the 98 Upload Forecast P 99 Upload Budget P B1 Board Plan P method) B2 D1 Q1 Board Plan + EL07 B1 - Discontinued Ops P P Operations Forecast Q1 Q Q2 Operations Forecast Q2 Q Q3 Operations Forecast Q3 Q Q4 Operations Forecast Q4 Q R1 NA Railroad Budget V1 P R2 NA Railroad Budget V2 P R3 NA Railroad Budget V3 P WM WM Weekly Meeting Forecasts 33 Supported Planning Transactions • Cost Center KP06: Cost/Activity Inputs KP26: Activity Output/Prices KP46: Statistical Key Figures • Profit Center Acct. (PCA) 7KE1: Costs/Revenues 7KE3: Balance Sheet 7KE5: Statistical Key Figures • Public Sector FMBBC: Budget Control System 34 Supported Planning Transactions (cont.) • Internal Orders KPF6: Cost/Activity Inputs KPD6: Statistical Key Figures • Project Systems CJR2: Cost Element/Activity Input CJS2: Statistical Key Figures 35 Supported Planning Transactions (cont.) • Profitability Analysis KEPM: CO-PA Planning • Activity-Based Costing CP06: Cost/Activity/Process Inputs CP26: Process Quant./Prices CP46: Statistical Key Figures 36 What We’ll Cover … • RailAmerica background – What drives the need for uploading data? • GLSU: Overview and Examples • Budget Loader: Overview and Examples • Question and Answer • Wrap-up 37 What We’ll Cover … • RailAmerica background – What drives the need for uploading data? • GLSU: Overview and Examples • Budget Loader: Overview and Examples • Question and Answer • Wrap-up 38 Resources • firstname.lastname@example.org – To contact Charlotte Janzen at RailAmerica • http://www.zoption.com – Z Option Web site • Jim@zoption.com – To contact Jim Halepaska • 877.653.7215 – Z Option toll-free from the U.S. and Canada • +1.972.315.8800 – Z Option (outside the U.S.) 39
"Free Excel Spreadsheet Templates"