Decide, use parameters, and calculate

• Source/Input: There is an ERP system that can export order/ledger information in a pre-defined report structure, on an Excel worksheet. The entries are listed per bookkeeping account and within an account in time order.
• Target/Output: The order information must be collected by product type and aggregated by time periods, creating also several different totals (per time period, per product type).

The manual strategy is to simply go through the rows of the ERP export and copy the values one by one to as many columns as the number of product types; also manually create the necessary totals temporarily using the SUM function in separate cells. These totals will be copied again manually to the output sheet. There are some caveats, though:

• Both the number of product types and also the number of time periods can change (in a month there are mostly four weeks but sometimes five) making the task each time a little bit different.
• Every time the task is executed the time period headers must be changed in the output report

These variations make the task not just boring but also challenging: too easy to make mistakes. Let's see, how Djeeni can help.

This demo process uses three worksheets:

• Input data: an imaginary export file from an accounting/ERP system, containing orders that are booked to different accounts.
• Report: A report that totals the orders by account, day, and grand total. See the parameters of the report below.
• DjeeniParameters: This worksheet contains parameters that make the process flexible.
  • The report covers a certain period. Parameters from B2-B7 specify the start and end date of the period (inclusive). When we work with Excel data the best practice is to let dates be specified as separate year, month, and day values to avoid misunderstanding (by users and by MS Excel itself).
  • Parameter B10 specifies the starting column of the report. By default, it is B leaving column A for labels. The process does not touch columns before the given first column.
  • Parameter B11 is a by an MS Excel formula calculated numeric value of the column letter in B10.
  • Parameters B8-B9 and B12 are used by the process to calculate or store different values.

You can download a workbook containing all three worksheets from https://djeeni.com/djeeni/demoprocess/decide_parameter_calculate . Save this workbook to any folder of your choice on your computer.

• You must change the Workbook parameter of all three Wsheet Use process steps at the beginning of the process to match the location where you have saved the file you just downloaded.

The automated process works in four phases:

1. defining the worksheets; initializing parameters
2. the number of accounts that can be found on the input worksheet is counted; the account numbers themselves are collected;
3. the structure of the report according to the number of days in the period and the number of accounts is generated; and
4. the orders themselves are processed; the totals are calculated.

• Step 1-3: The three worksheets are identified.
• Step 4: Explanatory comment.
• Step 5: The number of accounts is set to 0.
• Step 6: The account number list is cleared.
• Step 7: The old report structure is cleared.
• Step 8: The date value of the period start date is calculated.

• Step 9: Explanatory comment.
• Step 10: Go through the rows of the input worksheet to count and collect account numbers. Note, that the last cell with data is sought in column A (unlike at Step 22).
• Step 11: Check if the current row contains an account number by checking if the cell in column A of the current row is empty. If yes, perform steps 12-13. If no, do nothing.
• Step 12: Increase the value of the account number counter by 1.
• Step 13: Collect the account number from the cel value in column A of the current row.
• Step 14: End conditional steps started at Step 11.
• Step 15: Next row in the list started at Step 10.

• Step 16: Explanatory comment.
• Step 17: Go through the header cells (using Row List Start). To calculate the number of requested header cells:
  • The number of days within the period (parameters B5-B7 for the end date; parameter B12 for the start date) is calculated; and
  • Later for each day, the complete list of account numbers is copied.
• Step 18: Copy the list of account numbers (saved to the range D8:<last column>*8) to the next day header section (calculated by <start column> + <number of accounts> * <current day>). This is row 2 of the report.
• Step 19: Set the whole day range to the corresponding date. The day range is calculated by: from <current day column> to <next day column - 1>; where <current day column is calculated exactly like in Step 18.
• Step 20: Next day in the list started at Step 17.

• Step 21: Initializing the account number counter. It gets the value of -1 because at every account number we increase it by one i.e., it will be zero for the first account. And zero is the best starting value at the first item in a list for further calculations.
• Step 22: The rows of the input worksheet are processed for the second time. Note, that the last row with data is sought in column B (unlike at Step 10).
• Step 23: Check if the current row is an account number row(column A in the current row is not empty). If yes then perform Step 24. If not, do nothing.
• Step 24: Increment account number counter.
• Step 25: End of the conditional steps started at Step 23.
• Step 26: Check if order fully paid (column E in the current row does contain 'Y'). If yes then perform Steps 27-31. If not, do nothing.
• Step 27: Check if the order date (column B in the current row) is in the given period (falls between the start date and end date, inclusive). If yes, then perform Steps 28-30. If no do nothing.
• Step 28: Add the order amount to the account number total.
• Step 29: Add the order amount to the daily total.
• Step 30: Add the order amount to the grand total.
• Step 31: End of the conditional steps started at Step 27.
• Step 32: End of the conditional steps started at Step 26.
• Step 33: Next row in the list started at Step 22.
• Step 34: Finish and exit process execution.