Docstoc

Real Estate Cash Flow Model Template Excel

Document Sample
Real Estate Cash Flow Model Template Excel Powered By Docstoc
					Real Estate Foundation, Inc.

     Business Model in SAP
        March 26, 2002
                Objectives
Provide understanding of proposed REFI model in
  SAP:
• Describe SAP structures and process flow
• Demonstrate a simple business scenario
• Review reports
• Discuss open items
• Seek process owner approval of the model to
  begin next steps

                                                  2
                        Requirements
The Real Estate Foundation, Inc. is a charitable foundation established in
October 1997 to limit MIT’s liability resulting from donations to the
Institute. It is a separate MA corporation, exempt under the IRS code, and
overseen by a board.

Requirements:
• Produce REFI (Foundation company code) balance sheet, P&L from
  SAP by property and consolidated. Same FY as MIT.
• Minimize impact to central office processing – expenses will continue
  to post first to CUR (campus SAP company code). No need for HR,
  MM, or SD functions in REFI.
• Store data for donated properties, e.g., donor, location, sq. footage.
• Rollup with CUR for producing the Treasurer’s Report.

                                                                             3
    REFI Structure Model in SAP
FM: Financial Management Area                          MIT               FY = V6


                                                                    Chart = HEUS
CO: Controlling Area                                    MIT         FY = V6




FI: Company Code                      REFI                                         CUR


PCA: Profit Center                P420011       Real Estate Foundation         P420000        Treasurer’s Office



RE: Real Estate Objects                              Business
                                             RE00    Entity


CO: Internal Orders       4459500 –          R4459500 –                        4459000 –
                          4459999            R4459999                          4459499
                       IO Type REF0      Property (Land)                       IO Type 0440
                                         or Building

                                                                                                        4
                       REFI Process Flow in SAP
                                                                                      via frontend program
                                                                                                                         Loop as needed
           Create Master Data
Receive                                 Create REFI          Create CUR                  Record               Incur       Post expenses
                    Create RE
donated                                  IO; link to          IO; link to              property as          expenses       to CUR IO
                      object
property                                 RE object            RE object               asset in REFI                       (Travel, A/P)




                                                        Post cash           Prepare
                                                       voucher to            cash
       Prepare                                           REFI               voucher                          Sell
                          Inter-co JV                                                                                     Inter-co JV
      Excel JV                                                                                             property
                         cash to CUR                                                                                      expenses to
      template
                                                        Post wire                                                          REFI IO
                                                       transfer to
                                                       REFI cash
    Upload inter-                                                                                       via program run monthly @ closing
    co JV to post
     gift to CUR                SAP standard           Including
                                  reports                                             REFI / Treasurer’s Office         SAP activity
                                                       gain/loss
                                                                                      Central offices (Travel, A/P)
                                                                                                                        External activity
      Terminate
      REFI and                                                                        CAO – General Ledger
                                                                                                                        Data Warehouse
      CUR IOs                       DW                                                CAO - Cashier’s Office / A/R
                                  reports
                                                                                                                                    5
                       Points to Note
• For each property (salable unit) at the level where expenses will be
  segregated: one REFI order  one REFI RE object  one CUR
  order.
• The link between the order and RE object must be made before posting
  costs for those costs to be reflected in the RE module.
• Because expenses post initially to CUR, commitments and drill-down
  in SAP to the original document are available only on the CUR
  property Internal Orders.
• Utilize SAP standard trial balance, balance sheet, profit and loss, cash
  flow reports, and MIT custom Summary Statement and DTR.
• Reporting on real estate property master data will be via DW or
  standard RE reports. Standard RE reports do not combine master data
  with accounting data.


                                                                             6
                 Scenario Overview
Steps:
1. Michigan land with an appraisal value $340,000 is donated to REFI.
2. $7,000 expenses incurred to prepare property for sale; paid by CUR.
3. Expenses transferred from CUR to REFI.
4. Michigan land sells for $350,000. Gain of $10,000 is recorded by REFI.
5. Cash receipts transferred from REFI to CUR.
6. Net proceeds of $343,000 transferred to CUR as a gift.

Master Data:
a. RE object R4459500 (property)
b. REFI order 4459500, type REF0, linked to RE object R4459500
c. CUR order 4459000, type 0440, linked to RE object R4459500

                                                                        7
Linking REFI order 4459500 to RE
        object R4459000


                         The link to a RE
                         object is made in the
                         internal order.




                                                 8
RE object R4459500 links to REFI order
    4459500, CUR order 4459000




      The links from orders 4459000 and 4459500 to the RE object
    R4459500 are visible (display only) in the RE object “Orders” tab.
                 Will be used for mapping and reporting.


                                                                         9
       Step 1: REFI records value of
          donated property (asset)




Asset GL 170000 is used for demonstration purposes only. CAO provided
a new asset GL 102600, Donated Real Estate.



                                                                        10
Step 2: REFI expenses paid by CUR
The Foundation incurs expenses of $7,000 in preparing the property for sale. These
   costs (travel, legal, taxes, etc.) are paid by CUR through normal procedures.




       4459000 is a CUR order for the Michigan property, to which all expenses incurred in
          preparing the property for sale are initially posted. Note: It should not settle.



                                                                                              11
  Step 3: Expenses Transferred to REFI
Inter-company JV, doc type SX, transfers expenses from CUR to REFI. Utilizes clearing
   GLs 950008 REFI A/P, Due to MIT; and 950007 CUR A/R, Due from Foundation.




 Expenses should transfer by line item via custom ABAP program to be executed monthly by CAO. Travel
 advances will not transfer.
 NOTE: For ease in finding REFI SX docs, it is recommended that CAO always create the SX JVs by first
 entering the REF side of JV (cross-company doc# based on doc # and co.code of the first JV line item.).
                                                                                                           12
     Step 4: Property is sold by REFI
                        The property sells for $350,000.




         For demo only; actual gain/loss will be posted to GL 800316.

Proceeds may be received in the form of a wire transfer or check. If by check, the Cashier’s Office will
post the transaction, based on a cash voucher slip completed by REFI.
If proceeds are via wire transfer, Treasury will post the wire transfer to REFI as above preferably before
the wire transfer is posted to CUR uncleared wire transfers liability GL 201124.

                                                                                                           13
Step 5: Cash receipts transfer to CUR
The cash receipts are cleared from REFI and posted to CUR. This step is
                     performed by Cashier’s Office.




                                                                      14
Step 6: Net proceeds transfer to CUR
The net proceeds of $343,000 ($350,000 – 7,000) are transferred as a gift
                        from the REFI to CUR.



                                                                             MIT’s gift order




     Per CAO, new GL 800340, Real Estate Gift, should be used on both sides of this entry.



                                                                                                15
REFI order 4459500 Summary Statement




                                   16
REFI order 4459500 DTR




                         17
REFI Balance Sheet/P&L Statement




                                   18
      T-accounts                                 (#) = Step # that generated the posting.



            REFI Property IO 4459500                           REFI Cash Receipts GL 100111               REFI
GL 420050 (2)     2,500      340,000 (1) GL 800301
GL 421316 (2)     1,500                                          (4) 350,000
GL 421596 (2)     3,000                                                          350,000 (5)
                              10,000 (4) GL 801047

GL 800100 (6) 343,000
                                      REFI Asset GL 170000                                       Due to MIT A/P GL 950008

                                                                                                                7,000 (3)
     Per Sharon                    (1) 340,000
DeMaranville, should                             340,000 (4)                                    (5) 350,000
 use GL 800340 for                                                                                            343,000 (6)
both debit and credit

           CUR Property IO 4459000                                   CUR Cash GL 100530                       CUR
GL 420050 (2) 2,500       2,500 (3) GL 420050                                       7,000 (2)
GL 421316 (2) 1,500       1,500 (3) GL 421316                     (5) 350,000
GL 421596 (2) 3,000       3,000 (3) GL 421596
                                                                                            Due from Foundation A/R GL 950007
                                       CUR Gift IO 1180000
                                                                                                (3)   7,000
                                                     343,000 (6) GL 800303                                     350,000 (5)
                                                                                                (6) 343,000



                                                                                                                        19
    REFI cash receipts




REFI donated property (asset)




                                20
     REFI Due to MIT (liability)




CUR Due from Foundation (receivable)




                                   21
  CUR order 4459000 DTR




CUR gift order 1180000 DTR



                             22
CUR gift order 1180000 DTR



         CUR cash




                             23
Discussion Items for Process Owners
• Business entity number range: Recommend one entity for
  the Foundation, RE00.
• RE objects (property, buildings) recommended number
  range: R4459500-R4459999 - identical to the REF order
  number preceded by an “R”.
• When expenses are JV’d from CUR to REFI, do you prefer
  to view line item level or total by GL account in the REFI
  order? (Detail will always exist in the CUR order.)
• Inter-company JVs and use of Excel template
• Use of Data Warehouse for combination real estate and
  financial reporting.

                                                         24
                     Next Steps
If process owners sign off on business model today:
• Friday, April 12: FSS will complete configuration,
   development, and unit testing by this date.
• April 2002: FSS, process owners, and CAO will create test
   scripts and end-user documentation and participate in
   integration testing.
• April 17-24: Integration test.
• May 2002: Process owners will develop report
  specifications, with FSS assistance. Includes analyze SAP
  reports and/or determining fields to send to DW.

                                                          25
             Development Impact
• Modify front-end to create new internal orders for REFI
  and CUR (new order types).
• Modify Data Warehouse extracts to include real estate
  master data fields.
• Write program to post monthly SX JVs to transfer
  expenses from CUR orders to REFI orders, using link
  between IOs to RE object as mapping.
• Configure standard SAP RE reports. If do not meet needs,
  create custom ABAP to combine RE master data with
  financial data.

                                                            26
REFI Chart of Accounts




                         27
            REFI Process Flow w/ Accrual
Audit requirement: REFI must request a document in lieu of invoice before posting an accrual.
                                                                       Step 2.1                                 Step 6

               Inter-co JV               Doc to                                       Sell              Upload inter-
                                                               Post accrual
               expenses to               accrue                                     property            co JV to post
                                                               to REFI IO
                REFI IO                 expense                                                          gift to CUR

                                                                                                                Step 7
                                                                   Debit expense GL, REFI order
                      via program run monthly @                                                          Reverse
                      closing or ad-hoc as needed                  Credit REFI Liability GL 200060       accrual

                                                                          Step 9
                                                                                               Step 8
REFI / Treasurer’s Office                         If accrual
                                    Prepare       <> actual      Inter-co JV           Post actual        Receive
Central offices (Travel, A/P)      Excel JV                    actual expense          expense to          actual
CAO – General Ledger
                                   template                      to REFI IO             CUR IO            invoice

CAO - Cashier’s Office / A/R                              If accrual
                                                           = actual
                                        Step 10
SAP activity
                                Upload inter-                    Terminate
External activity               co JV to adj.                    REFI and
                                gift to CUR                      CUR IOs



                                                                                                                         28
                                                                                           (#) = Step # that generated the posting.


 T-accounts w/ Accrual                                                                                      REFI
            REFI Property IO 4459500                          REFI Cash Receipts GL 100111         REFI A/P Liability GL 200060
 GL 420050 (2) 2,500         340,000 (1) GL 800301
 GL 421316 (2) 1,500                                           (4) 350,000                                           1,000 (2.1)
                              10,000 (4) GL 801047
 GL 421596 (2) 3,000                                                         350,000 (5)             (7)   1,000
GL 4xxxxx (2.1) 1,000             1,000 (7) GL 4xxxxx
GL 800340 (6) 342,000                50 (10) GL 800340
 GL 4xxxxx (9) 1,050                          REFI Asset GL 170000                Due to MIT A/P GL 950008
                                                                                                  7,000 (3)
                                            (1) 340,000
                                                          340,000 (4)             (5) 350,000   342,000 (6)
                                                                                 (10)      50     1,050 (9)


        CUR Property IO 4459000                                         CUR Cash GL 100530                    CUR
GL 420050   (2)   2,500   2,500   (3)   GL 420050                                  7,000 (2)
GL 421316   (2)   1,500   1,500   (3)   GL 421316                  (5) 350,000
GL 421596   (2)   3,000   3,000   (3)   GL 421596                                  1,050 (8)
GL 4xxxxx   (8)   1,050   1,050   (9)   GL 4xxxxx
                                                    CUR Gift IO 1180000                      Due from Foundation A/R GL 950007

  Accrual of $1,000                                                                             (3)   7,000
                                                               342,000 (6) GL 800340
  and actual of $1,050                    GL 800340 (10) 50                                     (6) 342,000        350,000 (5)
                                                                                                (9)   1,050             50 (10)
  are recorded.
                                                                                                                             29
REFI Process Flow w/ Rental Income
Rental income reduces
expenses, thereby increasing
gift value on transfer to MIT.                                Record                       Incur
                                                            property in                  expenses
                                                               REFI
    REFI / Treasurer’s Office

    Central offices (Travel, A/P)

    CAO – General Ledger                  Prepare             Receive                   Post expenses
                                           cash                rental                    to CUR IO
    CAO - Cashier’s Office / A/R          voucher              check                    (Travel, A/P)

    SAP activity
                                                Step 2.1          Step 5.1
    External activity                                                                   Inter-co JV
                                          Post cash         Inter-co JV                 expenses to
                                         voucher to        cash to CUR                   REFI IO
                                           REFI



                                Debit Cash GL 101221                                                   via program run
 Addition to                                                                                          monthly @ closing
                                Credit Rental income GL 800309, REFI order
  process                                                                      Sell
                                                                             property



                                                                                                                    30
                                                                                      (#) = Step # that generated the posting.



 T-accounts w/ Rental Income                                                                           REFI

          REFI Property IO 4459500                           REFI Cash Receipts GL 100111         REFI A/R Cash GL 100221
GL 420050 (2)   2,500      340,000 (1) GL 800301
GL 421316 (2)   1,500       20,000 (2.1) GL 800309            (4) 350,000                        (2.1) 20,000
GL 421596 (2)   3,000                                                        350,000 (5)                          20,000 (5.1)
                            10,000 (4) GL 801047

GL 800340 (6) 363,000
                                    REFI Asset GL 170000
                                                                                   Due to MIT A/P GL 950008

                                 (1) 340,000                                                        7,000 (3)
                                               340,000 (4)
                                                                                   (5) 350,000
                                                                                  (5.1) 20,000   363,000 (6)


         CUR Property IO 4459000                                  CUR Cash GL 100530                      CUR
GL 420050 (2) 2,500     2,500 (3) GL 420050                                    7,000 (2)
GL 421316 (2) 1,500     1,500 (3) GL 421316                    (5) 350,000
GL 421596 (2) 3,000     3,000 (3) GL 421596
                                                              (5.1) 20,000
                                                                                       Due from Foundation A/R GL 950007
                                     CUR Gift IO 1180000
                                                                                           (3)    7,000
                                                363,000 (6) GL 800340                                        350,000 (5)
   Rental income of
                                                                                            (6) 363,000       20,000 (5.1)
   $20,000 (via check)
   is received in REFI.
                                                                                                                        31
REFI Process Flow w/ Capitalized
             Costs
                Loop as needed                                                 via program run monthly @
                                                                               closing or ad-hoc as needed
                                                 Step 3.1
                        Step 2.1                                    Step 3.2

  Incur         Post expenses             Inter-co JV        JV to transfer    Credit expense GL, REFI order
expenses         to CUR IO                expenses to       capital expense
                (Travel, A/P)              REFI IO            to asset GL      Debit Asset GL 102600


                           Step 4


                   Post cash           Prepare
                  voucher to            cash
                    REFI               voucher                    Sell            REFI / Treasurer’s Office
                                                                property
                                                                                  Central offices (Travel, A/P)
                    Post wire
                   transfer to                                                    CAO – General Ledger
                   REFI cash                                                      CAO - Cashier’s Office / A/R


                                                                                  SAP activity

          Include capitalized costs in                                            External activity
      gain/loss calculation, asset clearing

                                                                                                              32
                                                                                           (#) = Step # that generated the posting.



   T-accounts w/ Capitalized Costs
           REFI Property IO 4459500                                REFI Cash Receipts GL 100111              REFI
  GL 420050 (2) 2,500        340,000 (1) GL 800301
  GL 421316 (2) 1,500                                               (4) 350,000
                               4,000 (4) GL 801047
  GL 421596 (2) 3,000                                                             350,000 (5)
 GL 4xxxxx (3.1) 6,000            6,000 (3.2) GL 4xxxxx
 GL 800340 (6) 337,000
                                                  REFI Asset GL 170000                            Due to MIT A/P GL 950008
                                                                                                                   7,000 (3)
                                              (1) 340,000
                                                               346,000 (4)                       (5) 350,000       6,000 (3.1)
                                              (3.2)    6,000                                                     337,000 (6)


           CUR Property IO 4459000                                     CUR Cash GL 100530                        CUR
 GL 420050 (2)    2,500   2,500   (3) GL 420050                                    7,000 (2)
 GL 421316 (2)    1,500   1,500   (3) GL 421316                     (5) 350,000
 GL 421596 (2)    3,000   3,000   (3) GL 421596                                   6,000 (2.1)
GL 4xxxxx (2.1)   6,000   6,000   (3.1) GL 4xxxxx
                                                      CUR Gift IO 1180000                  Due from Foundation A/R GL 950007

    $6,000 capitalizable                                                                         (3)    7,000
                                                                337,000 (6) GL 800340
    costs incurred.                                                                             (3.1)    6,000    350,000 (5)
                                                                                                  (6) 337,000

                                                                                                                            33
         CAO Approvals / Decisions
3/14/2002:
•    New CUR IO type 0440, number range 4459000-4459499. Will not
    settle (zero balance at EOM due to SX JV transfers).
•    New REFI IO type REF0, number range 4459500-4459999. Will not
    settle.
•    All REFI orders will be assigned “BLANK” costing sheet (not subject
    to EB, F&A, fund fee).
•   Profit center in REFI will be P420011. Per CAO, continue to use
    Treasurer’s Office profit center P420000 in CUR.
•    Per CAO, capitalizable costs will be journal vouchered from REFI IO
    to Donated Real Estate GL 102600, and must be cleared at time of
    property sale.

                                                                       34
    CAO Approvals / Decisions, cont’d
3/19/2002:
•   Rental income will reduce expenses and therefore increase gift. Dr GL
    101220, Cr REFI order GL 800309.
•    CUR Treasury will pay for non-property specific REFI tax filing fees.
•    For SX JVs, process owners will create the entry in an Excel template,
    then e-mail to Mary Nicholson in CAO for review and upload. CAO
    only is authorized to post SX JVs.
•   CAO will provide a Trial Balance section code assignment in table
    ZZSECT for CUR and REFI property IOs (one code may be used for
    both).



                                                                         35

				
DOCUMENT INFO
Description: Real Estate Cash Flow Model Template Excel document sample