• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need a formula for budgeting.

Hi,
Attached is my budget allocation sheet. I am facing a problem in deriving a formula for getting the utilized amount.

There are 4 worksheets :
Costs --> Here my Associate's list and their designations are mentioned, under columns A to C. As well as designation wise per day cost is also mentioned under columns E to Q.

Budget_Master --> Here my WON (work order number) is mentioned along with each WON no. budget. Col. B is having WON No. and Col. F is having a WON budget.

Allocations --> Here I am going to provide allocations to my team under WONs. (Here, under 1 WON multiple associates can be allocated with from and to dates.) Total no. of allocation days are mentioned under Col. G.

The last sheet is Balance, where I need your help in deriving the Utilized amount under Col. D.

Under the Utilized column I want a formula that can derive an amount considering the following :
1. It should be the utilization of respective WON No. (mentioned under col. B) by considering
a. the associates allocated
b. their no. of days of allocation from sheet Allocation.
c. multiplying with the cost of respective designation, mentioned in Costs sheet Under column E2 : Q3

The formula should be considering :
1. Excel should check the name (Col. C) under sheet Allocations and check their designation (Col. D).
2. Then, excel should check the cost of that designation in the Costs worksheet from col. E2 to Q3 and find the cost (Row 3) of that particular designation cost.
3. Then, excel should multiply the identified cost with No. of days (Worksheet Allocation --> Col. G)
4. Then, excel should sum these all values for each respective WON nos. and show that summed figure under column D of the Balance worksheet.

I know it is very complex. But please advise.
 

Attachments

  • Allocations.xlsx
    18.4 KB · Views: 7
A couple of suggestions
Our answerers are looking to "help" more often than they are looking to "do an entire project for you for free".
Jump into this project yourself, when you get stuck on ONE specific hurdle, post that ONE item as a forum question.
Show us what you've tried in an attached sample workbook and how it's not working for you. When you're doing the work yourself, just writing out a complete example question in a forum often points you to solution before you even finish posting.
 
Hi vaidmohammed,

As a general tip. Combining data from multiple tables can turn out to be easier when you learn the basics of Power Query and maybe even Power Pivot (if you are up for it).
At least with PQ you can use inner/left/right outer/anti join... And it mashes up the data by a sequence of simple mouse clicks.

Do, fail, learn. As the great Yoda says: Do or do not, there is no try. And The greatest teacher, failure is.
 
Back
Top