Creating a new model
 

The name of the example company is "Test S.A."
Test S.A. manufactures footballs and has operations in the United States, Canada and Mexico.
Sales Management is based in the United States and needs to consolidated the sales
results for 2008, as well the costs that may be involved (as a simplified Income Statement)

 
Test S.A. sales points have the following structure:
 

 

Open the Consolidator Enterprise dialog box by clicking on this icon,
which is shown below:

 

 
In the "Consolidations" tab above you can see a list of all the consolidation models you have created.
A sample model is included which is loaded by default, named "IS2007", and is intended to speed
up your learning. However, you are going to create a new model.
The various parts of the "Consolidation" tab are explained below:
 
 

 
With the above explained, let us now proceed with the Test S.A. example.
 

1. Click on to create a new model. Enter "Test2008" as its name and fill out the other
information as seen in the screenshot below. Click on the Save button.

 

 
2. Build a consolidation tree based on the structure of the sales points shown earlier in this section.
In the "Consolidation view" tab, select "Test2008" from the Consolidations drop-down list.
There is only one item in the left pane with the same name as your model's "Test2008"
Right-click on this element to display a shortcut menu with some options disabled and some enabled.
First thing you have to do is to associate a Template to this model, so please select Open Template.
 

 
* Note: With the Import Structure option you can copy a complete existing model and use it with the new model you are
creating. This is very useful if the information of the your new model is similar to that of the model you want to copy.
 
There are three alternatives to create the template:
 

 
Create a new template: Select this to add an empty book and start a template from scratch.
Copy template of existing consolidations: Select this to copy an existing model template.
Create a template from an existing book: Select this to copy the structure from an existing book and
use it as a template for the active model. You will not be using the original book, but rather
a copy has been made of the sheet we want to use as a template.
 
In our example, since you already have a similar model (IS2007) with a template already assigned to it
you can get a copy for your model.
 

 
As you can see below, you get a copy of the template that you can work on and give format to.
 

 
In this example you are going to use the template exactly as it is without any changes.
Click on the Save Template button to save changes. As you close the template, you will return to the Consolidation View tab,
ready to create the consolidation tree.
Create a consolidation tree with the same structure as that of Test S.A.'s sales points.
Right-click on Test2008 as shown in the screenshot below. You will see that some options have been enabled
in the shortcut menu, which we will explain.
 
New Consolidation: Point to it to add a new consolidation item. This element groups other books and
creates consolidation levels.
New Spreadsheet: Select it to create a new book that will be a copy of the Template. You can send this book
to the people who are to fill them out with the data to be consolidated.
 

 
Start creating the new consolidation levels as required, with as many levels as sales points.
 

 
Then right-click on each consolidation level to create the books that have to be filled out with information.
Point to New Spreadsheet on the shortcut menu.
* Note: The Rename option is enabled and you can use it to rename the item of this consolidation level or to
rename a book created with the New Spreadsheet option.
* Note: There should not be two different items in the consolidation tree with the same name or errors may occur.
 

 
Create the books required for each consolidation level.
 

 
3 Now you may send the books to be filled out to the people responsible for entering the information. You may
send them via email or any other electronic messaging media at your discretion.
The books you have created with Consolidator Enterprise are located in the same folder as the add-in; in
this example the path is:
C:\Program Files\JABSOFT\Consolidator Enterprise for Excel\Consolidations\test2008\Files
 

 
You can get to this folder from the Consolidations tab, as shown in this screenshot:
 

 
*Note: Please instruct the people you are emailing the books to that they should only fill them out with the required data
without changing such other information as book name or sheet names; otherwise, errors may occur when consolidating.
4. When you get the books back with the required information, save them in the same folder, namely
C:\Program Files\JABSOFT\Consolidator Enterprise for Excel\Consolidations\test2008\Files
Microsoft Windows will ask you if you want to replace the existing book. Click Yes.
*Note: You will have to close the Consolidator Enterprise dialog box before saving the books returned; otherwise
Microsoft Windows will not let you overwrite the original book.
 
Now that you have all the books in place, you will see how enjoyably useful Consolidator Enterprise for Excel can be.
 
5. Now open the Consolidator Enterprise for Excel dialog box and start consolidating as shown below.
Right-click on Test2008 and point to Consolidate on the shortcut menu. When finished, you will be asked
if you want to see the report; click Yes.
 

 
You will see this consolidation result:
 

 
This a consolidation of all books. If you want to see consolidations at each level; for example Canada, right-click on Canada and select Open
in the shortcut menu, and you will see only the results for Canada.
 

 
6. Visualizing information with Advanced Reports
 
Consolidator Enterprise for Excel not only consolidates data -you can also use to make the most out of it
and better analyze it by using a powerful reporting system that includes from Pivot Tables to cross sections at different levels.
In the right pane under Space of analysis data a structure will be loaded automatically. If you cannot see it, right-click
on it and select Create Data Analysis on the shortcut menu.
 
This section will allow you to create different types of reports.
 

 
You first have to expand all items to visualize them all. Right-click on the right pane and select Expanded All
 

 
You can see in the circled area the same consolidation tree structure as in the left pane but with
blank checkboxes by them, so you can select the books that will be part of the reports.
 

 
Scroll down the left pane and you will see a section called Row Labels, which shows a number of items, from Units to Net Income/Unit
These items are created from the Template and the add-in has recognized them because they were colored using Consolidator Enterprise.
 

Continue scrolling down and you will see a section called Column Labels and shows the items January to December.
These items are created from the Template and the add-in has recognized them because they were colored using Consolidator Enterprise.
 

 
Select the following items to generate reports:
 

 
Right-click on Test2008 and point to Exploit Data on the shortcut menu as shown below:
 

 
You will get the following Pivot Table
 

 
Another type of report is the cross section report. Included in this version are 8 sample report templates or forms
to see how it operates.
 

 
In this example, click OK to work with the default option.
Right-click on the Data Analysis item and select Execute Data Analysis on the shortcut menu, as shown in the figure:
 

 
You will get the following report
 

 
If you want to save this selection of items for future reports, right-click on the right pane and select Save on the shortcut menu.
 

 
You have finished the example. However, some important restrictions should be taken into account:
a. Once you have created the consolidation model, you should not rename it, neither manually nor using the add-in.
b. Once you have assigned a Template, you cannot replace it with another. If you need to make many changes to the Template,
make them on the existing Template; this can be done provided you have not the consolidation tree yet.
If you have already created the consolidation tree, remember that all consolidation tree items are copies of the Template.xls book.
c. If you need to make changes to the Template when there already is a consolidation tree, you had better create a new model.
d. Remember that each book in the consolidation tree has a sheet with the same name as the model; you should not rename it or
Consolidator Enterprise will not recognize it for consolidation.
 

 
e. Do not use other color palettes. Leave the default palette for your Templates; otherwise the Reports section will not work. As you may have already noticed,
this section works by coloring the Row and Column cells you want to filter in the reports.
f. The only manual action you can make is to give format to the template, provided there is no consolidation tree yet. Books sent to be filled out and returned with
data should overwrite the original ones in the same folder, since the add-in will look for books to consolidate in the same folder as they were created.