Consolidate Company Data (Aggregate Data from Plants)
It is a task of the user of the Protocol Spreadsheet to aggregate the values from all Plant sheets. This is done by calculating the consolidated “SUM” in the Company Sheet. Please note the following information before starting:
-
You can use the Excel® function “SUM” to link the values from the Plant sheets to the corresponding values in the Company Sheet.
For example for aggregating the clinker production (line008) of three plants of a company in 2011:
Company!AB40 “=SUM(Plant_xyz!AB40;Plant_yzx!AB40;Plant_zxy!AB40)”You can use the help function of Excel® to learn more about how to use the function “SUM” and link the cells with each other.
-
If you aggregate and link the Plant sheets cells or values with corresponding cells in the Company Sheet, please do this very carefully and make sure to link rows (line numbers) and columns (reporting years) correctly.
You can use the copy & paste functions from the “Edit” Menu in Excel in order to transfer the formula containing the “SUM” function from one cell to neighbouring rows and columns (lines and years). Thereby you avoid editing each cell individually and potential errors in the links to Plant sheet cells.
-
Please carefully double check the correctness of the consolidated values in the Company Sheet and the copied formulas. See for example the consolidated company values for the total clinker consumed in line011 and the formula in the cell Company!AC45, which is used for aggregating the Plant sheet data in line010b and the year 2012:
- The internal transfer of materials among plants of the same company should not be taken into account in the values consolidated at company level. The values from the Plant sheets should cancel each other out and result in a SUM of 0 (zero) in the Company Sheet, for example in line010b and line010c.
-
Some lines of the Plant sheet, which are not relevant or meaningful on company level, have been erased from the Company Sheet.
Sheets
- See Company Sheet
FAQs
Line-by-Line
The Company Sheet is not contained in the Line-by-Line reference since its lines contain items identical to the lines in the Plant sheet.