Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

Free Excel Spreadsheet Templates

VIEWS: 836 PAGES: 39

Free Excel Spreadsheet Templates document sample

More Info
									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


• charlotte.janzen@railamerica.com – 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

								
To top