+ Reply to Thread
Results 1 to 35 of 35

Profit Calculator

  1. #1
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Smile Profit Calculator

    Hello,

    I uploaded a file I did to calculate the profit, it's working fine, but I'm missing the most important thing that will help me to make this more automatically when adding a lot of SKU's. I need to reverse profit. In the file you can see 2 spreadsheets. In every category I sell I pay different fees. I have inserted comments on every sell to indicate what is. For every category the fees are correct, I just want to insert in the "profit I want" cell the amount I want have after all the fees.

    Thanks!
    Last edited by dandi10; 07-25-2010 at 02:58 AM.

  2. #2
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    Hi dandi10;

    I'm assuming you want it to calculate what "Buy It Now" price to give you the desired profit.

    Here's a file that calculates the eBay price. Just enter the desired profit under "Profit I want" and it automatically changes the "Buy It Now" price to give it to you.

    I left it to you to adapt it to Amazon.
    Attached Files Attached Files
    Foxguy

    Remember to mark your questions [Solved] and rate the answer(s)
    Forum Rules are Here

  3. #3
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Re: Profit Calculator

    Hi foxguy,

    I just love you!!!, thank you thank you .

    I have no idea how you did that. I just need please a small help more.

    1. In the last category on eBay (motors) the amount that is changing is the column AD of the shipping and not the start price.

    2. It'll be great if you can do this. We have a many items and we change the price every time. When I copy the formula down I need to insert the "profit I want" again and press enter. Can that be automatically? I mean, just to copy the formula down?.

    3. I really don't know how to do that for Amazon, I'll appreciate your help with it too .

    4. Last small thing. When I open the file I see a tab of Security warning and enable macro and Excel asks me to enable it. How can I leave it enable all the time?

    Thanks a lot!!!!

    Daniel.

  4. #4
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    Hi dandi10;
    1. In the last category on eBay (motors) the amount that is changing is the column AD of the shipping and not the start price.

    3. I really don't know how to do that for Amazon, I'll appreciate your help with it too .
    The macro changes the column 3 columns to the left of "Profit I want". There are 3 ways to fix this and set up Amazon.
    1) leave it the way it is now, and you will have to make sure that "Price" is 3 columns to the left.
    2) the macro can look for "Buy It Now" and fill in that column.
    3) you put something in the row above the "Buy It Now" column for the macro to find (it can be hidden)
    Amazon can be set up to
    1) change the column 6 columns to the left
    2) look for "Price"
    3) look for something you place in the row above "Price"
    Which way would you like it?

    I would recommend the # 3 approach. I would put another sheet in the workbook where you can tell the macro what columns you want to check and change. That sheet can be hidden, and a button can be put on the eBay and Amazon sheets that UnHides it.
    That way, you can change the design of the eBay and Amazon sheets when ever you want. You would just have to go to the "Design" sheet and tell the macro what columns to look for.

    4. Last small thing. When I open the file I see a tab of Security warning and enable macro and Excel asks me to enable it. How can I leave it enable all the time?
    I don't use Excel 2007, so I'm not sure of how to do this, but this is what help says:
    Change macro security settings
    You can change macro security settings in the Trust Center, unless a system administrator in your organization has changed the default settings to prevent you from changing the settings.

    On the Developer tab, in the Code group, click Macro Security.
    Tip If the Developer tab is not displayed, click the Microsoft Office Button , click Excel Options, and then in the Popular category, under Top options for working with Excel, click Show Developer tab in the Ribbon.

    In the Macro Settings category, under Macro Settings, click the option that you want.
    Note Any changes that you make in the Macro Settings category in Excel apply only to Excel and do not affect any other Microsoft Office program.
    Tip You can also access the Trust Center in the Excel Options dialog box. Click the Microsoft Office Button , and then click Excel Options. In the Trust Center category, click Trust Center Settings, and then click the Macro Settings category.
    Note: this is not recommended: This is what help says:
    Enable all macros (not recommended, potentially dangerous code can run) Click this option to allow all macros to run. Using this setting makes your computer vulnerable to potentially malicious code and is not recommended.
    I don't think there is any way to make Excel trust the macros in this file, but not any other files. You might start a new thread to ask every one if there is such a way.
    Last edited by foxguy; 07-14-2010 at 01:16 PM.

  5. #5
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Re: Profit Calculator

    Hi Foxguy,

    Thanks again. I have fixed problem #1, many thanks. I'll check later problem #4, it's not a big deal, I can live with it.

    I also think that # 3 approach will be the best. If you recommend that we'll go for it and I'll be waiting for the new file from your side.

    I really appreciate your help!!!

    Thanks.

    Daniel.

  6. #6
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    Hi Daniel;
    Just wanted to let you know I haven't forgotten you.

    We had a power failure here for over 24 hours. I'm catching up on everything I didn't get done yesterday, so it will be a while, but hopefully today.

  7. #7
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Re: Profit Calculator

    Thanks, you're the best. I'll wait, don't worry.

    By the way, just a question. Do you have also knowledge programming for websites, html, css?

  8. #8
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    It has been about a year since I designed a really simple website in html. Don't even know what css is. I have programmed in Perl in the past, but I would have to refresh myself on it.
    Last edited by shg; 07-16-2010 at 06:48 PM. Reason: deleted spurious quote

  9. #9
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Re: Profit Calculator

    OK, thanks. I'm just building a website so if you know someone that can you recommend will be great.

  10. #10
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    What do you need to web site to do? How complicated is the programming?
    Last edited by shg; 07-16-2010 at 07:02 PM. Reason: deleted spurious quote

  11. #11
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: Profit Calculator

    Foxguy, please use the REPLY button and not the QUOTE button.
    Entia non sunt multiplicanda sine necessitate

  12. #12
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    Hi shg;
    How can you tell that I'm clicking Quote instead of Reply? I have the forum set up so that it puts the most recent message at the top, and Quote takes me right into Go Advanced, the reply button takes me to the bottom 1st, so Quote takes me directly where I want. But I always remove the quote before starting my message, so how can you tell?

  13. #13
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    Scratch this. Check back in 5 minutes
    Last edited by foxguy; 07-16-2010 at 09:55 PM.

  14. #14
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    Hi dandi10;

    Ok here's the file

    It looks for these words above the data
    sProfit_Wanted_Hdg = "Profit Wanted"
    sPrice_Hdg = "Price"
    sProfit_Hdg = "Profit"
    The same words work on eBay or Amazon.

    Note:
    "Price" has to be to the left of "Profit Wanted"
    "Profit" has to be to the right of "Profit Wanted"
    The row with these words on can be hidden.

    You can change the words if you want. Just go into Function Sell_Price() and change them.
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Re: Profit Calculator

    Hi Foxguy,

    Regarding the programming of the website. I have a template and what I need to do is to separate the template into parts. This template is all together and to install in our channeladvisor platform I need to separate into parts like header, footer, sidebar, etc. For someone who knows ccs and html it looks simple..

  16. #16
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Re: Profit Calculator

    Hi Foxguy,

    Many many thanks again for your help. We only are missing the point to do that easily in bulk. We have and we would like to copy from another file the sku's cost, etc and just to scroll down the formula and enter the "profit i want" just once. When I copy the formula down I need to insert the "profit I want" again and press enter. Do you think it could be possible?

    Thanks!!!!

  17. #17
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    Quote Originally Posted by dandi10 View Post
    We only are missing the point to do that easily in bulk. We have and we would like to copy from another file the sku's cost, etc and just to scroll down the formula and enter the "profit i want" just once. When I copy the formula down I need to insert the "profit I want" again and press enter. Do you think it could be possible?
    Are you saying that you want to enter a formula into a "Profit I Want" cell, and copy the formula down the column, and have the macro do the "Price" calculation for the whole column?

    Or do you want a macro that will go in and calculate all "Prices" for the entire sheet?

    I just looked at the subs, and it will take me a little while to adapt them to these new conditions. I'm leaving right now, but I should be back in a few hours, and hopefully I can do it then.

  18. #18
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Re: Profit Calculator

    I would like to do the following. I copy the sku's and cost prices from another file. Then I sort the cost price column from smaller to bigger. Let's say for all the range of cost prices between $1 and $20 I want a profit of $3, from $21 to $100 a profit of $5. Then when I copy the formula down including the "profit I want" for each range of cost prices and it'll give the buy it now for each one automatically. Now the buy it now changes only if I write manually the profit i want and press "enter".

    I hope we understand each other , let me know if I expressed myself right.

    Thanks!

  19. #19
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    Hi dandi10;

    Ok, this file will recalculate the Price needed EVERY time you manually change a cell in the Profit Wanted column. So if you enter a formula in 1 cell in the Profit Wanted column, it will recalculate the Price needed for that row. If you then copy the formula down the column, it will recalculate the Price needed for every row in the range that you copied into.

    Note: If you change a cell under "Cost", "Ship", etc. it will not calculate the Price needed. Only if you change a cell in the Profit Wanted column.
    Attached Files Attached Files

  20. #20
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Re: Profit Calculator

    Works!!!!

    We're almost done, I hope you can fix this.

    1. When I try to insert an entire column excel stops responding. Do you know why?
    2. When I copy down the formula it calculate the buy it now, it's perfect, but I still need to insert the "profit i want" only in the first row. Not a big deal but I may forget to do that, if you can fix that will be just great. After you copy down the formula, just write again the same value you had in the first row and you'll see it changes.
    3. Also not a big deal. When you delete some values, foe example deleting a row it says" reference is not valid. "cancel" to step thought code.

    Thank you thank you 10000 times .

  21. #21
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    Hi dandi10;

    Well you found 2 bugs in my macros. I didn't think about adding/deleting columns/rows. I'll work on them.
    1. When I try to insert an entire column excel stops responding. Do you know why?
    I don't think it has stopped responding. I believe it's just checking every cell to see if it's in a Profit Wanted column, and it's checking every cell down to row 1048576, and just taking a long time.
    3. Also not a big deal. When you delete some values, foe example deleting a row it says" reference is not valid. "cancel" to step thought code.
    I didn't anticipate cells not existing when the macro ran.

    2. When I copy down the formula it calculate the buy it now, it's perfect, but I still need to insert the "profit i want" only in the first row. Not a big deal but I may forget to do that, if you can fix that will be just great. After you copy down the formula, just write again the same value you had in the first row and you'll see it changes.
    I don't understand this one. Can you tell me exactly what you're doing?
    Example:
    In G2, I enter "=C2*.15+1.35"
    I copy G2 into G2:G70.
    then what is wrong?

  22. #22
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    Hi dandi10;
    Ok change the Calc_Range() sub in Module 1
    You can just add the section between the '------------------------------- or replace the entire sub
    Please Login or Register  to view this content.

  23. #23
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Re: Profit Calculator

    Dear Foxguy,

    Many thanks for your reply. Sorry I didn't express myself clear on question 2. In G2 I don;t insert any formula, just an amount, let's say $4. First I copy from an another excel the sku's and cost, then write the profit i want. I think I know where the problem is. If you modify any cell besides the "profit i want" like cost, ship price, the buy it now doesn't change automatically, you need to insert again the "profit i want" or press enter and then it's work.

    Regarding points 1-3 I appreciate again your help and hope you can fix that.

    I was trying to find where to change the code you mentioned but I have no idea about macros, code. Can you give me another hand with this? I'm sure that for you it's a piece of cake .

    Thanks!!!!!

  24. #24
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    Hi dandi10;

    I assumed that you copied my macros into your workbook. That's why I just posted the corrections here rather than upload another workbook.

    I believe you are saying that you want the "Price" to update whenever you change ANY cell not just the "Profit Wanted" cell.

    This will put more restrictions on you:
    The "Profit" column MUST BE the last column in the section.

    You must go into the macro "Definitions" and change info there if:
    1) If you change sheet names.
    2) The macro assumes that you have put the words "Price", "Profit Wanted", and "Profit" on Row 4 for eBay and row 2 for Amazon. If these change.
    3) If you change the words that the macro looks for.
    Attached Files Attached Files

  25. #25
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Re: Profit Calculator

    Hi Foxguy,

    Thanks for the new worksheet. Sorry for the delay in my answer, I was testing it. It looks great, now I can insert new columns if needed and the price changes wherever I make a change in any of the costs. I think the only problem now and you fixed in version 2 is that I need to press enter when I make a change, if I copy down the formula it doesn't change. Can this be done in the last version?

    I don't know how to thank you for all your help, but thanks again!!

    Daniel.

  26. #26
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    Hi dandi10;

    I hesitate to allow the macro to calculate prices for more than 1 cell at a time.
    As you saw when you inserted a column, for all intents and purposes Excel locked up. It didn't really lock up, it was just taking a long time to calculate prices for more than 1,000,000 rows.
    Also, as you improve the design of the workbook, you will change the way things look. You will move columns around and other things.
    I can't predict what kind of changes you will make 6 months from now. The more freedom you have to redesign things the more restrictive the macros have to be. Example: Right now you can move columns around as long as "Profit" is the last column in the section. In order to allow you to move columns and put "Profit I Want" as the last column in the section, the macro would have to slow down in order to figure out which "Price" goes with which "Profit"

    That said, I agree that you should be able to copy "Profit Wanted" down several rows, so I will put that in, but it may have a side effect that I didn't anticipate. Like I didn't anticipate inserting a new column in the 1st draft. I will put in a restriction that it will do more than 1 row at a time as long as they are all in the same column. So if you copy more than 1 cell in a row into another row, it will not calculate the profit. That way when you insert a row, it doesn't try to calculate 16,000 prices.

    Boy, that was harder than I thought. But here it is
    Attached Files Attached Files

  27. #27
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Re: Profit Calculator

    Hi Foxguy,

    Yes, it was harder more than thought too. I'm missing the feature to copy down the formula, it works only when I press enter. Are you sure you put it that in?

    Thanks!!!!! We're almost finishing .

  28. #28
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Re: Profit Calculator

    Sorry, another thing I forgot and just checked that. It was fine in the older versions (version 2). I also need to copy from another excel all the data, cost, shipping cost, buy it now and it was calculating alone the existing profit, in that way I knew if I need to modify any existing buy it now or not. I mean, it was telling the existing profit. If you can fix that and the last thing above, will be perfect!!

    Thank you!!!

    Daniel.

  29. #29
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    Hi dandi10;
    I'm missing the feature to copy down the formula, it works only when I press enter. Are you sure you put it that in?
    Sorry, missed something. I was taking a short cut to testing it, and forgot to remove the shortcut.
    This file does it.

    I also need to copy from another excel all the data, cost, shipping cost, buy it now and it was calculating alone the existing profit
    That would be very hard to do. Trying to tell the macro which "Price" column to match with which "Profit I Want" and "Profit" columns would be very hard when you change or copy in more than 1 column. If you're interested I will try and explain why.

    Right now, the best way is like this:
    If you copy in cost, shipping, etc. including "Profit I Want" and "Profit" into rows 5-20. After copying it in, select rows 5-20 in any column (except Profit) Click Ctrl+C and Ctrl+V. This will tell the macro to recalculate "Price" for rows 5-20 in that section.
    If you would like a button (or menu selection) to tell the macro to recalculate "Price" on selected rows, that can be done.
    Attached Files Attached Files

  30. #30
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Re: Profit Calculator

    Excellent, done with the first point , thanks a lot!!!.

    I'm not sure I completely understand what you mean. I see the formula it's working also after the row number 20, not only between 5-20. What I need to is the following. I have existing items that the cost is changing from time to time. So i copy the cost, the shipping cost and the existing buy it now, I don't copy the "profit I want", what I need to know it's if there are items that the profit is negative or low, less what I want to gain, after that I'll sort the data from the lowest value profit to the highest and only then I'll start to insert the "profit I want" where needed. This will help to track hundreds of items. Does it make sense? I try to express myself as good as I can (I'm not so good on this ). If I can tell the excel in that case to only let me know the actual profit either with a button or menu selection (whatever will be easier for you) and then I'll choose if give to it a "profit I want" value in order to change the profit.

    For new items, I just will insert the cost"profit I want" and that is working 100% perfect.

  31. #31
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    I think I misunderstood you in message 28. I thought you wanted it to goal seek the profit when ever you copied in data.

    I think you're saying that you want to goal seek the Profit only when you enter something in Profit Wanted column - otherwise leave it alone.

    Ok this file will NOT calculate "Profit" if you don't change "Profit Wanted" column. It will calculate "Profit" ONLY if you change "Profit Wanted" column. You can copy down "Profit Wanted" column and it will calculate them all.
    Attached Files Attached Files

  32. #32
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Re: Profit Calculator

    I really don't know how to thank you. I have no words. You really really help me. I started to test your final version and it seems to work just great. Thank you, thank you, thank you!

    Feel more than free to contact me anytime .

    Daniel.

  33. #33
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    Remember to mark your questions [Solved] and rate the answer(s)

  34. #34
    Forum Contributor
    Join Date
    03-18-2010
    Location
    FL
    MS-Off Ver
    Excel 2016
    Posts
    111

    Re: Profit Calculator

    How to mark it solved?

  35. #35
    Forum Expert
    Join Date
    03-31-2009
    Location
    Barstow, Ca
    MS-Off Ver
    Excel 2002 & 2007
    Posts
    2,164

    Re: Profit Calculator

    To mark it solved:

    Go to message # 1 in the thread. Click "Edit". Click "Go Advanced". In Title box change to "[Solved]".

    To rate answer, click blue scales in top right corner of my answer.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1