prorate costs

[tincholiver]

Hello everyone! I don't know how to solve the following case, I hope you can help me.
I have the following table with data:

AREA	PROJECT	AMOUNT
SALES	PROYECTO A	4.363.428
SALES	PROYECTO B	8.277.968
SALES	PROYECTO C	272.139
SALES	PROYECTO D	686.263
SALES	PROYECTO E	54.328
SALES	PROYECTO F	606.148
INDIRECT COSTS	ADMINISTRACION	-866.440
INDIRECT COSTS	INFRAESTRUCTURA	-4.300.843
INDIRECT COSTS	OPERACIONES	-4.115.433
EXPENSES	TAXES	-419.938

The idea is to be able to prorate indirect costs and expenses to each project according to its percentage of sales with respect to the total.

This would be the expected result.

the administration costs for project A is equal to the sales amount divided by the total sales amount multiplied by administration. Same criteria for other costs and expenses.

AREA	PROJECT	PROYECTO A	PROYECTO B	PROYECTO C	PROYECTO D	PROYECTO E	PROYECTO F	TOTAL
SALES	SALES	4.363.428	8.277.968	272.139	686.263	54.328	606.148	14.260.274
INDIRECT COSTS	ADMINISTRACION	-265.118	-502.961	-16.535	-41.697	-3.301	-36.829	-866.440
INDIRECT COSTS	INFRAESTRUCTURA	-1.315.993	-2.496.603	-82.076	-206.974	-16.385	-182.812	-4.300.843
INDIRECT COSTS	OPERACIONES	-1.259.260	-2.388.974	-78.538	-198.052	-15.679	-174.931	-4.115.433
EXPENSES	TAXES	-128.495	-243.770	-8.014	-20.209	-1.600	-17.850	-419.938
TOTAL		1.394.563	2.645.660	86.976	219.331	17.363	193.726	4.557.620

I hope I was clear. Thank you

[Saravanan_Desingh]

Try this,

tab1:
LOAD * INLINE [
    AREA, PROJECT, AMOUNT
    SALES, PROYECTO A, 4363428
    SALES, PROYECTO B, 8277968
    SALES, PROYECTO C, 272139
    SALES, PROYECTO D, 686263
    SALES, PROYECTO E, 54328
    SALES, PROYECTO F, 606148
    INDIRECT COSTS, ADMINISTRACION, -86644
    INDIRECT COSTS, INFRAESTRUCTURA, -4300843
    INDIRECT COSTS, OPERACIONES, -4115433
    EXPENSES, TAXES, -419938
];
Left Join(tab1)
LOAD Sum(AMOUNT) As TAMT
Resident tab1
Where AREA='SALES';

Left Join(tab1)
LOAD AMOUNT As ADMAMT
Resident tab1
Where PROJECT='ADMINISTRACION';

Left Join(tab1)
LOAD AMOUNT As INFAMT
Resident tab1
Where PROJECT='INFRAESTRUCTURA';

Left Join(tab1)
LOAD AMOUNT As OPAMT
Resident tab1
Where PROJECT='OPERACIONES';

Left Join(tab1)
LOAD AMOUNT As TXAMT
Resident tab1
Where PROJECT='TAXES';

tab2:
LOAD PROJECT, AMOUNT,
AMOUNT*ADMAMT/TAMT As ADMINISTRACION,
AMOUNT*INFAMT/TAMT As INFRAESTRUCTURA,
AMOUNT*OPAMT/TAMT As OPERACIONES,
AMOUNT*TXAMT/TAMT As TAXES
Resident tab1
Where AREA='SALES';


tab3:
Generic
LOAD 'SALES' AS Project, PROJECT, AMOUNT
Resident tab2;
Generic
LOAD 'ADMINISTRACION' AS Project, PROJECT, ADMINISTRACION
Resident tab2;
Generic
LOAD 'INFRAESTRUCTURA' AS Project, PROJECT, INFRAESTRUCTURA
Resident tab2;
Generic
LOAD 'OPERACIONES' AS Project, PROJECT, OPERACIONES
Resident tab2;
Generic
LOAD 'TAXES' AS Project, PROJECT, TAXES
Resident tab2;
Generic
LOAD 'TOTAL' AS Project, PROJECT, AMOUNT+ADMINISTRACION+INFRAESTRUCTURA+OPERACIONES+TAXES
Resident tab2;

Drop Table tab1, tab2;

[Saravanan_Desingh]

Output:

[Saravanan_Desingh]

I am not sure, how you have calculated the Total. But I did as 

AMOUNT+ADMINISTRACION+INFRAESTRUCTURA+OPERACIONES+TAXES

[tincholiver]

Thanks for your answer Sharan 7, it works very well, only 2 things: how would you solve it if there are different projects and different indirect costs and expenses? For example, in a month there may be two projects with only indirect costs and in another with only expenses. The second question is, how would you do to leave the table with the columns AREA, PROJECT, AMOUNT, PERCENT.

I am very thankfull for your help

[Saravanan_Desingh]

Yes, you are right. Most of the things are hard coded here.