Creating a pricing scale

[fjas]

Hi all,

I'm running creative space with the options to hire one or multiple rooms for any length of time. We can have any one space can host a photo shoot, showroom, exhibition or workshop so obviously some events require hosting more people at one time than others, which we do need to introduce a surcharge for.

Currently my pricing is really basic in excel, and I'm just discounting with progressive time taken. I do have a curved line graph so I can visualise how much of a discount I am giving as more time taken. However, it is all quite time consuming, and I don't really want a spreadsheet a mile long! I really need to create something that is formula based or in calculator format so I can make no errors, but also easily update the forumula for the scale as needed.

Is there way to either create a calculator (tick any rooms listed, enter number of hours/days, enter type of event & number of people using the space or type of event) or a formula that is based on a curved line i.e. price for a few hours or a day is $$$, price for a few days is $$, price for a over week is $.

Otherwise is there a online app that does this sort of thing? I really just need a private quote form.

Appreciate all the help!

[dflak]

It would help a lot to attach a sample spreadsheet. Then we need more business rules such as how does the room rental rate vary with the number of occupants? We'll need your data:
Room Name
Room Basic Rate
Surcharge per x number of occupants.
Discount by days rented.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[fjas]

Also, I'm not sure about how to work with the daily rate, as it too should be logical when graphed. So for the moment, I'll await your response as to how to manage that one. The pricing should reflect the hours and days most in demand (3,4,6,8 hours and 1,2,3,5,7,10,12,14 days being the more popular).

[fjas]

I've done some more research on the topic and what I need to do is either create an easily customisable volume discount/pricing scale or curve based pricing (which I can find very little info on). You can see that unless I adjust the categories to specifically end up being less than the next category I will end up with the 5 hour total being more than the 7 hour total. What I need is a somewhat smooth curve (or have a visual reference to that curve when I determine the cumulative hourly or daily pricing).

I have tried =IF() with cell references, however I am struggling when calculating multiple logics. I need to refer to cells not to amounts, and present each price in the matrix. So I'm after a dynamic formula, not one in which I'll have to manually edit each daily rate.

I have left out occupant surcharge & annual increase just for now pending your advice on how that should be applied to the basic room/day formula. I'm a bit confused as to the order in which I apply the variables.

I usually apply a rounding to all rates. Used to be to the nearest 5 but that doesn't work out if the price doesn't change for 2 years, especially the hourly rates.

Pricing is not real, but will need to adapt the solution to real world prices.

[JohnTopley]

See the attached which uses VLOOKUP tables


=B2*VLOOKUP(B$2,$AD$2:$AE$6,2,1)

[fjas]

Thanks so much for that I'll have a play with it now. Just a question, what does the "1" in range lookup do?

[JohnTopley]

Use the "1" when the lookup table data is in ascending order e.g. number of days. then the largest value that is less than lookup_value is returned

[fjas]

Ok thanks!

What's the best practice for combining hours and days into the one column/table, but still see the hours as hours and days as days? I ask this because I'm thinking it would be helpful in addition to having the matrix, to create a quote calculator based on the data.

I'm thinking I would need:
"Room/s" (select dropdown from named column, OR checklist where multiple or single rooms can be selected)
"Duration" (enter number, maybe select hours OR days? I'm not sure what's best)

Also while I need the matrix available so that I can see review all prices when needed, is it possible to create a quote calculator so as to quickly access pricing. What's the best way develop the other room data + multiple room discount so that the pricing will be the same on both the matrix and calculator?

I've added data to Room 2 and changed the VLOOKUP column to pull data into the matrix which works well, but not sure if I should say =SUM(AG3*2)*0.7 (2 rooms with a discount) or is there a more efficent way?

[JohnTopley]

It depends how many "variables" you have:

-Different Rooms
-Rates per given room
-Occupancy time (hours/Days)
-Discount Rate (per room and/or per occupancy time) ?

It is likely you will need hourly and daily tables.

Get you business model defined then we can worry about how to organise the data.

See attached which has sample tables in Sheet2 and new formulae in Sheet1.

The formulae use named ranges associated with each table.

in B4

=B$2*INDEX(Hourly_Rates,MATCH(B2,Hours,1),MATCH($A4,H_Rooms,1))

in J4

=J$1*INDEX(Daily_Rates,MATCH(J$1,Days,1),MATCH($A4,Day_Rooms,1))

[fjas]

Hey thanks again, re business model we have had some unexpected growth from what began as an small exercise in shared space which has been valued, but only on a long term rental basis. Because the situation is quite unique, competitors are not quite all in the same business. There's also vast difference in the operation of each competitor, and also a lot of change, especially seasonal. The level of any business modelling so far has been limited by basic Excel formulas.

We went into this without much expectation about usage other than exhibitions, hence we only needed one rate (volume pricing for number of spaces and amount of time has always been in place); however, there is now definitely a need to charge for high impact events (100+) versus the events with only 2-6 people in the same space. I also need to streamline the pricing as the previous method of calculating the pricing was clunky.

I will be keeping to roughly the same pricing, just tweaking where needed so for now I'm after best practice in developing an easily manageable schedule with a quicker way of providing quotes rather than referring to the whole table.

I'm quite happy to pay if there's a service available, but I'm not quite sure what is achievable. For now we just need something that functions well. I am after a portable solution, maybe to an online format, that eventually could allow us or the client to quote via phone. Any advice is welcome.

[JohnTopley]

"Commercial Services" (tab at top of forum) offer a fee-paying service.

The real issue as far as I can judge is (to repeat myself) the lack of a clear specification/model of what is required.

You mention in your last reply about number of attendees at an event and possible charging according to this figure: another layer of "complexity".

Until we really understand all the parameters it is difficult to proceed further.

If you are (possibly) paying for a service, then a very clear specification will be required.

[fjas]

I've have never really had to develop a complex pricing model, so I'm uncertain as to how to construct a calculation flow that draws in variables which are defined below.

The most pressing at the moment is the multiple room discount, using the suggested method. Do I just populate the rates for each room in the reference area and change the column in the formula? What is the best way to apply the multiple room discount as described?

If I end up with just a matrix for now, that's ok, but this is the basis for a quote calculator.

Can you also explain why the change from vlookup to index? Thanks!

Base rate
Rate has thus far been roughly based on original yearly individual room valuations (aspect, light, accessibility, privacy and asesthetic) x area. Currently the individual room rate for 1&2 is the same, and room 3 (larger size) a higher rate. I don't need to calculate the starting base rate (based on expected return, taxes, expenses etc) for now. Base rates can be manually entered as per spreadsheet.

Multiple rooms
Currently just a basic sum (=sum((room*room)*hours)*discount), but I would like advice on the best way to apply a global discount when I am quoting for multiple rooms. Discount % starts at two and amount of discount increases if all spaces are taken.

Curve based pricing
What I was originally after was a formula, if this isn't possible, and price breaks are the only way to do it that's fine. I can adjust the spreadsheet to what I need.

• Takes the base rate for each room
• Applies a curve to the base rate to the sum of time x room(s) to the hourly & daily matrix
• Curve starts with the base rate and the base rate gets less as more time and space taken, rather than having to estimate each rate for a price break.

Annual increase
The only factor that will influence the base rate. Should I just apply the increase manually to each base rate or is there a better way?

Occupancy loading/surcharge
Percentage increase or just flat rate to be be applied to the daily rate where there is a day of high usage OR just daily flat rate fee). The loading is not such a big deal, but I just need the best approach. If there is a good way to add tiers as needed for occupancy, that would be helpful . I will generally be sticking with the base rate unless anything high use comes along.

Parking
Currently just a flat per day, per car rate. This is one I'm happy to leave, as it works well and is easy to communicate. Calculation is on top of the other calculations. Total hire inc parking can be a separate matrix or included in one big one.

Tenants Rates
All tenants currently have access to discounted rates, this is usually a separate table, and discount is applied over the top of the public rates

Rounding
All rates are currently rounded using MROUND, this will stay in place as I may need to round to nearest 5, 2 or 1 depending on the duration of hire