Budgeting and Forecasting with Prophix Part 1 - Creating Pre-Populated Budget and Forecast Versions
What is Prophix?
Prophix is a powerful corporate performance management (CPM) software application which offers a new, streamlined approach to the budget and planning process. Most companies manage their budgeting process with spreadsheets and data from multiple sources. These rudimentary processes often require manual entries which can be time consuming and error prone.
Prophix addresses budgeting concerns by creating budgeting scenarios, called versions, to gain efficiencies, improve accuracy and establish an auditable and collaborative process that provides actionable intelligence and enhances the benefits of planning.
Prophix allows you to:
- Create more accurate budgets with far less labor and minimize exposure to errors.
- Budget top-down, bottom-up, or a combination of both.
- Create countless scenarios and save each iteration.
- Easily analyze and review previous versions.
- Add collaborative comments and supporting details to budgets.
- Incorporate historical and plan data to model unlimited scenarios.
- Manage, protect and analyze scenarios.
- Use prebuilt tools to easily conduct variance analysis.
The Prophix Budgeting Tool Box
Prophix can help make the budgeting process much more efficient by giving the Administrator the ability to quickly create new versions and scenarios, with the power to incorporate any combination of actual data with data from prior versions. Once the new version is created, various features can be used to ease the budgeting and planning cycle by providing automation and improving security. Prophix tools include Excel-like data views, offline data entry templates, data spreading methods and workflow management.
This blog post focuses on creating a new, pre-populated budget version and a mid-year forecast based on actual data and previous forecasts.
Create a Pre-Populated Budget Version in Prophix
Business Case: Your team has been asked to create a budget for the next 12 months based on actual data from the previous months. A typical organization will begin the budget cycle for a new year prior to year-end. Instead, for simplicity’s sake, we shall assume that your company has closed the books for the previous year and would like to create a budget for the new year. You’ll be using a top-down approach to begin the process by pre-populating a new budget template with the prior year’s data, marked up by 15%.
1. From the PROPHIX Home screen, click on Model Manager.
2. Select the Version dimension and click the OK button.
3. In Model Manager, click ((All)) All and then Add.
4. The Add Member window will pop up. Enter a new key and name. Select Plan for the Version Type and Rule Set. Set your start and end dates for the new year.
5. Save and exit the Model Manager.
6. From the Home Page, go to Process Manager. To populate the scenario, an InfoFlex process is needed. This is where you’ll define the data to be copied into the new version.
7. In Process Manager, click Insert on the Home tab and select InfoFlex.
8. Click the Common Dimensions tab. Un-check the Version and Time boxes since we want to change these dimensions. Select all the common members to be used in the plan. For example, we’ll grab the pertinent departments and accounts to be budgeted.
9. Click on the Source/Target Members tab. In the Source Data section, on top, choose the version the data is coming from and the months in the prior year. In the target Members area, select the Budget version (created in steps 1-5) and the months in the new year. So, in this example we are instructing the system to copy the data from the Actual version in months Jan14-Dec14 and paste them into the Budget version for months Jan15-Dec15.
10. Click the Data Transformation tab and select Copy between time members for the Source data aggregation options. Also, increase the data by 15%.
11. Insert an Update Cube process from the Home tab and run the process.
12. Modifications can be made to the Pre-Populated version through data entry by creating a template. Here is a data view of a few accounts to be budgeted for January and the YTD total.
Create a Revised Forecast Version
Business Case: Several months have passed in the year, 2014, and the company would like to create a revised forecast to plan the last quarter. The new version will include actual data for the months closed to date and incorporate the previous forecast for the months remaining, October through December.
1. From the PROPHIX Home screen, click on Model Manager.
2. Select the Version dimension in the Financial Cube and click the OK button.
3. Click ((All)) All and then Add.
4. The Add Member window will pop up. Enter a new key and name and select Plan for the Version Type and Rule Set.
5. Define the start and end dates, for Q4 select October – December. To use actual data from previous months, click Carry Forward from: and select Act. This instructs the system to populate the new scenario with actual data for the months, Jan. 14-Sep. 14.
6. Be sure to SAVE before exiting the Model Manager.
7. To populate the remaining months of the new version, an InfoFlex process is needed which is created in Process Manager. In Process Manager, click Insert on the Home tab and select Infoflex.
8. Click on the Common Dimensions tab and un-check the Version box. Select all the common members by clicking on the Select Members buttons. Time will be the Q4 months and the rest will be the leaf descendants of each dimension.
9. Click on the Source/Target Members tab. In the Source Data section, on top, choose the version the data is coming from, the previous forecast. Below, in the Target Members, choose the Forecast version you are creating.
10. Insert an Update Cube process and run the process from the root.
11. Modifications can be made to the revised forecast by creating a data entry template. Also, a variance report comparing actuals to the budget can be used to highlight the accounts which might need to be modified. Below you’ll see Forecast 3 with actuals through June as compared to Forecast 4 with actuals through September.
That’s it! We’ve now gone through the steps of creating a pre-populated budget based on the previous year’s numbers as well as a revised forecast version based on the current year’s historical data.
I hope this blog has provided you with some insight into how a Corporate Performance Management (CPM) solution like Prophix can help you improve and speed up your budgeting and financial forecasting processes, while eliminating manual, error-prone and cumbersome processes.
Please contact FMT Consultants for assistance or more information about Prophix.
Rob Meader, Prophix Consultant
Posted by: Jakob Bechgaard