intercompany consolidation model

[dz6kb4]

Hi all.

I'd really appreciate some help with a work problem I'm having.

I work for a waste recycling and treatment company (collect black bags and recycling bins from homes and commercial businesses and either burn it, recycle it or take it to landfill).

The company has various different contracts with different councils and each contract operates as a different business.

Here is my problem:

Each contract (business) needs to budget and track the tonnes of waste that has been collected by the contract, split by black bag, paper, card, plastic etc. It also needs to track the end destination of where the waste ends up (sold to third party,burnt, sent to landfill etc).

Each contract tracks this for itself using a excel spreadsheet which is fine.

The problem comes when we need to do a company wide consolidation of waste collected and destinations of the waste. This is because:

Contract A
from households - Black bag - 10 tonnes is burnt
from households - black bag - 10 tonnes taken to transfer depot 123


Contract B
fom households - Black bag - 20 tonnes - burnt
from transfer depot 123 - black bag - 10 tonnes taken to landfill


This basically means contract A is taking waste to a transfer depot and then contract B takes the waste from the transfer depot and takes it to landfill

When the 2 contracts data is consolidated, I need to show that 10 tonnes of waste was collected from households and disposed of at landfill. However, because we are consolidating the contract spreadsheets, the 10 tonnes of black bag from contract A is added to the 10 tonnes of black bag contract B is recording as being taken from the transfer depot - which shows as 20 tonnes on the consolidated sheet!

This is wrong as it is the same 10 tonnes of waste, it's just that contract A deals with the collection but contract B deals with the disposal.

SO my question is, has anyone used or does anyone have an idea how I can consolidate the tonnes of waste collected and disposed of across the company without including these double counts when waste is handled by 2 different contracts??

Many thanks for your help.

[GIS2013]

Hi, Please upload a sample file, it'd be easier to assist.

[dz6kb4]

okay will do. I'll have to replicate and simplify as it's confidential but I did think I need to do that as I was writing my post

Cheers

[dz6kb4]

Hi. I've uploaded a quick sample with 3 tabs. 1 for each contract level tracker and one company consolidated sheet that needs to remove the double counts as automatically as possible as there will be far too many inter-contract movements to remove them manually.

Many thanks.