Set Up a Dynamic Rolling Forecast Input Form in the EPM Add-In

  • by Charles "Tim" Soper, Senior Education Consultant, SAP
  • September 18, 2013
Learn how to create a fully dynamic input form in the SAP Enterprise Performance Management (EPM) add-in. The benefit to a company is that it offers a low cost-of-maintenance feature that can generate monthly forecast data. See how to create this form from start to finish. Discover how to use Excel functions for automatically determining the month and year, how to control time in the columns, and how to use formatting to prevent data entry for prior time periods.
Key Concept
An input form is an SAP Enterprise Performance Management (EPM) report that is enabled for data input. In SAP Business Planning and Consolidation 10.0, you can use the SAP EPM add-in along with Excel functions to create an input form that displays a 12-month rolling horizon, including the three prior and nine future months as an example. Formatting is used to prevent data entry for prior months’ actual data, but to allow data entry for the forecast values for future months.

Most companies require rolling forecast input forms that display actual data in read mode and forecast data in write mode. Because this process is repeated each month, it is also a requirement to automatically determine the current month for the user and then format prior versus future months separately. I use a property of the category members (such as ACTUAL or FCST_AUG) to drive the formatting for actual versus forecast data for a low-maintenance solution that automatically locks the actual months for data entry and highlights the forecast months to allow data entry. The master data can then remain the same from month to month.

The benefit is that you can read and write into the same input form that is automatically adjusted each time the next month arrives.

This is an SAP Business Planning and Consolidation (BPC) version 10.0 scenario. SAP BPC version 10.0 uses the Excel SAP Enterprise Performance Management (EPM) add-in. Now I list the steps that you need to take:

  1. Use Excel functions to automatically determine the current month and year
  2. Use the EPMMemberOffset function to determine the prior and future months
  3. Use the EPMMemberProperty function to determine the category ID based on the current month
  4. Create a new report with accounts in the rows and time/category in the columns (Note: the sheet option is set to Use as Input Form, which means that the report can be used for both reading and writing.)
  5. Change the EPMOlapMember function to bring the current year, month, and category into the EPM report
  6. Set up a category dimension property to be used for the formatting
  7. Format actual months to be locked for data input and forecast months to data input

Charles "Tim" Soper

Charles “Tim” Soper is a senior education consultant at SAP. For the last 17 years, he has been teaching SAP classes on a variety of topics in financial and managerial accounting, business warehouse, business consolidations, and planning. He is the planning and consolidation curriculum architect and has written SAP course manuals. He also works as an SAP Senior Educational Consultant. Early in his career, he worked for Eastman Kodak Company in Rochester, NY, as a senior financial analyst. He has an undergraduate degree in economics and an MBA in finance from the University of Rochester.

See more by this author


No comments have been submitted on this article. 

Please log in to post a comment.

To learn more about subscription access to premium content, click here.