Multi Year Planning Template Mypt Job Aid
1679733687
File Name: multi-year_planning_template_job_aid_03_13_18.pdf
File Size: 216.90 KB
File Type: Application/pdf
Last Modified: 3 years
Status: Available
Last checked: 9 days ago!
This Document Has Been Certified by a Professional
100% customizable
Language: English
We recommend downloading this file onto your computer
Summary
Multi-Year Planning Template (MYPT) Job Aid:Contact: Herve’ Bruckert: [email protected] How to sign in to the multi-year planning template • Please sign into Smart View using the Citrix (https://citrix.berkeley.edu/Citrix/vAppsWeb). MS Excel will open automatically. If using Citrix creates a problem please use the standard excel
• Open the Multi-Year Planning Template after saving a local copy
• Select the "Template" tab
• Select the "Smart View” tab on the Excel ribbon
• Select the “Panel” button in the top left corner in order to create a connection with the database
• On the right hand side of your screen Select "Shared Connections", then connect to the database by using your Calnet ID username and password
• Select a data source using the dropdown menu just below “Shared Connections”
• Select "Oracle Essbase" (See image in Appendix)
• Select the "+" sign to the right of "EssbaseCluster-1"
• Select the "+" sign next to "CalRptg", double click the new “CalRptg” sub menu • Select "Ad Hoc Analysis"
• Select the Smart View tab, go to “options” and uncheck “adjust column width” (See image in Appendix)
• Select the Refresh button
• You should now be signed into “Essbase” and can refresh the MYPT
How to refresh the template tab • On the "Template" tab, locate and Select your L3 Org Node in the POV dropdown above • Select “Ok”
• The template tab should now populate with your divisional data
• Please take this opportunity to cross-foot your divisional data to ensure that the data were pulled correctly for your division
Decision on preferred Org Node Level to model FY20 and FY21 • Decide whether you will build your multi-year plan at the L4 or L5 Org Node Level
If you want to build your multi-year plan at a level lower than L5, please contact Herve' Bruckert at [email protected] Cascading the Template Down Please attempt to perform this step during non-peak hours. We suggest having a staff member run this well in advance of submission. Please see the Cal Planning Schedule (https://calplanning.berkeley.edu/system-availability) for the best hours of operation. Use a live feed for your connection and not a Wi-Fi connection to perform your cascade
• On the Essbase tab, select the "cascade” function. If the cascade function is not active see the TIPS section in the appendix for guidance
• Select “same workbook”
• A member selection box will open automatically at the top left corner
• Select "Total Entity”
• Find and select your L3 Org Node and select the “+” sign to be able to select the appropriate L4 or L5 Org Nodes to cascade to
• Select the blue arrows in the middle to bring your selections to the right
1|Page • Remove the L3 Org Node from your list by selecting it and moving it to the left using the blue arrows
• Select OK
• The process of cascading down is now in progress and might take up to 20 minutes to run for large divisions. Do not run any other operation on your computer during this data transfer
• The Org Nodes you selected to cascade down to will now be shown as separate tabs on the right side of your original L3 Org Node "Template" tab
Sharing the Template in MS Excel If you are developing your submission collectively you may want to use the Share Workbook function in MS Excel
• To use this function, Select the review tab, and Select the Share Workbook button
• The Share Workbook dialog box will appear
• Select allow changes by more than one user at the same time. This also allows workbook merging check box on the Editing tab
• On the Advanced tab, select the options that you want to use for tracking and updating changes, and then click OK
• Click File and Save
Modeling FY20 and FY21 in the Template In each of the cascaded L4 or L5 Org Node tabs, you will now need to model FY20 and FY21 for each account category with the exception of campus support, which is modeled by the Campus Budget Office. Campus support elements have been modeled differently and those lines should not be changed. FY20 and FY21 has been pre- populated using modeling based on prior year’s trends. Since FY20 and FY21 projections are based, in part, on the FY19 operating budget, any changes made to the FY19 operating budget will impact the FY20 and FY21 projections
To post changes in FY20 and FY21, you have the choice to use a percentage increase compared to prior year budget and/or a dollar increase. See below on how to make those changes:• Percentage increases should be entered in column M and Q and dollar increases should be entered in column N and R
• Example of input format: A 3% increase combined with a $100k increase should be entered as: =.03 under the % increase column and =100 under the $ amount increase column The equal sign is needed before the digits since smartview would delete those entries when refreshing if the equal sign was not included
• Once your modeling has been completed for each elements of your L4 or L5 we will now need to consolidate the data at the L3 Org Node Level
IMPORTANT: The cells in light blue in column M, N, Q and R are the only cells to use for input. Do not enter data in other cells
Consolidating the L4 or L5 Org Node tabs• The "consolidation" tab will consolidate all tabs between the "Beg" tab and the "End" tab
• To consolidate the data, move (click and drag) the "Beg" tab right after the "Template" tab
• The "Consolidation" tab will now be automatically populated
• Ensure that all your L4 or L5 Org Nodes are shown in the tabs between the "Beg" and "End" tab
• Check Row 68 of the "Consolidation" tab, which has a control function. The amount on this line should be $0. If it is not, the sum of the L4 or L5 Org Nodes tabs between the "Beg" and "End" tab does not properly aggregate to the L3 Org Node data
2|Page • This error can occur when some L4 or L5 Org Node are either missing or are extra between the "Beg" and "End" tab. This error can also occur when the L3 Org Node was left in the dataset and not removed from the Org Nodes to cascade to
Executive Summary View Tab• Once the consolidation is completed, the "executive summary view" tab will populate automatically with the lower level data• Please review and verify this information prior to its submission for completeness
Change in POV as forecast and operating budget are completed• The template is set with an initial POV of “Q2 submission” for the forecast and “working” for the operating budget. Units can change the POV to “working” for the forecast anytime
• Once a “planner submission” instance has been created in Calplan for both the forecast and operating budget, units need to change the POV to “planner submission” for the forecast and operating budget in the template tab and in the cascaded down tabs
APPENDIXTips for using the templateRestarting a cascade• To restart a new or different cascade, simply delete the tabs that have been created as a result of your first cascade
• Then re-cascade the template using the process described above
Activating the cascading down function• If the cascade function is shown in grey instead of black in the ESSBASE tab:• Select the Smartview tab
• Select panel
• Select shared connection
• In the drop down menu select "Oracle Essbase"
• Select the "+" sign next to "EssbaseCluster-1", and then again select the "+" sign next to "CalRptg"
• You should now be able to select the “cascade” function
Excel Functions Used (For Reference)• CAGR is used to determine the compounded average growth rate
=IFERROR((G5/C5)^(1/3)-1,"-")• CAGR is used in column L to determine the compounded average growth rate but based on the linear modeling of the data, not just the two end points
=IFERROR(((C5+3*SLOPE(CHOOSE({1,2,3,4},C5,D5,F5,G5),CHOOSE({1,2,3,4},$C$47,$D$47,$F$47,$G$47)))/C5)^(1/3 )-1,"-")• Two data sets are needed to calculate a CAGR. The first data is calculated by taking the data for the first year (C5) and adding 3 times the slope of the linear model. The second data is the data for the first year
• The CHOOSE function allows to select cells that are not next to each other
• FORECAST is used to determine FY20 and FY21 projections based on linear modeling in column O and S
• =IFERROR(IF(K5+L5=0,FORECAST($M$47,CHOOSE({1,2,3,4,5},B5,C5,D5,F5,G5),CHOOSE({1,2,3,4,5},$B$47,$C$47,$D $47,$F$47,$G$47)),G5*(1+K5)+L5),0) 3|Page Connection Image for Smart ViewSetting Image for Smart View 4|Page
• Decide whether you will build your multi-year plan at the L4 or L5 Org Node Level. If you want to build your multi-year plan at a level lower than L5, please contact Herve' Bruckert at . …
Documemt Updated
Popular Download
Frequently Asked Questions
What is the multi year training and exercise plan?
The Multi-Year Training and Exercise Plan (MYTEP) is a document that establishes overall exercise program priorities and outlines in a multi-year schedule of training and exercise activities designed to address those UIC's priorities and validate core capabilities.
How to create a multi year strategic plan?
In order to create a multi-year Strategic plan, the following steps should take place: Clearly define the organization’s goals and objectives Work with the relevant departments to identify gaps in the current processes and environment Consider the organizational objectives to identify the ...
What browser should i use for the multi year training and exercise plan?
We recommend using the latest version of IE11, Edge, Chrome, Firefox or Safari. The Multi-Year Training and Exercise Plan (MYTEP) is a document that establishes overall exercise program priorities and outlines in a multi-year schedule of training and exercise activities designed to address those UIC's priorities and validate core capabilities.