Structured Data Input and Consolidation

Structured Data Input and Consolidation

Issues with structured data input and consolidation

If you need to collect structured input from colleagues (e.g., you need to consolidate data from business units), you are experiencing some of the following issues:

  • You have to create a spreadsheet template, clone it and send it out
  • You do not know whether your contributors received it or are working on it
  • Contributors may alter the layout
  • Contributors may (even accidentally) alter formulae
  • Contributors send their spreadsheets, usually on the day of the deadline
  • You spend a lot of time to audit, under time pressure, the consistency of the contributors’ spreadsheets
  • You run script to consolidate them and create reports

Templating in Visyond

Let’s start by preparing the a simple template to share with Contributors.

Switch to Audit Mode to see the auto-formatting as in the exhibits below.

 

You want to set some of the input cells and only allow contributors to edit certain cells.

Select the range A3:C3 and A6:C6 and click on the M (for “Mark as editable”) button.

Marking cells editable by Contributors

 

Now you can invite contributors by clicking on Template button.

Every time you invite a contributor (e.g., a colleague in a department or subsidiary), Visyond creates a clone of the worksheet with a name you assign.

Invite Contributors

 

The contributor will receive a link via email that will take her to a Visyond page(see image below) where:

  •  She can only see her worksheet. A contributor has no access to other contributors’ worksheets or your consolidated data.
  • If she tries to edit a cell you have not marked as editable, he will not be able to (see image below taken while trying to edit cell C2).
Contributor’s view

 

This gives you full control on the layout and the data entry. If you want one of the contributors to see everything, just change his permission to Collaborator.

Consolidating data in Visyond

Now sit back, relax and wait for your Contributors to complete the data entry. Once done, select the template worksheet (the one you cloned for Contributors) and click on the “C” (consolidation)button next to the Template button.

Data Consolidation

 

Visyond will create a consolidated worksheet where, by default, each cell is the summation of all the corresponding cells in the Contributors’ worksheets.

Consolidated Worksheet

 

Using templating for “private” unstructured input gathering

You can use the templating workflow to collect assumptions from contributors without them seeing anything but the worksheet shared with them:

  1. Create various worksheets (with different structures) with different assumptions you want to gather from different contributors.
  2. Invite contributors as described in the section above.
  3. Have the formulae in your calculation worksheet linked to the worksheets filled by the contributors.
how to
August 11, 2018

Was that post helpful?

Related Articles