Budgeting Forecasting in the General Ledger Prepared by Son

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

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

People. Soft Suite Enterprise Applications Modules Finance 560 users • Asset Management • Billing • Expenses • General Ledger • Payables • Projects • Purchasing • Receivables • Treasury • Financials Portal Pack Portal • Enterprise Portal • Investor Portal • Enforcer Portal • e. Procurement HR 1245 users CRM 690 users • Human Resources • Time and Labour • Payroll Interface • e. Compensation • e. Profile • Employee Portal • Field Service • Marketing • Support • Sales • Integration Management • CTI Integration • e. Recruit • e. Benefits • e. Pay • Directory Interface • Resume Processing • Talent Acquisition • e. Performance • Mobile Field Service • Mobile Order Capture • Portal Pack • Mobile Sales • CRM Financial Services • CRM for Communications

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

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 People. Soft Financials V 8. 4 does have some Budgetary functions but is limited for our needs. – People. Soft EPM – considered. It’s costly, and required longer implementation time frame

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

Assumptions/Business Rules w w w 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’.

Assumptions/Business Rules cont… w w w 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.

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

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

Summary of modifications w An extension of existing budgeting function in People. Soft Financials V 8. 4 w Ledger Parameters Control Page w Data Entry Pages (Financial); Budget and Forecast data w Data Entry Page (Non-Financial) w Summary Accounts Tree

Summary of modifications: Ledger Parameters Control Page w w 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

General Ledger Parameter Control Page

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

Budget data parameters page

Budget Date Entry Page 1

Budget Date Entry Page 2

Budget Date Entry – Non Financial Info

Non Financial Information Entry

Summary Accounts Tree

Where to next w Continuation process of improvement to meet new requirements w Upgrade to People. Soft Financial V. 9: migrate customisation to new version w Budgeting module in Financial V. 9?

Any questions or comments?

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