Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Output:
I am not sure, how you have calculated the Total. But I did as
AMOUNT+ADMINISTRACION+INFRAESTRUCTURA+OPERACIONES+TAXES
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
Yes, you are right. Most of the things are hard coded here.