Budgeting and Forecasting with Prophix Part 2 – Creating Budget Templates

Recap

Welcome Back!
In Part 1 of this blog series, we started our conversation about using the power of Prophix’s corporate performance management (CPM) software to gain efficiencies and improve accuracy in the budgeting and planning processes by easily creating multiple budget scenarios, called versions.

We discussed how Prophix allows you to build new versions based on any combination of historical data and previously created planning scenarios. Once the new version is created, the finance team can utilize the other tools in Prophix to develop consistent and secure budget templates to be distributed to the department heads. Managers can then populate designated fields and return the templates. Upon approval, Prophix then imports the templates and automatically incorporates the new data into the appropriate budget version. The finance team monitors this process in Prophix’s Workflow Manager which will be detailed in a future post.

This second blog post illustrates the steps needed to create budget templates in Prophix.

See also: Budgeting and Forecasting with Prophix Part 1 – Creating Pre-Populated Budget and Forecast Versions

Creating a Data View

What is it?
Data views are informal reports created in the Ad Hoc Analysis app for both data entry and on-the-fly analysis. Data views are used to view, sort and filter data. Prophix provides flexible arrangements of data within Ad Hoc Analysis, and the data views can then be inserted into a template (described later) to create a more formal data-entry and reporting screen.

Business Case
Your office has completed the steps required to create a new budget scenario (aka, version) and used an InfoFlex process to populate the accounts to be budgeted. See Part 1 of this series for instructions. You’re now asked to create a data view to verify that the data has been properly imported. You will also configure the data view so that it can be inserted later into a formal data-entry template.

1. From the PROPHIX Home screen, click on Ad Hoc Analysis.

2. Select the correct cube and check the box to open with new layout. Click OK.

3. Click Edit Layout, on the Home tab.

4. The Edit Layout window will pop up. For this layout, move the Accounts dimension into the rows section and arrange the accounts you would like to budget.

5. Move TIME into the Columns section and select the appropriate members. Here, we’ve picked 2015 annual total and the 12 months. Version was moved to the column section to illustrate that we’ve selected the 2015 Budget version.

6. In the Pages area, select the departments to be included in the budgeting cycle.

7. Click OK to view the report.

8. With the data view displayed, change the pages to view independent departments. Click back into the Edit Layout mode if you need to rearrange the layout. Click Save As on the File tab so that the data view will be available later when you want to insert it into the formal template.

Creating a Data Entry Template

What is it?
A template is essentially a container for one or more data views. A new template starts as an empty spreadsheet to which you add data views, formatting and formulas. The data views can be created in Ad Hoc Analysis and saved, or new ones may be created in Template Studio.

Business Case
Management has requested a uniformed budget process with a single report template to be used by all departments for gathering their inputs. Your task is to create the template to be distributed to the department heads. In Part 1 you created a new scenario with historical data, marked up 15%. You’ve also created a data view which can be inserted into a template. Use the tools in Prophix’s Template Studio to design a data-entry budget template.

1. From the PROPHIX Home screen, click on Template Studio. A blank template will be displayed in design mode.
2. Add the existing data view by selecting Insert > Open.

3. Browse to the saved data view and position it within the blank spreadsheet.

4. Insert titles and headers in the document by adding Auto Text. Template Studio offers the ability to insert relevant descriptions to your reports such as the template name, user name, current time or date and page reference to appear on the final document. Click the Insert tab, then choose from the Auto Text options.

5. Provide further descriptions in the heading so that the recipient knows what filters, page members, are being viewed. For example, the department or organization whose data is being viewed should be listed in the document’s title to avoid confusion. Click the Insert tab and select Page Member.

6. Modify the rows, columns and pages individually by right clicking on Rows, Columns or a Page dimension and choosing Select Members. The Dimensional Selector window will open.

a. Add or remove members
b. Insert blank rows or columns
c. Change the order or member position

7. Continue to customize the template by clicking the Home tab. The Home ribbon lists popular formatting functions, similar to Excel spreadsheets.

8. You may lock cells to prevent data entry. For example, say, you want to include revenue figures in the report but you don’t want to allow department managers to modify the data. Highlight the cell(s) and right click. Select Format Cells and then choose Protection. Check the box to lock. You can also use fill colors to lead the manager to the correct sections to be completed.

9. To finalize the document and prepare it for distribution, go to the Home tab and click the Options button. This section provides important features concerning document protection, customizing the Excel worksheets and their naming conventions as well as setting up the documents start mode for data entry.

10. Preview the template by clicking View > Run as.  Select data entry mode.

Here’s the template after formatting. Note the various departments listed in the drop down in the Pages section. Predefined security settings ensure that department heads will only receive the template pages related to them.

Feel free to reach out should you have any questions, or need assistance with Prophix.

In Part 3 of the series we will discuss working with templates in data-entry mode, including methods for spreading values, analyzing data and performing delta analysis.

Written by:
Micah Trujillo, CPM Practice Manager
FMT Consultants