Exercise – Create a sales forecasting template
In this exercise, you'll apply your learned knowledge to create an Excel template that presents sales forecasting to colleagues.
Scenario
You work at a bicycle retail chain and are responsible for your organization's Dataverse environment.
The finance department manager requests that you create a spreadsheet that contains sales forecast information, which the manager includes in an investment business plan. The values must be presented in a column-type graph with values summarized by months for the estimated revenues of the customers. A view on the customer's current revenues is also required.
Prepare Dataverse
For this exercise, you'll need to create a sales forecasting table to use as the data source for the spreadsheet.
The objective of this module focuses on templates, so to accelerate the next steps, you can download a file that contains the tables that will help you proceed.
Follow these steps in a Dataverse environment where you can proceed with the import of a solution, such as a dedicated training or development environment:
Sign in to the Power Apps portal.
In the left pane, select Solutions. In the list of solutions, select Import solution.
In the Import a solution form, select Browse to find the location of the solution zip file. Select Next to proceed to the next step.
Select Import to confirm the process of the file.
When the solution import process is complete, a notification message should appear in Power Apps.
To ensure that all customizations are applied in the environment, select the newly imported solution and then Publish all customizations.
Prepare data
Next, you're going to need some data to your table. Here's some sample data you can input:
| Customer Company Name | Actual Sales | Actual Sales Updated Date | Estimated Revenues | Estimated Revenue Target Date |
|---|---|---|---|---|
| Adatum Corporation | 50000000 | 2022-12-01 | 150000000 | 2023-03-01 |
| Adventure Works Cycles | 25000000 | 2022-12-01 | 250000000 | 2022-12-30 |
| Alpine Ski House | 15000000 | 2022-12-01 | 32500000 | 2023-01-15 |
| Bellows College | 5360000 | 2022-12-01 | 2780000 | 2023-01-15 |
Alternatively, if you're a little more experienced in importing data, you can download this data as a comma-separated values (CSV) file. (For more information, see Import from an Excel or CSV file.)
Create a spreadsheet template
You've created the sales forecast table and have added records to it. Now, you can create the spreadsheet template.
In the imported solution, go to the MS Learn - Word Templates - Exercise 5 model-driven app and then select Play.
For the Active MS Learn Sales Forecast view, select the Excel Templates dropdown menu and then Download Template. Depending on your browser window size, you may need to select the ellipsis first to find the dropdown menu.
In the Download Template form, select Edit Columns to remove fields that aren't required in the Excel spreadsheet.
In the list of columns, clear the Name checkbox. Select Download to proceed to the next step.
Open the file that's downloaded in your computer. Save it with a name that will clearly identify the template after it's been uploaded back into Dataverse, such as Sales Forecast Template.xlsx. You may be prompted to Enable editing, go ahead and do that.
Records are listed as a data table. The first step to display the estimated revenues by month in a graph is to select the Insert tab, select the PivotTable dropdown, and choose From Table/Range.
By default, the current table is set as the Table/Range value. Select OK to proceed with the creation of the PivotTable.
In the newly created worksheet, set up the PivotTable columns to display monthly estimated revenues by customers and format these columns to be type Currency.
To create the requested graph, select the PivotTable Analyze tab and then PivotChart. under the Column category, select the Clustered Column chart type. Select OK to proceed to the next step.
Resize and move the generated graph over the table. (It isn't required to display the PivotTable.)
The next request from the finance department manager is to list the customers with their actual sales. Repeat Step 6 with the original table selected, then select the Insert tab, select the PivotTable dropdown, and choose From Table/Range. This time, select the Existing Worksheet option to place the PivotTable next to the graph.
In the worksheet where the graph and new PivotTable are located, set up the PivotTable columns to display actual sales by customers and format these columns to be type Currency.
To update Dataverse with the new template, save the file, then select the Excel Templates menu for the applicable view, and choose Upload Template.
Select the Excel template file and then select Upload to proceed.
To test the template, modify the sales forecast records with other values. With the intended view selected for the forecast, select the Excel Templates menu, and under the Personal Excel Templates section, select Sales Forecast Template.
When you open the generated spreadsheet, the graph and PivotTables may not be updated with the new values. Select the Data tab, then select Refresh All. Alternatively, you can set up the document to automatically refresh the data when you open it by following these instructions.
Next steps
You've now learned how to create an Excel template based on a Dataverse table view, which allows you to present this data by using the full analysis and presentation features of Excel. Next, you'll validate the learned concepts for this module.