Maximum profit level

**COGICPENNY** · 06-23-2010, 06:30 PM

Hi,

I have an Excel problem that I've been working on for the past 2 hour without success. I can't seem to attach the spreadsheet for reference, but if you email me I can send it directly to you: pennylopez@msn.com

Goal: Need data in Column C for each row compared against "Profit Margins" (Colums J through Y). Evaluate the following using the data in Columns J-Y "Profit Margins":

In Column Z "TD Max Bid Price": What is the MAXIMUM price we can charge WITHOUT exceeding the "SC Maximum Bill Rate" (Column C).
If the maximum (or minimum) is OVER the "SC Maximum Bill Rate" (Column C), write the words "OVERBID"
In Column AA "Minimum Profit Over SC Max Bill Rate": "Is the MINIMUM values for Columns J-Y "Profit Margins" over the "SC Maximum Bill Rate" (Column C)?, if so write "Yes", if no write "No".

Thank you

Penny

**shg** · 06-23-2010, 07:01 PM

Approached slightly differently, you don't need cols J through Y at all.

Label col Z "Max Margin", and in Z3 and copy down,

=C3/I3 - 1

... and format as Percent. That tells you how much markup you can add to col I to equal col C.

Then label col AA "Markup", and in AA3 and copy down,

=IF(Z3<10%, "Overbid", MIN(Z3, 25%))

Which declines to bid with less that 10% markup, and clamps the markup to a max of 25% (if that's what you want to do).

EDIT: Col I doesn't make sense to me. Col H is "W-2 Payroll Costs", and is a dollar amount ($1.20 throughout). Col I is col G (Hourly rate) * col H, which makes no sense if col H is a dollar amount. Know what I mean?

**COGICPENNY** · 06-23-2010, 10:59 PM

Hi,

Thanks for your help, however, I need the information displayed as I am requesting it so that we can easily change the profit margin levels as needed. Also, to clarify the cells not in question (H and I), I should have been a decimal (1.20) which reflects the 20% mark-up of payroll.

If you could please advise on how to view the data as I specified so that we can glance across to see how various profit margins affect our bid pricing.

Thanks for everything,
Penny

**shg** · 06-24-2010, 12:23 AM

Sorry to frustrate your approach, but please see attached.

**COGICPENNY** · 06-24-2010, 12:46 AM

Sir,

I thank you for assisting me, especially since you're doing it for free

However, I need the data exactly as I explained it, nothing changed, just an analysis of simply "at which of the various profit levels going across from J to Y can we charge without going over the SC Maximum Bill Rate in Column C. That's question #1.

Question #2 asks "are there rows of data (J-Y) where the minimum price in the profit margins EXCEED the SC Maximum Bill Rate in Column C. Having an answer to this question will help us to adjust our profit margins where we might go even lower than the profit ranges we have or even adjust it on the payroll cost multiplier (currently at 1.20) bringing down the total payroll cost which is calculated prior to adding our profit markup.

Question #3: Just puts words in the column to the questions so that when I do a data filter I can quickly see which rows need price adjustments.

I hope this clarifies things, and any help you give is GREATLY appreciated.