Mark up % question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a worksheet that presently permits entering values for costs of good and services, entering the projected wholesale price (price we would sell to our customer) and then returns the MUP% value.

What I would like to do is conversely be able to enter the desired MUP% and see the required wholesale price?

This is what I have now in cells E4:F17:
FOB $3.95
ADDT'L ACC $0.00
HANGER $0.17
DUTY $-
FREIGHT $0.07
ADDT'L HNGR FREIGHT $0.08
SUB TOTAL $4.27
COMMISSION $-
OVER HEAD $0.11
LDP (W/O ROYALTY) $4.38
ROY $0.83
FINAL $5.20
WHLS $8.25
MUP% 37% =(F16-F15)/F16)

Ideally, I would like to make this so that several instances of the same example can be displayed. That is, what -if it were 22% or 26% or 29%, etc.

I have tried scenarios as an option but the problem with that is that the user doesn't know how to use this. I need something that is in their face, if you know what I mean.

Any help would be most welcome!
 
Marcy, I use "Data Tables" from the Excel menu for these
situations. In the WHLS cell enter the formula shown below.

In the Data Table section enter a reference to the WHLS
cell, then down the left side of the Data Table area,
enter your desired mark ups (as many as you like).

Then run the Data Table feature from the menu, with column
values going into the MUP% cell, press the F9 key to
recalc, and the required WHLS prices will appear.

D E
19 Final $5.20
20 WHLS $8.25 =E19/(1-E21)
21 MUP% 37%


Area below is a "Data Table"
$8.25
22% $6.67
27% $7.12
32% $7.65
37% $8.25

Good Luck

Mike
-----Original Message-----
I have a worksheet that presently permits entering values
Click to expand...
for costs of good and services, entering the projected
wholesale price (price we would sell to our customer) and
then returns the MUP% value.
What I would like to do is conversely be able to enter
Click to expand...
the desired MUP% and see the required wholesale price?
This is what I have now in cells E4:F17:
FOB $3.95
ADDT'L ACC $0.00
HANGER $0.17
DUTY $-
FREIGHT $0.07
ADDT'L HNGR FREIGHT $0.08
SUB TOTAL $4.27
COMMISSION $-
OVER HEAD $0.11
LDP (W/O ROYALTY) $4.38
ROY $0.83
FINAL $5.20
WHLS $8.25
MUP% 37% =(F16-F15)/F16)

Ideally, I would like to make this so that several
Click to expand...
instances of the same example can be displayed. That is,
what -if it were 22% or 26% or 29%, etc.
I have tried scenarios as an option but the problem with
Click to expand...
that is that the user doesn't know how to use this. I need
something that is in their face, if you know what I mean.
 
Marcy,

Data Tables are somewhat confusing, but very powerful

Data tables require the following:

1. A list of values that you want to "substitute" into a
cell. In your case these are the %Mark Ups. These are
placed in a column.

2. A formula in a cell at the top of the next column that
will change based on the substitution described above. (In
your case this would just be: =E20 This will return the
WHLS.

Then hightlite both columns. Make sure to include the
formula also. Then select "Data Table", it will ask
you "column input cell", this means: "into what cell, do
you want to place, one at a time, the values you listed in
the left column?" - In your case this would be the MU%
cell address or E21.

You do not need to worry about the "row input" - this is
used if you want to create a table with 2 variables.

Good luck,

Let me know how it works, or if you need further help.

Mike
-----Original Message-----
Hi, Mike--

Forgive my ignorance on this subject...data tables...never used it before.
Please advise the following using your example:

1. I entered the formula you provided in cell e20 (wholesale $)
2. I entered the data table in cells D24:d27.
3. So, what is the 'ROW INPUT CELL"?
4. What is the "COLUMN INPUT CELL"?

I keep getting an error message that cell reference is
Click to expand...
not valid using E20 (wholesale $) for row input cell.
I think I am confused!
Please bear with me a bit longer..I think this could be
Click to expand...
the answer I was looking for if I can get the concept down.
 
Good to hear it worked.

Two variables work in a similar fashion. The reference
formula goes above the column values, and the other
variables go to the right of formula. The column values go
into the "column input cell" and the row variables go
into the "row input cell".

Again, the formula references a cell that will change when
the column values and row values are placed in their
respective cells.

Good luck. I find lots of applications for Data Tables.

Also - set your calculation to "automatic except tables"
to prevent the entire table from recalculating each time a
cell changes. - Then use F9 to update the table when you
want.

Mike
 
Mike,

Here is my last question on the subject....

In the original post, I outlined the various components that make up the FINAL COST. (E4:F17)

So, how can I use this data table data you so kindly provided to accomodate the following:

F16 = Wholesale price (data table info)

Royalty is in F14 which has this formula: = F16*.01
FINAL COST is in F15 which has this formula: F14+F13

Since F14 is dependent upon the result in F16, how can I write the table or better question, CAN I write the table to reflect the changes in ROYALTY which will then change the FINAL COST and so on?

Gratefully,
 
Marcy, I am not sure I understand. What you are describing
sounds pretty close to a circular reference. - WHLS is
dependent on Final + Royal, but Royal seems to be WHLS
X .01 ?

If you like you can send me a sample sheet showing what
you are describing - "(e-mail address removed)"
-----Original Message-----
Mike,

Here is my last question on the subject....

In the original post, I outlined the various components
Click to expand...
that make up the FINAL COST. (E4:F17)
So, how can I use this data table data you so kindly
Click to expand...
provided to accomodate the following:
F16 = Wholesale price (data table info)

Royalty is in F14 which has this formula: = F16*.01
FINAL COST is in F15 which has this formula: F14+F13

Since F14 is dependent upon the result in F16, how can I
Click to expand...
write the table or better question, CAN I write the table
to reflect the changes in ROYALTY which will then change
the FINAL COST and so on?
 
Back
Top