Finance Division USER PROCEDURE CO Excel Planning
CO Excel Planning
Objective Budget Preparers are to use CO Excel Planning to post and change budgets in the Controlling module of SAP. Prerequisites The following prerequisites apply when posting budgets via CO Excel Planning: 1. The Budget Version is unlocked. Finance Division will ensure Budget Version F01 and Budget Version 3 are unlocked so that users may use either one. 2. Relevant Funds, Cost Centres, and Cost Elements are available for posting 3. Set Controlling Area. Refer to notes below. 4. Set Planner Profile. Refer to notes below 5. Set Excel macro security level to medium. The menu path In Excel is Tools> Macro>Security; choose Medium. 6. Input budget data into the Planning Template (worksheet 1) only. 7. Users may save the Planning Template on local PC. The file must not contain more than 999 rows or 9,999 cells. The data may then be copied, via the “copy and paste” function in Excel to CO Excel Planning in SAP, (Planning Template) for posting budget in SAP. Trouble Shooting displaying / entering data Avoid any use of the following Excel functions - Delete - Paste - Sort - Filter To delete a row of data, choose the SAP delete icon individual cell, choose delete from your keyboard. . To delete data in an
To search available elements, use the SAP possible entries icon . To sort, select data to be sorted from header row 4 to last data row. From the Excel menu bar, choose Data > Sort. Enter new objects directly beneath the existing SAP data (beneath the totals row if planning data already exists). To run a validity check for the data entered, choose the SAP enter icon . Use of filters should be avoided in the Excel view within SAP. If you are using the Totals Layout to view or edit your Plan data, rows with Distribution Key 0 should not be updated in this layout. Instead, the plan data should be updated in the Calendarised layout. The Excel Cost Centre Planning screen can only display up to 950 rows and 9,999 cells of data. This could cause errors if the Selection Criteria is a cost centre group.
Set Controlling Area and Planner Profile
CO Excel Planning can be located under Budget Version Clerk > Cost Centre Planning & Cost Element/activity input
or alternatively, under Budget Version Planning > Cost Centre Planning > Cost element/activity input
Firstly set the Controlling Area. The Controlling Area defaults to 1000, confirm (enter) by clicking on the green tick Secondly set the Planner Profile by choosing from the drop down box one of the following; 1. ZLTUGL 2. ZLTUGRP GL Excel Planning (when budgeting at the general ledger level) or Group Cost Element Excel Planning (when budgeting at the general ledger group level)
Budget Units may budget at the general ledger Group level or at the general ledger level. For 2008 the Teaching, Research and Corporate Administration Areas generally budget at the general ledger Group level and the Commercial Trading & Service Areas budget at the general ledger level.
2
Co Excel Planning
To post budget data in SAP choose Cost Element > Change There are two budget layouts available; 1. Totals Excel Cost Centre Planning Layout – refer to diagram below and 2. Calenderised Excel Cost Centre Plan.
3
Input required for this screen: Field Version Value ? Comments Budget Version: 1 is Approved Budget 2 is CST Budget 3 is Internal Budget F01 is Annual Forecast Note: To adjust/change the Budget Forecast select Budget Version F01 Year From Period To Period Cost Centre Fund Cost Element ???? 1 12 ???? ???? ?????? Enter the Budget Year Period 1 should be entered as the budget is being loaded for the full year Period 12 should be entered as the budget is being loaded for the full year Enter Cost Centre / Range / Cost Centre Group Enter Fund / Range / Fund Group Enter Cost Element / Range / Cost Element Group icon in the standard toolbar or by pressing F5.
Enter this data by clicking on the overview
Select Enable Macros to ensure the template is displayed in Excel format. When posting the Original budget in SAP it is recommended that the Totals Excel Cost Centre Planning Layout is used initially. i.e. for every Cost Centre, Fund and cost element enter total annual budget amount and a distribution key. The system will automatically distribute the total amount over the 12 month budget year. Users may then choose to the Calenderised Excel Cost Centre Plan layout to redistribute budget data to a more appropriate distribution.
Planning Template Budget data is to be entered only in the Planning Template (WORKSHEET 1). Other worksheets include a summary report (s) and master data. As budget data is input, the summary report is updated to assist users in building their budget. If users do not wish to start building their budget with a blank template, information from the master data worksheet may be copied to the Planning Template. The Planning Template can be saved on to the local PC. Once the template is complete budget data can be copied using the Excel „copy and paste‟ functions back to CO Excel Planning, Planning template for posting into SAP.
4
Users may choose to save the template on their PC or work on line. Note that when posting budget data online users may experience “time out” if period between posting data is lengthy.
Input required for this screen: Field Cost Element Value ???? Comments for further information refer to the LTU Chart of Accounts list on the web site http://www.latrobe.edu.au/finance/docs/accounts.xls Cost Centre and Fund Functional Area Total Planning amount Distribution Key ???? , ?????? 01 ????? 1 or 7 ensure Cost Centre and Fund are open for posting All areas (except CFA) to use 01- LTU Operating total annual budget amount 1 – for even amount over 12 months or 7- number of days per month
Note: The Planning Template format must remain unchanged.
If a budget has already been loaded for the selected values, the data will appear in the worksheet displayed above. Users may continue to post budget data by entering additional budget data on the next available blank line.
5
Saving the template To save this file as a template to your PC, from the Excel toolbar, choose File > Save copy
. Input required for this screen: Field Save in File name Save as type Value ? ???? Must be .xls Comments Choose where to store your template on your PC Enter the file name. The file name must match the naming conventions used on page 2 of this manual The file type must be .xls to ensure the complete document with all worksheets saves to your PC
Choose save button to save the file to your PC.
6
Posting budgets in SAP Users may enter budgets in SAP by keying data directly in the Planning Template and then save using the Post icon. If users are working from a saved template on their PC budget, data is to be copied (highlight cells to the copied) and paste (using Excel functions) budget data into CO Excel Planning, Planning Template and then save using the Post icon.
7
SAP Cost Centre Plan Calenderised Report Budget posted or changed in SAP must be checked by running a Cost Centre Plan report. The menu path is Financial report > Calenderised Reports > LTU Cost Centre Plan P&L Periodic view
If adjustments to the budget are required CO Excel Planning is to be used. Users follow the same process as above. The budget posted in SAP will appear in the Planning Template, users are then required to make the necessary changes (in the Totals Excel Cost Centre Planning layout or Calenderised Excel Cost Centre Plan layout) and save using the Post icon. The system will automatically save the changes only.
Note: The Controlling Budget is copied to Funds Management via a scheduled daily background job in SAP for F01, BV2 and BV3.
8