This article will describe how to use the example ‘ForecastMargin’ Excel report generator, available to etzInsights customers within the etz online repository.
After downloading and opening the file, enter the connection settings for your etzInsights server in the ‘Connection settings’ tab (see Accessing etzInsights data from Excel for further details). Once this is completed, the Excel file will be able to communicate with the server in order to download report data.
The four cells of code with the headings ‘Predicted values query’, ‘Actual values query’, ‘ForecastReport values’ and ‘Key query’ all perform different tasks that will be described during this orientation of the report generator.
Next, examine the ‘WorkingDays’ tab – here, you will find two important things to configure that will enable the forecast report to work out the number of working days of each assignment.
The first is to define which days of the week your agency considers to be working days – for most, this will be all of them apart from Saturday and Sunday. Place a ‘Y’ or ‘N’ next to each day, as appropriate.
The second is to define the holidays that your agency recognises. Any holidays that are visible (i.e. not hidden) here will be considered to be agency-recognised holidays. The list that is included in the report by default covers holidays for many different countries – to filter only those for your own country, click on the filter button on the header of either the ‘Region Code’ or ‘Region’ and select one of the country tickboxes. Only holidays from the selected country will be displayed – the remainder of the list will be hidden. New holidays can be manually added to the bottom of the list, if it requires updating.
The next tab to pay attention to is the ‘Filters’ tab – like the other sample reports within the etz online repository, here you will find a number of parameters that can be changed to control the data that is brought back and included in the report. Edit these as necessary and then click ‘Update data’ to generate the forecast report.
The process of generating the report requires several stages:
1. For each assignment within the date range, use the start date and end date to calculate the pay and bill values that will be generated for each month of the year. This is carried out in the ‘Predicted’ tab and uses the ‘Predicted values query’.
2. Convert this into a pivot table to aggregate all of the values for each assignment. This is carried out in the ‘PredictedPivot’ tab.
3. For each assignment, sum the actual pay and bill values recorded in timesheets that have already been processed for each month of the year. This is carried out in the ‘Actual’ tab and uses the ‘Actual values query’.
4. Similarly to the predicted values, convert this table into a pivot table to aggregate the values. This is carried out in the ‘ActualPivot’ tab.
5. Generate the forecast report by combining the data in the ‘PredictedPivot’ and ‘ActualPivot’ tabs with additional data collected using the ‘ForecastReport values’ query to produce the final report. This is inserted into the ‘ForecastReport’ tab.
This is an example report and uses a fairly simple method to generate the predicted values for future months. Every agency will have a different view on what constitutes an accurate estimate of upcoming revenue, which they may implement by changing the code behind the report.