Pending Price Changes, Want To Share How I Am Working This.

Discussion in 'Shapeways Shops' started by MadBikeSkills, Jan 15, 2019.

  1. MadBikeSkills
    MadBikeSkills Well-Known Member
    We all know about the price structure changes coming on January 31st. I am trying to make it as painless as possible for my shop so I decided I would work on a formula for recalculating all my markups and wind up with pricing that is both acceptable to me as well as following some of Shapeways shop owner suggestions.

    In my particular instance I have a minimum markup I want to set. It simply isn't worth my time if I cannot make a minimum amount even on small items. Next I want to make sure that all my pricing ends with the same fractional currency value since it is a psychological thing. Pleas note I only deal in USD. So I have come up with a way to accomplish both. I am going to show you an example using MS Excel, but I am sure other spreadsheets can do the same thing.

    So the first thing I do is download all the CSV files from Shapeways. It makes it easier if you already have them all in a folder on your PC rather than doing them one at a time. As an example, my shop requires that I download 4 CSV files for the complete inventory. Once I have them downloaded I can proceed to import the first file into Excel. After importing you should notice that column 'L' is the cost Shapeways collects from that particular model in that particular material, it is labeled 'base_price'. Column 'N' is the price you want to charge for your item and it is labeled 'total_price' in the CSV. So here is my example. I never want to make less than $5 USD on any given item, but I want to have at the very least a 35% markup. At the same time Shapeways suggests that your pricing end in .99 for the psychological consumer pricing effect.

    We are going to use two functions in Excel called INT and MAX. INT returns the integer only portion of the value. MAX compares two values and returns only the maximum of the two values. I am doing the formula completely in-cell so that I do not have to copy and paste multiple ranges into the spreadsheet. Row 2 is the first row that has actual model data, so in cell N2 I put in the following formula:

    =MAX((INT((L2+0.5)*1.35)+0.98), (INT(L2+5)+0.98))

    A bit more explanation is in order. the cost found in column 'L' is first increased by 0.50 then multiplied by the cost with the additional markup as a fraction of the cost. In my example above I want the price to be full price + 35% so I multiply my cost by 1.35. Adding the .5 before multiplying insures that when I drop the resulting decimal places I am still approximately +35%. The INT function takes the result and drops the decimal places, to which I add back .98. For example, say Shapeways charges 5.17 for an item, the first part of the equation would give you a price to charge of 7.98, but now I wanted to make at the very least 5.00 so I need a bigger markup for this model, this is where the MAX function comes in. So I take my formula which now looks like this =MAX(7.98, (INT(L2+5)+0.98)) and solve the back half. The back half of the calculation results in a total of 10.98, so now my equation looks like this, =MAX(7.98, 10.98). MAX will return the larger of the two values and will fill in 10.98 into cell N2.

    So you can see how this works on a higher priced item, I will illustrate it again with an item that Shapeways charges 19.49. The first value calculates to 26.98, the second expression calculates to 24.98. Now the equation looks like =MAX(26.98, 24.98) and places 26.98 in the 'total_price' column.

    Once I have copied the formula from cell N2 into all the subsequent cells in column 'N' the spreadsheet will recalculate all the pricing for you. Export the file to CSV then do the next one. Once you have all the files saved with the new pricing, upload them to your shop and automagically all your prices are set.

    I am not trying to say that the formula I provided is the only way to accomplish this, it is just a starting point. You may still have outliers that don't exactly fit your model pricing structure. Please if you have any suggestions on how to improve the formula or how to make it even easier, please feel free to comment.
    EvieL likes this.
  2. EvieL
    EvieL Well-Known Member
    Just wanted to say that as a former daily user of Excel, it makes me so happy to see this post.
  3. MadBikeSkills
    MadBikeSkills Well-Known Member
    Thanks @EvieL, I was dreading having to go through my entire shop and selecting the markup I wanted so I figured I would try this with my very rusty Excel skills.

    If you are not an Excel user, I tried the same formula in Google Docs Spreadsheet and it works as written.

    One thing to note the way I have written this formula, when you add the 0.50 it basically means that anything with a fractional cost 0.49 and under gets rounded down, anything 0.50 and over gets rounded up. You can change the addition if you want a different range, for example adding 0.75 will shift the rounding range down by 0.25.
    Last edited: Jan 17, 2019
  4. MadBikeSkills
    MadBikeSkills Well-Known Member
    I changed my formula a bit to better round up or down, it is now

    =MAX((INT(L2*1.35+0.5)+0.98), (INT(L2+5)+0.98))
    Last edited: Feb 2, 2019
  5. MadBikeSkills
    MadBikeSkills Well-Known Member
    Now complete, since I used a formula in combination with Excel, it was 8 minutes total to change 3200 prices.