• 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.

Change % Value to get Price

T

Thomas Kuriakose

Active Member
Dear Sirs,

We have a sheet where there are some costs defined and profit calculated based on the costs.

The selling price is a manual entry. We need to vary the profit percentage and the selling price to arrive at different options for all parameters.

Kindly find attached the sheet. I tried Goal seek, can this be used for a range of cells as now it is working for one data set only.

We need to change all values with one entry.

Kindly provide a solution for this.

Thanks,

with regards,
thomas
 

Attachments

  • Goal Seek.xlsx
    10.4 KB · Views: 10
One possible ...
You can change 'yellow' min% and max% values
Check [ min ], [ max ], [ average ]-buttons
 

Attachments

  • Goal Seek.xlsm
    24.8 KB · Views: 6
I'm working on this but just to query one thing:
Using column B values, your 37% represents the percentage of the selling price that is your profit.
I'm just wondering whether that's really what you want. Normally, profit is expressed as a percentage of cost, so if it costs you 100 to make something and you sell it for 110 your profit is 10%, (110-100) being 10% of 100.
Using the same profit calculation on your column B values of 12600 cost and 20000 selling price your profit is nearer 59%.

So really, I'm asking if you want the percentage you see in row 4 to be
a) What percentage of the selling price is profit (as it is now),
or
b) What percentage of the cost is profit (as I'd expect it to be).
 
Dear Sirs,

Thank you so much for you kind help on this.

We need the solution as it is now.

Basically, the cost is calculated based on a combination of different materials and on arriving at the cost, the user needs to arrive at the selling price with respect to he percentage profit.

On arriving at the nearest selling price, the user needs to adjust the selling price to arrive at a targeted percentage profit and hence the problem on manually changing the selling price each time to see what would be the percentage profit.

Currently we are trying to check, the values, for 10%, 11% 12%, 13%, 14% and so on until 30% and this is being done manually by changing the selling price.

Can this be automated.

Thank you so much once again,

with regards,
thomas
 
@vletm, Respected Sir, Thank you so much for the wonderful solver you have provided. It is simply amazing and you are the best.

I replied to ensure, I had explained my problem correctly.

I would like to know what formulas are used in your code.

Kindly also let me know whether we can change this to a minimum value from 5% to 30% and the number of columns greater than J column, this is a sample file, the original file has more than 175 columns with the same result expected.

Kindly also let me know whether we can use another option of fixing the profit% and calculate the selling price in another sheet.

Thank you so much once again.

with regards,
thomas
 
I did that sample almost as You told,
by setting values to SP to get C% value between 10%-30%.
Now, with this version, You can fix 'yellow' 10% & 30% values as You want... (but, please lower value to cell [A6] and higher value to cell [A7]) and press [Solve]-button.
I can make more columns to solve and also many more options to use, no problem. For example: P cost can have 5+ parameters, P1 Cost can have min%,set%,max% and so on ...
I'll try to do something ... today... and
if You have more ideas and so on, please let me know ASAP.
... and about 'formulas', I used >=, = , <=, + and / (*).
 
@vletm, Respected Sir, Thank you so much for this information and explanantion

Sir, we need the following -

a) Can we get the C percentage in row B4: infinity same as the percentage entered cell A7 to calculate the SP and other values.
b) As requested earlier and suggested by you, can we have options as per below -
I) Fix SP and vary other values
ii) Fix C% and vary other values
iii) Fix P Cost, P1 Cost and vary other values

I my be asking too much, but this is to get the desired values on trying various options. Do correct me if this will lead to changes in the formula as per original

Thank you so much once again.

with regards,
thomas
 
It's better to ask ... so far I have this kind of vision ...
You could name 'P Cost 1 ... P Cost 6' to something better ...
TK.png
You could solve somehow with one parameter (min-max) ...
I can make those 270 columns as You wished ...
You could see only 'important rows' ( not all )
Maybe some kind of chart should to do too? ...
But, soon I'll do walkaround and think ... think
Ideas?
 
Respected Sir,

Thank you very much for this idea and .

For the columns, I would suggest a button to trigger an input box with question enter number of columns and then based on the entered columns the number of columns get created.

For the C% , we need the value to be equal to the min or max value entered without altering the c% computation with respect to PC cost and Selling price.

Thank you so much,

with regards,
thomas
 
Oh! Really?
When/how do You set P Cost values?
You have to choose values (not write in formula).
After that, You can hide ... like this.
Did I miss something?
... testing with FIXed C% (FIXed C Value is possible too)
Compare with previous photo.
TK.png
... coming more later
 
I don't think you need to use solver or iterate through a bunch of values to find the right answer; I think it's just arithmetic. Attached is a variation of vletm's workbook. In cell A7:A9 there's a spinner which adjusts the percentage in A8, play with that to see the results along that row. For confirmation, use vletm's buttons (plus one) to move that row's values into row 6.
 

Attachments

  • chandoo26056Goal Seek-1.xlsm
    23.2 KB · Views: 8
@p45cal, Spinner is Okay for one value - step by step - max and min values.
There are many more values to change or find, depends what to want to solve.
I would take many clicks to find values with spinner.
 
vletm said:
@p45cal, Spinner is Okay for one value - step by step - max and min values.
There are many more values to change or find, depends what to want to solve.
I would take many clicks to find values with spinner.
Click to expand...
The spinner was just a toy.
The point I was making was that there is no need to seek a goal by running through thousands of values; straightforward arithmetic can give you any of the values.
 
@vletm, Respected Sir,

This is looking very good, just one check, have you inserted the number of columns input box to populate the columns as required.

Thank you so much,

with regards,
thomas
 
Next version to check ... to modify ...
There are 'few rules' to follow ... like:
1) You have to mark values 'under' P Cost, check [x]
2) 2nd row is 'Title row', if no title then that column will hide
3) 'org SP' has to save with [save org]-button
4) col buttons resets it's rows preset value
5) [C]col buttons uses own [D]col value after press
... and so on
Something still missing, I know!
Ideas?
 

Attachments

  • Goal Seek.xlsm
    70.1 KB · Views: 5
@vletm, Respected Sir,
Thank you very much for the file provided.

I need help to use this file.

(a) Please let me know whether we have to input the P Cost for all columns or only in cell D4 and this will populate the values for other columns.
(b) For Rows 32 to 35 the P Cost is not there, is this the summary of the inputs from above entries.

I need a guide to use this. Sorry for asking this.

Thank you so much,

with regards,
thomas
 
Good!
I updated the file with two sample text boxes (You'll delete those later).
Please, read text boxes and ...
a) ... and remember to press [B4] [x]-button to count P Cost's
b) yes, I can add P Cost too ... but that area is like only short time use ... now.
This isn't ready, I know ...
Test, ask and You'll make this work better for You.
 

Attachments

  • Goal Seek.xlsm
    70.8 KB · Views: 2
... continue ... I found something missing ...
TK4.png
I add to top of sheet [Chart A] and [Chart B] 'ON/OFF'- buttons.
You can select wanted data with [∞]-buttons; Column[A].
Both charts has own selections.
 

Attachments

  • Goal Seek.xlsm
    80.2 KB · Views: 2
@vletm, Respected Sir,

Thank you so much for all the time and your valuable inputs in getting this work. This is amazing.

On last query;

kindly let me know the results of activating the highlighted button and the details of the table clipped below.

Thank you so much once again,

with regards,
thomas
 

Attachments

  • 2015-10-18_19-54-30.png
    2015-10-18_19-54-30.png
    29.9 KB · Views: 2
@Thomas Kuriakose
I've newer version. I've updated the file after I sent #22 Reply.
There were at least few 'mistakes' that I noticed.
About Your query ... I made an answer to file, right side of that area.
There are basic part of formulas in right order.
If You want/need more details, please ask.
... this could be better, I ideas, but
I just should need to know 'How would You use this?'
Okay?
 

Attachments

  • Goal Seek.xlsm
    89.4 KB · Views: 3
You must log in or register to reply here.
Back
Top