In this section we will learn how to use Forecast Pro’s Excel Collaboration capabilities to allow members of your team to view and make adjustments to your Forecast Pro forecasts in Excel which can then be imported back into your Forecast Pro project.
Please view Excel Collaboration in Forecast Pro (video) for an overview of Excel collaboration.
This lesson will walk through a sample collaborative process where four salespeople will review and modify the Forecast Pro forecasts in Excel. The process consists of the following four steps:
Step one: Generate the baseline forecasts in Forecast Pro.
Step two: Use Forecast Pro’s Excel Collaboration Manager to create four collaboration spreadsheets—one for each salesperson.
Step three: Distribute the collaboration spreadsheets to the salespeople and have them review and make changes in Excel using the Forecast Pro Excel Add-in.
Step four: Use Forecast Pro’s Excel Collaboration Manager to import the revised collaboration spreadsheets back into Forecast Pro.
Let’s get started.
STEP ONE: Generate the baseline forecasts.
We’ll begin this lesson by generating a forecast for the cakes and muffins data that we have explored in prior lessons.
Click on the “New Project” icon to go to the Forecast Pro main window.
Make sure you are on the Home tab and click the Data Manager icon.
Click the Add drop-down on the Historic Data row, select Excel and select 123 Bakery – Historic Data.xlsx to add it to the Historic Data row.
Click the Add drop-down on the Filters row, select Excel and select 123 Bakery – Filters.xlsx to add it to the Conversions row. The Filters file includes a Sales Rep column which shows the sales representative assigned to each SKU. There are four sales representatives- Pedro Martinez, Aly Raisman, Nancy Kerrigan and Daisuke Matsuzaka.
Your Data Manager should now match the one below.
Click the Read & Forecast button to read in the historic demand data and generate the statistical forecasts.
Click the Exit button to exit the Data Manager.
Move the cursor to the Navigator and right click to bring up the Navigator’s context menu. Select Expand All.
Notice that we now have generated our baseline forecasts.
STEP TWO: Create the collaboration spreadsheets.
It’s now time to create the Forecast Pro Collaboration Worksheets to send to the all-star sales team members. On the Home tab, click the Excel Collaboration icon to open the Excel Collaboration Manager.
Click the Create New button to open the Create dialog box.
The first step is to select a template. Forecast Pro provides two templates- In Place and By Selection. The In Place template shows all exported items on an editable worksheet—one item on each row. The By Selection template shows a single exported item at a time in a display that mirrors Forecast Pro’s Override grid. The By Selection template also provides an interface for selecting the exported item to display. We will see both of these templates during this lesson. Make sure that In Place.xlsx is selected in the Template drop-down.
Below the Template selection drop-down is a textbox labeled ´Export to”. This is where Forecast Pro will save the exported Forecast Pro Collaboration worksheets. For this lesson we’ll use the default setting.
Next, we need to define the data to export. Forecast Pro allows you to make overrides at any level of the hierarchy, however, each collaboration spreadsheet you generate will be for a specific hierarchy level. In our example, we want the salespeople to review the SKU-level forecasts, so make sure that SKU is selected in the Hierarchy level drop-down.
Forecast Pro also needs to know which override row should be used when the collaboration spreadsheet is imported back into the project. The Override row drop-down lists all available override rows as well as a row that is not currently shown in the override grid—Excel Collaboration.
Recall that when working with override rows, if there are multiple overrides for the same item and forecast period, the override row that is lowest in the grid is used in the final forecast. The Excel Collaboration row always appears as the final override row and thus takes precedence over all other override rows. Therefore, if your collaboration spreadsheets import into the Excel Collaboration row, it guarantees that the overrides imported from the collaboration spreadsheets will be used in the final forecast. Make sure that Excel Collaboration is selected on the Override row drop-down.
Next you need to tell Forecast Pro how many historical and forecast periods you want to include in the collaboration spreadsheet. Including some historical sales along with the forecasts can be useful. Let’s include the final three months of historical demand in our collaboration spreadsheets. To do so, use the spinner on the first date box to change the value from “1” to “-3”. Note that the corresponding month is shown right above the spinner. We will export data starting in April 2020.
Finally, we need to select which items we want to export to our collaboration spreadsheets. Select Filtered – multiple files. This option allows you to create separate collaboration spreadsheets based on the value of a filter field that you select. In our example, we will use the Sales Rep filter to generate a separate file for each salesperson. Three of our salespeople want to use the In Place template and the fourth prefers the By Selection template.
Notice that the drop-down to the right of Filtered – multiple files is now available. The drop-down displays all of the currently available filers. Select Sales Rep. Notice the Value(s) drop-down is now available. This drop-down allows us to specify which filter values we want to create Forecast Pro Collaboration spreadsheets for. Use the drop-down to specify the first three salespeople (Aly Raisman, Daisuke Matsuzaka and Nancy Kerrigan). Click the Create button and move the Create dialog box so you can see the Excel Collaboration Manager.
When we clicked Create, Forecast Pro generated the three collaboration spreadsheets we requested using a standard file naming convention and listed them on the Excel Collaboration Manager dialog box. The files have been saved to the Export directory listed at the top of the Create dialog box and Forecast Pro has associated these collaboration spreadsheets with the Forecast Pro project. These files may be updated with the most recent data and forecasts by simply selecting the file(s) in the list box and clicking Update.
We now need to generate the fourth collaboration spreadsheet for Pedro Martinez. In the Create dialog box, use the Select template drop-down to choose the By Selection.xlsx template. Use the Value(s) drop-down to the right of Filtered – multiple files to specify Pedro Martinez (deselect the others) and then click the Create button. Your screen should now look like the one below.
Click the Exit button to close the Create dialog box. Leave the Excel Collaboration Manager open.
STEP THREE: Make changes in Excel.
If this were a real forecasting session, you would now save your Forecast Pro project and alert the salespeople that their collaboration spreadsheets are ready for review. After the review is complete, you would reopen your project and import the changes they made in Excel into the Forecast Pro project.
For our lesson we will simulate this process, but we will actually launch Excel from within Forecast Pro to demonstrate some of the Excel Collaboration Manager’s functionality.
In the existing sheets section of the Excel Collaboration Manager select the spreadsheet for Nancy Kerrigan right click to bring up the context menu and select Open in Excel. This will launch Excel and open the spreadsheet. Your Excel screen should now match the one below.
The Forecast Pro Collaboration Worksheet includes all items assigned to Nancy Kerrigan. The 3 months of historical sales are shown in the grey columns with the 12 monthly forecasts to the right. Click on a value on row 8. Notice the graph updates to display the item in the row currently selected in Excel.
Now let’s take on Nancy’s role and make some changes. Suppose that Food-King has told Nancy they are running a promotion for BU-20-02 in September and she wants to increase the forecast and document the changes with a comment. Select the September cell (J3) and type in “30000”. Notice that the cell is now orange to show that changes have been made.
Select the Forecast Pro tab to show the Forecast Pro ribbon items.
Select the September 2020 forecast cell for BU-20-02 (J3) again and click the Insert/Edit Comment icon on the Forecast Pro tab.
Enter “Raised for a planned promotion” and click the OK button. Notice that there is now a red triangle in the upper right-hand corner of the cell. If you hover over the cell, you will see the comment.
Feel free to make additional changes to the forecasts to experiment with how the interface works. When you are done making changes, save the workbook and exit Excel.
Return to Forecast Pro. Exit the Excel Collaboration Manager and then reopen it using the Excel Collaboration icon (this will refresh the displayed information).
Notice that the Last action field for Nancy’s spreadsheet now displays “Modified externally”. Forecast Pro has noticed that changes have been made to the spreadsheet and has updated its’ status accordingly.
Now let’s open Pedro’s collaboration spreadsheet. Right click on the collaboration worksheet for Pedro in the Excel Collaboration Manager list control to bring up the context menu and select Open in Excel. This will launch Excel and open the spreadsheet. Your Excel screen should now match the one below.
Because we generated Pedro’s spreadsheet using the By Selection template it looks a bit different than the one for Nancy. The drop-down menus in the upper left corner define the SKU we are currently viewing. Rows 24 to 27 display the override rows in Forecast Pro (they are empty in this example because the project doesn’t currently contain any overrides).
The orange Excel Collaboration – Updates row is where you enter your desired overrides and comments. Enter 10,000 for September (F31) and then re-select the September override cell (F31). Select the Forecast Pro tab and click the Insert/Edit Comment icon. In the Comment dialog box, type in “adjusted for a promotion” and click OK.
Now let’s select a different item. Open the drop-down that is currently displaying “Cakes” and change it to “Muffins.” Notice that the graph and grid now display the first Muffin SKU. Use the SKU drop-down and select BN-20-01.
Let’s adjust the August forecast by entering 6000 in cell E31 and add a comment to document it.
Feel free to make additional changes to the forecasts to experiment with how the interface works. When you are done making changes, save the workbook and exit Excel.
STEP FOUR: Import the overrides into Forecast Pro.
Return to Forecast Pro, reopen the Excel Collaboration Manager, if necessary, and select the Import tab.
The Last action column displays the status for all collaboration spreadsheets associated with the project. Notice that since we did not modify the spreadsheets for Aly and Daisuke they display “Created in FP”, while the spreadsheets for Nancy and Pedro display “Modified externally.”
Select the spreadsheets for Nancy and Pedro by holding down the [Ctrl] key and clicking on them. Import the overrides into your project by clicking the Import button.
Exit the Excel Collaboration Manager and select Total>Cakes>Food-King>BU-20-02 on the Navigator. Notice that there is now an Excel Collaboration row on the Override grid and that it contains the overrides and comments we made in Excel. The red icons on the Navigator identifies items with overrides.
Explore these items and verify that the overrides were imported as expected.
Exit Forecast Pro.