Excel Help - allocating a positive number based on positive and negative values.

[eddly]

Hi,


I'm trying to complete a monthly expense allocation. The expense allocation is to three regions and it is based on their incurred claims. This is an insurance company so claims would be a cash outflow. I need to allocate an expense amount of 104,000.


Location #1: 700,000
Location #2: 550,000
Location #3: -1,750,000 (claim recovery)

Total of -500,000.


I haven't had a negative number as a total before...



Does anyone have any suggestions as to how to do this? I have spent way too much time on this and have searched google extensively. Any help is much appreciated.

[oilyfan]

Would you not allocate zero to location 3 because in essence it spent no money?

[eddly]

Location 3 needs a negative expense allocation to take into account that they have made a recovery. Their YTD claim incurred amount is now lower, so their expense allocation YTD needs to be reduced. But yes, they definitely should not receive a positive expense allocation for this particular month.

[psyang]

I agree with oilyfan. Location 3 isn't making a claim, so shouldnt be involved with the allocation. Divide the 104k amongst locations 1 and 2 only.

[eddly]

Location 1 and 2 are paying out claims for this particular month.

Location 3 has had a reduction in a claims incurred reserve. Thus the recovery/reduction from what was recorded for claims from prior months. So the expense allocation from previous months was as a result... too high. They need an expense reduction for this month.

[calf]

Only thing I can think of is:
700,000 = 140%
550,000=110%
-1,750,000=-350%
-500,000=100%

To allocate your $104,000:
140% =145,600
110% = 114,400
-350% = -364,000

This reduces your expenses for #3 on a fair/prorated basis. It looks weird given that 1 and 2 are being allocated more than the expense is for the month, but since you over-charged 3 in prior months, you effectively under-charged 1 and 2, so this catches you up.

[trew]

Best way would be to maintain YTD balances of claims and expenses. Allocate the revised YTD expense balance based on revised YTD claim balance. Show monthly numbers as the difference between last months allocated expense balance and this months, revised, balance for each division.

[Ducay]

Shouldn't there be some type of set agreement between locations or at a minimum an accounting policy for this? Im sure CRA loves auditing CP provided solutions (assuming separate legal entities)