Formula to calculate pending week no. from defined cost

[Rajender]

Hi All,

I have two sheet in a workbook, 1 sheet named Summary & 2nd Data

In Summary sheet I am taking input from User for country name selection , No of resourses,
& KT duration(In weeks) in cells D6,G6 & J6 respectively.

Now in data sheet cell contains the following values:

K26 : Total Approved Budget
E3 : No of resourses
J3 : Actual total expenses
G3 : KT duration in Days
K3 : Difference between total approved Budget & Actual Expenses.

Now in cell M3 I applied a below formula to calculate by how much weeks we need to extend our KT to utilize the maximum budget ?

=IFERROR(IF(F3<J3,"Project cost is already over budgeted",IF(K3/(J3/G3*7)<1,"Budget is almost fully utilized ","KT duration can be extended by "&TEXT(M8,0))&" more weeks"),"")

I am unable to apply logic how to calculation and unable to find what i am missing in above formula.

Looking for your help how to identify by how many weeks we can extend KT.


Thanks & Regards
Rajender

 

[Luke M]

Hi Rajender,

Your formula makes references to several cells that you did not include in your description. We would need either the description of what those cells are, or for you to upload a sample workbook showing these cells, and preferably an example of what you want to calculate.

 

[Rajender]

Hi Luke,

I uploaded the file in below given link.

https://onedrive.live.com/?cid=393CD98CEE2A349F&id=393CD98CEE2A349F!105
if above link not work than please use
https://onedrive.live.com/redir?resid=393CD98CEE2A349F!115

Regards,
Rajender

Hi Rajender,

Your formula makes references to several cells that you did not include in your description. We would need either the description of what those cells are, or for you to upload a sample workbook showing these cells, and preferably an example of what you want to calculate.

 

[NARAYANK991]

Hi Rajender ,

I think your formula should be :

=IFERROR(IF(J3>F3,"Project cost is already over budget",IF(K3<(H3*E3),"Budget is almost fully utilized ","KT duration can be extended by " & ROUNDDOWN(K3/(H3*E3),0) & " more days")),"")

Narayan

 

[Rajender]

Hi Rajender ,

I think your formula should be :

=IFERROR(IF(J3>F3,"Project cost is already over budget",IF(K3<(H3*E3),"Budget is almost fully utilized ","KT duration can be extended by " & ROUNDDOWN(K3/(H3*E3),0) & " more days")),"")

Narayan

Hi Narayan,

Thanks for your prompt assistance. but I need the answer in weeks , when i am replacing given the formula in Tracker than it showing 2 more days instead of "week" , also the output showing as 2 days.

Could you please assist for the logic to convert the same into week.
this cell I will linked to summary sheet , in which if any user change any country , KT duration than it should calculate the according to it but how much weeks I need to extend the KT in order to utilize the maximum budget.

Regards,
Rajender

 

[NARAYANK991]

Hi Rajender ,

The point is that a multiplication of the per day cost and the number of resources will give the answer in number of days ; thus , given the values of 135,545 under budget , 15193 per day cost and 3 resources , 135545/(15193*3) is less than 1 week. In this situation , what do you want should be displayed , 3 days or 0 weeks ?

Narayan

 

[Rajender]

Hi Rajender ,

The point is that a multiplication of the per day cost and the number of resources will give the answer in number of days ; thus , given the values of 135,545 under budget , 15193 per day cost and 3 resources , 135545/(15193*3) is less than 1 week. In this situation , what do you want should be displayed , 3 days or 0 weeks ?

Narayan

Hi Narayan,

I want 0 weeks instead of days in that case.
Also I want to know the
15,193 is the average of Approved budget cost & actual expense 5,21,868(Cell J3) , so i am not sure if average of approved budget we need to refer or Average of actual expenses we need to take.

Regards,
Rajender

 

[NARAYANK991]

Hi Rajender ,

I do not know from where your figure of 5,21,868 has been derived ; in the file you have uploaded , J3 has 17,78,788 , which I assume is for all the 3 persons. As against this estimated cost , the approved budget in F3 is 19,14,333 , which means there is still 1,35,545 which can be utilised.

If you want that in this situation , you should get 0 weeks , then in the posted formula , just divide the result by 7 , as in :

=IFERROR(IF(J3>F3,"Project cost is already over budget",IF(K3<(H3*E3*7),"Budget is almost fully utilized ","KT duration can be extended by " & ROUNDDOWN(K3/(H3*E3*7),0) & " more weeks")),"")

Narayan

 

[Rajender]

Hi Rajender ,

I do not know from where your figure of 5,21,868 has been derived ; in the file you have uploaded , J3 has 17,78,788 , which I assume is for all the 3 persons. As against this estimated cost , the approved budget in F3 is 19,14,333 , which means there is still 1,35,545 which can be utilised.:

If you want that in this situation , you should get 0 weeks , then in the posted formula , just divide the result by 7 , as in :

=IFERROR(IF(J3>F3,"Project cost is already over budget",IF(K3<(H3*E3*7),"Budget is almost fully utilized ","KT duration can be extended by " & ROUNDDOWN(K3/(H3*E3*7),0) & " more weeks")),"")

Narayan

Hi Narayan,

For current inputs which are selected in this file the results show correct , but when I am changing my inputs in summary sheet to another country it shows wrong result: for example I changed the input in summary sheet as below :-

Select Country Netherlands No. of resources 4 1 KT Duration 3 Weeks

then the current formula shows result "KT duration can be extended by 1 more week" , if I am cross checking the KT duration should be extended by 4 Weeks i.e in total 7 weeks we can utilize the maximum budget.

Could you please assist for the same once again.

Apart from this I am cross checking if any cell is wrongly mapped or not.

Thanks in advance.

Regards,
Rajender

 

[NARAYANK991]

Hi Rajender ,

First confirm the following data :

1. Approved budget : 25,55,333

2. Estimated Cost : 12,37,757

3. Remaining Amt. : 13,17,477

4. Amt. per person per day : 30,419

5. Number of resources : 4

6. Per day cost for 4 resources : 1,21,678

7. Total number of days extension possible : (3) divided by (6) = 10.8

From where have you got the 4 weeks extension possible ?

Narayan

 

[Rajender]

Hi Rajender ,

First confirm the following data :

1. Approved budget : 25,55,333

2. Estimated Cost : 12,37,757

3. Remaining Amt. : 13,17,477

4. Amt. per person per day : 30,419

5. Number of resources : 4

6. Per day cost for 4 resources : 1,21,678

7. Total number of days extension possible : (3) divided by (6) = 10.8

From where have you got the 4 weeks extension possible ?

Narayan

Hi Narayan,

there is a small difference of amount may be due to ROE, I interlinked the file with online prices.
In current the prices are showing as

1. Approved budget : 25,55,333 25,52,444

2. Estimated Cost : 12,37,757 12,37,380

3. Remaining Amt. : 13,17,477 13,15,064

4. Amt. per person per day : 30,419 30,386

5. Number of resources : 4 4

6. Per day cost for 4 resources : 1,21,678 1,21545


7. Total number of days extension possible : (3) divided by (6) = 10.8

From where have you got the 4 weeks extension possible ?

I calculated 4 weeks by manually increasing the KT duration from 3 weeks to 7 weeks and found that if i increase my KT upto 7 weeks than it will be maximum utilize and if I take 8 weeks in summary sheet than it shows over budget.

I Hope now I am able to clear my point to you ?

Regards,
Rajender

 

[NARAYANK991]

Hi Rajender ,

Excel cannot rewrite simple arithmetic !

If we have 13,15,064 remaining as the unutilized amount , and the total cost per day for 4 persons is 1,21,545 , then surely the unutilized amount will last only 13,15,064/1,21,545 days , which works out to 10.8 days

How can you make this amount last for 4 weeks ?

Narayan

 

[NARAYANK991]

Hi Rajender ,

What you are describing cannot be done by formulae ; you are thinking of something on the lines of Solver or Goal Seek ; your estimated cost is not directly proportional to the number of days , since as the number of days exceeds 30 , some of the figures come down , and hence if you directly enter 7 weeks , the amount might suffice , but if you initially enter 4 , then the amount remaining will suffice for only 10.8 days

A formula on its own cannot do this kind of maximization or optimization.

Narayan

 

[Rajender]

Hi Rajender ,

Excel cannot rewrite simple arithmetic !

If we have 13,15,064 remaining as the unutilized amount , and the total cost per day for 4 persons is 1,21,545 , then surely the unutilized amount will last only 13,15,064/1,21,545 days , which works out to 10.8 days

How can you make this amount last for 4 weeks ?

Narayan

Hi Narayan,

If any other workaround or any change in my current dashboard can help to calculate this ?

thanks for your valuable suggestion.

Regards,
Rajender

 

[NARAYANK991]

Hi Rajender ,

What is the maximum number of weeks that is possible ? Or is it not possible to put such a limit ?

If a limit is possible , then what can be done is have a Calculations worksheet , where given a budget , we can have the full working for all values of weeks , starting from the maximum , and working downwards. The point at which the estimated cost comes below the given budget is the optimal duration.

Narayan

 

[Rajender]

Hi Rajender ,

What is the maximum number of weeks that is possible ? Or is it not possible to put such a limit ?

If a limit is possible , then what can be done is have a Calculations worksheet , where given a budget , we can have the full working for all values of weeks , starting from the maximum , and working downwards. The point at which the estimated cost comes below the given budget is the optimal duration.

Narayan

Hi Narayan,

Maximum weeks as per current data set I have is 52 weeks.

Regards,
Rajender