AnsweredAssumed Answered

Split down quotation/invoice to options at component level

Question asked by mootles22 on Sep 22, 2010
Latest reply on Sep 22, 2010 by philmodjunk


Split down quotation/invoice to options at component level



We are a small installation company working in the audio/video industry and we are currently trying out Filemaker Pro (and hopefully eventually Server). I will explain a little about the (overcomplicated!) system we use at the minute so hopefully you can see what we want to do with Filemaker.

When we carry out a quotation we produce a 'shopping list' of options for the client to pick what they want installed. We work out all the figures on a large spreadsheet which contains individual component codes for every part of an installation, the next part of the process is where we have a series of blocks (the options) about 25 rows deep into which we input component codes and quantities, which then produces subtotals for each option. There is a sumamry of all the options at the bottom (about 30 in total) and then grand total, VAT, etc. We then print out the summary and manually input all those figures into a word document which has a list of all the options, a notes section with information in, and a price for each option. This document is the 'official' bit with the customers address on which gets sent to them.

If the quotation is accepted then we produce a more detailed version called a specification which main difference is a more in depth ‘notes’ section and slightly different wording. Again if there are any changes we go back to the spreadsheet and do all that and then come back to word and change all the text and figures. At specification stage, we have the job so stock is confirmed as allocated to that job and any serial numbered stock is put aside and we write all the numbers on our office copy of the spec.

After installation we give it a few weeks and then invoice which is another copy of the spreadsheet (updated if necessary) and a different word document which this time contains the names of the options and figures only (so fairly simple layout). But it does contain all the serial numbers and model numbers of any big pieces of equipment.

Any labels for envelopes are copied over to a word or photoshop template manually. We buy in massive bulk to keep prices down and although loss of stock is not a problem at all, we find it very hard to keep track of where stock is purchased, where it is at our offices (ie. in stock, allocated, on dem, returned as faulty, etc), and ultimately where it has been sold to. It becomes a huge problem when something comes back as faulty because we have to send it back to the supplier with purchase invoice and sales invoice numebrs and dates. It's all time searchign through the paper that could easily be gotten rid of.

This is a very simplified version of what we do but I'm sure you can see just from this that there is lots of paper lying around and lots of little things that take time which could easily be forgotten about with a bit of automation.

I've only had Filemaker on trial for a couple of days but I've managed to setup a simple contact database with contacts automatically associated by organisation. I've also created a seperate section which shows the information of the organisation and their file number (each organisation has a unique file reference). I've also adapted the preset notes sections to give individual notes but also organisation notes, so search for anyone with associated with the same organisation and they all get the same notes about that place but also their individual notes. What I would like is when you create a new contact, you type in the organisation file reference and it fills in all the other information (name, location, type, address, etc) automatically - basically like a lookup formula in a spreadsheet. That's a minor thing but some advice would be appreciated.

I've also input some details of serial numbered stock along with relevant purchase dates and price etc. but not got much further with that yet.

So in summary, we need some way of creating a quote with several (sometimes 25+) options, each consisting of a title, notes/text, a breakdown of individual components and time and then a subtotal, which we can then print in full for us, but then print only the titles, notes and subtotals for the customer copy. We then need this information automatically transferred to a specification if we get the job. And then on to invoicing which would only take the option title and subtotal information. Throughout this process we also need all the stock to be status ‘allocated’ at quotation level for a period of 3 months. If the quotation is not accepted within 3 months we need it to go back as status ‘stock’, if a specification is produced it needs to stay as ‘allocated’ until invoicing at which point it will be ‘sold’. All that allocated stock’s relevant serial/model numbers would need to automatically appear on the invoice in the relevant option.

Another thing I forgot to mention earlier is our pricing setup. Just take for example a piece of stock that costs us £130. We might sell that stock out at a "false trade" of £145 which then goes through our markup and comes out at £220 say. So if our "true trade" is £130 for the 5 in stock and we suddenly run out and the price goes up to £150, we have made a certain amount of provision for the stock we are buying in at the new inflated price. It also gives us scope to lower our "false trade" down to "true trade" and still make a decent profit, should we want to lower our prices a bit in order to either get a job. It's a bit of an odd concept I think (I didn't come up with it) but that's how it has to be done so there does need to be provision in the system for the "false trade" and markup = retail on all quotes, invoicing, etc. But then on profit/loss sheets (which I hope to create through filemaker as well?) it needs to obviously use the "true trade" comapred with the retail. We also do work with varying VAT rates (although everyone has done the past couple of years!) so this needs to be easily changeable as well so we can zero rate and also go up to 20% next year.

Like I said before, we are a small business so we do not employ a specific computer engineer or anything. I do most of the computer side of things but as we are expanded I'm needing more and more help. I am pretty good with spreadsheets and I did use Access a fair bit at one point but that was about 5 years ago so I'm very rusty and also Filemaker, althoguh similiar in concept is obviously very different to use so any help would be appreciated. There's no doubt more stuff you need to know, and stuff I've forgotten but I will do my best to add to this as and when.

I guess the first question is: Is the above possible? and if so, can anyone help me on my way?

Many thanks to all who can help!

Sam K

PS. Stupid question but how I change from dollars to pounds?! Stupid thing keeps spitting about numbers in dollars!