Calculating the Forecast/Potential

[_eNVy_]

Hi all,

I have been given a piece of work to carry on which I am more than happy to do however, there is an element within that has me confused as it just doesn't look or feel right.

So the data is laid out like this and is manually updated through templates sent in.

	Value of Batch	Remitted to Client	On Arrangement	Potential total Collection
	£36,833	£5,065	£0.00	£5,065

<tbody>
</tbody>

So the Value of Batch is the total amount for all the work.
Remitted to Client is out of the Value of Batch how much was returned.
On Arrangement is any additional costs.
Potential Total Collection Remitted to Client + On Arrangement.

There another table which is next to the one above with the same headings. That table is the out-sourced work whereas the above is internal.

Simple stuff.

There is another table below which is formula driven.

Total Warrant	% collection rate	% Potential Collection	Income if all work given to Internal	Potential if all work given to Internal	Income / loss to us	Potential Income / loss to us
£71,400	14%	14%	£9,818.40	£9,818.40	-£4,332.07	-£4,332.07

<tbody>
</tbody>

So the Total Warrant is the Internal's Value of Batch + out-sourced Value of Batch.
% collection rate is Remitted to Client / Value of Batch.
% Potential Collection is Potential total collection / Value of Batch.
Income if all work given to Internal is Total Warrant * % collection rate.


I guess my question is, that bottom equation : Income if all work given to Internal is Total Warrant * % collection rate. doesn't look right.
Surely you can't use the 14% against the £71,400 seeing as the 14% was originally calculated for Internals work?
for that week. In reality, it would be very difficult to calculate their income had they got all of the warrants. Am I right?

I was thinking of using AVERAGE however, again, I am stuck in the same position. Is there a way to do this? As I'm racked my brain over it and getting burnt out.

Thanks!