Transcript Session Title
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
2,000 employees
500 Locomotives
9,100 Railcars Alcoa Railroads
Hawaii
PSAP CSCD CORP CFNR SJVR ARZC VCRR SDIY OTVR OVR KYLE DGNO KRR HESR MMRR GEXR MSR CFE GR TPW CERA IORY NECR MSTR SOR CSO MNA ISRR CIND VSRR CA CPDR BXN EARY NCVA CBNS Eastern Corridor TNER AGR SCRF North East 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 Document balanced?
Currencies Cross company posting Doc Num returned 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 Loader
Excel
GLSU
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 from 3 rd party Interim calculations in Excel GLSU Workbook (40 – 50) worksheets / Entries SAP
19
GLSU Examples: Complex Example
• Vendor Data arrives in a flat file from 3
rd 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
associated exchange rate
• Confirm that all the
appropriate exchange rates are updated before loading plan data (regardless of the method) 10 11 12 98 99 B1 B2 D1 Q1 Q2 Q3 Q4 R1 R2 R3 WM Version Description 1 January Forecast 2 3 4 5 6 7 8 9 February Forecast March Forecast April Forecast May Forecast June Forecast July Forecast August Forecast September Forecast October Forecast November Forecast December Forecast Upload Forecast Upload Budget Board Plan Board Plan + EL07 B1 - Discontinued Ops Operations Forecast Q1 Operations Forecast Q2 Operations Forecast Q3 Operations Forecast Q4 NA Railroad Budget V1 NA Railroad Budget V2 NA Railroad Budget V3 Rate O O O O O O O O O O O O P P P P P P P P Q Q Q Q
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
• •
Charlotte Janzen at RailAmerica – To contact http://www.zoption.com
– Z Option Web site
• [email protected] – 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