Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    hire shop example (office2003)

    Good afternoon,

    I wonder if anybody has an exmple that they could let me see of a workbook that calculates hire periods. I intend to hire out skips on a franchise basis and am trying to set up a workbook whereby I had a hire start date, a hire finish date, skip sizes and prices per day (i.e. 2 yard =

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: hire shop example (office2003)

    Although not exactly geared towards your question, you might browse Microsoft Office Templates Home Page to see if there is something you can use.

    Otherwise, if you have specific questions, feel free to ask them here.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: hire shop example (office2003)

    See attached.

    The workbook contains two sheets - a rental record and an equipment list including daily and weekly rental rates.

    <UL><LI> On the HirePeriods tab, you enter a customer name in the next available cell.
    <LI> Select a cell in the Equipment column; it will present a dropdown populated with all the equipment IDs available. This functionality comes by having the column set to data validation where the allowed entries are taken from a list; in this case, the list is a defined name called "List" (who said accountants aren't imaginative?). More about defined names below
    <LI> Enter the date the equipment is hired out
    <LI> Enter the date the equipment is returned - the "Return" column is also protected by data validation - it will not allow a date earlier than the hired out date (note - it you input the return date before you input the hired out date you can get around that protection - caveat emptor
    <LI> The period of hire is calculated in column F as the difference between the two dates, with a minimum of one day. You will have to decide for yourself how you want to handle a rental taken out on Monday morning and returned on Tuesday afternoon, but for the moment it is counted as one day.
    <LI> The rental is calculated in columns G through K - these can be deleted since they are really there for illustration. The first 2 columns calculate the number of whole weeks and days the rental has been outstanding - these should be fairly self-explanatory
    <LI> Column I calculates the charge for the full weeks rented by multiplying the number of weeks (that part is easy <img src=/S/smile.gif border=0 alt=smile width=15 height=15>) by the weekly rate. The weekly rate comes from a "lookup" - we look for the value found in column <font color=blue>B</font color=blue>, in a named range <font color=blue>"Pool"</font color=blue>, in the <font color=blue>4</font color=blue>th column of the named range, and by setting the Range Lookup value to <font color=blue>False</font color=blue> we tell excel that the table is not in order, and it must find an exact match (that wasn't so bad, was it?). We perform a similar calculation to determine the charge for the days after a full week, and then add the two values together in column K
    <LI> In column L we have an alternate calculation for the total charge. This uses the same formulas as column I and J, except:
    >> it uses the number of days and calculates weeks and stub-days directly
    >> more importantly, it tests whether a longer full-week rental is cheaper than the actual weeks/days rental - ie, if a customer has equipment for 26 days a 4 week rental might be cheaper than a 3 week, 5 day rental - and it charges the lower amount. It is up to you whether you want to allow that or not
    <LI> Most columns are set up to display a blank if the value they would otherwise display would be an error - you can see where that is done by inspection[/list]More about Named Ranges
    The RentalPool tab contains all the data about equipment available - it is reasonable to expect that the list will grow over time. At the same time, it is very useful to be able to name the range in the worksheet that contains this sort of data - as shown, we use the equipment IDs to validate input data and the full range of the equipment list to return equipment descriptions and hire rates. The best way to handle names for ranges that will grow (or shrink) over time is as a "Dynamic Range."
    A 'regular' named range refers to a static area on a worksheet - for instance RentalPool!A3:C12. That's nice, if I know that I will always need that range for whatever I am doing. A Dynamic Range refers to an area defined by the Excel "OFFSET" function instead. The OFFSET function returns a range, given a starting point, the number of rows and columns away from the staring point to start the range and the height and width of the range to return.
    In this case, the Pool range name is defined as:
    =OFFSET(RentalPool!$A$4,0,0,COUNTA(RentalPool!$A:$ A)-3,4)
    this returns a range starting 0 rows and 0 columns from RentalPool!A4 (ie - the cell A4 on RentalPool), and extending down XX rows and across 4 columns. So what is this mysterious "XX rows?" It is the number of non-blank cells in column A of RentalPool, minus three. Every time you enter a new equipment ID in column A, this value increases by 1 (and decreases when you delete an equipment ID) and makes the named range one row bigger. The 3 rows subtracted correspond to the three rows (1 through 3) at the top of the sheet that contain headings.
    NB - don't put anything on the RentalPool sheet other than equipment information in the format shown, and don't leave any blank lines - it will screw things up. Quite badly.

    A couple of general comments, Danny:
    > Your enquiry was very broad - I responded because it was a chance to try a couple of things that I know how to do, but don't do commonly. In essence, it was good practice for me; many others will not bother, just because such broad enquiries (generally - not always) indicate someone who has not put much effort into finding a solution themselves.
    > There is enough in the response and the attached file that you can follow along and learn how to do this sort of stuff. There is not enough that you should start building a business around this.
    > If you want something you can build a business around, be prepared to pay for it - there are people here who make their living by selling their ability to do this stuff. I am not soliciting, and I am not trying to dissuade you from coming here and asking questions - but if you want an industrial-strength response you need someone who is seriously devoting their time to it, not doing you a favour.
    > If you seriously want a front end for a rental shop, I would suggest Access (or other relational database) - it will be much more robust. Some particular problems with this approach will be that if you change your price list it will apply the current prices to all past rentals - this can be disconcerting if you are talking to Inland Revenue - and if you delete a piece of equipment it will screw up any existing rentals of that equipment.
    > This file (with appropriate modifications) will work as a record of rentals and to calculate rental charges if you intend to then manually transfer information into an accounting system if you are careful. For any more critical use like a direct front-end, for data storage, or as a billing system, I reiterate the recommendation that you investigate a true database.

  4. #4
    Star Lounger
    Join Date
    May 2005
    Location
    west drayton, Middlesex, United Kingdom
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hire shop example (office2003)

    Thank you so much for such a detailed response and my apologies for not saying so sooner, I have been on holiday for a fortnight and I could not view the workbook on my sons Mac machine. I must admit that I do not fully understand how everything is working at the moment but as I work through it I may have some questions if that is OK

    Thanks again

    Danny

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: hire shop example (office2003)

    Hi Danny - our timing is not very good, I'm afraid; I will be off on vacation myself and will not have computer access. They tell me there is this thing called "outside" - but I don't believe them; if it was all it's cracked up to be it'd have its own URL and I could click straight through to it.

    In any event, have a look at the file provided and feel free to post any questions, but it will be a while before I can respond, although others might.

Posting Permissions

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