<p>about your learning experience to help us improve.</p>

You've completed

cleanFiles/SACX11_en-US_learning-sap-com_lsc-prod.zip (MMCP-DROPZONE) - 2025-01-27T12:51:13.000Z

Creating a Rolling Forecast Report

<div id="content"><div class="parentsection"><div class="section"><p tabindex="0">This demonstration video will show you how you can use all Excel functionality in addition to SAP Analytics Cloud functionality to create robust and interactive workbooks.</p><p tabindex="0">These workbooks enable you to leverage your existing knowledge and investment in Microsoft Excel while providing additional access to your SAP Analytics Cloud resources. In other words, if you are comfortable with Excel, then you can continue to use it with no need to learn a new analysis or planning tool!</p><p tabindex="0">In this lesson, we want to show you an end to end example of how you can use the SAP Analytics Cloud, add-in for Microsoft Excel to analyze data using many of the features covered in this course.</p><div class="kalturaVideoLessonWrapper"><div aria-label="" class="kalturaVideoLessonContainer" data-testid="video" id="1_r6jdd04d"></div></div></div></div></div>

Rolling Forecasts

Creating and Working with Microsoft Excel Workbooks Using the SAP Analytics Cloud, Add-in for Microsoft Excel

Learn how to create a rolling forecast report using SAP formula functions and standard Excel functionality. Choose a month and year to display actual and forecast values from SAP Analytics Cloud data.<br><br># This description was auto-generated #

Using the SAP Formula Functions and Standard Excel Functionality to Create a Rolling Forecast

In this video, we will use SAP formula functions and standard Excel functionality to create a rolling forecast report. Users will be able to choose a particular month and year and have the SAP Analytics Cloud data display the actual values for the months prior to the selected year, and the forecast values for the month of and after the selected year. To get started, we paste the list of account names and IDs that we will use in to cell H10. These names are simply text, they are not the actual Account members from SAP Analytics Cloud. Because the names match actual Account members, we can use them to help create a rolling forecast report. Now, we use Excel Data Validation functionality from the Data tab in the ribbon. We will add two drop-downs that allow the user to choose a month and a year. In Cell B5, we add an Excel formula that checks to see if the month value selected is a 1 or 2 digit month. If it is a single digit month, then a zero is added to the start of the month number. For example, if February is selected, then the 2 will be updated to 02. Next, we will add a few more Excel formulas that will help us create the final table containing the SAP Analytics cloud data. In cells I5 to T5, the formula that is displayed on the screen concatenates the year in B4 and the double digit month. Now, we will continue to use standard Excel functions to construct the column headers for our rolling forecast. In cells I8 to T8, the formula compares the year month in row 5, (starting with column I), to the month selected in cell B2. If the year/month values are less than the month selected, then the word actuals appears. Otherwise, the word forecast is displayed in the cell. In I9 to T9, the formula concatenates the 3 letter abbreviation for the month and the last 2 digits of the year. You can see that by selecting cell K9, the formula updates. Finally, we use the sap function SAP.GETDATA to populate the table with the values from SAP Analytics Cloud. Let's look at cell I10 to explain the logic for this formula. First, it checks to see if the word in row 8 is Actual. If the word is Actual, then the actual version data for the Account listed in column H is displayed in the cell. Otherwise, the Forecast data for the account and month year is displayed. Since the user can choose a month value in cell B2, they determine if the table displays Actual or forecast values for a given month/year combination. With April selected, January to March contain Actual data, and April onwards contains Forecast data. As the months progress through the year, Forecast values are replaced by actual values, thus creating the rolling effect. With February selected, January contains Actual data, and February onwards contain Forecast data. All of the data is pulled from SAP Analytics Cloud using the SAP.GETDATA function in a standard Excel formula. Using the SAP functions in Excel formulas and with other Excel functionality gives you tremendous flexibility to create robust worksheets that incorporate SAP Analytics Cloud data with any other data sources you use in your organization.