CalPlan Form Overview
The usage of the various CalPlan forms is covered in the Navigating CalPlan class. As a reminder, here is a quick overview of the forms. Follow the link for each form to learn about the minor differences between the web and Smart View Excel interfaces.
| Form | Functionality |
| Account Intersection | Presents one row for each unique combination of Account, Fund, Chart1, and DeptID for all DeptIDs rolling up to the Entity selected in Preferences. Ready for data entry. |
| Entity Intersection | Presents one row for each unique combination of DeptID, Fund, Chart1, and Account for all DeptIDs rolling up to the Entity selected in Preferences. Ready for data entry. |
| Update Data in Form | After selecting Entity, Fund and Chart1 in the POV, presents data ordered by Account. You may select a summary level Entity (or Fund or Chart1) to see the aggregation and then change the POV to a DeptID so you can enter data. |
| Drill Through - Account Details | With summary level members selected in the POV, this form allows you to select an account and drill in to see the detail level members of the Entity, Fund, and Chart1 intersections. In this form, you can enter plan data at a summary level for period (quarter or YearTotal) and the system will spread the data to each month. |
| Adjust Internal Operating Transfers Mgmt | Use this form to enter intra-department or intra-division operating transfers for accounts in the 75### and 74### range. Set your entity preference to a department or a division. Form displays the department or division and all of the DeptIDs so you can easily ensure that transfers net to zero. |
| Actualized Forecast | Displays the prior month forecast and actuals and calculates the variance with conditional formatting to highlight favorable and unfavorable variances in excess of $1,000. Use this form to update your forecast in response to expected revenue and expenses that didn't materialize or came in at a materially different amount. |
Account and Entity Intersection Forms
These forms display the intersection for the lowest level of detail for Account, Fund, Chart1, and Entity to make it easy to review and enter data. Occasionally you will need to add data to an intersection that does not yet have any data. To add an intersection:
1. Move the mouse to a row that has the desired entries in as many of these columns as possible (Account, Fund, Chart1, or Entity (DeptID)) and right-click. If you're using a Mac, press the Option button on the keyboard while you click.
2. From the bottom of the quick menu, select Smart View
3. Select Add Intersection . . .
4. Type the desired members for each of the dimensions in the text boxes or click the button to open the Member Selection dialog box
5. Review the selections and select OK to create the new intersection
Tip: Find a row that is almost exactly the intersection you need and right-click on the dimension that will be different. This will result in the fewest number of dimensions you’ll need to edit.
Update Data in Form
This form allows you to change the POV for Entity, Fund, and Chart1 so that you can view data in summary or detail. Of course, you can only enter data when all of the dimensions in the POV are at the detail level. Key things to know about this form in Smart View:
- Double-click the + next to YearTotal to expand the Period dimension or you can use Excel functionality to unhide columns
- The drop-down lists in the POV do not visually distinguish between summary and detail members.
- Entity - Suffix for detail level (DeptID) has 5 digits; all other levels have alphanumeric characters
- Fund - Suffix for detail level funds is - Plan
- Chart1 - Suffix
Drill Through – Account Details Form
Overview
With summary level members selected in the POV, this form allows you to select an account and drill in to see the detail level members of the Entity, Fund, and Chart1 intersections.
From the drop-down lists for Entity, Fund, and Chart1 you can select any member. Then select Refresh to update the data for the new POV.
Across the columns, the form has data for several Scenario and Version combinations so you can compare what was planned at various points to what actually transpired.
|
Current Year |
Next Year |
|
Actual / Final Operating Budget / Planner Submission Operating Budget / Final Forecast / Working |
Operating Budget / Working |
Columns may be hidden when you open this form. To unhide:
- Select the range of columns that includes those you would like to show
- From the Excel Home ribbon, click the drop-down arrow on the Format icon
- Select Hide & Unhide \ Unhide Columns
Drill Down to Account Details
Once you have the POV you want, find the account you would like to investigate. Right-click the account and select Smart View \ Acct Details by POV from the quick menu.
A new sheet opens with the details for the account you selected.
Now there are two sheets: the high level summary view and the detailed view for an account. You can make updates on the detail sheet as indicated by the light yellow cells. And you can easily return to the summary view by clicking on the tab.
Explore Variances and Update Data in the Detail View
You can hide columns to easily compare the YearTotal amounts for the Final Operating Budget your organization committed at the beginning of the year to the Working Forecast that shows where you will end the year. In the example below, there are a couple of DeptIDs that have underspent significantly compared to the plan.
In the Drill Through – Account Details form, you can enter plan data by month or in the summary cells of Q1, Q2, Q3, Q4 and YearTotal. When you enter data in a summary period, CalPlan will automatically spread the amount entered to the detailed periods that are editable. For example, if Actuals have been loaded for months through January, then February – June are editable. An amount entered in Q3 would be spread to February and March. An amount entered in YearTotal would be spread to February, March, April, May and June. Spreading is done in proportion to the amounts already entered. If we had planned to spend $600 in Q3 and actually spent $100 in January, the revised amount entered in Q3 would subtract the $100 already spent and spread the remaining $1,000, with $400 in February and $600 in March.
Note: The Drill Through – Account Details form is the only form in CalPlan (or HCP) that has spreading functionality. On all other forms, you must enter plan data one month at a time.