Prepared by: Son Nguyen – Finance Business & Systems Analyst

1. Budgeting/Forecasting in the General Ledger Prepared by: Son Nguyen – Finance Business & Systems Analyst

2. Agenda • Manukau City Council (MCC) Enterprise Applications Overview • Requirements • Issues • Objectives • How we addressed it • Where to next

3. PeopleSoft Suite Enterprise Applications CRM 690 users Portal HR 1245 users Finance 560 users • Field Service • Marketing • Support • Sales • Integration Management • CTI Integration • Enterprise Portal • Asset Management • Billing • Expenses • General Ledger • Payables • Projects • Purchasing • Receivables • Treasury • Financials Portal Pack • Human Resources • Time and Labour • Payroll Interface • eCompensation • eProfile • Employee Portal Modules • Mobile Field Service • Mobile Order Capture • Portal Pack • Mobile Sales • CRM Financial Services • CRM for Communications • eRecruit • eBenefits • ePay • Directory Interface • Resume Processing • Talent Acquisition • ePerformance • Investor Portal • Enforcer Portal • eProcurement

4. Requirements • Long Term Council Community Plan (LTCCP), Local Government Act 2002: requires local councils to produce and regularly update a 10 years long term council plan. • MCC adopted a planning process for 20 years ahead to deliver clear goals that have been agreed between the Council and the Community • Long term planning / modeling tool • Replacement for existing budgeting tool - Camelot and Essbase • Potential Restructure

5. Issues • Historically MCC has used a combination of applications such as Camelot, Essbase, Excel and Word for budgeting and planning purpose with mixed results • Camelot, existing budgeting tool: • Limited, inflexible, duplication of data and set up • An external application • Reconciliation • Lack of support and in-house knowledge • Current version of PeopleSoft Financials V 8.4 does have some Budgetary functions but is limited for our needs. • PeopleSoft EPM – considered. It’s costly, and required longer implementation time frame

6. Objectives • To provide an easy, simple to use planning tool for the LTCCP round 2009/10 • To provide better financial planning information for decision making purposes • To simplify the process by using existing data structures, removing the need for an external application and therefore avoiding the duplication of setup • To enable multiple scenario analysis based on different assumptions • To reduce risks around the planning process • To provide a tool to enable the organisation to perform rolling forecasts

7. Assumptions/Business Rules • Budget in current day dollar amounts • Minimum of 4 planning budget stages = 4 planning budget ledgers • BASE • ELT • DRAFT • FINAL • ‘BUDGETS’ ledger remains final ledger for reporting purposes • Scenario’s are used for creating different planning models at each stage • Alternative scenario’s are always calculated from scenario ‘BASE’.

8. Assumptions/Business Rules cont… • Limited Access: only authorised users can make changes to budget ledger • Budgeting phase can control which ledger is available for adjustment • Budget for ‘normal’ Council activity years 2-20 will be extrapolated • For future years, all budgets will be loaded to Period 1 • All budgets will be entered at summary accounts level • Forecast ledger will be a rolling forecast of 18 months, it will be set up for a period of three fiscal years i.e. towards the end of the first financial year, the next 18 months forecast is in two future fiscal years.

9. Budgeting Process Data Overview BASE Ledger INFLATION scenario Yr 1-20 BASE Ledger INITIAL scenario Yr 2-20 BASE Ledger INITIAL scenario Yr 1 BASE Ledger BASE scenario Yr 1-20 ‘SOURCE’ Ledger BASE Ledger PROJECTS scenario Yr 2-20 BASE Ledger ‘XXX’ scenario Yr 1-20 ELT Ledger INFLATION scenario Yr 1-20 ELT Ledger INITIAL scenario Yr 1-20 ELT Ledger BASE scenario Yr 1-20 nVision Reports Query Reports Crystal Reports ELT Ledger ‘XXX’ scenario Yr 1-20 ELT Ledger PROJECTS scenario Yr 1-20 General Ledger Budget Enquiry &/or Update screen DRAFT Ledger INFLATION scenario Yr 1-20 DRAFT Ledger INITIAL scenario Yr 1-20 DRAFT Ledger BASE scenario Yr 1-20 DRAFT Ledger ‘XXX’ scenario Yr 1-20 DRAFT Ledger PROJECTS scenario Yr 1-20 Parameter Control Page Security FINAL Ledger INFLATION scenario Yr 1-20 FINAL Ledger BASE scenario Yr 1-20 FINAL Ledger INITIAL scenario Yr 1-20 FINAL Ledger ‘XXX’ scenario Yr 1-20 FINAL Ledger PROJECTS scenario Yr 1-20

10. Forecasting Process Data Overview BUDGETS Ledger Fiscal Years 1 – 3 Copied at end of budget cycle FORECAST Ledger FORECAST scenario P1 to 36 Update General Ledger Forecast Enquiry &/or Update screen View nVision Reports Query Reports Crystal Reports First Forecast Period FORECAST Ledger ACTUALS scenario P1 to 12 View Only Parameter Control Page Security ACTUALS Ledger Closed periods as part of month end process Summarised monthly

11. Summary of modifications • An extension of existing budgeting function in PeopleSoft Financials V 8.4 • Ledger Parameters Control Page • Data Entry Pages (Financial); Budget and Forecast data • Data Entry Page (Non-Financial) • Summary Accounts Tree

12. Summary of modifications: Ledger Parameters Control Page • Ledger Parameters Control page: A new page to control which fiscal year, Ledger, scenario and period is available for change in the data entry pages. • Restricted access via specific security role

13. General Ledger Parameter Control Page

14. Budget data Entry • Two new pages in GL; one for viewing and updating budget data and the other for viewing and updating forecast data. • A new page has been created in Projects General Information to capture non-financial planning information

15. Budget data parameters page

16. Budget Date Entry Page 1

17. Budget Date Entry Page 2

18. Budget Date Entry – Non Financial Info

19. Non Financial Information Entry

20. Summary Accounts Tree

21. Where to next • Continuation process of improvement to meet new requirements • Upgrade to PeopleSoft Financial V.9: migrate customisation to new version • Budgeting module in Financial V.9?

22. Any questions or comments?

23. Private Bag 76917 Manukau City 2104 New Zealand Ph +64 9 263 7100 www.manukau.govt.nz