the non-numeric brain and Excel...
May 2, 2017 5:41 PM Subscribe
I do something for a fee per square metre, currently $4.00/m². I have a minimum charge of $1600.00 which accounts for areas < 400m²; for areas from 400m² to 1200m² I charge a flat $4/m². Beyond 1200m² jobs (usually ) get less and less complex as area increases; so the line is some kind of curve.
I would like to be able to enter an area in Excel (win10 2016 version) and get a cost back for any area from 1001 to 50,000.
Areas from 400 to 1000 are a flat rate of $4.00/unit, so simple so far - Incidentally when I divide this fee by my hourly rate (currently $130.00), the hours worked is usually highly accurate.
I know that for areas above approx. 1000 that work becomes less and less time-consuming as the area increases - complexity falls with area with this work. The largest area is about 50,000 units which would be say $25,000.00. - I have no idea how to do get Excel to calculate the amount at any point on the slope. I also don't know what this type of curve is called - that would be useful too.
I would like to be able to enter and get a cost back for any area from 1001 to 50,000. Also job complexity varies and I'd like to enter a factor to account for that - . If it was possible to integrate hourly rate into the calculation that would be great.
I love numbers but have never been able to learn much about them, dysnumera?
Should I put this up on mefi_jobs?
Areas from 400 to 1000 are a flat rate of $4.00/unit, so simple so far - Incidentally when I divide this fee by my hourly rate (currently $130.00), the hours worked is usually highly accurate.
I know that for areas above approx. 1000 that work becomes less and less time-consuming as the area increases - complexity falls with area with this work. The largest area is about 50,000 units which would be say $25,000.00. - I have no idea how to do get Excel to calculate the amount at any point on the slope. I also don't know what this type of curve is called - that would be useful too.
I would like to be able to enter and get a cost back for any area from 1001 to 50,000. Also job complexity varies and I'd like to enter a factor to account for that - . If it was possible to integrate hourly rate into the calculation that would be great.
I love numbers but have never been able to learn much about them, dysnumera?
Should I put this up on mefi_jobs?
I do pricing in totally different field, but I would approach this with a tiered pricing structure. The important thing should be that the customer should earn their way through the tiers.
I.e. if first 1,200m² are $4.00/m² and the pricing is for 1,200m²+ is $3.50/m², then the total price for 1,500 m² job would be 1,200 x $4,00 + 300 x $3,50 = $5,850 (not 1,500 x $3,50 = $5,250).
Obviously, you never need to show the actual tiers to the customer when quoting jobs. However, it is good to have this concept, because otherwise it would be easy to quote 1,200m² job at $4,800 and 1,201m² job at $4203.50.
Here's a very simple spreadsheet for playing around with pricing tiers. For instance in your example the 50,000 unit pricing is at 1/8 of your base rate; are you sure that you are metering your overhead, etc. correctly.
posted by zeikka at 6:30 PM on May 2, 2017
I.e. if first 1,200m² are $4.00/m² and the pricing is for 1,200m²+ is $3.50/m², then the total price for 1,500 m² job would be 1,200 x $4,00 + 300 x $3,50 = $5,850 (not 1,500 x $3,50 = $5,250).
Obviously, you never need to show the actual tiers to the customer when quoting jobs. However, it is good to have this concept, because otherwise it would be easy to quote 1,200m² job at $4,800 and 1,201m² job at $4203.50.
Here's a very simple spreadsheet for playing around with pricing tiers. For instance in your example the 50,000 unit pricing is at 1/8 of your base rate; are you sure that you are metering your overhead, etc. correctly.
posted by zeikka at 6:30 PM on May 2, 2017
I think this can be linear. You have two known x,y points (1000,4000) and (50000,25000). Find the slope (25000-4000)/(50000-1000) and plug in either pair. So, in Excel B1, =A1*((25000-4000)/(50000-1000))+(4000-((25000-4000)/(50000-1000))*1000) -- where A1 is your x, your square meter input.
posted by glibhamdreck at 6:32 PM on May 2, 2017
posted by glibhamdreck at 6:32 PM on May 2, 2017
dness2's logarithmic curve is obviously the most mathematically correct answer. And I have been trying to quote prices using something similar to that, but even in high-tech that doesn't seem to fly. So, in my day-to-day I have to deal with pricing tiers.
I can only dream.
posted by zeikka at 6:32 PM on May 2, 2017
I can only dream.
posted by zeikka at 6:32 PM on May 2, 2017
Can you provide some more example points? As it stands, you only have two points, so the shape of the curve between them can only be guessed at. Could you estimate the cost at 5000 m2, 10 000 m2 and 25 000 m2?
posted by ssg at 8:04 PM on May 2, 2017
posted by ssg at 8:04 PM on May 2, 2017
Response by poster: Thanks all so far; your answers led me to this which gives me a recipe for Excel and R. A little more technical than what I need but I can start working with the Excel bit.
ssg; 5000 m² would be about $4500, the 50,000th m² would be about 00.20 to 00.09, I'm still thinking of a middle figure.
posted by unearthed at 8:19 PM on May 2, 2017
ssg; 5000 m² would be about $4500, the 50,000th m² would be about 00.20 to 00.09, I'm still thinking of a middle figure.
posted by unearthed at 8:19 PM on May 2, 2017
Best answer: This was fun. I made you this sheet. You'll have to make a copy to your own google sheet or download to excel to play with it.
I made you two models: One is piecewise linear, meaning there's a linear rate (that you specify) up to a point, then a new rate (calculated) based on how much you charge for a large project.
The other model is the same except it is linear in the first regime then logarithmic in the second regime. It is designed so that the log model matches in price and rate where the models meet. This model suggests you are slightly overcharging for the 50000 sq m project (it gives $22,700 instead of $25000). This model is more smooth in the sense that the rate doesn't change so dramatically right at the threshold (right now, 1200 sq m).
I hope this helps!
posted by secretseasons at 6:40 AM on May 3, 2017 [1 favorite]
I made you two models: One is piecewise linear, meaning there's a linear rate (that you specify) up to a point, then a new rate (calculated) based on how much you charge for a large project.
The other model is the same except it is linear in the first regime then logarithmic in the second regime. It is designed so that the log model matches in price and rate where the models meet. This model suggests you are slightly overcharging for the 50000 sq m project (it gives $22,700 instead of $25000). This model is more smooth in the sense that the rate doesn't change so dramatically right at the threshold (right now, 1200 sq m).
I hope this helps!
posted by secretseasons at 6:40 AM on May 3, 2017 [1 favorite]
5000 m² would be about $4500
Is this a typo? According to your question, 1200 m² would cost $4800, so presumably 5000 m² would be more than that.
posted by ssg at 4:52 PM on May 4, 2017
Is this a typo? According to your question, 1200 m² would cost $4800, so presumably 5000 m² would be more than that.
posted by ssg at 4:52 PM on May 4, 2017
Response by poster: Yes, that was a typo, should've been $6000ish
posted by unearthed at 7:46 PM on May 4, 2017
posted by unearthed at 7:46 PM on May 4, 2017
Response by poster: I'm coming back a bit late on this.
To ssg: for areas larger than 1200m² the complexity nearly always drops off significantly.
To secretseasons - thanks very much, that makes a big difference to how I will prices things from now on.
Whatever I use I only use it as a guide; I like to have a semi-rational basis for starting to develop a price, and a mechanism based around diminishing price (to reflect a reduction in complexity) as area rises seems to reflect my experience.
posted by unearthed at 9:30 PM on August 7, 2017
To ssg: for areas larger than 1200m² the complexity nearly always drops off significantly.
To secretseasons - thanks very much, that makes a big difference to how I will prices things from now on.
Whatever I use I only use it as a guide; I like to have a semi-rational basis for starting to develop a price, and a mechanism based around diminishing price (to reflect a reduction in complexity) as area rises seems to reflect my experience.
posted by unearthed at 9:30 PM on August 7, 2017
« Older Should we change realtors? How do we sell this... | Practical ways to test whether I'd enjoy working... Newer »
This thread is closed to new comments.
At 1001 units, the cost is $4.00/unit. At 50,000 units, the cost is $.50/unit. So that's your starting and end points.
I think you are saying that the line between 4.00 and .50 is not a steady line, but it drops slowly at first and quickly at the end. Or that at MAX, the price per unit is .50, and at MIN, the price per unit is 4.00. Let's flip the units so they are scaled so X=50000/(Num Units) and Y will look like some form of ln(X), thus you can use a logarithmic curve generator with the two points (1, .50) and (50, 4.00) to generate a curve that would work.
I found a stackexchange example that should be useful from there https://math.stackexchange.com/questions/716152/graphing-given-two-points-on-a-graph-find-the-logarithmic-function-that-passes. See the 2nd one especially.
I hope this helps (and works). You gave my brain a workout!
posted by dness2 at 6:28 PM on May 2, 2017 [1 favorite]