Please help novice user figure out this formula.

[irish2171]

Novice user here, trying to cabbage together a challenging (for me) formula.  I know you advance users will immediately know how to do it, and it's probably something trivial or basic I'm missing.

Trying to create a formula that will calculate Total Profitability Impact.  I already know what the answer is, but I'm looking for the formula to build it in my spreadsheet.

So, direct costs (DC) are $16,205; indirect cost (IDC) are based on table below.

I'm using an IFS statement to do this, so my IDC equals $17,825.50.  DC+IDC= Total Cost (TC) of $34,030.50.  Profit Margin (PM) is 5%.  So, TC/PM = Profitability Impact of $680,610.

Here is the formula I am currently using, where AD5 is direct cost (DC) =AD5+IFS(AD5>0<3000,AD5*4.5,AD5=3000<5000,AD5*1.6,AD5=5000<10000,AD5*1.2,AD5>10000,AD5*1.1)

As you probably already know, this formula as written = TC of $34,030.  The problem is I've reached the end of my knowledge and Google cheats to figure out how and/or what to do to my formula to add the 5% PM to give me the final answer of Profitability Impact to equal $680,610.  I've moved the formula 10k different ways, and still cannot get it to work.  The formula above is on cell AE5 on the attached spreadsheet.  I'm missing some small piece.  Any help you can provide would be appreciated.

[#2 · Accepted solution]

@irish2171 

Hi

I've attached an example using the MATCH and INDEX functions. I've added a second sheet to try to explain how the MATCH functions works. Essentially , MATCH searches the first column to identify the correct row depending on the value and the INDEX go to the second column and retrieves the correct multiplier. Once this is done the arithmetic should be fairly simple. See yellow highlighted cells.

I've calculated the Prof Imp using the 5% - note i have 'hard coded' this in the example. this is bad practice. You should keep the 5% in a separate cell and refer to that cell in the calculation. That way if the percentage changes in the future to say 6% you just need to change one cell and not all the cells you had 5% in!

Hope this helps.

Peter

[#3]

@irish2171 

Another variant for the collection

=AD6*(1+LOOKUP(AD6,ConvTable[Direct Cost],ConvTable[Cost Multiplier]))

[#4]

Thank you, I will try this.

[#5]

@irish2171 

if this works for you please mark this as completed solution.

thanks!

Peter

[#6]

Complete.  @peteryac60