Stumped on creating quotation based on an inventory - multiple users

[peter dakin]

I freelance to a company that hires out equipment for large scale events. Each job will have thousands of individual items, making up one large package.

I created an excellent quotation excel sheet, which creates a "Pick-Sheet" for warehouse guys, as well as an auto generating quotation document which also calculates costs/discounts and travel etc.

The problem I face is multiple employees generating quotations and inventory.

For example on a busy Saturday, maybe 3-5 jobs are leaving the warehouse. Two people are creating quotations, the problems arises in that there is only a finite amount of equipment to hire out from the warehouse. At the moment the employees talk to each other and work out a balance of resources.

What I'm looking to do, is incorporate some form of inventory into my existing quotation spreadsheet.

So for example there are 100 pieces of Item ZZ.

Employee A quotes for 70 items of ZZ going out on 01/10/14.

Employee B answers phone and there's a new job on 01/10/14, opens up a new quotations spreadsheet. I then want Employee B to see there are only 30 ZZ's left for that date.

A work-around I can see is for employee's to save quotations by date and other employees will check to see if there is a pre-existing document for the date, before simply opening a new quotations, but this is then reliant upon employee editing a pre-existing document and errors could occur. The other problem is some jobs last days/weeks/months, so that "one document for each date" system would fall down.

Any suggestions welcomed.

 

[WarPigl3t]

The first thing i want to talk about is that you mentioned that errors could occur if you have employees enterine the date. This is correct. An employee could type in the wrong date. But you could create an error handler using conditional formatting.
The next thing I want to talk about is that a job can last longer than one day. So really there is no choice in the matter, you will need to do one of 2 choices.
1. You can have a date that says when the equipment will be returned. This is not a good idea because you will run into a problem if equipment is not returned on the day it was scheduled to return.
2. You can have employees enter that equipment was returned. You can do this in so many ways. I'm going to give you an example. You can use a drop down list that has 3 choices. The first choice is a blank, the second choice is "rented", the third choice is "returned". So when an employee rents out equpment, he selects rented from the drop down list. Then when the equipment is returned, they go back to the document and change it to returned. This would be the best way to do it because you don't know how long the equipment will be rented out for.